Aangepaste functies in Excel maken

Aangepaste functies maken net als macro’s 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, volgt u deze stappen:

1. Druk op Alt+F11 om Visual Basic Editor te openen (druk op de Mac op FN+ALT+F11) en klik vervolgens op Invoegen > Module. 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
   

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 excel deze functieprocedure interpreteert. Wanneer u op Enter drukt, zoekt Excel naar de naam DISCOUNT in de huidige werkmap en wordt ontdekt dat het een aangepaste functie is in een VBA-module. De argumentnamen 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 bekijkt het argument aantal en bepaalt of het aantal verkochte artikelen groter is dan 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.

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.

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

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

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.

Een apostrof vertelt Excel om alles rechts op dezelfde regel te negeren, zodat u opmerkingen kunt maken op regels zelf of aan de rechterkant van regels die VBA-code bevatten. U kunt een relatief lang codeblok beginnen met een opmerking waarin het algemene doel wordt uitgelegd en vervolgens inline-opmerkingen worden gebruikt om afzonderlijke instructies te documentariëren.

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.

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, krijgt u de fout #NAAM? als u de functie wilt 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.

Dubbelklikken op die module in Projectverkenner zorgt ervoor dat uw functiecode wordt weergegeven in Visual Basic Editor. Als u een nieuwe functie wilt toevoegen, plaatst u de invoegpositie na de instructie Eindfunctie waarmee de laatste functie in het codevenster wordt beëindigd en begint u te typen. U kunt op deze manier zoveel functies maken als u nodig hebt en deze zijn altijd beschikbaar in de categorie Door de gebruiker gedefinieerd in het dialoogvenster Functie invoegen .

Deze inhoud is oorspronkelijk geschreven door Mark Dodge en Craig Stinson als onderdeel van hun boek Microsoft Office Excel 2007 Inside Out. Het is sindsdien bijgewerkt om ook van toepassing te zijn op nieuwere versies van Excel.