Opprette egendefinerte funksjoner i Excel

Selv om Excel inneholder en rekke innebygde regnearkfunksjoner, kan det hende det ikke har en funksjon for hver beregningstype du utfører. Designerne av Excel kunne umulig forutse alle brukernes beregningsbehov. I stedet gir Excel deg muligheten til å opprette egendefinerte funksjoner, som er beskrevet i denne artikkelen.

Opprette en enkel, egendefinert funksjon

Egendefinerte funksjoner, for eksempel makroer, bruker programmeringsspråket Visual Basic for Applications (VBA). De skiller seg fra makroer på to viktige måter. For det første bruker de funksjonsprosedyrer i stedet for underprosedyrer . Det vil si at de begynner med en Function-setning i stedet for en Sub-setning og slutter med End Function i stedet for End Sub. Deretter utfører de beregninger i stedet for å utføre handlinger. Bestemte typer setninger, for eksempel setninger som velger og formaterer områder, utelates fra egendefinerte funksjoner. I denne artikkelen lærer du hvordan du oppretter og bruker egendefinerte funksjoner. Når du skal opprette funksjoner og makroer, arbeider du med Visual Basic Editor (VBE), som åpnes i et nytt vindu uavhengig av Excel.

La oss si at firmaet tilbyr en antallsrabatt på 10 prosent på salg av et produkt, forutsatt at bestillingen er på mer enn 100 enheter. I de følgende avsnittene demonstrerer vi en funksjon for å beregne denne rabatten.

Eksemplet nedenfor viser et ordreskjema som viser hver vare, antall, pris, rabatt (hvis aktuelt) og den resulterende utvidede prisen.

Gjør følgende for å opprette en egendefinert RABATT-funksjon i denne arbeidsboken:

1. Trykk ALT+F11 for å åpne Visual Basic Editor (på Mac trykker du FN+ALT+F11), og klikk deretter Sett inn>modul. Et nytt modulvindu vises på høyre side av Visual Basic Editor.

2. Kopier og lim inn følgende kode i den nye modulen.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

Bruke egendefinerte funksjoner

Nå er du klar til å bruke den nye RABATT-funksjonen. Lukk Visual Basic Editor, merk celle G7 og skriv inn følgende:

=DISKONTER(D7,E7)

Excel beregner rabatten på 10 prosent på 200 enheter til kr 47,50 per enhet, og returnerer kr 950,00.

I den første linjen i VBA-koden, Funksjon RABATT(antall, pris), angav du at RABATT-funksjonen krever to argumenter, antall og pris. Når du kaller funksjonen i en regnearkcelle, må du inkludere disse to argumentene. I formelen =DISKONTO(D7,E7) er D7 antall-argumentet, og E7 er pris-argumentet. Nå kan du kopiere RABATT-formelen til G8:G13 for å få resultatene vist nedenfor.

La oss vurdere hvordan Excel tolker denne funksjonsprosedyren. Når du trykker på ENTER, ser Excel etter navnet RABATT i gjeldende arbeidsbok, og finner ut at det er en egendefinert funksjon i en VBA-modul. Argumentnavnene i parentes, antall og pris, er plassholdere for verdiene som beregningen av rabatten er basert på.

Hvis-setningen i følgende kodeblokk undersøker antall-argumentet og fastslår om antall solgte varer er større enn eller lik 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Hvis antall solgte varer er større enn eller lik 100, utfører VBA følgende setning, som multipliserer antall-verdien med prisverdien og deretter multipliserer resultatet med 0,1:

Discount = quantity * price * 0.1

Resultatet lagres som den variable rabatten. Et VBA-uttrykk som lagrer en verdi i en variabel, kalles et tildelingsuttrykk fordi det evaluerer uttrykket til høyre for likhetstegnet, og tilordner resultatet til variabelnavnet til venstre. Fordi variabelen Rabatt har samme navn som funksjonsprosedyren, returneres verdien som er lagret i variabelen, til regnearkformelen som kalles RABATT-funksjonen.

Hvis antall er mindre enn 100, utfører VBA følgende setning:

Discount = 0

Til slutt runder følgende setning verdien som er tilordnet til diskonteringsvariabelen til to desimaler:

Discount = Application.Round(Discount, 2)

VBA har ingen AVRUND-funksjon, men Excel har det. Hvis du vil bruke AVRUND i denne setningen, ber du derfor VBA om å se etter avrundingsmetoden (funksjon) i programobjektet (Excel). Det gjør du ved å legge til ordet Søknad før ordet Avrund. Bruk denne syntaksen når du trenger tilgang til en Excel-funksjon fra en VBA-modul.

Forstå regler for egendefinerte funksjoner

En egendefinert funksjon må starte med en Function-setning og slutte med en End Function-setning. I tillegg til funksjonsnavnet spesifiserer funksjonssetningen vanligvis ett eller flere argumenter. Du kan imidlertid opprette en funksjon uten argumenter. Excel inneholder flere innebygde funksjoner, for eksempel TILFELDIG og NÅ – som ikke bruker argumenter.

Etter funksjonsuttrykket inneholder en funksjonsprosedyre én eller flere VBA-setninger som tar avgjørelser og utfører beregninger ved hjelp av argumentene som sendes til funksjonen. Til slutt, et eller annet sted i funksjonsprosedyren, må du inkludere et uttrykk som tildeler en verdi til en variabel med samme navn som funksjonen. Denne verdien returneres til formelen som kaller opp funksjonen.

Bruke VBA-nøkkelord i egendefinerte funksjoner

Antallet VBA-nøkkelord du kan bruke i egendefinerte funksjoner, er mindre enn antallet du kan bruke i makroer. Egendefinerte funksjoner har ikke tillatelse til å gjøre noe annet enn å returnere en verdi til en formel i et regneark eller til et uttrykk som brukes i en annen VBA-makro eller -funksjon. Egendefinerte funksjoner kan for eksempel ikke endre størrelsen på vinduer, redigere en formel i en celle eller endre alternativer for skrift, farge eller mønster for teksten i en celle. Hvis du inkluderer denne typen handlingskode i en funksjonsprosedyre, returnerer funksjonen #VALUE! -feil.

Den eneste handlingen en funksjon kan utføre (bortsett fra å utføre beregninger), er å vise en dialogboks. Du kan bruke en InputBox-setning i en egendefinert funksjon for å få inndata fra brukeren som utfører funksjonen. Du kan bruke en MsgBox-setning til å formidle informasjon til brukeren. Du kan også bruke egendefinerte dialogbokser eller UserForm-skjemaer, men dette er et emne som ligger utenfor omfanget av denne innføringen.

Dokumentere makroer og egendefinerte funksjoner

Selv enkle makroer og egendefinerte funksjoner kan være vanskelige å lese. Du kan gjøre dem enklere å forstå ved å skrive inn forklarende tekst i form av kommentarer. Du legger til kommentarer ved å sette en apostrof foran den forklarende teksten. Det følgende eksemplet viser for eksempel RABATT-funksjonen med kommentarer. Hvis du legger til kommentarer som disse, blir det enklere for deg eller andre å vedlikeholde VBA-koden etter hvert som tiden går. Hvis du må gjøre en endring i koden i fremtiden, vil du ha lettere for å forstå hva du gjorde opprinnelig.

En apostrof forteller Excel at alt til høyre på samme linje skal ignoreres, slik at du kan opprette kommentarer enten på linjer alene eller på høyre side av linjer som inneholder VBA-kode. Du kan begynne en relativt lang kodeblokk med en kommentar som forklarer det overordnede formålet, og deretter bruke innebygde kommentarer til å dokumentere individuelle setninger.

En annen måte å dokumentere makroer og egendefinerte funksjoner på, er å gi dem beskrivende navn. I stedet for å gi en makro navnet Etiketter, kan du for eksempel gi den navnet Månedsetiketter for å beskrive mer spesifikt formålet makroen. Det er spesielt nyttig å bruke beskrivende navn på makroer og egendefinerte funksjoner når du har opprettet mange prosedyrer, spesielt hvis du oppretter prosedyrer som har lignende, men ikke identiske formål.

Hvordan du dokumenterer makroer og egendefinerte funksjoner er et spørsmål om personlig preferanse. Det som er viktig er å ta i bruk en metode for dokumentasjon, og bruke den konsekvent.

Gjør de egendefinerte funksjonene tilgjengelige hvor som helst

Hvis du vil bruke en egendefinert funksjon, må arbeidsboken som inneholder modulen som du opprettet funksjonen i, være åpen. Hvis arbeidsboken ikke er åpen, får du en #NAME? -feilen når du prøver å bruke funksjonen. Hvis du refererer til funksjonen i en annen arbeidsbok, må du sette navnet på arbeidsboken der funksjonen befinner seg foran funksjonsnavnet. Hvis du for eksempel oppretter en funksjon kalt RABATT i en arbeidsbok kalt Personal.xlsb, og du kaller denne funksjonen fra en annen arbeidsbok, må du skrive =personal.xlsb!discount(), ikke bare =discount().

Du kan spare deg selv for noen tastetrykk (og mulige skrivefeil) ved å velge de egendefinerte funksjonene fra dialogboksen Sett inn funksjon. De egendefinerte funksjonene vises i kategorien Brukerdefinert:

En enklere måte å gjøre egendefinerte funksjoner tilgjengelige hele tiden på, er å lagre dem i en egen arbeidsbok og deretter lagre denne arbeidsboken som et tillegg. Deretter kan du gjøre tillegget tilgjengelig når du kjører Excel. Slik gjør du det:

Når du har gjennomført disse trinnene, blir de egendefinerte funksjonene tilgjengelige hver gang du kjører Excel. Hvis du vil legge noe til i funksjonsbiblioteket, går du tilbake til Visual Basic Editor. Hvis du ser under en VBAProject-overskrift i Visual Basic Editor Project Explorer, ser du en modul oppkalt etter tilleggsfilen. Tillegget har filtypen XLAM.

Hvis du dobbeltklikker på denne modulen i Prosjektutforsker, viser Visual Basic Editor funksjonskoden. Hvis du vil legge til en ny funksjon, plasserer du innsettingspunktet etter end-funksjon-setningen som avslutter den siste funksjonen i kodevinduet, og begynner å skrive. Du kan opprette så mange funksjoner du trenger på denne måten, og de vil alltid være tilgjengelige på fanen Brukerdefinert i dialogboksen Sett inn funksjon .

Om forfatterne

Dette innholdet ble opprinnelig skrevet av Mark Dodge og Craig Stinson som en del av deres bok Microsoft Office Excel 2007 Inside Out. Den har siden blitt oppdatert for å gjelde nyere versjoner av Excel også.

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excels tekniske fellesskap eller få støtte i fellesskap.