Aangepaste functies in Excel maken

Hoewel Excel een groot aantal ingebouwde werkbladfuncties bevat, is er waarschijnlijk geen functie voor elk type berekening dat u uitvoert. Het is mogelijk dat de ontwerpers van Excel niet konden voldoen aan de berekening van alle gebruikers. In plaats daarvan kunt u in Excel aangepaste functies maken die in dit artikel worden beschreven.

Zoekt u informatie over het maken van een aangepaste JavaScript-functie die u kunt uitvoeren in Excel voor Windows, Excel voor Mac of Excel voor het web ? Zie het artikel aangepaste functies in Excelvoor een overzicht van Excel.

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 wilt dat uw code beter leesbaar is, kunt u de Tab -toets gebruiken om de regels te laten inspringen. De inspringing is alleen voor uw voordeel en is optioneel, aangezien de code wordt uitgevoerd met of zonder de code. Wanneer u een ingesprongen lijn hebt getypt, wordt de volgende regel uitgekeerd ingesprongen in Visual Basic editor. Druk op SHIFT + TABom de invoegpositie te verlaten (van links) één 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.

U kunt ook overwegen hoe Excel deze functieprocedure interpreteert. Wanneer u op Enterdrukt, wordt er in Excel gezocht naar de naam korting in de huidige werkmap en wordt in een VBA-module gezocht naar een aangepaste functie. De namen van de argumenten tussen haakjes, aantal en prijszijn tijdelijke aanduidingen voor de waarden waarop de korting wordt berekend.

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 getal dat u in macro's kunt gebruiken. Aangepaste functies zijn niet toegestaan anders te doen dan een waarde te retourneren naar een formule in een werkblad of naar een expressie die in een andere VBA-macro of-functie wordt gebruikt. Met aangepaste functies kunt u bijvoorbeeld niet het formaat van vensters wijzigen, een formule in een cel bewerken of het lettertype, de kleur en de patroonopties voor de tekst in een cel wijzigen. Als u de actie ' actie ' in een functieprocedure opneemt, resulteert de functie in de #VALUE! -fout.

Met de ene actie een functieprocedure kunt uitvoeren (afgezien van het uitvoeren van berekeningen) wordt een dialoogvenster weergegeven. U kunt met de instructie InputBox een aangepaste functie gebruiken om de gegevens van de gebruiker die de functie uitvoert, te verkrijgen. U kunt een berichtvenster gebruiken als manier om gegevens over te brengen naar de gebruiker. U kunt ook aangepaste dialoogvensters of UserForms gebruiken, maar dat is een onderwerp buiten het bereik van deze inleiding.

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

Met een apostrof wordt in Excel alles rechts op dezelfde regel genegeerd, zodat u opmerkingen kunt maken op regels of aan de rechterkant van regels met VBA-code. U kunt een relatief lang codeblok beginnen met een opmerking waarin het totale doel wordt uitgelegd en vervolgens inline-opmerkingen gebruiken om afzonderlijke overzichten van documenten te maken.

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 op deze module dubbelklikt in Project Verkenner, wordt de functiecode in Visual Basic editor weergegeven. Als u een nieuwe functie wilt toevoegen, plaatst u de invoegpositie na de instructie End, functie waarmee de laatste functie in het venster programma code wordt beëindigd en begint te typen. U kunt zo veel functies maken als u op deze manier nodig hebt en deze zijn altijd beschikbaar in de categorie door gebruiker gedefinieerd in het dialoogvenster functie invoegen .

Deze inhoud werd gemaakt door tegenhouden en Karel Stinson als onderdeel van hun Book Microsoft Office Excel 2007 in. De update is nu ook toegepast op nieuwere versies van Excel.

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.

Opmerking:  Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor je is. Wil je ons laten weten of deze informatie nuttig is? Hier is het Engelstalige artikel ter referentie.

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-ondersteuningsagenten.

×