VBA-functies - Gids voor het maken van een aangepaste functie met VBA

Inhoudsopgave

Excel VBA-functies

Excel VBA-functies

We hebben gezien dat we de werkbladfuncties in VBA kunnen gebruiken, dat wil zeggen de functies van Excel-werkblad in VBA-codering met behulp van de application.worksheet-methode, maar hoe gebruiken we een functie van VBA in Excel, nou, dergelijke functies worden door de gebruiker gedefinieerde functies genoemd, wanneer een gebruiker een functie in VBA maakt, kan deze ook in een Excel-werkblad worden gebruikt.

Hoewel we veel functies in Excel hebben om de gegevens te manipuleren, moeten we soms wat aanpassingen in de tools hebben, zodat we onze tijd kunnen besparen omdat we sommige taken herhaaldelijk uitvoeren. We hebben voorgedefinieerde functies in Excel zoals SUM, AANTAL.ALS, SUMIF, AANTAL.ALS, VERT.ZOEKEN, INDEX, VERGELIJKEN in Excel, enz. Maar we voeren dagelijks een aantal taken uit waarvoor een enkele opdracht of functie niet beschikbaar is in Excel, en vervolgens door te gebruiken VBA kunnen we de aangepaste functie maken die User Defined Functions (UDF) wordt genoemd.

Wat doen VBA-functies?

Ze voeren bepaalde berekeningen uit; en
Retourneer een waarde

In VBA gebruiken we bij het definiëren van de functie de volgende syntaxis om de parameters en hun gegevenstype op te geven.

Het gegevenstype is hier het type gegevens dat de variabele zal bevatten. Het kan elke waarde bevatten (elk gegevenstype of object van elke klasse).

We kunnen het object met zijn eigenschap of methode verbinden door de punt of punt (.) Symbool te gebruiken.

Hoe maak je aangepaste functies met VBA?

Voorbeeld

Stel dat we de volgende gegevens hebben van een school waar we het totaal aantal punten moeten vinden dat door de leerling, het resultaat en het cijfer is gescoord.

Om de cijfers van een individuele student in alle vakken samen te vatten, hebben we een ingebouwde functie, dwz SUM, maar om het cijfer en het resultaat te achterhalen op basis van de criteria die door de school zijn vastgesteld, is dit standaard niet beschikbaar in Excel .

Dit is de reden waarom we door de gebruiker gedefinieerde functies moeten maken.

Stap 1: Vind totaal aantal punten

Eerst zullen we het totale aantal cijfers vinden met behulp van de SOM-functie in Excel.

Druk op Enter om het resultaat te krijgen.

Sleep de formule naar de rest van de cellen.

Om het resultaat (geslaagd, mislukt of essentiële herhaling) te achterhalen, is dat de door de school vastgestelde criteria.

Als de student meer dan of gelijk is aan 200 als totaal aantal punten op 500 en de student is ook niet gezakt in een vak (heeft meer dan 32 gescoord in elk vak), dan is een student geslaagd,
Als de student meer dan of gelijk aan 200 heeft gescoord, maar de student is niet geslaagd voor 1 of 2 vakken, dan heeft een student "Essential Repeat" voor die vakken,
Als de student minder dan 200 heeft gescoord of niet is geslaagd in 3 of meer vakken, is de student niet geslaagd.

Stap 2: Maak een ResultOfStudent-functie

Om een functie met de naam 'ResultOfStudent' te maken, moeten we "Visual Basic Editor" openen door een van de onderstaande methoden te gebruiken:

Door het Excel-tabblad Ontwikkelaar te gebruiken.

Als het tabblad Ontwikkelaar niet beschikbaar is in MS Excel, kunnen we dat krijgen door de volgende stappen te volgen:

Klik met de rechtermuisknop ergens op het lint en kies vervolgens het lint aanpassen in Excel ' .

Wanneer we deze opdracht kiezen, wordt het dialoogvenster "Excel-opties" geopend.

We moeten het vakje voor "Ontwikkelaar" aanvinken om het tabblad te krijgen.

Door de sneltoets te gebruiken, dwz Alt + F11.

Wanneer we de VBA-editor openen, moeten we de module invoegen door naar het menu Invoegen te gaan en een module te kiezen.

We moeten de volgende code in de module plakken.

Functie ResultOfStudents (Marks As Range) As String Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer For Each mycell In Marks Total = Total + mycell.Value If mycell.Value = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 en CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Mislukt" End If End-functie

De bovenstaande functie retourneert het resultaat voor een student.

We moeten begrijpen hoe deze code werkt.

De eerste instructie, 'Function ResultOfStudents (Marks As Range) As String', declareert een functie met de naam 'ResultOfStudents' die een bereik accepteert als invoer voor markeringen en het resultaat als een string retourneert.

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.’