Excel VBA-functiehandleiding: Return, Call, Voorbeelden

Wat is een functie?

Een functie is een stukje code dat een specifieke taak uitvoert en een resultaat retourneert. Functies worden meestal gebruikt om repetitieve taken uit te voeren, zoals het opmaken van gegevens voor uitvoer, het uitvoeren van berekeningen, enz.

Stel dat u aan het ontwikkelen bentping Een programma dat de rente op een lening berekent. Je kunt een functie maken die het leenbedrag en de terugbetalingstermijn als invoer accepteert. De functie kan vervolgens het leenbedrag en de terugbetalingstermijn gebruiken om de rente te berekenen en het resultaat terug te geven.

Waarom functies gebruiken

De voordelen van het gebruik van functies zijn dezelfde als die voor subroutines: ze verdelen een lang programma in beheersbare delen, ze kunnen overal in het project hergebruikt worden en een beschrijvende naam documenteert wat de code doet. Handleiding voor Excel VBA-subroutines dekt die voordelen volledig.

Regels voor het benoemen van functies

De naamgevingsregels zijn identiek aan die voor subroutines. Een functienaam mag geen spatie bevatten, moet beginnen met een letter of een underscore en mag geen gereserveerde waarde zijn. VBA trefwoorden zoals Function, Private of End.

VBA-syntaxis voor het declareren van functie

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

HIER in de syntaxis,

Hoe retourneer je een waarde en stel je het gegevenstype van de functie in?

Een functie heeft één taak die een subroutine niet heeft: het geeft een waarde terug. Twee details bepalen die waarde, en beide zijn gemakkelijk over het hoofd te zien.

Het eerste punt betreft de toewijzing. VBA kent geen Return-instructie. In plaats daarvan wijs je het resultaat toe aan de functie zelf, vandaar dat de regel er zo uitziet: mijnFunctie = arg1 + arg2Als die toewijzing nooit wordt uitgevoerd, retourneert de functie stilzwijgend een lege waarde in plaats van een foutmelding te geven, dus elke tak van de code moet die waarde instellen.

Het tweede punt betreft het retourtype. De bovenstaande declaratie eindigt bij de sluitende haak, waardoor de functie een Variant retourneert. Door na de haakjes een As-clausule toe te voegen, wordt het type vastgelegd. Dit is sneller, gebruikt minder geheugen en zorgt ervoor dat de compiler een typefout kan detecteren.

💡Tip: Gebruik Exit Function om vroegtijdig te stoppen zodra de retourwaarde is ingesteld, op dezelfde manier als Exit Sub een subroutine verlaat.

Functie gedemonstreerd met voorbeeld:

Functies lijken erg op de subroutine. Het belangrijkste verschil tussen een subroutine en een functie is dat de functie een waarde retourneert wanneer deze wordt aangeroepen. Terwijl een subroutine geen waarde retourneert wanneer deze wordt aangeroepen. Stel dat u twee getallen wilt optellen. U kunt een functie maken die twee getallen accepteert en de som van de getallen retourneert.

1. Maak de gebruikersinterface
2. Voeg de functie toe
3. Schrijf code voor de opdrachtknop
4. Test de code

Stap 1) Gebruikersinterface

Voeg een opdrachtknop toe aan het werkblad, zoals hieronder weergegeven

Stel de volgende eigenschappen van CommandButton1 in op de volgende waarden.

Uw interface zou er nu als volgt uit moeten zien

Stap 2) Functiecode.

1. Druk op Alt + F11 om het codevenster te openen
2. Voeg de volgende code toe

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

HIER in de code,

Stap 3) Schrijf Code die de functie aanroept

1. Klik met de rechtermuisknop op de knop 'btnAdd'.Numbers opdrachtknop
2. Selecteer Bekijken Code
3. Voeg de volgende code toe

Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

HIER in de code,

Stap 4) Voer het programma uit en u krijgt de volgende resultaten

Download Excel met bovenstaande code

Download het bovenstaande Excel-bestand. Code

De bovenstaande knop roept de functie aan vanuit VBA-code. Een functie kan ook rechtstreeks vanuit het werkblad worden aangeroepen, zonder dat er een knop nodig is.

Hoe gebruik je een VBA-functie in een werkbladcel?

Een functie die in VBA is geschreven, kan net als SUM of VLOOKUP rechtstreeks in een cel worden getypt. Excel noemt dit een door de gebruiker gedefinieerde functie (UDF), en dit is de reden waarom veel mensen eerst functies leren voordat ze subroutines leren. Er moet aan drie voorwaarden worden voldaan.

• Plaats het in een standaardmodule: Voeg een module in de editor in. Een functie die is opgeslagen achter een werkblad of in ThisWorkbook is niet zichtbaar in de formulebalk.
• Maak het openbaar: Het bovenstaande voorbeeld gebruikt 'Privé', waardoor het verborgen blijft voor Excel. 'Openbaar' is de standaardinstelling, dus het is voldoende om het trefwoord te verwijderen.
• Geef een waarde terug, verander niets: Een UDF (User Defined Function) kan geen cellen formatteren, rijen verwijderen of naar een andere cel schrijven. Excel blokkeert deze acties en de cel toont #WAARDE!.

De onderstaande functie zet een temperatuur om en kan overal op het werkblad worden gebruikt.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Sla het werkblad op als een .xlsm-bestand met macro's en typ vervolgens het volgende: =CelsiusToF(A1) in elke cel. Het resultaat wordt bijgewerkt wanneer A1 verandert en de naam verschijnt in de lijst met formule-aanvullingen onder de categorie 'Gebruikersgedefinieerd'. Omdat het werkblad nu macro's bevat, moet iedereen die het opent de inhoud inschakelen voordat de formule een waarde retourneert in plaats van #NAAM?.

Veelvoorkomende fouten in VBA-functies en hoe u deze kunt oplossen

Vier problemen verklaren het merendeel van de functies die wel compileren, maar een verkeerd antwoord teruggeven.

• De functie retourneert Leeg of 0: Het resultaat werd nooit toegewezen aan de functienaam, of een tak van een If-instructie slaat de toewijzing over. Stel de retourwaarde in op elk pad.
• #NAAM? in een werkbladcel: De functie is privé, bevindt zich in een werkbladmodule in plaats van een standaardmodule, of de werkmap is opgeslagen zonder dat macro's waren ingeschakeld.
• Overloopfout bij argumenten van het type integer: Het voorbeeld gebruikt As Integer, wat stopt bij 32,767. Wijzig zowel de parameters als het retourtype naar Long voor echte gegevens.
• Een gewijzigd argument verrast de beller: Als je ByVal weglaat, geeft VBA de variabele zelf door, waardoor de functie de waarde van de aanroeper kan wijzigen. Schrijf ByVal tenzij je dat effect wilt.

Veelgestelde vragen