Opret brugerdefinerede funktioner i Excel

Brugerdefinerede funktioner, f.eks. makroer, bruger VBA-programmeringssproget (Visual Basic for Applications). De adskiller sig fra makroer på to væsentlige måder. Først bruger de Function-procedurer i stedet for Sub-procedurer . Det betyder, at de starter med en Function-sætning i stedet for en Sub-sætning og slutter med End Function i stedet for End Sub. For det andet udfører de beregninger i stedet for at udføre handlinger. Visse typer sætninger, f.eks. sætninger, der markerer og formaterer områder, udelades fra brugerdefinerede funktioner. I denne artikel lærer du, hvordan du opretter og bruger brugerdefinerede funktioner. Hvis du vil oprette funktioner og makroer, skal du arbejde med Visual Basic Editor (VBE), som åbnes i et nyt vindue, der er adskilt fra Excel.

Antag, at dit firma tilbyder mængderabat på 10 procent af salget af et produkt, forudsat, at ordren er på mere end 100 enheder. I de følgende afsnit viser vi dig en funktion til at beregne denne rabat.

I eksemplet nedenfor vises en ordreformular, der viser hvert element, antal, pris, rabat (hvis relevant) og den resulterende udvidede pris.

Hvis du vil oprette en brugerdefineret RABAT-funktion i projektmappen, skal du følge disse trin:

1. Tryk på Alt+F11 for at åbne Visual Basic Editor (på Mac skal du trykke på FN+ALT+F11), og klik derefter på Indsæt > Modul. Der vises et nyt modulvindue i højre side af Visual Basic Editor.

2. Kopiér og indsæt følgende kode for det nye modul.

   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
   

Nu er du klar til at bruge den nye RABAT-funktion. Luk Visual Basic Editor, markér celle G7, og indtast følgende:

=RABAT(D7,E7)

Excel beregner rabatten på 10 % på 200 enheder til 47,50 USD pr. enhed og returnerer 950,00 USD.

I den første linje i din VBA-kode, funktionen RABAT(antal, pris), angav du, at funktionen RABAT kræver to argumenter, Antal og Pris. Når du kalder funktionen i en celle i regnearket, skal du medtage disse to argumenter. I formlen = RABAT(D7,E7) er D7 argumentet Antal, og E7 er argumentet Pris. Du kan nu kopiere RABAT-formlen til G8:G13 for at få de resultater, der er vist nedenfor.

Lad os se nærmere på, hvordan Excel fortolker denne function-procedure. Når du trykker på Enter, søger Excel efter navnet RABAT i den aktuelle projektmappe og finder, at det er en brugerdefineret funktion i et VBA-modul. Argumentnavnene i parentes, Antal og Pris, er pladsholdere for de værdier, som beregningen af rabatten er baseret på.

Hvis-sætningen i følgende kodeblok undersøger argumentet Antal og bestemmer, om antallet af solgte varer er større end eller lig med 100:

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

Hvis antallet af solgte varer er større end eller lig med 100, udfører VBA følgende sætning, der multiplicerer værdien af antallet med værdien af pris, og multiplicerer derefter resultatet med 0,1:

Discount = quantity * price * 0.1

Resultatet er gemt som variablen Rabat. En VBA-sætning, der indeholder en værdi i en variabel, kaldes et tilknytningsudtryk, fordi det evaluerer udtrykket i højre side af lighedstegnet og tildeler resultatet til variabelnavnet til venstre. Da variablen Rabat har samme navn som function-proceduren, returneres værdien, der er gemt i variablen til regnearksformlen, der kaldes RABAT-funktionen.

Hvis Antal er mindre end 100, udfører VBA følgende sætning:

Discount = 0

Til sidst afrunder følgende sætning værdien, der er tildelt variablen Rabat til to decimalpladser:

Discount = Application.Round(Discount, 2)

VBA indeholder ikke funktionen AFRUND, men det gør Excel. For at bruge AFRUND i denne sætning skal du derfor bede VBA om at søge efter metoden Afrund (funktion) i programobjektet (Excel). Det gør du ved at tilføje ordet Program før ordet Afrund. Når du vil åbne en Excel-funktion i et VBA-modul, skal du bruge denne syntaks.

En brugerdefineret funktion skal starte med en Function-sætning og slutte med en End Function-sætning. Ud over funktionsnavnet angiver Function-sætningen normalt et eller flere argumenter. Du kan dog oprette en funktion uden argumenter. Excel indeholder flere indbyggede funktioner – f.eks. SLUMP og NU – der ikke bruger argumenter.

Efter Function-sætningen indeholder en function-procedure én eller flere VBA-sætninger, der træffer beslutninger og udfører beregninger ved hjælp af de argumenter, der videregives til funktionen. Til sidst skal du medtage en sætning et sted i function-proceduren, der tildeler en værdi til en variabel med samme navn som funktionen. Denne værdi returneres til formlen, der kalder funktionen.

Antallet af VBA-nøgleord, du kan bruge i brugerdefinerede funktioner, er mindre end antallet, du kan bruge i makroer. Brugerdefinerede funktioner har ikke tilladelse til at gøre andet end at returnere en værdi til en formel i et regneark eller til et udtryk, der bruges i en anden VBA-makro eller -funktion. Brugerdefinerede funktioner kan f.eks. ikke ændre størrelsen på vinduer, redigere en formel i en celle eller ændre skrifttype, farve eller mønsterindstillinger for teksten i en celle. Hvis du medtager koden "handling" for denne type i en function-procedure, returnerer funktionen fejlen #VÆRDI!.

Den eneste handling, en function-procedure kan udrette (udover at udføre beregninger), er at vise en dialogboks. Du kan bruge en InputBox-sætning i en brugerdefineret funktion som en metode til at få input fra brugeren, der kører funktionen. Du kan bruge en MsgBox-sætning som en metode til at formidle oplysninger til brugeren. Du kan også bruge brugerdefinerede dialogbokse eller Brugerformularer, men det er et emne, som denne introduktion ikke berører.

Selv simple makroer og brugerdefinerede funktioner kan være svære at læse. Du kan gøre dem nemmere at forstå ved at skrive forklarende tekst i form af kommentarer. Du tilføjer kommentarer ved at foranstille den forklarende tekst med en apostrof. I følgende eksempel vises funktionen RABAT med kommentarer. Tilføjelse af kommentarer som disse gør det nemmere for dig eller andre at vedligeholde din VBA-kode, efterhånden som tiden går. Hvis du har brug for at foretage en ændring af koden i fremtiden, har du nemmere ved at forstå, hvad du oprindeligt gjorde.

En apostrof beder Excel om at ignorere alt til højre på samme linje, så du kan oprette kommentarer enten på linjer alene eller i højre side af linjer, der indeholder VBA-kode. Du kan starte en relativt lang kodeblok med en kommentar, der forklarer det overordnede formål, og derefter bruge indbyggede kommentarer til at dokumentere individuelle sætninger.

Du kan også dokumentere dine makroer og brugerdefinerede funktioner ved at give dem beskrivende navne. I stedet for at navngive en makro Navne kan du navngive den Månedsnavne for mere præcist at beskrive det formål, makroen tjener. Brug af beskrivende navne for makroer og brugerdefinerede funktioner er især nyttigt, når du har oprettet mange procedurer, især hvis du opretter procedurer, som har lignende, men ikke helt identiske formål.

Hvordan du dokumenterer dine makroer og brugerdefinerede funktioner er et spørgsmål om personlige præferencer. Det, der er vigtigt, er at indføre en metode til dokumentation og at bruge den konsekvent.

For at bruge en brugerdefineret funktion skal projektmappen med det modul, som du oprettede funktionen i, være åben. Hvis projektmappen ikke er åben, får du vist fejlen #NAVN?, når du forsøger at bruge funktionen. Hvis du refererer til funktionen i en anden projektmappe, skal du sætte navnet på den projektmappe, hvor funktionen er placeret, foran funktionsnavnet. Hvis du f.eks. opretter en funktion, der hedder RABAT i en projektmappe, der hedder Personlig.xlsb, og du kalder funktionen fra en anden projektmappe, skal du skrive =personlig.xlsb!rabat(), ikke blot =rabat().

Du kan spare anslag (og mulige slåfejl) ved at vælge brugerdefinerede funktioner fra dialogboksen Indsæt funktion. Dine brugerdefinerede funktioner vises i kategorien Brugerdefinerede:

En nemmere måde til at gøre dine brugerdefinerede funktioner tilgængelige hele tiden er at gemme dem i en separat projektmappe og derefter gemme projektmappen som et tilføjelsesprogram. Du kan derefter gøre tilføjelsesprogrammet tilgængeligt, når du kører Excel. Sådan gør du:

Når du har fulgt disse trin, bliver dine brugerdefinerede funktioner tilgængelige, hver gang du kører Excel. Hvis du vil føje til funktionsbiblioteket, skal du vende tilbage til Visual Basic Editor. Hvis du ser i Visual Basic Editor Project Explorer under overskriften VBAProject, får du vist et modul, der er navngivet efter filen Tilføjelsesprogrammer. Tilføjelsesprogrammet har udvidelsen .xlam.

Når du dobbeltklikker på dette modul i Project Explorer, viser Visual Basic Editor funktionskoden. Hvis du vil tilføje en ny funktion, skal du placere indsætningspunktet efter End Function-sætningen, der afslutter den sidste funktion i kodevinduet, og begynde at skrive. Du kan oprette lige så mange funktioner, du skal bruge, på denne måde, og de vil altid være tilgængelige i kategorien Brugerdefinerede i dialogboksen Indsæt funktion.

Dette indhold blev oprindeligt oprettet af Mark Dodge og Craig Stinson som en del af bogen Microsoft Office Excel 2007 indefra og ud. Det er siden blevet opdateret til også at omfatte nyere versioner af Excel.