Deze tutorial leert je om functies met en zonder parameters in VBA te maken en te gebruiken
VBA bevat een groot aantal ingebouwde functies die u kunt gebruiken, maar u kunt ook uw eigen functies schrijven. Wanneer u code in VBA schrijft, kunt u deze in een subprocedure of een functieprocedure schrijven. Een functieprocedure kan een waarde teruggeven aan uw code. Dit is uiterst handig als u wilt dat VBA een taak uitvoert om een resultaat te retourneren. VBA-functies kunnen ook vanuit Excel worden aangeroepen, net als de ingebouwde Excel-functies van Excel.
Een functie maken zonder argumenten
Om een functie aan te maken, moet je de functie definiëren door de functie een naam te geven. De functie kan dan worden gedefinieerd als een gegevenstype dat aangeeft welk type gegevens u wilt dat de functie retourneert.
Misschien wilt u een functie maken die elke keer dat deze wordt aangeroepen een statische waarde retourneert - een beetje zoals een constante.
| 123 | Functie GetValue() Als geheel getalGetValue = 50Functie beëindigen |
Als u de functie zou uitvoeren, zou de functie altijd de waarde 50 retourneren.
U kunt ook functies maken die verwijzen naar objecten in VBA, maar u moet het Trefwoord instellen gebruiken om de waarde van de functie te retourneren.
| 123 | Functie GetRange() als bereikStel GetRange = Bereik ("A1:G4") inFunctie beëindigen |
Als u de bovenstaande functie in uw VBA-code zou gebruiken, zou de functie altijd het celbereik A1 tot G4 retourneren in welk blad u ook werkt.
Een functie aanroepen vanuit een subprocedure
Nadat u een functie hebt gemaakt, kunt u deze vanuit elke andere plaats in uw code aanroepen door een subprocedure te gebruiken om de functie aan te roepen.
De waarde van 50 zou altijd worden geretourneerd.
U kunt de GetRange-functie ook aanroepen vanuit een subprocedure.
In het bovenstaande voorbeeld wordt de GetRange-functie aangeroepen door de subprocedure om de cellen in het bereikobject vet te maken.
Functies maken
Eén argument
U kunt ook een parameter of parameters aan uw functie toewijzen. Deze parameters kunnen worden aangeduid als argumenten.
| 123 | Functie ConvertKilosToPounds (dblKilo als Double) als DoubleConvertKiloToPounds = dblKilo*2.2Functie beëindigen |
Bovenstaande functie kunnen we dan aanroepen vanuit een Sub Procedure om uit te rekenen hoeveel ponden een bepaald aantal kilo's zijn.
Een functie kan indien nodig vanuit meerdere procedures binnen uw VBA-code worden aangeroepen. Dit is erg handig omdat het voorkomt dat u dezelfde code steeds opnieuw moet schrijven. Het stelt u ook in staat om lange procedures op te delen in kleine beheersbare functies.
In het bovenstaande voorbeeld hebben we 2 procedures - elk van hen gebruikt de functie om de pondwaarde te berekenen van de kilo's die aan hen zijn doorgegeven in de dblKilo Argument van de functie.
Meerdere argumenten
U kunt een functie maken met meerdere argumenten en de waarden doorgeven aan de functie door middel van een subprocedure.
| 123 | Functie BerekenDagDiff(Datum1 als Datum, Datum2 als Datum) als DubbelBerekenDagDiff = Datum2-Datum1Functie beëindigen |
We kunnen dan de functie aanroepen om het aantal dagen tussen 2 datums te berekenen.
Optionele argumenten
U kunt ook optionele argumenten doorgeven aan een functie. Met andere woorden, soms heb je het argument nodig en soms niet - afhankelijk van de code waarmee je de functie gebruikt.
| 123456 | Functie BerekenDagDiff(Datum1 als Datum, Optionele Datum2 als Datum) als Dubbel'controleer op tweede datum en zo niet, maak Date2 gelijk aan de datum van vandaag.Als Datum2=0 dan Datum2 = Datum'verschil berekenen'BerekenDagDiff = Datum2-Datum1Functie beëindigen |
Standaard argumentwaarde
U kunt ook de standaardwaarde van de optionele argumenten instellen wanneer u de functie maakt, zodat als de gebruiker het argument weglaat, in plaats daarvan de waarde wordt gebruikt die u als standaard hebt opgegeven.
| 1234 | Functie BerekenDagDiff(Datum1 als Datum, Optionele Datum2 als Datum="02/06/2020") als Dubbel'verschil berekenen'BerekenDagDiff = Datum2-Datum1Functie beëindigen |
ByVal en ByRef
Wanneer u waarden doorgeeft aan een functie, kunt u de DoorVal of DoorRef trefwoorden. Als u een van deze twee weglaat, wordt de DoorRef wordt standaard gebruikt.
DoorVal betekent dat u een kopie van de variabele doorgeeft aan de functie, terwijl DoorRef betekent dat u verwijst naar de oorspronkelijke waarde van de variabele. Wanneer u een kopie van de variabele (DoorVal), de oorspronkelijke waarde van de variabele is NIET gewijzigd, maar wanneer u naar de variabele verwijst, wordt de oorspronkelijke waarde van de variabele gewijzigd door de functie.
| 1234 | Functie GetValue(ByRef intA As Integer) As IntegerintA = intA * 4GetValue = intAFunctie beëindigen |
In de bovenstaande functie zou de ByRef kunnen worden weggelaten en zou de functie op dezelfde manier werken.
| 1234 | Functie GetValue(intA As Integer) As IntegerintA = intA * 4GetValue = intAFunctie beëindigen |
Om deze functie aan te roepen, kunnen we een subprocedure uitvoeren.
| 123456789 | Subtestwaarden()Dim intVal als geheel getal'vul de variabele in met de waarde 10'intVal = 10'voer de GetValue-functie uit en toon de waarde in het directe venster'Debug.Print GetValue(intVal)'toon de waarde van de variabele intVal in het directe venster'Debug.Print intValEinde sub |
Merk op dat de foutopsporingsvensters beide keren de waarde 40 tonen. Wanneer u de variabele IntVal doorgeeft aan de functie - wordt de waarde van 10 doorgegeven aan de functie en vermenigvuldigd met 4. Door het ByRef-sleutelwoord te gebruiken (of het helemaal weg te laten), wordt de waarde van de IntVal-variabele AMEND. Dit wordt getoond wanneer u eerst het resultaat van de functie in het directe venster (40) laat zien, en vervolgens de waarde van de IntVal-variabele in het debugvenster (ook 40).
Als we de waarde van de originele variabele NIET willen veranderen, moeten we ByVal in de functie gebruiken.
| 1234 | Functie GetValue(ByVal intA As Integer) As IntegerintA = intA * 4GetValue = intAFunctie beëindigen |
Als we nu de functie aanroepen vanuit een subprocedure, blijft de waarde van de variabele IntVal op 10.
Functie verlaten
Als u een functie maakt die op een bepaalde voorwaarde test, en als de voorwaarde eenmaal waar is bevonden en u de waarde van de functie wilt retourneren, moet u mogelijk een Exit Function-instructie toevoegen aan uw functie om de functie te verlaten voordat je hebt alle code in die functie doorlopen.
| 12345678910111213 | Functie FindNumber(strSearch As String) As IntegerDim ik als geheel getal'loop door elke letter in de string'Voor i = 1 Naar Len (strZoeken)'als de letter numeriek is, retourneer je de waarde naar de functie'If IsNumeric(Mid(strSearch, i, 1)) DanFindNumber= Mid(strSearch, i, 1)'verlaat dan de functie'Functie verlatenStop alsVolgendeFindNumber= 0Functie beëindigen |
De bovenstaande functie doorloopt de opgegeven tekenreeks totdat deze een getal vindt en retourneert vervolgens dat nummer uit de tekenreeks. Het zal alleen het eerste nummer in de string vinden, zoals het dan zal doen Uitgang de functie.
De bovenstaande functie kan worden aangeroepen door een Sub-routine zoals die hieronder.
| 1234567 | Sub CheckForNumber()Dim NumIs als geheel getal'geef een tekstreeks door aan de zoeknummerfunctie'NumIs = FindNumber ("Bovenste verdieping, 8 Oak Lane, Texas")'toon het resultaat in het directe venster'Debug.Print NumIsEinde sub |
Een functie gebruiken vanuit een Excel-blad
Naast het aanroepen van een functie vanuit uw VBA-code met behulp van een subprocedure, kunt u de functie ook vanuit uw Excel-blad aanroepen. De functies die u hebt gemaakt, moeten standaard in uw functielijst verschijnen in het gedeelte Door de gebruiker gedefinieerd van de functielijst.
Klik op de fx om het dialoogvenster Functie invoegen weer te geven.
Selecteer Gebruiker gedefinieerde uit de categorielijst
Selecteer de gewenste functie uit de beschikbare Door de gebruiker gedefinieerde functies (UDF's).
Als alternatief, wanneer u begint met het schrijven van uw functie in Excel, zou de functie in de vervolgkeuzelijst met functies moeten verschijnen.
Als u niet wilt dat de functie beschikbaar is in een Excel-blad, moet u het privéwoord vóór het woord Functie plaatsen wanneer u de functie in uw VBA-code maakt.
| 123 | Privéfunctie BerekenDagDiff (Datum1 als Datum, Datum2 als Datum) als DubbelBerekenDagDiff = Datum2-Datum1Functie beëindigen |
Het verschijnt nu niet in de vervolgkeuzelijst met de beschikbare Excel-functies.
Interessant genoeg kunt u de functie echter nog steeds gebruiken - deze verschijnt alleen niet in de lijst wanneer u ernaar zoekt!
Als je het tweede argument hebt gedeclareerd als Optioneel, kunt u het zowel in het Excel-blad als in de VBA-code weglaten.
U kunt ook de a-functie gebruiken die u zonder argumenten in uw Excel-blad hebt gemaakt.
