Aangepaste functies in Excel maken

Hoewel Excel een groot aantal ingebouwde werkbladfuncties bevat, bestaat de kans dat er niet altijd een functie is voor elk type berekening dat u uitvoert. De ontwerpers van Excel kunnen onmogelijk rekening houden met alle berekeningen die gebruikers nodig hebben. In plaats daarvan kunt u in Excel aangepaste functies maken. Dit wordt in dit artikel uitgelegd.

Eenvoudige, aangepaste functies maken

Aangepaste functies, zoals macro's, maken gebruik van de programmeertaal Visual Basic for Applications (VBA). Ze verschillen op twee manieren van macro’s. In de eerste plaats maken ze gebruik van functieprocedures in plaats van subprocedures. Dat wil zeggen dat ze beginnen met de instructie Function in plaats van de instructie Sub en ze eindigen op End Function in plaats van End Sub. Ten tweede worden berekeningen uitgevoerd in plaats dat er acties worden uitgevoerd. Bepaalde soorten instructies, zoals instructies die bereiken selecteren en opmaken, worden uitgesloten van aangepaste functies. In dit artikel leert u hoe u aangepaste functies maakt en gebruikt. Als u functies en macro's wilt maken, werkt u met Visual Basic Editor (VBE), dat wordt geopend in een nieuw, van Excel gescheiden venster.

Stel dat uw bedrijf een volumekorting biedt van tien procent van de verkoop van een product, onder de voorwaarde dat de bestelling meer dan honderd eenheden omvat. In de volgende alinea's wordt een functie voor het berekenen van deze korting gedemonstreerd.

In het onderstaande voorbeeld ziet u een bestelformulier waarop de artikelen, aantallen, prijzen, kortingen (indien van toepassing) en de uiteindelijke prijzen staan vermeld.

Als u een aangepaste functie KORTING in deze werkmap wilt maken, voert u de volgende stappen uit:

1. Druk op Alt+F11 om Visual Basic Editor te openen (druk op de Mac op FN+ALT+F11) en klik vervolgens op Module invoegen>. Er wordt een nieuw modulevenster weergegeven aan de rechterkant van Visual Basic Editor.

2. Kopieer en plak de volgende code in de nieuwe module.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Aangepaste functies gebruiken

U kunt nu de nieuwe functie KORTING gebruiken. Sluit Visual Basic Editor, selecteer cel G7 en typ het volgende:

=KORTING(D7,E7)

Excel berekent de tien procent korting van 200 eenheden van €47,50 per stuk en retourneert €950,00.

Op de eerste regel van de VBA-code, Function KORTING(aantal, prijs), hebt u aangegeven dat voor de functie KORTING twee argumenten nodig zijn, aantal en prijs. Als u in een werkblad de functie aanroept, moet u deze twee argumenten opnemen. In de formule =KORTING(D7,E7) is D7 het argument aantal en E7 het argument prijs. U kunt de formule KORTING nu naar G8:G13 kopiëren, waarna u de resultaten ziet zoals hieronder weergegeven.

Laten we eens kijken hoe deze functieprocedure wordt geïnterpreteerd. Als u op Enter drukt, wordt gekeken naar de naam KORTING in de huidige werkmap, en wordt vastgesteld dat dit een aangepaste functie in een VBA-module is. De namen van de argumenten tussen haakjes, hoeveelheid en prijs, zijn tijdelijke aanduidingen voor de waarden waarop de berekening van de korting is gebaseerd.

De ALS-instructie in het volgende codeblok onderzoekt het argument hoeveelheid en bepaalt of het aantal verkochte artikelen groter is dan of gelijk is aan 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Als het aantal verkochten artikelen groter is dan of gelijk is aan 100, wordt de volgende instructie uitgevoerd. Hiermee wordt de waarde aantal vermenigvuldigd met de waarde prijs. Het resultaat wordt vermenigvuldigd met 0,1:

Discount = quantity * price * 0.1

Het resultaat wordt opgeslagen als de variabele Korting. Een VBA-instructie waarin een waarde in een variabele wordt opgeslagen, wordt een toewijzingsinstructie genoemd, omdat hiermee de expressie aan de rechterkant van het gelijkteken wordt geëvalueerd en het resultaat aan de variabele aan de linkerkant wordt toegewezen. Omdat de variabele Korting dezelfde naam heeft als de functieprocedure, wordt de waarde die in de variabele is opgeslagen, geretourneerd aan de formule in het werkblad die de functie KORTING heeft aangeroepen.

Als aantal kleiner is dan 100, wordt de volgende instructie uitgevoerd:

Discount = 0

Ten slotte rondt de volgende instructie de aan de variabele Korting toegewezen waarde op twee decimalen af.

Discount = Application.Round(Discount, 2)

VBA kent geen functie AFRONDEN, maar Excel wel. Als u dus AFRONDEN in deze instructie wilt gebruiken, laat u VBA weten naar de methode Afronden (functie) te kijken in het toepassingsobject (Excel). Hiervoor voegt u het woord Toepassing vóór het woord Afronden toe. Gebruik deze syntaxis als u een Excel-functie vanuit een VBA-module wilt openen.

Aangepaste functieregels

Een aangepaste functie moet beginnen met de instructie Function en eindigen met de instructie End Function. Naast de functienaam geeft de instructie Function gewoonlijk ook een of meer argumenten op. U kunt echter een functie zonder argumenten maken. Excel bevat een aantal ingebouwde functies, bijvoorbeeld ASELECT en NU, die geen argumenten gebruiken.

Wat de instructie Function betreft, een functieprocedure omvat een of meer VBA-instructies die beslissingen en berekeningen uitvoeren met de argumenten die aan de functie zijn doorgegeven. Ten slotte moet u ergens in de functieprocedure een instructie opnemen die een waarde toekent aan een variabele met dezelfde naam als de functie. Deze waarde wordt geretourneerd aan de formule die de functie aanroept.

VBA-trefwoorden in aangepaste functies gebruiken

VBA-trefwoorden kunt u gebruiken in aangepaste functies is kleiner dan in macro's. Aangepaste functies kunnen niets anders doen dan een waarde retourneren aan een formule in een werkblad of aan een expressie die in een andere VBA-macro of -functie wordt gebruikt. Aangepaste functies kunnen bijvoorbeeld niet het formaat van vensters wijzigen, een formule in een cel bewerken of het lettertype, de kleur of het patroon van de tekst in een cel wijzigen. Als u dergelijke actiecode in een functieprocedure opneemt, retourneert de functie de #VALUE! fout.

De enige actie die een functieprocedure kan uitvoeren (afgezien van het uitvoeren van berekeningen) is een dialoogvenster weergeven. U kunt een InputBox-instructie in een aangepaste functie gebruiken als middel om invoer te krijgen van de gebruiker die de functie uitvoert. U kunt de instructie MsgBox gebruiken als middel om informatie over te brengen aan de gebruiker. U kunt ook aangepaste dialoogvensters of UserForms gebruiken, maar dat is een onderwerp dat buiten het bestek van deze inleiding valt.

Macro’s en aangepaste functies documenteren

Zelfs eenvoudige macro’s en aangepaste functies zijn soms moeilijk te lezen. Door verklarende tekst in de vorm van opmerkingen te gebruiken kunt u ze begrijpelijker maken. U voegt een opmerking toe door de verklarende tekst vooraf te laten gaan door een apostrof. Het volgende voorbeeld toont de functie KORTING met opmerkingen. Als u dergelijke opmerkingen toevoegt, kunnen u of anderen de VBA0code makkelijker onderhouden. Als u later een wijziging aan de code wilt aanbrengen, begrijpt u beter wat u eerder met de code hebt bedoeld.

Dankzij de apostrof wordt alles aan de rechterkant in dezelfde regel genegeerd. U kunt dus opmerkingen maken in afzonderlijke regels of aan de rechterkant van regels die VBA-code bevatten. U kunt een relatief lang codeblok beginnen met een opmerking waarin u het algemene doel ervan uitlegt en vervolgens inline-opmerkingen gebruiken om afzonderlijke instructies te documenteren.

Een andere manier om macro’s en aangepaste functies te documenteren, is door ze beschrijvende namen te geven. U kunt bijvoorbeeld in plaats van een macro de naam Etiketten te geven, de naam MaandEtiketten geven, waarmee u specifieker het doel van de macro aangeeft. Het gebruik van beschrijvende namen voor macro's en aangepaste functies is met name handig wanneer u veel procedures hebt gemaakt, vooral bij procedures met soortgelijke maar niet identieke doeleinden.

Op welke manier u uw macro’s en aangepaste functies documenteert, is een kwestie van persoonlijke voorkeur. Het is wel belangrijk dat u een consistente manier van documenteren gebruikt.

Aangepaste functies overal beschikbaar maken

Als u een aangepaste functie wilt gebruiken, moet de werkmap met de module waarin u de functie hebt gemaakt, geopend zijn. Als de werkmap niet geopend is, wordt er een #NAME? wordt weergegeven wanneer u de functie probeert te gebruiken. Als u in een andere werkmap naar de functie verwijst, moet u de functienaam vooraf laten gaan door de naam van de werkmap die de functie bevat. Als u bijvoorbeeld een functie KORTING maakt in een werkmap met de naam Persoonlijk.xlsb en u roept die functie aan vanuit een andere werkmap, dan typt u =persoonlijk.xlsb!korting(), dus niet slechts =korting().

U kunt zichzelf een paar toetsaanslagen (en mogelijke typfouten) besparen door uw aangepaste functies te selecteren in het dialoogvenster Functie invoegen. De aangepaste functies worden weergegeven in de categorie Door gebruiker gedefinieerd:

Een eenvoudigere manier om uw aangepaste functies te allen tijde beschikbaar te maken, is door ze op te slaan in een aparte werkmap en die werkmap vervolgens als een invoegtoepassing op te slaan. U kunt die invoegtoepassing vervolgens beschikbaar maken als u Excel uitvoert. U doet dit als volgt:

Als u deze stappen hebt gevolgd, worden uw aangepaste functies beschikbaar zodra u Excel uitvoert. Als u uw functiebibliotheek wilt toevoegen, gaat u terug naar Visual Basic Editor. Als u in Projectverkenner van Visual Basic Editor onder de kop VBAProject kijkt, ziet u een module met de naam van het bestand met uw invoegtoepassing. Uw invoegtoepassing heeft de extensie .xlam.

Als u in Projectverkenner op de module dubbelklikt, wordt de functiecode in Visual Basic Editor weergegeven. Als u een nieuwe functie wilt toevoegen, plaatst u het invoegpunt na de instructie End Function die de laatste functie in het codevenster beëindigt en begint u te typen. U kunt op deze manier zo veel functies maken als u wilt, en ze zijn altijd beschikbaar in de categorie Door gebruiker gedefinieerd in het dialoogvenster Functie invoegen .

Over de auteurs

Deze inhoud is oorspronkelijk geschreven door Mark Dodge en Craig Stinson. Het maakt deel uit van hun boek Microsoft Office Excel 2007 Inside Out. De inhoud is bijgewerkt zodat deze ook voor nieuwere versies van Excel van toepassing is.

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in Community's.