Denne opplæringen vil forklare hvordan du oppretter brukerdefinerte funksjoner i VBA.
VBA består av delprosedyrer og funksjonsprosedyrer. Funksjonsprosedyrer returnerer en verdi og kan kalles med delprosedyrer, eller kan brukes i Excel -arket, der verdien som funksjonen produserer, returneres til Excel -arket. Excel har selvfølgelig en rekke innebygde funksjoner - som sumfunksjonen eller If -funksjonen. Funksjonsprosedyrene du skriver i VBA brukes på samme måte som den innebygde funksjonen og kalles User Defined Functions (UDF).
Hvorfor lage en UDF?
Nødvendig funksjon mangler
En av hovedårsakene til at du vil opprette en UDF i Excel er at det ikke er en eksisterende innebygd funksjon som gjør oppgaven for deg. Å skrive din egen funksjon i VBA er vanligvis den mest effektive måten å løse problemet på. Funksjonen nedenfor vil konvertere en verdi fra kilo til pund hvor en variabel parameter (dblKilo) brukes for å få verdien av kilo for å gjøre beregningen.
Erstatt en underrutine (makro)
Du kan skrive en delprosedyre (makro) for å løse oppgaven for deg - men delprosedyrer returnerer ikke en verdi og de er ikke dynamiske - med andre ord, hvis verdiene i regnearket endres, må du kjøre makro for at beregningene i makroen skal oppdatere dataene dine. Delprosedyren nedenfor vil også konvertere kiloene våre til pund, men hver gang dataene endres i A1, må du kjøre makroen på nytt for å oppdatere resultatet.
Bytt ut en formel
Du kan ha en veldig komplisert formel i Excel som du må bruke gjentatte ganger - å sette formelen inn i VBA -koden gjør det lettere å lese og forstå - i tillegg til å fjerne plass for brukerfeil når du skriver formelen.
Opprette UDF
For å opprette en UDF, må du først legge til en modul i enten Excel -arbeidsboken din, eller hvis du har en personlig makro -arbeidsbok, kan du enten bruke en eksisterende modul der eller legge til i en ny. For å gjøre dette må du være i Visual Basic Editor (VBE). Trykk på for å komme til VBE ALT + F11 eller klikk på Visual Basic -alternativet i kategorien Utvikler på båndet.
TIPS: Hvis du ikke har kategorien Utvikler aktivert på båndet, går du til Fil, alternativer og klikk på Tilpass båndet. Kontroller at det er merket av for utvikler, og klikk OK.
For å sette inn en ny modul, velg VBA -prosjektet du vil sette modulen inn i (enten VBA -prosjektet for den gjeldende boken du jobber med, eller Personal Macro Workbook), klikk på Sett inn Meny, og klikk Modul
Når du har opprettet modulen, kan du begynne å lage din UDF.
Alle UDF -er starter med Function og deretter navnet på UDF. Funksjoner kan være private eller offentlige, men vanligvis vil du at en UDF skal være offentlig slik at de vises i dialogboksen Sett inn funksjon i Excel (se bruk av en funksjon fra et Excel -ark lenger ned i denne artikkelen). Hvis du ikke setter det private søkeordet foran funksjonen, blir funksjonen automatisk offentlig.
| 123 | Funksjon TestFunction1 (intA Som Heltall) Som HeltallTestFunction1 = intA * 7Sluttfunksjon |
Funksjonen ovenfor har et enkelt argument (intA). Du kan opprette en funksjon med flere argumenter
| 123 | Funksjon TestFunction2 (intA Som Heltall, intB Som Heltall, intC Som Heltall) Som HeltallTestFunction2 = (intA * intB) + intCSluttfunksjon |
Du kan også opprette en funksjon med valgfrie argumenter. Hvis argumentet utelates, kan du angi en standardverdi for argumentet i funksjonen.
| 123 | Funksjon TestFunction3 (intA Som Heltall, intB Som Heltall, Valgfritt intC Som Heltall = 10) Som HeltallTestFunction3 = (intA * intB) + intCSluttfunksjon |
Bruke en funksjon fra et Excel -ark
Funksjonene du har opprettet vil som standard vises i funksjonslisten i brukerdefinerte delen av funksjonslisten.
Klikk på fx for å vise dialogboksen Sett inn funksjon.
Plukke ut Brukerdefinert fra kategorilisten
Velg funksjonen du trenger fra den tilgjengelige Brukerdefinerte funksjoner.
Alternativt, når du begynner å skrive funksjonen din i Excel, skal funksjonen vises i rullegardinlisten med funksjoner.
Lagre funksjonene med Excel -filen
Ettersom Funksjoner er skrevet i VBA -kode, er det en grunn til at koden må være tilgjengelig for arbeidsboken for å være tilgjengelig for bruk i Excel -arket. Du kan enten lagre funksjonene dine i arbeidsboken du bruker dem i, eller du kan lagre dem i din personlige makro -arbeidsbok. Din personlige makro -arbeidsbok er en skjult fil som er tilgjengelig når Excel er åpent, og derfor tilgjengelig for alle arbeidsbøker i Excel. Den opprettes vanligvis når du spiller inn en makro og velger alternativet for å lagre makroen i Personal Macro Workbook.
Hvis du vil beholde funksjonene dine i arbeidsboken du jobber med, må du sørge for at når du lagrer arbeidsboken, blir den lagret som en ‘Makroaktivert arbeidsbok'Eller en xlsm fil.
