RSS

Egne regnearksfunktioner og tilføjelsesprogrammer

Excel har mange indbyggede regnearksfunktioner som fx SUM(), MIDDEL() osv. Denne side viser, hvordan du kan lave dine egne regnearksfunktioner, der fungerer på samme måde.

Den viser også, hvordan du kan gemme dem som et tilføjelsesprogram, der er tilgængeligt fra alle dine regneark.

Det kan være uhyre nyttigt at lave nogle skræddersyede regnearksfunktioner til beregninger eller operationer, man ofte foretager, og som ikke dækkes af Excels standardfunktioner.

På arbejde har jeg lavet regnearksfunktioner til bl.a. kemiske beregninger, der bruger formler, som er vanskelige at huske.

Regnearksfunktionerne har jeg så gemt i et Excel tilføjelsesprogram, så jeg kan bruge dem i alle mine regneark.

Jeg skal så blot huske, at hvis jeg sender et regneark til en kollega, skal funktionerne kopieres til et VBA-modul i regnearket, eller han skal have tilføjelsesprogrammet installeret - ellers dur funktionerne ikke.

Herunder ses et eksempel på en funktion. Nu er beregning af en firkants areal ikke vildt kompliceret, men det illustrerer, hvordan en funktion bruger de parametre (argumenter), man angiver - her længde og bredde.

Funktionen ganger længde med bredde og returnerer resultatet: Firkantens areal.


Function FIRKANTAREAL(Længde As Double, Bredde As Double) As Double
'Beregner en firkants areal
FIRKANTAREAL = Bredde * Længde
End Function

Funktionens navn er FIRKANTAREAL, ligesom Excels standardfunktioner har navne (fx SUM).

Længde og bredde er her deklareret som datatypen Double. Hvis man ikke deklarerer en datatype, bruges datatypen Variant som standard. Værdien, som funktionen returnerer, er i dette eksempel også af datatypen Double, dvs. et tal som kan have decimaler.

Hvis du kopierer funktionen ovenover til et VBA-modul, aktiverer et af regnearkets faneblade og vælger "Indsæt funktion" (SHIFT+F3) og vælger kategorien "Brugerdefineret", vil du se FIRKANTAREAL på listen. Når du vælger den, popper den sædvanlige dialogboks op:

Funktionsargumenter

Nu kan du enten klikke på en celle med værdien for længden, eller du kan skrive celleadressen, eller du kan indtaste et tal.

Fordelen ved at henvise til en celle er selvfølgelig, at cellen med det beregnede areal automatisk ændrer sig, hvis længde-cellen skifter værdi.

Lidt hjælpetekst

På billedet ovenover ser du, at der står "Der er ingen hjælp til rådighed". Det kan vi gøre noget ved, om end vi ikke har plads til mere end et par linjer.

Vælg "Makroer" fra menuen (ALT+F8). Frem kommer en dialogboks, men medmindre du har nogle makroer, som ikke er funktioner, vil den være tom.

I feltet for makronavn skriver du funktionens navn, her FIRKANTAREAL. Så bliver knapperne aktive, og dialogboksen ser således ud (i Excel 2003):

Funktionsbeskrivelse

Klik på "Indstillinger," og du får mulighed for at skrive et par linjer med en beskrivelse af funktionen - fx "Beregner en firkants areal." Derefter ser det således ud, når du kalder funktionen:

Funktionsargumenter

Brug af Excels regnearksfunktioner

I et vist omfang kan man gøre brug af Excels indbyggede regnearksfunktioner i sine egne regnearksfunktioner. I nedenstående funktion bruges regnearksfunktionen for Pi i beregningen af en cirkels areal.


Function CIRKELAREAL(Radius As Double) As Double
'Beregner en cirkels areal
CIRKELAREAL = Application.WorksheetFunction.Pi * Radius ^ 2
End Function

Lidt mere komplicerede funktioner

Hidtil har eksemplerne været ret simple, så her følger et par stykker med lidt mere kød på kroppen.

Den første funktion, RABATPRIS, beregner prisen med rabat som funktion af, hvor mange stk. der købes. Op til 100 stk. giver ingen rabat, op til 200 giver 2 % rabat, op til 300 giver 4 % rabat, op til 400 giver 6 % rabat og antal derover giver 8 % rabat.

Funktionsargumenterne er prisen uden rabat samt antal.


Function RABATPRIS(Pris_uden_rabat As Double, Antal As Long) As Double

Select Case Antal
   Case Is < 100
      RABATPRIS = Pris_uden_rabat
   Case 100 To 199
      RABATPRIS = Pris_uden_rabat * 0.98
   Case 200 To 299
      RABATPRIS = Pris_uden_rabat * 0.96
   Case 300 To 399
      RABATPRIS = Pris_uden_rabat * 0.94
   Case Is > 399
      RABATPRIS = Pris_uden_rabat * 0.92
End Select

End Function

Så fik vi også lige set, at VBA bruger punktum som decimaladskiller, og det uanset om der bruges komma i regnearksdelen.

I det sidste funktionseksempel omregnes et målt gasflow i m3/time til normalkubikmeter ved 1013 mbar, 0° C, tør og 10 % ilt.


Function NM3_TØR_10pct_O2(M3, Temperatur, Tryk, O2, H2O) As Double

NM3_TØR_10pct_O2 = M3 * Tryk / 1013 * (273.15 / (273.15 + Temperatur)) * _
(1 - H2O / 100) * (20.9 - O2) / 10.9

End Function

Argumenterne er de målte værdier for tryk osv., og så normaliseres flowet med nogle konstanter, nemlig atmosfærisk tryk på 1013 mbar, 0° Celcius = 273,15 Kelvin samt et iltindhold på 20,9 % i atmosfærisk luft.

Sådan er der rige muligheder for at lave små hjælpere til opgaver, man ofte skal løse!

Husk!

Men husk, at en regnearksfunktion i en celle kun kan returnere en eller anden værdi (eller tekst). Den kan ikke ændre noget såsom fx cellefarver.

Sørg også for, at dine funktionsnavne er unikke. Hvis 2 funktioner hedder det samme, går der ged i det.

Lav et tilføjelsesprogram

Hvis du har lavet nogle regnearksfunktioner (eller makroer for den sags skyld), er det smart at gemme dem som et tilføjelsesprogram, så de kan bruges i alle dine regneark.

Jeg gør det på den måde, at jeg kopierer funktionerne til et VBA-modul i et tomt regneark. Derefter gemmer jeg regnearket som et Excel tilføjelsesprogram (addin).

Det er bare at vælge "Gem som" og så vælge den rigtige filtype (Excel tilføjelsesprogram). Excel gemmer automatisk tilføjelsesprogrammer i den rigtige mappe.

Tilføjelsesprogrammer har i filnavnet ".xlam" som endelse (version 2007 og nyere), eller ".xla" (version 2003 eller ældre).

I VBA-editoren kan man beskytte koden med password og forhindre andre i at se den, men vær opmærksom på, at der findes programmer designet specielt til at knække Excel-adgangskoder.

Dernæst skal man aktivere tilføjelsesprogrammet. I Excel 2003 gøres det under "Funktioner" - "Tilføjelsesprogrammer. I nyere versioner af Excel er det mere omstændeligt.

Der skal man ind under "Filer", "Indstillinger" og "Tilføjelsesprogrammer". Nederst er der en rulleliste med muligheder, men som regel vil Excel-tilføjelsesprogrammer være valgt, og man klikker på knappen "Udfør," hvorefter man kan vælge og aktivere sit tilføjelsesprogram.

Hvis man på et senere tidspunkt ønsker at ændre noget, gør man det i VBA editoren, hvor man også kan gemme ændringerne.

Relateret: