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 geen rekening houden met alle berekeningen die gebruikers willen uitvoeren. In Excel kunt u wel aangepaste functies maken. Dit wordt in dit artikel uitgelegd.

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.

Voorbeeld van bestelformulier zonder een aangepaste functie

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

Opmerking: Als u de code beter leesbaar wilt maken, kunt u de Tab-toets gebruiken regels in te springen. De inspringing is alleen voor het gemak en is optioneel. De code wordt sowieso uitgevoerd. Nadat u een ingesprongen regel hebt getypt, wordt ervan uitgegaan dat de volgende regel ook wordt ingesprongen. Als u één tab naar links wilt verspringen, drukt u op Shift+Tab.

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. Dit blijkt een aangepaste functie in een VBA-module te zijn. De namen van de argumenten tussen haakjes, aantal en prijs, zijn tijdelijke aanduidingen voor de waarden waarop de berekening van de korting is gebaseerd.

Voorbeeld van bestelformulier met een aangepaste functie

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 kunt gebruiken in aangepaste functies is kleiner dan het aantal dat u in macro’s kunt gebruiken. Bijvoorbeeld: aangepaste functies mogen alleen maar een waarde retourneren aan een formule in een werkblad of aan een expressie die in een andere VBA-macro of -functie wordt gebruikt. Met aangepaste functies kan bijvoorbeeld de grootte van vensters niet worden aangepast, kan geen formule in een cel worden bewerkt en kan het lettertype, de kleur of het patroon voor de tekst in een cel niet worden gewijzigd. Als u dit soort actiecode in een functieprocedure opneemt, retourneert de functie de waarde #WAARDE!

De enige actie die een functieprocedure kan uitvoeren (afgezien van het uitvoeren van berekeningen) is een dialoogvenster weergeven. U kunt de instructie InputBox 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 de gebruiker informatie te verschaffen. U kunt ook aangepaste dialoogvensters gebruiken, oftewel UserForms. Dit is echter een onderwerp dat buiten het bestek 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.

Voorbeeld van een VBA-functie met opmerkingen

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 om vervolgens opmerkingen tussen de code te plaatsen om afzonderlijke instructies van commentaar te voorzien.

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:

dialoogvenster Functie invoegen

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:

  1. Als u de benodigde functies hebt gemaakt, klikt u op Bestand > Opslaan als.

    Klik in Excel 2007 op de Microsoft Office-knop en klik vervolgens op Opslaan als.

  2. Open in het dialoogvenster Opslaan als de vervolgkeuzelijst Opslaan als en selecteer Excel-invoegtoepassing. Sla de werkmap in de map Invoegtoepassingen op onder een herkenbare naam (bijvoorbeeld MijnFuncties). Het dialoogvenster Opslaan als geeft deze map als optie, dus u hoeft deze standaardlocatie alleen maar te accepteren.

  3. Nadat u de werkmap hebt opgeslagen, klikt u op Bestand > Opties voor Excel.

    Klik in Excel 2007 op de Microsoft Office-knop en klik vervolgens op Opties voor Excel.

  4. Klik in het dialoogvenster Excel-opties op de categorie Invoegtoepassingen.

  5. Selecteer in de vervolgkeuzelijst Beheren de optie Invoegtoepassingen. Klik vervolgens op de knop Start.

  6. Schakel in het dialoogvenster Invoegtoepassingen het selectievakje in naast de naam die u gebruikt om uw werkmap op te slaan, zoals hieronder aangegeven.

    dialoogvenster Invoegtoepassingen

  1. Als u de benodigde functies hebt gemaakt, klikt u op Bestand > Opslaan als.

  2. Open in het dialoogvenster Opslaan als de vervolgkeuzelijst Opslaan als en selecteer Excel-invoegtoepassing. Sla de werkmap op onder een herkenbare naam (bijvoorbeeld MijnFuncties).

  3. Als u de werkmap hebt opgeslagen, klikt u op Extra > Excel-invoegtoepassingen.

  4. Selecteer in het dialoogvenster Invoegtoepassingen de knop Bladeren om de invoegtoepassing te vinden, klik op Openen en schakel het selectievakje in naast de invoegtoepassing in het vak Beschikbare invoegtoepassingen.

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.

module met een naam in VBE

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. Daarna begint u te typen. U kunt op deze manier zo veel functies maken als u wilt. Ze zijn in het dialoogvenster Functie invoegen altijd beschikbaar in de categorie Door gebruiker gedefinieerd.

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, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×