Opprette egendefinerte funksjoner i Excel

Egendefinerte funksjoner, for eksempel makroer, bruker programmeringsspråket Visual Basic for Applications (VBA). De skiller seg fra makroer på to viktige måter. Først bruker de Function-prosedyrer i stedet for Sub-prosedyrer . Det vil se ut som om de starter med en Function-setning i stedet for en Sub-setning og slutter med End Function i stedet for End Sub. For det andre utfører de beregninger i stedet for å utføre handlinger. Enkelte typer setninger, for eksempel setninger som merker og formaterer områder, utelates fra egendefinerte funksjoner. I denne artikkelen lærer du hvordan du oppretter og bruker egendefinerte funksjoner. Hvis du vil opprette funksjoner og makroer, arbeider du med Visual Basic Redaktør (VBE), som åpnes i et nytt vindu atskilt fra Excel.

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

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

Følg disse trinnene for å opprette en egendefinert RABATT-funksjon i denne arbeidsboken:

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

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
   

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

=RABATT(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, Function DISCOUNT(quantity, price), indikerte du at DISKONTERT-funksjonen krever to argumenter, antall og pris. Når du kaller funksjonen i en regnearkcelle, må du inkludere disse to argumentene. I formelen =DISKONTERT(D7;E7) er D7 argumentet antall , og E7 er prisargumentet . 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 ENTER, ser Excel etter navnet RABATT i gjeldende arbeidsbok og finner ut at det er en egendefinert funksjon i en VBA-modul. Argumentnavnene som er omsluttet av parenteser, 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 bestemmer 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, kjører VBA følgende setning, som multipliserer antallsverdien med prisverdien og multipliserer deretter resultatet med 0,1:

Discount = quantity * price * 0.1

Resultatet lagres som variabelen Rabatt. En VBA-setning som lagrer en verdi i en variabel, kalles en tildelingssetning fordi den evaluerer uttrykket på høyre side av 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 DISKONTERT-funksjonen.

Hvis antallet er mindre enn 100, kjører VBA følgende setning:

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA har ingen AVRUND-funksjon, men Excel gjør det. Hvis du vil bruke AVRUND i denne setningen, ber du derfor VBA om å søke etter round-metoden (funksjon) i Programobjekt (Excel). Du gjør dette ved å legge til ordet Program før ordet Rund. Bruk denne syntaksen når du trenger tilgang til en Excel-funksjon fra en VBA-modul.

En egendefinert funksjon må starte med en Function-setning og avslutte med en End Function-setning. I tillegg til funksjonsnavnet angir Function-setningen vanligvis ett eller flere argumenter. Du kan imidlertid opprette en funksjon uten argumenter. Excel inneholder flere innebygde funksjoner – rand og nå, for eksempel – som ikke bruker argumenter.

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

Antallet VBA-nøkkelord du kan bruke i egendefinerte funksjoner, er mindre enn tallet du kan bruke i makroer. Egendefinerte funksjoner har ikke lov 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 alternativene for skrift, farge eller mønster for teksten i en celle. Hvis du inkluderer «handling»-kode av denne typen i en funksjonsprosedyre, returnerer funksjonen #VALUE! -feil.

Den ene handlingen en funksjonsprosedyre kan gjøre (bortsett fra å utføre beregninger), er å vise en dialogboks. Du kan bruke en InputBox-setning i en egendefinert funksjon som et middel til å hente inndata fra brukeren som utfører funksjonen. Du kan bruke en MsgBox-setning som et middel til å formidle informasjon til brukeren. Du kan også bruke egendefinerte dialogbokser eller UserForm-skjemaer, men det er et emne utenfor omfanget av denne introduksjonen.

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. Eksemplet nedenfor viser RABATT-funksjonen med kommentarer. Når du legger til kommentarer som disse, blir det enklere for deg eller andre å vedlikeholde VBA-koden etter hvert som tiden går. Hvis du trenger å gjøre en endring i koden i fremtiden, får du en enklere tid til å forstå hva du gjorde opprinnelig.

En apostrof ber Excel ignorere alt til høyre på samme linje, slik at du kan opprette kommentarer enten på linjer alene eller på høyre side av linjene som inneholder VBA-kode. Du kan starte en relativt lang kodeblokk med en kommentar som forklarer det generelle formålet, og deretter bruke innebygde kommentarer til å dokumentere enkeltutsagn.

En annen måte å dokumentere makroer og egendefinerte funksjoner på, er å gi dem beskrivende navn. I stedet for å gi navn til en makroetikett, kan du for eksempel kalle den MonthLabels for å beskrive mer spesifikt formålet makroen tjener. Det er spesielt nyttig å bruke beskrivende navn for 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 personlige preferanser. Det som er viktig er å ta i bruk en metode for dokumentasjon og bruke den konsekvent.

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

Du kan lagre 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 de egendefinerte funksjonene tilgjengelige til enhver tid på, er å lagre dem i en egen arbeidsbok og deretter lagre arbeidsboken som et tillegg. Deretter kan du gjøre tillegget tilgjengelig når du kjører Excel. Slik gjør du dette:

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

Hvis du dobbeltklikker modulen i Project Explorer, får Visual Basic-Redaktør til å vise funksjonskoden. Hvis du vil legge til en ny funksjon, plasserer du innsettingspunktet etter End Function-setningen som avslutter den siste funksjonen i Kode-vinduet, og begynner å skrive. Du kan opprette så mange funksjoner du trenger på denne måten, og de vil alltid være tilgjengelige i kategorien Brukerdefinert i dialogboksen Sett inn funksjon .

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