Sådan oprettes brugerdefinerede funktioner i Microsoft Excel

2024 Forfatter: Jason Gerald | [email protected]. Sidst ændret: 2023-12-16 11:02

Selvom Excel allerede har hundredvis af indbyggede funktioner som SUM, VLOOKUP, LEFT og så videre, er de tilgængelige indbyggede funktioner normalt ikke tilstrækkelige nok til at udføre ret komplekse opgaver. Du skal dog ikke bekymre dig, for du behøver kun at oprette de nødvendige funktioner selv.

Trin

Trin 1. Opret en ny projektmappe, eller åbn den projektmappe, du vil behandle med User Defined Functions (UDF)

Trin 2. Åbn Visual Basic Editor i Microsoft Excel via Værktøjs-> Makro-> Visual Basic Editor (eller tryk på genvejen Alt+F11)

Trin 3. Klik på knappen Modul for at tilføje et nyt modul til dit regneark

Du kan oprette en UDF i en projektmappe uden at tilføje et nyt modul, men funktionen fungerer ikke i andre regneark i den samme projektmappe.

Trin 4. Opret "hovedet" eller "prototypen" af din funktion

Funktionsprototypen skal følge følgende struktur:

offentlig funktion "Funktionsnavn" (parameter1 Som type1, parameter2 Som type2) Som Resultattype.

Prototyper kan have så mange funktioner som muligt, og deres typer kan være alle grundlæggende datatyper eller Excel -objekttyper i form af område. Du kan tænke på parametre som "operanter" (operatører), som funktionen vil handle på. For eksempel, når du skriver SIN (45) for at beregne sinus på 45 grader, bliver tallet 45 taget som en parameter. Derefter vil funktionskoden bruge disse værdier til at udføre beregninger og vise resultaterne.

Trin 5. Tilføj funktionskoden for at sikre, at du: 1) bruger værdien givet af parameteren; 2) videregive resultatet til funktionsnavnet; og 3) luk funktionen med sætningen "slutfunktion". At lære at programmere i VBA eller på et andet sprog tager meget tid og detaljeret vejledning. Heldigvis har disse funktioner normalt små kodeblokke og gør ikke meget brug af programmeringssprogsfunktioner. Her er nogle elementer i VBA -sproget, der kan bruges:

1. If (if) -blokken, som kun giver dig mulighed for at udføre en del af koden, hvis betingelsen er opfyldt. Som et eksempel:

Public Function Course Result (As Integer value) As String

Hvis værdi> = 5 Så

Kursusresultater = "Accepteret"

Andet

Kursusresultater = "Afvist"

Afslut Hvis

Afslut funktion

Bemærk elementerne i If -kodeblokken:

HVIS tilstand DERNE kode ELSE -kode END HVIS

2. . Du kan udelade Else -søgeordet sammen med den anden del af koden, da det er valgfrit.
3. Do (do) -blokken, der udfører en del af While eller indtil -koden, når eller indtil betingelsen er opfyldt. Som et eksempel:

Offentlig funktion BilPrima (værdi som heltal) Som boolsk

Dim i As Integer

jeg = 2

BilPrima = Sandt

Gør

Hvis værdi / i = Int (værdi / i) Så

BilPrima = Falsk

Afslut Hvis

i = i + 1

Loop While i <value And NumberPrima = True

Afslut funktion

Se igen på elementerne:

GØR kode LOOP WHILE/UNTIL condition

4. . Bemærk også den anden linje, der "erklærer" variablen. Du kan tilføje variabler til din kode til senere brug. Variabler fungerer som midlertidige værdier i koden. Betragt endelig funktionserklæringen som BOOLEAN, som er en datatype, der kun tillader SAND eller FALSK værdier. Denne metode til at bestemme primtal er langt fra optimal, men koden er skrevet på en sådan måde, at den er let at læse.
5. Til blok (til), som udfører en vis mængde kode. Som et eksempel:

Offentlig funktionsfaktoriel (værdi som heltal) Så længe

Dim resultater så længe

Dim i As Integer

Hvis værdi = 0 Så

resultat = 1

ElseIf value = 1 Herefter

resultat = 1

Andet

resultat = 1

For i = 1 Til værdi

resultat = resultat * i

Næste

Afslut Hvis

Factorial = resultat

Afslut funktion

Se igen på elementerne:

FOR variabel = nedre grænse TIL øvre grænse for kode NÆSTE

. Bemærk også det ekstra ElseIf -element i If -sætningen, som giver dig mulighed for at tilføje flere muligheder til den kode, der udføres. Overvej endelig funktionen "resultat" og variabel erklæret som lang. Lang datatype tillader meget større værdier end heltal.

Nedenfor vises koden for en funktion, der konverterer små tal til ord.



Trin 6. Gå tilbage til projektmappen, og brug funktionen ved at skrive symbolet "lig" (=) efterfulgt af navnet på funktionen i cellen

Skriv åbningsbeslagene (“(“) efter funktionsnavnet ved hjælp af tegnet koma for at adskille parametrene og slutte med lukkebeslag (“)”). Som et eksempel:

= NumberToLetter (A4)

. Du kan også bruge hjemmelavede formler ved at søge efter dem i kategorier Brugerdefineret inde i indstillingen Indsæt formel. Du klikker bare på knappen Fx til venstre for formellinjen. Der er tre typer parameterformer i funktioner:

1. En konstant værdi, der indtastes direkte i celleformlen. I dette tilfælde skal teksten (strengen) citeres.
2. Cellehenvisninger, for eksempel B6 eller rækkevidde som A1: C3 (parameteren skal være datatypen "Range")

3. En anden funktion, der er indeholdt i din funktion (din funktion kan også indesluttes i en anden funktion), for eksempel: = Factorial (MAX (D6: D8))

   

   Trin 7. Sørg for, at resultaterne er korrekte

   Brug den flere gange for at sikre, at funktionen er i stand til at håndtere forskellige parameterværdier korrekt:

   Tips

   • Når du skriver kodeblokke i kontrolstrukturer såsom If, For, Do osv., Skal du sørge for at indrykke (indsæt den venstre liniekant lidt inden i) kodeblokken ved at trykke på mellemrumstasten flere gange eller fanen. Dette vil gøre koden lettere at forstå, og fejl vil være meget lettere at finde. Derudover bliver stigningen i funktionalitet lettere at foretage.
   • Hvis du ikke ved, hvordan du skriver kode til funktioner, kan du læse artiklen Sådan skriver du en simpel makro i Microsoft Excel.
   • Nogle gange behøver funktioner ikke alle parametrene for at beregne resultatet. I dette tilfælde kan du bruge det valgfrie søgeord før parameternavnet i funktionsoverskriften. Du kan bruge funktionen IsMissing (parameternavn) i din kode til at afgøre, om en parameter er tildelt en værdi eller ej.
   • Brug ubrugte navne som funktioner i Excel, så ingen funktioner overskrives og slettes.
   • Excel har mange indbyggede funktioner, og de fleste beregninger kan udføres ved hjælp af disse indbyggede funktioner, enten individuelt eller alle på én gang. Sørg for at tage et kig på listen over tilgængelige funktioner, før du selv begynder at kode. Udførelse kan udføres hurtigere, hvis du bruger indbyggede funktioner.

   Advarsel

   • Af sikkerhedsmæssige årsager deaktiverer mange mennesker makroer. Sørg for at underrette dine projektmappemodtagere om, at den indsendte projektmappe har makroer, og at disse makroer ikke skader deres computere.
   • Funktionen, der bruges i denne artikel, er ikke den bedste måde at løse det relaterede problem på. Eksemplet bruges til at forklare brugen af sprogkontrolstrukturer.
   • VBA har ligesom andre sprog flere andre kontrolstrukturer udover Do, If og For. Strukturen, der diskuteres her, beskriver kun, hvad der kan gøres i funktionens kildekode. Der er mange guider på internettet, der kan bruges til at hjælpe dig med at lære VBA.