Excel VBA-Funktionen
Wir haben gesehen, dass wir die Arbeitsblattfunktionen in VBA verwenden können, dh die Funktionen des Excel-Arbeitsblatts in der VBA-Codierung unter Verwendung der application.worksheet-Methode. Wie verwenden wir jedoch eine Funktion von VBA in Excel? Solche Funktionen werden als benutzerdefinierte Funktionen bezeichnet. Wenn ein Benutzer eine Funktion in VBA erstellt, kann diese auch im Excel-Arbeitsblatt verwendet werden.
Obwohl wir in Excel viele Funktionen zum Bearbeiten der Daten haben, müssen die Tools manchmal angepasst werden, damit wir Zeit sparen können, wenn wir einige Aufgaben wiederholt ausführen. Wir haben in Excel vordefinierte Funktionen wie SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH in Excel usw., aber wir erledigen täglich einige Aufgaben, für die ein einzelner Befehl oder eine einzelne Funktion in Excel nicht verfügbar ist, und verwenden dann VBA können wir die benutzerdefinierte Funktion erstellen, die als User Defined Functions (UDF) bezeichnet wird.
Was machen VBA-Funktionen?
- Sie führen bestimmte Berechnungen durch; und
- Geben Sie einen Wert zurück
In VBA verwenden wir beim Definieren der Funktion die folgende Syntax, um die Parameter und ihren Datentyp anzugeben.
Der Datentyp ist hier der Datentyp, den die Variable enthält. Es kann einen beliebigen Wert enthalten (einen beliebigen Datentyp oder ein beliebiges Objekt einer Klasse).
Wir können das Objekt mit seiner Eigenschaft oder Methode verbinden, indem wir das Punkt- oder Punktsymbol (.) Verwenden.
Wie erstelle ich benutzerdefinierte Funktionen mit VBA?
Beispiel
Angenommen, wir haben die folgenden Daten von einer Schule, an der wir die Gesamtpunktzahl des Schülers, das Ergebnis und die Note ermitteln müssen.
Um die von einem einzelnen Schüler in allen Fächern erzielten Noten zusammenzufassen, verfügen wir über eine integrierte Funktion, dh SUMME. Die Ermittlung der Note und des Ergebnisses anhand der von der Schule festgelegten Kriterien ist jedoch standardmäßig nicht in Excel verfügbar .
Dies ist der Grund, warum wir benutzerdefinierte Funktionen erstellen müssen.
Schritt 1: Gesamtpunktzahl ermitteln
Zuerst finden wir die Gesamtpunktzahl mit der SUMME-Funktion in Excel.
Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Ziehen Sie die Formel auf die restlichen Zellen.
Um nun das Ergebnis herauszufinden (bestanden, nicht bestanden oder wesentliche Wiederholung), werden von der Schule folgende Kriterien festgelegt.
- Wenn der Schüler mehr als oder gleich 200 als Gesamtnote von 500 Punkten erzielt hat und der Schüler auch in keinem Fach versagt hat (in jedem Fach mehr als 32 Punkte erzielt hat), wird ein Schüler bestanden.
- Wenn der Schüler mehr als oder gleich 200 Punkte erzielt hat, der Schüler jedoch in 1 oder 2 Fächern gescheitert ist, hat ein Schüler in diesen Fächern die „wesentliche Wiederholung“.
- Wenn der Schüler weniger als 200 Punkte erzielt hat oder in 3 oder mehr Fächern versagt, ist der Schüler gescheitert.
Schritt 2: Erstellen Sie die ResultOfStudent-Funktion
Um eine Funktion mit dem Namen "ResultOfStudent" zu erstellen, müssen Sie den "Visual Basic-Editor" mit einer der folgenden Methoden öffnen:
- Mit der Registerkarte Entwickler Excel.
Wenn die Registerkarte Entwickler in MS Excel nicht verfügbar ist, können Sie dies mithilfe der folgenden Schritte abrufen:
- Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in der Multifunktionsleiste und wählen Sie dann die Option Multifunktionsleiste in Excel anpassen .
Wenn Sie diesen Befehl auswählen, wird das Dialogfeld "Excel-Optionen" geöffnet.
- Wir müssen das Kontrollkästchen für "Entwickler" aktivieren , um die Registerkarte zu erhalten.
- Mit der Tastenkombination Alt + F11.
- Wenn wir den VBA-Editor öffnen, müssen wir das Modul einfügen, indem wir im Menü Einfügen ein Modul auswählen.
- Wir müssen den folgenden Code in das Modul einfügen.
Funktion ResultOfStudents (Marks As Range) As String Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer Für jede mycell In Marks Total = Total + mycell.Value Wenn mycell.Value = 200 und CountOfFailedSubject 0, dann ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 und CountOfFailedSubject = 0 Dann ResultOfStudents = "Bestanden" Sonst ResultOfStudents = "Fehlgeschlagen" End If End-Funktion
Die obige Funktion gibt das Ergebnis für einen Schüler zurück.
Wir müssen verstehen, wie dieser Code funktioniert.
Die erste Anweisung, 'Function ResultOfStudents (Marks As Range) As String', deklariert eine Funktion mit dem Namen 'ResultOfStudents' , die einen Bereich als Eingabe für Marks akzeptiert und das Ergebnis als String zurückgibt .
Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer
These three statements declare variables, i.e.,
- ‘myCell’ as a Range,
- ‘Total’ as Integer (to store total marks scored by a student),
- ‘CountOfFailedSubject’ as integer (to store the number of subjects in which a student has failed).
For Each mycell In Marks Total = Total + mycell.Value If mycell.Value < 33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell
This code checks for every cell in the ‘Marks’ range and adds the value of every cell in the ‘Total’ variable, and if the value of the cell is less than 33, then adds 1 to the ‘CountOfFailedSubject’ variable.
If Total>= 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total>= 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If
This code checks the value of ‘Total’ and ‘CountOfFailedSubject’ and passes the ‘Essential Report,’ ‘Passed,’ or ‘Failed’ accordingly to the ‘ResultOfStudents.’
Step 3: Apply ResultOfStudents Function to Get Result
ResultOfStudents function takes marks, i.e., selection of 5 marks scored by the student.
Now Select the Range of cells, i.e., B2: F2.
Drag the Formula to the rest of the Cells.
Step 4: Create ‘GradeForStudent’ Function to get Grades
Now to find out the grade for the student, we will create one more function named ‘GradeForStudent.’
The code would be:
Function GradeForStudent(TotalMarks As Integer, Result As String) As String If TotalMarks> 440 And TotalMarks 380 And TotalMarks 320 And TotalMarks 260 And TotalMarks = 200 And TotalMarks <= 260 And (Result = "Passed" Or Result = "Essential Repeat") Then GradeForStudent = "E" ElseIf TotalMarks < 200 Or Result = "Failed" Then GradeForStudent = "F" End If End Function
This function assigns a ‘Grade’ to the student based on the ‘Total Marks’ and ‘Result.’
We just need to write the formula and open the brackets in Cell H2 and pressing Ctrl+Shift+A to find out about the arguments.
Die Funktion GradeForStudent verwendet die Gesamtpunktzahl (Summe der Noten) und das Ergebnis des Schülers als Argument für die Berechnung der Note.
Wählen Sie nun die entsprechenden Zellen aus, dh G2, H2.
Jetzt müssen wir nur noch Strg + D drücken, nachdem wir die Zellen ausgewählt haben, um die Formeln zu kopieren.
Wir können die Werte von weniger als 33 mit der roten Hintergrundfarbe hervorheben, damit wir herausfinden, in welchen Fächern der Schüler versagt hat.
