Hoewel Excel al honderden ingebouwde functies heeft, zoals SOM, VERT. ZOEKEN, LINKS, enzovoort, zijn de beschikbare ingebouwde functies meestal niet voldoende om redelijk complexe taken uit te voeren. Maak je echter geen zorgen, want je hoeft alleen de benodigde functies zelf aan te maken.
Stap
Stap 1. Maak een nieuwe werkmap of open de werkmap die u wilt verwerken met User Defined Functions (UDF)
Stap 2. Open de Visual Basic Editor in Microsoft Excel via Extra->Macro->Visual Basic Editor (of druk op de sneltoets Alt+F11)
Stap 3. Klik op de knop Module om een nieuwe module aan uw werkblad toe te voegen
U kunt een UDF in een werkmap maken zonder een nieuwe module toe te voegen, maar de functie werkt niet in andere werkbladen in dezelfde werkmap.
Stap 4. Maak de "head" of "prototype" van uw functie
Het functie-prototype moet de volgende structuur volgen:
openbare functie "Functienaam" (parameter1 Als type1, parameter2 Als type2) Als resultaattype.
Prototypes kunnen zoveel mogelijk functies hebben en hun typen kunnen alle basisgegevenstypen zijn of Excel-objecttypen in de vorm van Bereik. U kunt parameters beschouwen als "operanten" (operators) waarop de functie zal reageren. Als u bijvoorbeeld SIN(45) schrijft om de sinus van 45 graden te berekenen, wordt het getal 45 als parameter genomen. Vervolgens gebruikt de functiecode die waarden om berekeningen uit te voeren en de resultaten weer te geven.
Stap 5. Voeg de functiecode toe om ervoor te zorgen dat u: 1) de waarde gebruikt die door de parameter wordt gegeven; 2) geef het resultaat door aan de functienaam; en 3) sluit de functie af met de zin " end function ". Leren programmeren in VBA of in een andere taal kost veel tijd en gedetailleerde begeleiding. Gelukkig hebben deze functies meestal kleine codeblokken en maken ze niet veel gebruik van programmeertaalfuncties. Hier zijn enkele elementen van de VBA-taal die kunnen worden gebruikt:
- Het If (if)-blok, waarmee u een deel van de code alleen kunt uitvoeren als aan de voorwaarde is voldaan. Als voorbeeld:
- . U kunt het Else-sleutelwoord samen met het tweede deel van de code weglaten omdat dit optioneel is.
- Het Do (do)-blok, dat een deel van de While- of Tot-code uitvoert wanneer of totdat aan de voorwaarde is voldaan. Als voorbeeld:
- . Let ook op de tweede regel die de variabele "declareert". U kunt variabelen aan uw code toevoegen voor later gebruik. Variabelen fungeren als tijdelijke waarden in de code. Beschouw ten slotte de functiedeclaratie als BOOLEAN, een gegevenstype dat alleen TRUE of FALSE-waarden toestaat. Deze methode om priemgetallen te bepalen is verre van optimaal, maar de code is zo geschreven dat hij goed leesbaar is.
- voor blok (to), die een bepaalde hoeveelheid code uitvoert. Als voorbeeld:
- Een constante waarde die rechtstreeks in de celformule wordt getypt. In dit geval moet de tekst (string) tussen aanhalingstekens staan.
- Celverwijzingen, bijvoorbeeld B6 of bereik zoals A1:C3 (parameter moet gegevenstype " Range " zijn)
-
Een andere functie die in uw functie is ingesloten (uw functie kan ook in een andere functie zijn ingesloten), bijvoorbeeld: =Factoriaal(MAX(D6:D8))
Maak een door de gebruiker gedefinieerde functie in Microsoft Excel Stap 7 Stap 7. Zorg ervoor dat de resultaten correct zijn
Gebruik het meerdere keren om ervoor te zorgen dat de functie verschillende parameterwaarden correct kan verwerken:
Tips
- Wanneer u codeblokken schrijft in besturingsstructuren zoals If, For, Do, enz., zorg er dan voor dat u het codeblok inspringt (voeg de linkerlijnrand iets naar binnen toe) door meerdere keren op de spatiebalk te drukken, of tab. Dit maakt de code gemakkelijker te begrijpen en fouten zijn veel gemakkelijker te vinden. Daarnaast wordt de verhoging van de functionaliteit makkelijker te maken.
- Als u niet weet hoe u code voor functies moet schrijven, lees dan het artikel Een eenvoudige macro schrijven in Microsoft Excel.
- Soms hebben functies niet alle parameters nodig om het resultaat te berekenen. In dit geval kunt u het sleutelwoord Optioneel gebruiken vóór de parameternaam in de functiekop. U kunt de functie IsMissing(parameter_name) in uw code gebruiken om te bepalen of aan een parameter een waarde is toegewezen of niet.
- Gebruik ongebruikte namen als functies in Excel, zodat er geen functies worden overschreven en verwijderd.
- Excel heeft veel ingebouwde functies en de meeste berekeningen kunnen worden uitgevoerd met behulp van deze ingebouwde functies, afzonderlijk of allemaal tegelijk. Zorg ervoor dat je de lijst met beschikbare functies bekijkt voordat je zelf gaat coderen. Uitvoering kan sneller als u ingebouwde functies gebruikt.
Waarschuwing
- Om veiligheidsredenen schakelen veel mensen macro's uit. Zorg ervoor dat u uw werkmapontvangers op de hoogte stelt dat de ingediende werkmap macro's bevat en dat deze macro's hun computers niet schaden.
- De functie die in dit artikel wordt gebruikt, is niet de beste manier om het gerelateerde probleem op te lossen. Het voorbeeld wordt gebruikt om het gebruik van taalcontrolestructuren uit te leggen.
- VBA heeft, net als andere talen, verschillende andere besturingsstructuren naast Do, If en For. De hier besproken structuur beschrijft alleen wat er in de broncode van de functie kan worden gedaan. Er zijn veel handleidingen op internet die kunnen worden gebruikt om u te helpen VBA te leren.
Cursusresultaat openbare functie (als geheel getal) As String
Als waarde >= 5 Dan
Cursusresultaten = "Geaccepteerd"
Anders
Cursusresultaten = "Afgewezen"
Stop als
Functie beëindigen
Let op de elementen in het If-codeblok:
IF conditie THEN code ELSE code END IF
Publieke functie BilPrima(waarde als geheel getal) As Boolean
Dim ik als geheel getal
ik = 2
BilPrima = True
Doen
Als waarde / i = Int(waarde / i) Dan
BilPrima = Onwaar
Stop als
ik = ik + 1
Loop While i < value And NumberPrima = True
Functie beëindigen
Kijk nog eens naar de elementen:
DO code LOOP WHILE/UNTIL conditie
Public Function Factorial (waarde als geheel getal) Zo lang
Dim resultaten Zo lang
Dim ik als geheel getal
Als waarde = 0 Dan
resultaat = 1
ElseIf-waarde = 1 Dan
resultaat = 1
Anders
resultaat = 1
Voor i = 1 Naar waarde
resultaat= resultaat * i
Volgende
Stop als
Faculteit = resultaat
Functie beëindigen
Kijk nog eens naar de elementen:
FOR variabele = ondergrens TO bovengrens van code NEXT
. Let ook op het extra ElseIf-element in de If-instructie, waarmee u meer opties kunt toevoegen aan de code die wordt uitgevoerd. Overweeg ten slotte de functie "resultaat" en de variabele die is gedeclareerd als Long. Het gegevenstype Long staat veel grotere waarden toe dan Integer.
Hieronder ziet u de code voor een functie die kleine getallen omzet in woorden.
Stap 6. Keer terug naar de werkmap en gebruik de functie door het symbool "is gelijk aan" (=) gevolgd door de naam van de functie in de cel te schrijven
Schrijf de openingshaakjes (“(“) achter de functienaam, met het teken coma om de parameters te scheiden, en eindigen met haakjes sluiten (“)”). Als voorbeeld:
= Getal Naar Letter (A4)
. U kunt ook zelfgemaakte formules gebruiken door ernaar te zoeken in categorieën Gebruiker gedefinieerde binnen de optie Formule invoegen. Je klikt gewoon op de knop FX links van de formulebalk. Er zijn drie soorten parametervormen in functies:
