Een door de gebruiker gedefinieerde functie (UDF) maken in Excel VBA (Ultimate Guide)

Met VBA kunt u een aangepaste functie maken (ook wel een door de gebruiker gedefinieerde functie genoemd) die net als gewone functies in de werkbladen kan worden gebruikt.

Deze zijn handig wanneer de bestaande Excel-functies niet voldoende zijn. In dergelijke gevallen kunt u uw eigen aangepaste door de gebruiker gedefinieerde functie (UDF) maken om aan uw specifieke behoeften te voldoen.

In deze zelfstudie behandel ik alles over het maken en gebruiken van aangepaste functies in VBA.

Als je geïnteresseerd bent om VBA op een gemakkelijke manier te leren, bekijk dan mijn Online Excel VBA-training.

Wat is een functieprocedure in VBA?

Een functieprocedure is een VBA-code die berekeningen uitvoert en een waarde (of een reeks waarden) retourneert.

Met behulp van een functieprocedure kunt u een functie maken die u in het werkblad kunt gebruiken (net als elke normale Excel-functie zoals SOM of VERT.ZOEKEN).

Wanneer u een functieprocedure hebt gemaakt met VBA, kunt u deze op drie manieren gebruiken:

1. Als een formule in het werkblad, waar het argumenten als invoer kan gebruiken en een waarde of een reeks waarden retourneert.
2. Als onderdeel van uw VBA-subroutinecode of een andere functiecode.
3. In voorwaardelijke opmaak.

Hoewel er al 450+ ingebouwde Excel-functies beschikbaar zijn in het werkblad, hebt u mogelijk een aangepaste functie nodig als:

• De ingebouwde functies kunnen niet doen wat u gedaan wilt krijgen. In dit geval kunt u een aangepaste functie maken op basis van uw vereisten.
• De ingebouwde functies kunnen het werk gedaan krijgen, maar de formule is lang en ingewikkeld. In dit geval kunt u een aangepaste functie maken die gemakkelijk te lezen en te gebruiken is.

Merk op dat aangepaste functies die met VBA zijn gemaakt, aanzienlijk langzamer kunnen zijn dan de ingebouwde functies. Daarom zijn deze het meest geschikt voor situaties waarin u het resultaat niet kunt krijgen met behulp van de ingebouwde functies.

Functie versus Subroutine in VBA

Met een 'Subroutine' kunt u een reeks code uitvoeren, terwijl een 'Functie' een waarde (of een reeks waarden) retourneert.

Om je een voorbeeld te geven, als je een lijst met getallen hebt (zowel positieve als negatieve), en je wilt de negatieve getallen identificeren, dan kun je het volgende doen met een functie en een subroutine.

Een subroutine kan door elke cel in het bereik lopen en alle cellen markeren die een negatieve waarde hebben. In dit geval verandert de subroutine uiteindelijk de eigenschappen van het bereikobject (door de kleur van de cellen te veranderen).

Met een aangepaste functie kunt u deze in een aparte kolom gebruiken en een TRUE retourneren als de waarde in een cel negatief is en ONWAAR als deze positief is. Met een functie kun je de eigenschappen van het object niet wijzigen. Dit betekent dat u de kleur van de cel met een functie zelf niet kunt wijzigen (u kunt dit echter doen met behulp van voorwaardelijke opmaak met de aangepaste functie).

Wanneer u een door de gebruiker gedefinieerde functie (UDF) maakt met VBA, kunt u die functie in het werkblad net als elke andere functie gebruiken. Ik zal hier meer over vertellen in het gedeelte 'Verschillende manieren om een ​​door de gebruiker gedefinieerde functie in Excel te gebruiken'.

Een eenvoudige door de gebruiker gedefinieerde functie maken in VBA

Laat me een eenvoudige, door de gebruiker gedefinieerde functie in VBA maken en je laten zien hoe het werkt.

De onderstaande code maakt een functie die de numerieke delen uit een alfanumerieke reeks haalt.

Functie GetNumeric(CellRef As String) as Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Resultaat & Mid(CellRef, i, 1) Volgende i GetNumeric = Resultaat Eindfunctie

Wanneer je bovenstaande code in de module hebt staan, kun je deze functie gebruiken in de werkmap.

Hieronder is hoe deze functie - GetNumeriek - kan worden gebruikt in Excel.

Voordat ik je vertel hoe deze functie in VBA wordt gemaakt en hoe het werkt, zijn er een paar dingen die je moet weten:

• Wanneer u een functie in VBA maakt, wordt deze beschikbaar in de hele werkmap, net als elke andere reguliere functie.
• Wanneer u de functienaam typt gevolgd door het gelijkteken aan teken, toont Excel de naam van de functie in de lijst met overeenkomende functies. In het bovenstaande voorbeeld, toen ik =Get invoerde, liet Excel me een lijst zien met mijn aangepaste functie.

Ik geloof dat dit een goed voorbeeld is wanneer je VBA kunt gebruiken om een ​​eenvoudig te gebruiken functie in Excel te maken. Je kunt hetzelfde doen met een formule (zoals getoond in deze tutorial), maar dat wordt ingewikkeld en moeilijk te begrijpen. Met deze UDF hoeft u maar één argument door te geven en krijgt u het resultaat.

Anatomie van een door de gebruiker gedefinieerde functie in VBA

In het bovenstaande gedeelte heb ik je de code gegeven en je laten zien hoe de UDF-functie werkt in een werkblad.

Laten we nu diep duiken en kijken hoe deze functie is gemaakt. U dient onderstaande code in een module in de VB Editor te plaatsen. Ik behandel dit onderwerp in de sectie - 'Waar plaats je de VBA-code voor een door de gebruiker gedefinieerde functie'.

Functie GetNumeric(CellRef As String) as Long ' Deze functie extraheert het numerieke deel van de string Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Resultaat & Mid(CellRef, i, 1) Volgende i GetNumeric = Resultaat Eindfunctie

De eerste regel van de code begint met het woord - Functie.

Dit woord vertelt VBA dat onze code een functie is (en geen subroutine). Het woord Functie wordt gevolgd door de naam van de functie - GetNumeric. Dit is de naam die we in het werkblad zullen gebruiken om deze functie te gebruiken.

• De naam van de functie mag geen spaties bevatten. U kunt een functie ook geen naam geven als deze botst met de naam van een celverwijzing. U kunt de functie bijvoorbeeld ABC123 niet een naam geven, omdat deze ook verwijst naar een cel in het Excel-werkblad.
• Je moet je functie niet dezelfde naam geven als die van een bestaande functie. Als u dit doet, geeft Excel de voorkeur aan de ingebouwde functie.
• U kunt een onderstrepingsteken gebruiken als u woorden wilt scheiden. Get_Numeric is bijvoorbeeld een acceptabele naam.

De functienaam wordt gevolgd door enkele argumenten tussen haakjes. Dit zijn de argumenten die onze functie van de gebruiker nodig heeft. Dit zijn net de argumenten die we moeten leveren aan de ingebouwde functies van Excel. In een AANTAL.ALS-functie zijn er bijvoorbeeld twee argumenten (bereik en criteria)

Binnen de haakjes moet u de argumenten specificeren.

In ons voorbeeld is er maar één argument: CellRef.

Het is ook een goede gewoonte om aan te geven wat voor soort argument de functie verwacht. Omdat we in dit voorbeeld de functie een celverwijzing zullen geven, kunnen we het argument specificeren als een 'Bereik'-type. Als u geen gegevenstype opgeeft, beschouwt VBA het als een variant (wat betekent dat u elk gegevenstype kunt gebruiken).

Als u meer dan één argument heeft, kunt u die tussen dezelfde haakjes specificeren - gescheiden door een komma. We zullen later in deze zelfstudie zien hoe u meerdere argumenten kunt gebruiken in een door de gebruiker gedefinieerde functie.

Merk op dat de functie is gespecificeerd als het gegevenstype 'String'. Dit zou VBA vertellen dat het resultaat van de formule van het gegevenstype String zou zijn.

Hoewel ik hier een numeriek gegevenstype kan gebruiken (zoals Lang of Dubbel), zou dat het aantal getallen dat het kan retourneren, beperken. Als ik een reeks van 20 getallen heb die ik uit de algemene reeks moet halen, zou het declareren van de functie als Long of Double een fout geven (omdat het nummer buiten het bereik zou zijn). Daarom heb ik het functie-uitvoergegevenstype als String behouden.

De tweede regel van de code - die in het groen die begint met een apostrof - is een opmerking. Bij het lezen van de code negeert VBA deze regel. U kunt dit gebruiken om een ​​beschrijving of een detail over de code toe te voegen.

De derde regel van de code declareert een variabele 'StringLength' als een Integer-gegevenstype. Dit is de variabele waarin we de waarde opslaan van de lengte van de string die wordt geanalyseerd door de formule.

De vierde regel declareert de variabele Resultaat als een String-gegevenstype. Dit is de variabele waar we de getallen uit de alfanumerieke reeks halen.

De vijfde regel wijst de lengte van de string in het invoerargument toe aan de variabele 'StringLength'. Merk op dat 'CellRef' verwijst naar het argument dat door de gebruiker zal worden gegeven tijdens het gebruik van de formule in het werkblad (of het gebruik ervan in VBA - wat we later in deze tutorial zullen zien).

De zesde, zevende en achtste regel vormen het deel van de For Next-lus. De lus wordt net zo vaak uitgevoerd als het aantal tekens in het invoerargument. Dit nummer wordt gegeven door de LEN-functie en wordt toegewezen aan de variabele 'StringLength'.

De lus loopt dus van ‘1 tot Stringlength’.

Binnen de lus analyseert de IF-instructie elk teken van de tekenreeks en als het numeriek is, voegt het dat numerieke teken toe aan de variabele Resultaat. Het gebruikt hiervoor de MID-functie in VBA.

De voorlaatste regel van de code wijst de waarde van het resultaat toe aan de functie. Het is deze regel code die ervoor zorgt dat de functie de waarde 'Resultaat' teruggeeft in de cel (van waaruit deze wordt aangeroepen).

De laatste regel van de code is End Function. Dit is een verplichte regel code die VBA vertelt dat de functiecode hier eindigt.

De bovenstaande code legt de verschillende delen uit van een typische aangepaste functie die in VBA is gemaakt. In de volgende secties zullen we diep in deze elementen duiken en ook de verschillende manieren bekijken om de VBA-functie in Excel uit te voeren.

Argumenten in een door de gebruiker gedefinieerde functie in VBA

In de bovenstaande voorbeelden, waar we een door de gebruiker gedefinieerde functie hebben gemaakt om het numerieke deel van een alfanumerieke tekenreeks (GetNumeric) te krijgen, is de functie ontworpen om één enkel argument te accepteren.

In deze sectie zal ik bespreken hoe u functies kunt maken die geen argument hebben voor degenen die meerdere argumenten hebben (vereiste en optionele argumenten).

Een functie maken in VBA zonder enige argumenten

In het Excel-werkblad hebben we verschillende functies waarvoor geen argumenten nodig zijn (zoals RAND, TODAY, NOW).

Deze functies zijn niet afhankelijk van invoerargumenten. De functie VANDAAG zou bijvoorbeeld de huidige datum retourneren en de functie RAND zou een willekeurig getal tussen 0 en 1 retourneren.

Je kunt zo'n vergelijkbare functie ook in VBA maken.

Hieronder vindt u de code die u de naam van het bestand geeft. Er zijn geen argumenten voor nodig, omdat het resultaat dat moet worden geretourneerd, niet afhankelijk is van een argument.

Functie WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

De bovenstaande code specificeert het resultaat van de functie als een String-gegevenstype (omdat het resultaat dat we willen de bestandsnaam is - wat een string is).

Deze functie wijst de waarde van 'ThisWorkbook.Name' toe aan de functie, die wordt geretourneerd wanneer de functie in het werkblad wordt gebruikt.

Als het bestand is opgeslagen, retourneert het de naam met de bestandsextensie, anders geeft het gewoon de naam.

Bovenstaande heeft echter een probleem.

Als de bestandsnaam verandert, wordt deze niet automatisch bijgewerkt. Normaal gesproken wordt een functie vernieuwd wanneer er een wijziging is in de invoerargumenten. Maar aangezien deze functie geen argumenten bevat, herberekent de functie niet (zelfs als u de naam van de werkmap wijzigt, deze sluit en opnieuw opent).

Als je wilt, kun je een herberekening forceren met behulp van de sneltoets - Control + Alt + F9.

Om de formule opnieuw te laten berekenen wanneer er een wijziging in het werkblad is, moet u er een regel code aan toevoegen.

De onderstaande code zorgt ervoor dat de functie opnieuw wordt berekend wanneer er een wijziging in het werkblad is (net als andere vergelijkbare werkbladfuncties zoals TODAY of RAND-functie).

Functie WorkbookName() As String Application.Volatile True WorkbookName = ThisWorkbook.Name End Function

Als u nu de naam van de werkmap wijzigt, wordt deze functie bijgewerkt wanneer er een wijziging in het werkblad is of wanneer u deze werkmap opnieuw opent.

Een functie maken in VBA met één argument

In een van de bovenstaande secties hebben we al gezien hoe je een functie kunt maken die slechts één argument nodig heeft (de GetNumeric-functie die hierboven is behandeld).

Laten we nog een eenvoudige functie maken waarvoor slechts één argument nodig is.

Functie die met de onderstaande code is gemaakt, zou de tekst waarnaar wordt verwezen, omzetten in hoofdletters. Nu hebben we er al een functie voor in Excel, en deze functie is alleen om je te laten zien hoe het werkt. Als je dit moet doen, is het beter om de ingebouwde UPPER-functie te gebruiken.

Functie ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) Eindfunctie

Deze functie gebruikt de UCase-functie in VBA om de waarde van de CellRef-variabele te wijzigen. Vervolgens wijst het de waarde toe aan de functie ConvertToUpperCase.

Aangezien deze functie een argument aanneemt, hoeven we het gedeelte Application.Volatile hier niet te gebruiken. Zodra het argument verandert, wordt de functie automatisch bijgewerkt.

Een functie maken in VBA met meerdere argumenten

Net als werkbladfuncties, kunt u in VBA functies maken waarvoor meerdere argumenten nodig zijn.

De onderstaande code zou een functie creëren die de tekst voor het opgegeven scheidingsteken extraheert. Er zijn twee argumenten nodig: de celverwijzing met de tekenreeks en het scheidingsteken.

Functie GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Resultaat = Left (CellRef, DelimPosition) GetDataBefore EndDelimiter = Resultaat

Als u meer dan één argument moet gebruiken in een door de gebruiker gedefinieerde functie, kunt u alle argumenten tussen haakjes plaatsen, gescheiden door een komma.

Merk op dat u voor elk argument een gegevenstype kunt specificeren. In het bovenstaande voorbeeld is 'CellRef' gedeclareerd als een bereikgegevenstype en is 'Delim' gedeclareerd als een String-gegevenstype. Als u geen gegevenstype opgeeft, beschouwt VBA dit als een variantgegevenstype.

Wanneer u de bovenstaande functie in het werkblad gebruikt, moet u de celverwijzing met de tekst als het eerste argument en de scheidingsteken(s) tussen dubbele aanhalingstekens als het tweede argument opgeven.

Vervolgens wordt de positie van het scheidingsteken gecontroleerd met behulp van de INSTR-functie in VBA. Deze positie wordt vervolgens gebruikt om alle tekens vóór het scheidingsteken te extraheren (met behulp van de LEFT-functie).

Ten slotte wijst het het resultaat toe aan de functie.

Deze formule is verre van perfect. Als u bijvoorbeeld een scheidingsteken invoert dat niet in de tekst voorkomt, geeft dit een foutmelding. Nu kunt u de IFERROR-functie in het werkblad gebruiken om de fouten te verwijderen, of u kunt de onderstaande code gebruiken die de volledige tekst retourneert wanneer het scheidingsteken niet kan worden gevonden.

Functie GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Resultaat As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len(CellRef) Resultaat = Left( CellRef, DelimPosition) GetDataBeforeDelimiter = Resultaat Eindfunctie

Deze functie kunnen we verder optimaliseren.

Als u de tekst (waaruit u het gedeelte vóór het scheidingsteken wilt extraheren) rechtstreeks in de functie invoert, krijgt u een foutmelding. Ga je gang… probeer het!

Dit gebeurt omdat we de 'CellRef' hebben gespecificeerd als een bereikgegevenstype.

Of, als u wilt dat het scheidingsteken zich in een cel bevindt en de celverwijzing gebruikt in plaats van deze hard te coderen in de formule, kunt u dat niet doen met de bovenstaande code. Het is omdat de Delim is gedeclareerd als een string-gegevenstype.

Als u wilt dat de functie de flexibiliteit heeft om directe tekstinvoer of celverwijzingen van de gebruiker te accepteren, moet u de gegevenstypedeclaratie verwijderen. Dit zou het argument uiteindelijk maken als een variant gegevenstype, dat elk type argument kan aannemen en verwerken.

De onderstaande code zou dit doen:

Functie GetDataBeforeDelimiter(CellRef, Delim) As String Dim Resultaat As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Als DelimPosition <0 Dan DelimPosition = Len(CellRef) Resultaat = Links (CellRef) GetDataBeforeDelimiter = Resultaat Eindfunctie

Een functie maken in VBA met optionele argumenten

Er zijn veel functies in Excel waarbij sommige van de argumenten optioneel zijn.

De legendarische functie VERT.ZOEKEN heeft bijvoorbeeld 3 verplichte argumenten en één optioneel argument.

Een optioneel argument is, zoals de naam al doet vermoeden, optioneel om op te geven. Als u geen van de verplichte argumenten opgeeft, geeft uw functie u een foutmelding, maar als u het optionele argument niet opgeeft, zou uw functie werken.

Maar optionele argumenten zijn niet nutteloos. Ze bieden u de mogelijkheid om uit een reeks opties te kiezen.

Als u bijvoorbeeld in de functie VERT.ZOEKEN het vierde argument niet opgeeft, zoekt VERT.ZOEKEN bij benadering en als u het laatste argument opgeeft als ONWAAR (of 0), dan doet het een exacte overeenkomst.

Onthoud dat de optionele argumenten altijd achter alle vereiste argumenten moeten komen. U kunt aan het begin geen optionele argumenten hebben.

Laten we nu eens kijken hoe we een functie in VBA kunnen maken met optionele argumenten.

Functie met alleen een optioneel argument

Voor zover ik weet, is er geen ingebouwde functie die alleen optionele argumenten gebruikt (ik kan het hier mis hebben, maar ik kan zo'n functie niet bedenken).

Maar we kunnen er een maken met VBA.

Hieronder vindt u de code van de functie die u de huidige datum geeft in het dd-mm-jjjj-formaat als u geen argument invoert (dwz laat het leeg) en in het "dd mmmm, jjjj"-formaat als u iets invoert als het argument (dat wil zeggen, alles zodat het argument niet leeg is).

Functie CurrDate(Optioneel fmt als variant) Dim Resultaat If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") Else CurrDate = Format(Date, "dd mmmm, yyyy") End If End Functie

Merk op dat de bovenstaande functie 'IsMissing' gebruikt om te controleren of het argument ontbreekt of niet. Als u de functie IsMissing wilt gebruiken, moet uw optionele argument van het gegevenstype variant zijn.

De bovenstaande functie werkt ongeacht wat u als argument invoert. In de code controleren we alleen of het optionele argument is opgegeven of niet.

U kunt dit robuuster maken door alleen specifieke waarden als argumenten te nemen en in de rest van de gevallen een fout weer te geven (zoals weergegeven in de onderstaande code).

Functie CurrDate(Optioneel fmt als variant) Dim Resultaat If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr(xlErrValue) End If End-functie

De bovenstaande code creëert een functie die de datum toont in het "dd-mm-jjjj" formaat als er geen argument is opgegeven, en in "dd mmmm,jjjj" formaat wanneer het argument 1 is. Het geeft een fout in alle andere gevallen.

Functie met zowel vereiste als optionele argumenten

We hebben al een code gezien die het numerieke deel uit een string haalt.

Laten we nu eens kijken naar een soortgelijk voorbeeld dat zowel vereiste als optionele argumenten bevat.

De onderstaande code maakt een functie die het tekstgedeelte uit een tekenreeks haalt. Als het optionele argument WAAR is, geeft het het resultaat in hoofdletters, en als het optionele argument ONWAAR is of wordt weggelaten, geeft het het resultaat zoals het is.

Functie GetText(CellRef As Range, Optioneel TextCase = False) As String Dim StringLength As Integer Dim Resultaat As String StringLength = Len(CellRef) For i = 1 To StringLength Indien niet (IsNumeric(Mid(CellRef, i, 1))) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) Volgende i If TextCase = True Dan Resultaat = UCase(Result) GetText = Resultaat Eindfunctie

Merk op dat we in de bovenstaande code de waarde van 'TextCase' hebben geïnitialiseerd als False (kijk tussen de haakjes in de eerste regel).

Door dit te doen, hebben we ervoor gezorgd dat het optionele argument begint met de standaardwaarde, die ONWAAR is. Als de gebruiker de waarde opgeeft als WAAR, retourneert de functie de tekst in hoofdletters, en als de gebruiker het optionele argument opgeeft als ONWAAR of weglaat, dan is de geretourneerde tekst zoals deze is.

Een functie maken in VBA met een array als argument

Tot nu toe hebben we voorbeelden gezien van het maken van een functie met optionele/vereiste argumenten - waarbij deze argumenten een enkele waarde waren.

U kunt ook een functie maken die een array als argument kan hebben. In Excel-werkbladfuncties zijn er veel functies die arrayargumenten gebruiken, zoals SOM, VERT.ZOEKEN, SOM.ALS, AANTAL.ALS, enz.

Hieronder staat de code die een functie maakt die de som geeft van alle even getallen in het opgegeven bereik van de cellen.

Functie AddEven (CellRef as Range) Dim Cell As Range voor elke cel in CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Resultaat + Cell.Value End If End If Next Cell AddEven = Resultaat End Functie

U kunt deze functie in het werkblad gebruiken en het cellenbereik opgeven met de getallen als argument. De functie zou een enkele waarde teruggeven - de som van alle even getallen (zoals hieronder getoond).

In de bovenstaande functie hebben we in plaats van een enkele waarde een array (A1:A10) geleverd. Om dit te laten werken, moet u ervoor zorgen dat uw gegevenstype van het argument een array kan accepteren.

In de bovenstaande code heb ik het argument CellRef gespecificeerd als bereik (dat een array als invoer kan hebben). U kunt hier ook het variantgegevenstype gebruiken.

In de code is er een For Each-lus die door elke cel gaat en controleert of het een aantal niet is. Als dit niet het geval is, gebeurt er niets en gaat het naar de volgende cel. Als het een getal is, controleert het of het even is of niet (met behulp van de MOD-functie).

Uiteindelijk worden alle even getallen opgeteld en wordt de som terug toegewezen aan de functie.

Een functie maken met een onbepaald aantal argumenten

Tijdens het maken van sommige functies in VBA, weet u misschien niet het exacte aantal argumenten dat een gebruiker wil opgeven. Het is dus nodig om een ​​functie te maken die zoveel mogelijk argumenten kan accepteren en deze kan gebruiken om het resultaat te retourneren.

Een voorbeeld van zo'n werkbladfunctie is de SOM-functie. Je kunt er meerdere argumenten aan geven (zoals deze):

=SOM(A1,A2:A4,B1:B20)

De bovenstaande functie zou de waarden in al deze argumenten optellen. Merk ook op dat dit een enkele cel of een reeks cellen kan zijn.

U kunt zo'n functie in VBA maken door het laatste argument (of het kan het enige argument) als optioneel te hebben. Dit optionele argument moet ook worden voorafgegaan door het trefwoord 'ParamArray'.

'ParamArray' is een modifier waarmee u zoveel argumenten kunt accepteren als u wilt. Merk op dat het gebruik van het woord ParamArray voor een argument het argument optioneel maakt. U hoeft het woord Optioneel hier echter niet te gebruiken.

Laten we nu een functie maken die een willekeurig aantal argumenten kan accepteren en alle getallen in de opgegeven argumenten zou optellen:

Functie AddArguments(ParamArray arglist() As Variant) Voor elke arg In arglist AddArguments = AddArguments + arg Volgende arg End Functie

De bovenstaande functie kan een willekeurig aantal argumenten aannemen en deze argumenten toevoegen om het resultaat te geven.

Merk op dat u slechts één enkele waarde, een celverwijzing, een boolean of een uitdrukking als argument kunt gebruiken. U kunt geen array als argument opgeven. Als een van uw argumenten bijvoorbeeld D8:D10 is, geeft deze formule u een foutmelding.

Als je beide meercellige argumenten wilt gebruiken, moet je de onderstaande code gebruiken:

Functie AddArguments(ParamArray arglist() As Variant) Voor elke arg In arglijst Voor elke cel in arg AddArguments = AddArguments + Cel Volgende cel Volgende arg End Functie

Merk op dat deze formule werkt met meerdere cellen en matrixverwijzingen, maar het kan geen hardgecodeerde waarden of uitdrukkingen verwerken. U kunt een robuustere functie creëren door deze voorwaarden te controleren en te behandelen, maar dat is hier niet de bedoeling.

De bedoeling hier is om u te laten zien hoe ParamArray werkt, zodat u een onbepaald aantal argumenten in de functie kunt toestaan. Als u een betere functie wilt dan degene die door de bovenstaande code is gemaakt, gebruikt u de SOM-functie in het werkblad.

Een functie maken die een array retourneert

Tot nu toe hebben we functies gezien die een enkele waarde retourneren.

Met VBA kunt u een functie maken die een variant retourneert die een hele reeks waarden kan bevatten.

Matrixformules zijn ook beschikbaar als ingebouwde functies in Excel-werkbladen. Als u bekend bent met matrixformules in Excel, weet u dat deze worden ingevoerd met Control + Shift + Enter (in plaats van alleen Enter). U kunt hier meer lezen over matrixformules. Als u niets weet over matrixformules, hoeft u zich geen zorgen te maken, blijf lezen.

Laten we een formule maken die een matrix van drie getallen (1,2,3) retourneert.

De onderstaande code zou dit doen.

Functie ThreeNumbers() As Variant Dim NumberValue(1 To 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue Eindfunctie

In bovenstaande code hebben we als variant de functie ‘ThreeNumbers’ gespecificeerd. Hierdoor kan het een reeks waarden bevatten.

De variabele 'NumberValue' wordt gedeclareerd als een array met 3 elementen. Het bevat de drie waarden en wijst deze toe aan de functie 'ThreeNumbers'.

U kunt deze functie in het werkblad gebruiken door de functie in te voeren en op de Control + Shift + Enter-toets te drukken (houd de Control- en de Shift-toetsen ingedrukt en druk vervolgens op Enter).

Wanneer u dit doet, retourneert het 1 in de cel, maar in werkelijkheid bevat het alle drie de waarden. Gebruik de onderstaande formule om dit te controleren:

=MAX(Drie Nummers())

Gebruik de bovenstaande functie met Control + Shift + Enter. U zult merken dat het resultaat nu 3 is, omdat dit de grootste waarden zijn in de array die wordt geretourneerd door de Max-functie, die de drie getallen krijgt als het resultaat van onze door de gebruiker gedefinieerde functie - ThreeNumbers.

U kunt dezelfde techniek gebruiken om een ​​functie te maken die een reeks maandnamen retourneert, zoals weergegeven door de onderstaande code:

Functie Months() As Variant Dim MonthName(1 To 12) MonthName(1) = "Januari" MonthName(2) = "Februari" MonthName(3) = "Maart" MonthName(4) = "April" MonthName(5) = "Mei" MonthName(6) = "Juni" MonthName(7) = "Juli" MonthName(8) = "Augustus" MonthName(9) = "September" MonthName(10) = "Oktober" MonthName(11) = "november " MonthName (12) = "December" Months = MonthName Eindfunctie

Wanneer u nu de functie =Months() in het Excel-werkblad invoert en Control + Shift + Enter gebruikt, wordt de volledige reeks maandnamen geretourneerd. Merk op dat u alleen januari in de cel ziet, aangezien dat de eerste waarde in de array is. Dit betekent niet dat de array slechts één waarde retourneert.

Om u te laten zien dat het alle waarden retourneert, doet u dit - selecteer de cel met de formule, ga naar de formulebalk, selecteer de volledige formule en druk op F9. Dit toont u alle waarden die de functie retourneert.

U kunt dit gebruiken door de onderstaande INDEX-formule te gebruiken om in één keer een lijst met alle maandnamen te krijgen.

=INDEX(Maanden(),RIJ())

Als u nu veel waarden heeft, is het geen goede gewoonte om deze waarden één voor één toe te wijzen (zoals we hierboven hebben gedaan). In plaats daarvan kunt u de functie Array in VBA gebruiken.

Dus dezelfde code waarin we de functie 'Maanden' maken, zou korter worden, zoals hieronder wordt weergegeven:

Functie Months() As Variant Months = Array("januari", "februari", "maart", "april", "mei", "juni", _ "juli", "augustus", "september", "oktober" , "November", "December") Eindfunctie

De bovenstaande functie gebruikt de functie Array om de waarden rechtstreeks aan de functie toe te wijzen.

Merk op dat alle hierboven gemaakte functies een horizontale reeks waarden retourneren. Dit betekent dat als u 12 horizontale cellen selecteert (laten we zeggen A1:L1) en de formule =Months() invoert in cel A1, u alle maandnamen krijgt.

Maar wat als u deze waarden in een verticaal cellenbereik wilt hebben.

U kunt dit doen door de TRANSPOSE-formule in het werkblad te gebruiken.

Selecteer eenvoudig 12 verticale cellen (aaneengesloten) en voer de onderstaande formule in.

De reikwijdte van een door de gebruiker gedefinieerde functie in Excel begrijpen

Een functie kan twee bereiken hebben: Openbaar of Privaat.

• EEN Publiek bereik betekent dat de functie beschikbaar is voor alle werkbladen in de werkmap en voor alle procedures (Sub en Functie) in alle modules in de werkmap. Dit is handig als u een functie wilt aanroepen vanuit een subroutine (we zullen zien hoe dit in de volgende sectie wordt gedaan).
• EEN Privé bereik betekent dat de functie alleen beschikbaar is in de module waarin deze bestaat. Je kunt het niet in andere modules gebruiken. Je zult het ook niet zien in de lijst met functies in het werkblad. Als uw functienaam bijvoorbeeld 'Months()' is en u typt functie in Excel (na het =-teken), wordt de functienaam niet weergegeven. U kunt het echter nog steeds gebruiken als u de formulenaam invoert.

Als u niets opgeeft, is de functie standaard een openbare functie.

Hieronder staat een functie die een privéfunctie is:

Private Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

U kunt deze functie gebruiken in de subroutines en de procedures in dezelfde modules, maar niet in andere modules. Deze functie zou ook niet in het werkblad verschijnen.

De onderstaande code zou deze functie openbaar maken. Het verschijnt ook in het werkblad.

Functie WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Verschillende manieren om een ​​door de gebruiker gedefinieerde functie in Excel te gebruiken

Nadat u een door de gebruiker gedefinieerde functie in VBA hebt gemaakt, kunt u deze op veel verschillende manieren gebruiken.

Laten we eerst bespreken hoe u de functies in het werkblad kunt gebruiken.

UDF's gebruiken in werkbladen

We hebben al voorbeelden gezien van het gebruik van een functie die in VBA is gemaakt in het werkblad.

Het enige dat u hoeft te doen, is de functienaam invoeren en deze verschijnt in de intellisense.

Merk op dat om de functie in het werkblad te laten verschijnen, het een openbare functie moet zijn (zoals uitgelegd in de sectie hierboven).

U kunt ook het dialoogvenster Functie invoegen gebruiken om de door de gebruiker gedefinieerde functie in te voegen (met behulp van de onderstaande stappen). Dit zou alleen werken voor functies die openbaar zijn.

• Ga naar het tabblad Gegevens.
• Klik op de optie 'Functie invoegen'.
• Selecteer in het dialoogvenster Functie invoegen de optie Door gebruiker gedefinieerd als categorie. Deze optie verschijnt alleen als je een functie hebt in de VB Editor (en de functie is Openbaar).
• Selecteer de functie in de lijst met alle door de openbare gebruiker gedefinieerde functies.
• Klik op de OK-knop.

De bovenstaande stappen zouden de functie in het werkblad invoegen. Het geeft ook een dialoogvenster Functieargumenten weer dat u details geeft over de argumenten en het resultaat.

U kunt een door de gebruiker gedefinieerde functie gebruiken, net als elke andere functie in Excel. Dit betekent ook dat u het kunt gebruiken met andere ingebouwde Excel-functies. Bijvoorbeeld. de onderstaande formule geeft de naam van de werkmap in hoofdletters:

=UPPER(Werkmapnaam())

Door de gebruiker gedefinieerde functies gebruiken in VBA-procedures en -functies

Als u een functie heeft aangemaakt, kunt u deze ook in andere subprocedures gebruiken.

Als de functie Openbaar is, kan deze in elke procedure in dezelfde of een andere module worden gebruikt. Als het privé is, kan het alleen in dezelfde module worden gebruikt.

Hieronder staat een functie die de naam van de werkmap retourneert.

Functie WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

De onderstaande procedure roept de functie aan en geeft vervolgens de naam weer in een berichtvenster.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

Je kunt ook een functie aanroepen vanuit een andere functie.

In de onderstaande codes retourneert de eerste code de naam van de werkmap en de tweede retourneert de naam in hoofdletters door de eerste functie aan te roepen.

Functie WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function

Functie WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Function

Een door de gebruiker gedefinieerde functie aanroepen vanuit andere werkmappen

Als je een functie in een werkmap hebt, kun je deze functie ook in andere werkmappen aanroepen.

Er zijn meerdere manieren om dit te doen:

1. Een invoegtoepassing maken
2. Functie opslaan in de persoonlijke macrowerkmap
3. Verwijzen naar de functie uit een andere werkmap.

Een invoegtoepassing maken

Door een invoegtoepassing te maken en te installeren, heeft u de aangepaste functie erin beschikbaar in alle werkmappen.

Stel dat u een aangepaste functie hebt gemaakt - 'GetNumeric' en u wilt deze in alle werkmappen. Om dit te doen, maakt u een nieuwe werkmap en heeft u de functiecode in een module in deze nieuwe werkmap.

Volg nu de onderstaande stappen om het op te slaan als een invoegtoepassing en vervolgens te installeren in Excel.

• Ga naar het tabblad Bestand en klik op Opslaan als.
• Wijzig in het dialoogvenster Opslaan als het type 'Opslaan als' in .xlam. De naam die u aan het bestand toewijst, is de naam van uw invoegtoepassing. In dit voorbeeld wordt het bestand opgeslagen met de naam GetNumeric.
  • U zult merken dat het pad van het bestand waar het wordt opgeslagen automatisch verandert. U kunt de standaard gebruiken of deze wijzigen als u dat wilt.
• Open een nieuwe Excel-werkmap en ga naar het tabblad Ontwikkelaars.
• Klik op de optie Excel-invoegtoepassingen.
• Blader in het dialoogvenster Add-ins naar het bestand dat u hebt opgeslagen, zoek het op en klik op OK.

Nu is de invoegtoepassing geactiveerd.

Nu kunt u de aangepaste functie in alle werkmappen gebruiken.

De functie opslaan in de persoonlijke macrowerkmap

Een persoonlijke macrowerkmap is een verborgen werkmap in uw systeem die wordt geopend wanneer u de Excel-toepassing opent.

Het is een plaats waar u macrocodes kunt opslaan en deze macro's vervolgens vanuit elke werkmap kunt openen. Het is een geweldige plek om die macro's op te slaan die u vaak wilt gebruiken.

Standaard is er geen persoonlijke macrowerkmap in uw Excel. U moet deze maken door een macro op te nemen en deze op te slaan in de persoonlijke macrowerkmap.

De gedetailleerde stappen voor het maken en opslaan van macro's in de persoonlijke macrowerkmap vindt u hier.

Verwijzen naar de functie uit een andere werkmap

Hoewel de eerste twee methoden (een invoegtoepassing maken en een persoonlijke macrowerkmap gebruiken) in alle situaties zouden werken, moet die werkmap geopend zijn als u naar de functie wilt verwijzen vanuit een andere werkmap.

Stel je hebt een werkmap met de naam ‘Werkboek met Formule’, en het heeft de functie met de naam 'GetNumeriek'.

Om deze functie in een andere werkmap te gebruiken (terwijl de Werkmap met formule is geopend), kunt u de onderstaande formule gebruiken:

='Werkmap met formule'! GetNumeric (A1)

De bovenstaande formule gebruikt de door de gebruiker gedefinieerde functie in de Werkmap met formule bestand en geeft u het resultaat.

Merk op dat aangezien de naam van de werkmap spaties heeft, u deze tussen enkele aanhalingstekens moet plaatsen.

Exit Function Statement VBA gebruiken

Als u een functie wilt verlaten terwijl de code wordt uitgevoerd, kunt u dat doen met behulp van de 'Exit Function'-instructie.

De onderstaande code haalt de eerste drie numerieke tekens uit een alfanumerieke tekenreeks. Zodra het de drie tekens krijgt, eindigt de functie en retourneert het resultaat.

Functie GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Als J = 3 Dan functie afsluiten If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Resultaat = Resultaat & Mid(CellRef, i, 1) GetNumericFirstThree = Resultaat End If Next i End Function

De bovenstaande functie controleert het aantal tekens dat numeriek is, en wanneer het 3 numerieke tekens krijgt, verlaat het de functie in de volgende lus.

Debuggen van een door de gebruiker gedefinieerde functie

Er zijn een paar technieken die u kunt gebruiken bij het debuggen van een door de gebruiker gedefinieerde functie in VBA:

Fouten opsporen in een aangepaste functie met behulp van het berichtenvenster

Gebruik de MsgBox-functie om een ​​berichtvenster met een specifieke waarde weer te geven.

De waarde die u weergeeft, kan gebaseerd zijn op wat u wilt testen. Als u bijvoorbeeld wilt controleren of de code wordt uitgevoerd of niet, zou elk bericht werken, en als u wilt controleren of de lussen werken of niet, kunt u een specifieke waarde of de lusteller weergeven.

Fouten opsporen in een aangepaste functie door het breekpunt in te stellen

Stel een breekpunt in om elke regel één voor één te kunnen doorlopen. Om een ​​breekpunt in te stellen, selecteert u de regel waar u deze wilt hebben en drukt u op F9, of klikt u op het grijze verticale gebied dat is overgelaten aan de coderegels. Elk van deze methoden zou een breekpunt invoegen (u ziet een rode stip in het grijze gebied).

Nadat u het breekpunt hebt ingesteld en u de functie uitvoert, gaat deze tot de breekpuntregel en stopt dan. Nu kunt u de code doorlopen met de F8-toets. Als u eenmaal op F8 drukt, gaat u naar de volgende regel in de code.

Een aangepaste functie debuggen met behulp van Debug.Print in de code

U kunt de instructie Debug.Print in uw code gebruiken om de waarden van de opgegeven variabelen/argumenten in het directe venster te krijgen.

In de onderstaande code heb ik bijvoorbeeld Debug.Print gebruikt om de waarde van twee variabelen te krijgen - 'j' en 'Resultaat'

Functie GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Als J = 3 Dan functie afsluiten If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Resultaat = Resultaat & Mid(CellRef, i, 1) Debug.Print J, Resultaat GetNumericFirstThree = Resultaat End If Next i End Function

Wanneer deze code wordt uitgevoerd, wordt het volgende weergegeven in het directe venster.

Excel Ingebouwde Functies Vs. Door de gebruiker gedefinieerde VBA-functie

Er zijn weinig sterke voordelen van het gebruik van ingebouwde Excel-functies ten opzichte van aangepaste functies die in VBA zijn gemaakt.

• Ingebouwde functies zijn een stuk sneller dan de VBA-functies.
• Wanneer u een rapport/dashboard maakt met VBA-functies en dit naar een klant/collega stuurt, hoeven zij zich geen zorgen te maken of de macro's zijn ingeschakeld of niet. In sommige gevallen worden klanten/klanten bang door een waarschuwing in de gele balk te zien (die hen simpelweg vraagt ​​om macro's in te schakelen).
• Met ingebouwde Excel-functies hoeft u zich geen zorgen te maken over bestandsextensies. Als u macro's of door de gebruiker gedefinieerde functies in de werkmap hebt, moet u deze opslaan in .xlsm.

Hoewel er veel goede redenen zijn om ingebouwde Excel-functies te gebruiken, kunt u in enkele gevallen beter een door de gebruiker gedefinieerde functie gebruiken.

• Het is beter om een ​​door de gebruiker gedefinieerde functie te gebruiken als uw ingebouwde formule enorm en ingewikkeld is. Dit wordt nog relevanter wanneer u iemand anders nodig heeft om de formules bij te werken. Als u bijvoorbeeld een enorme formule hebt die uit veel verschillende functies bestaat, kan zelfs het wijzigen van een verwijzing naar een cel vervelend en foutgevoelig zijn. In plaats daarvan kunt u een aangepaste functie maken die slechts een of twee argumenten nodig heeft en de backend al het zware werk doet.
• Wanneer u iets gedaan moet krijgen dat niet kan worden gedaan door de ingebouwde Excel-functies. Een voorbeeld hiervan kan zijn wanneer u alle numerieke tekens uit een tekenreeks wilt extraheren. In dergelijke gevallen weegt het voordeel van het gebruik van een door de gebruiker gedefinieerde functie gar zwaarder dan de nadelen.

Waar plaats je de VBA-code voor een door de gebruiker gedefinieerde functie?

Wanneer u een aangepaste functie maakt, moet u de code in het codevenster plaatsen voor de werkmap waarin u de functie wilt hebben.

Hieronder vindt u de stappen om de code voor de functie 'GetNumeric' in de werkmap te plaatsen.

1. Ga naar het tabblad Ontwikkelaars.
2. Klik op de Visual Basic-optie. Dit opent de VB-editor in de backend.
3. Klik in het deelvenster Projectverkenner in de VB-editor met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen. Als u de Projectverkenner niet ziet, gaat u naar het tabblad Weergave en klikt u op Projectverkenner.
4. Ga naar Invoegen en klik op Module. Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
5. Kopieer en plak de code in het modulevenster.

Misschien vind je de volgende Excel VBA-zelfstudies misschien ook leuk:

• Werken met cellen en bereiken in Excel VBA.
• Werken met werkbladen in Excel VBA.
• Werken met werkmappen met VBA.
• Hoe loops te gebruiken in Excel VBA.
• Excel VBA-evenementen - Een eenvoudige (en complete) gids
• IF Then Else-statements gebruiken in VBA.
• Hoe maak je een macro opnemen in Excel
• Hoe een macro in Excel uit te voeren
• Gegevens sorteren in Excel met VBA (een stapsgewijze handleiding).
• Excel VBA InStr-functie - uitgelegd met voorbeelden.