(Aangepaste) door de gebruiker gedefinieerde functies (UDF's) maken

In deze zelfstudie wordt uitgelegd hoe u door de gebruiker gedefinieerde functies in VBA kunt maken.

VBA bestaat uit subprocedures en functieprocedures. Functieprocedures retourneren een waarde en kunnen worden aangeroepen door Subprocedures, of kunnen worden gebruikt in het Excel-blad, waar de waarde die de functie produceert, wordt geretourneerd naar het Excel-blad. Excel heeft natuurlijk een reeks ingebouwde functies - zoals de Som-functie of de If-functie. De functieprocedures die u in VBA schrijft, worden op dezelfde manier gebruikt als de ingebouwde functie en staan ​​bekend als User Defined Functions (UDF's).

Waarom een ​​UDF maken?

Vereiste functie ontbreekt

Een van de belangrijkste redenen om een ​​UDF in Excel te willen maken, is dat er geen bestaande ingebouwde functie is die de taak voor u zal doen. Het schrijven van uw eigen functie in VBA is meestal de meest efficiënte manier om het probleem op te lossen. De onderstaande functie converteert een waarde van kilogram naar pond waarbij een variabele parameter (dblKilo) wordt gebruikt om de waarde van de kilogrammen te verkrijgen om de berekening uit te voeren.

Een subroutine vervangen (macro)

U kunt een subprocedure (macro) schrijven om de taak voor u op te lossen - maar subprocedures retourneren geen waarde en ze zijn niet dynamisch - met andere woorden, als de waarden in uw werkblad veranderen, moet u de macro zodat de berekeningen in de macro uw gegevens bijwerken. De onderstaande subprocedure zou onze kilo's ook naar ponden converteren, maar elke keer dat de gegevens in A1 veranderen, moet u de macro opnieuw uitvoeren om het resultaat bij te werken.

Een formule vervangen

Je hebt misschien een heel gecompliceerde formule in Excel die je herhaaldelijk moet gebruiken - door de formule in VBA-code te plaatsen, wordt het gemakkelijker te lezen en te begrijpen - en misschien is er ook ruimte voor gebruikersfouten bij het typen van de formule.

UDF's maken

Als u een UDF wilt maken, voegt u eerst een module toe aan uw Excel-werkmap of, als u een persoonlijke macro-werkmap hebt, kunt u een bestaande module daarin gebruiken of een nieuwe toevoegen. Om dit te doen, moet u zich in de Visual Basic Editor (VBE) bevinden. Druk op . om naar de VBE te gaan ALT + F11 of klik op de Visual Basic-optie op het tabblad Ontwikkelaars van uw lint.

TIP: Als u het tabblad Ontwikkelaars in uw lint niet hebt ingeschakeld, gaat u naar Bestand, Opties en klik op Lint aanpassen. Zorg ervoor dat het selectievakje Ontwikkelaar is aangevinkt en klik op OK.

Om een ​​nieuwe module in te voegen, selecteert u het VBA-project waarin u de module wilt invoegen (ofwel het VBA-project voor het huidige boek waarin u werkt, of het persoonlijke macrowerkboek), klik op de knop Invoegen Menu, en klik op module

Nadat u uw module hebt gemaakt, kunt u beginnen met het maken van uw UDF.

Alle UDF's beginnen met Function en vervolgens de naam van de UDF. Functies kunnen privé of openbaar zijn, maar normaal gesproken wilt u dat een UDF openbaar is, zodat ze verschijnen in het dialoogvenster Functie invoegen in Excel (zie een functie gebruiken vanuit een Excel-blad verderop in dit artikel). Zet je het sleutelwoord Private niet voor de functie, dan is de functie automatisch openbaar.

123 FunctietestFunctie1(intA als geheel getal) als geheel getalTestFunctie1= intA * 7Functie beëindigen

De functie hierboven heeft een enkel argument (intA). U kunt een functie maken met meerdere argumenten

123 Functie TestFunctie2(intA als geheel getal, intB als geheel getal, intC als geheel getal) als geheel getalTestFunctie2 = (intA * intB) + intCFunctie beëindigen

U kunt ook een functie maken met optionele argumenten. Als het argument wordt weggelaten, kunt u een standaardwaarde instellen voor het argument in de functie.

123 Functie TestFunctie3(intA als geheel getal, intB als geheel getal, optioneel intC als geheel getal=10) als geheel getalTestFunctie3 = (intA * intB) + intCFunctie beëindigen

Een functie gebruiken vanuit een Excel-blad

De functies die u hebt gemaakt, verschijnen standaard in uw functielijst in het gedeelte Door de gebruiker gedefinieerd van de functielijst.

Klik op de fx om het dialoogvenster Functie invoegen weer te geven.

Selecteer Gebruiker gedefinieerde uit de categorielijst

Selecteer de gewenste functie uit de beschikbare Door de gebruiker gedefinieerde functies.

Als alternatief, wanneer u begint met het schrijven van uw functie in Excel, zou de functie in de vervolgkeuzelijst met functies moeten verschijnen.

De functies opslaan met uw Excel-bestand

Omdat functies in VBA-code zijn geschreven, ligt het voor de hand dat de code beschikbaar moet zijn voor de werkmap om beschikbaar te zijn voor gebruik in het Excel-blad. U kunt uw functies ofwel opslaan in de werkmap waarin u ze gebruikt, of u kunt ze opslaan in uw persoonlijke macrowerkmap. Uw persoonlijke macro-werkmap is een verborgen bestand dat beschikbaar is wanneer Excel is geopend en daarom beschikbaar is voor elke werkmap in Excel om te gebruiken. Het wordt normaal gesproken gemaakt wanneer u een macro opneemt en de optie selecteert om de macro op te slaan in de persoonlijke macrowerkmap.

Als u uw functies wilt bewaren in de werkmap waaraan u werkt, moet u ervoor zorgen dat wanneer u de werkmap opslaat, deze wordt opgeslagen als een 'Macro ingeschakeld werkboek' of een xlsm het dossier.

wave wave wave wave wave