Selvom VBA tilbyder et anstændigt udvalg af indbyggede funktioner, finder du måske ikke altid lige det, du har brug for. Heldigvis kan du også bruge de fleste af Excels regnearksfunktioner i dine VBA-procedurer. De eneste regnearksfunktioner, du ikke kan bruge, er dem, der har en tilsvarende VBA-funktion. For eksempel kan du ikke bruge Excels RAND-funktion (som genererer et tilfældigt tal), fordi VBA har en tilsvarende funktion: Rnd.
VBA gør Excels regnearksfunktioner tilgængelige via WorksheetFunction-objektet, som er indeholdt i Application-objektet. Her er et eksempel på, hvordan du kan bruge Excels SUM-funktion i en VBA-sætning:
Total = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Du kan udelade enten Application-delen eller WorksheetFunction-delen af udtrykket. I begge tilfælde finder VBA ud af, hvad du laver. Med andre ord virker disse tre udtryk alle nøjagtigt det samme:
Total = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Total = ArbejdsarkFunction.SUM(Range(“A1:A12”))
Total = Application.SUM(Range(“A1:A12”))
Min personlige præference er at bruge WorksheetFunction-delen bare for at gøre det helt klart, at koden bruger en Excel-funktion.
Eksempler på arbejdsarkfunktioner
Her finder du ud af, hvordan du bruger regnearksfunktioner i dine VBA-udtryk.
Find den maksimale værdi i et område
Her er et eksempel, der viser, hvordan du bruger Excels MAX-regnearksfunktion i en VBA-procedure. Denne procedure viser den maksimale værdi i kolonne A i det aktive regneark:
Brug af en regnearksfunktion i din VBA-kode.
Sub ShowMax()
Dim TheMax som dobbelt
TheMax = WorksheetFunction.MAX(Range(“A:A”))
MsgBox TheMax
Slut Sub
Du kan bruge MIN-funktionen til at få den mindste værdi i et område. Og som du måske forventer, kan du bruge andre regnearksfunktioner på lignende måde. For eksempel kan du bruge den store funktion til at bestemme k th-største værdi i et område. Følgende udtryk demonstrerer dette:
SecondHighest = ArbejdsarkFunction.LARGE(Range(“A:A”),2)
Bemærk, at funktionen LARGE bruger to argumenter. Det andet argument repræsenterer den k'te del — 2, i dette tilfælde (den næststørste værdi).
Beregning af afdrag på realkreditlån
Det næste eksempel bruger PMT-regnearksfunktionen til at beregne en realkreditbetaling. Tre variabler bruges til at gemme de data, der sendes til Pmt-funktionen som argumenter. En beskedboks viser den beregnede betaling.
Sub PmtCalc()
Dim InRate As Double
Dim LoanAmt As Double
Dunkle perioder så lange
IntRate = 0,0625 / 12
Perioder = 30 * 12
LånAmt = 150000
MsgBox WorksheetFunction.PMT(IntRate, Perioder, -LoanAmt)
Slut Sub
Som det fremgår af følgende sætning, kan du også indsætte værdierne direkte som funktionsargumenter:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Brug af variabler til at gemme parametrene gør imidlertid koden nemmere at læse og ændre, hvis det er nødvendigt.
Brug af en opslagsfunktion
Følgende eksempel bruger VBA's InputBox- og MsgBox-funktioner, plus Excels VLOOKUP-funktion. Den beder om et varenummer og får derefter prisen fra en opslagstabel. Nedenfor er række A1:B13 navngivet Prisliste.
Sortimentet, der hedder PriceList, indeholder priser på dele.
Sub GetPrice()
Dim PartNum Som Variant
Dim Pris Som Dobbelt
PartNum = InputBox(“Indtast varenummeret”)
Sheets ("Priser"). Aktiver
Pris = ArbejdsarkFunktion.VLOOKUP(DelNum, Range(“Prisliste”), 2, Falsk)
MsgBox PartNum & " omkostninger " & Pris
Slut Sub
Sådan fungerer GetPrice-proceduren:
-
VBA's InputBox-funktion beder brugeren om et varenummer.
-
Varenummeret, som brugeren indtaster, tildeles til Variablen PartNum.
-
Den næste erklæring aktiverer arbejdsarket Priser, hvis det ikke allerede er det aktive ark.
-
Koden bruger VLOOKUP-funktionen til at finde varenummeret i tabellen.
-
Bemærk, at de argumenter, du bruger i denne sætning, er de samme som dem, du ville bruge med funktionen i en regnearksformel. Denne erklæring tildeler resultatet af funktionen til prisvariablen.
-
Koden viser prisen for delen via MsgBox-funktionen.
Denne procedure har ingen fejlhåndtering, og den mislykkes dybt, hvis du indtaster et ikke-eksisterende varenummer. (Prøv det.) Hvis dette var en faktisk applikation, der bruges i en egentlig virksomhed, vil du gerne tilføje nogle udsagn, der behandler fejl mere elegant.
Indtastning af regnearksfunktioner
Du kan ikke bruge dialogboksen Excel Paste Function til at indsætte en regnearksfunktion i et VBA-modul. Indtast i stedet sådanne funktioner på gammeldags måde: i hånden. Du kan dog bruge dialogboksen Indsæt funktion til at identificere den funktion, du vil bruge, og finde ud af dens argumenter.
Du kan også drage fordel af VBE's Auto List Members mulighed, som viser en rulleliste over alle regnearksfunktioner. Bare skriv Application.WorksheetFunction , efterfulgt af et punktum. Så ser du en liste over de funktioner, du kan bruge. Hvis denne funktion ikke virker, skal du vælge kommandoen VBE's Tools → Options, klikke på fanen Editor og sætte et flueben ud for Auto List Members.
Få en liste over regnearksfunktioner, som du kan bruge i din VBA-kode.
Mere om brug af regnearksfunktioner
Nybegyndere til VBA forveksler ofte VBAs indbyggede funktioner og Excels projektmappefunktioner. En god huskeregel er, at VBA ikke forsøger at genopfinde hjulet. For det meste dublerer VBA ikke Excel-regnearksfunktioner.
For de fleste regnearksfunktioner, der ikke er tilgængelige som metoder til WorksheetFunction-objektet, kan du bruge en tilsvarende indbygget VBA-operator eller funktion. For eksempel er MOD-regnearksfunktionen ikke tilgængelig i WorksheetFunction-objektet, fordi VBA har en tilsvarende: dens indbyggede Mod-operator.
Bundlinie? Hvis du skal bruge en funktion, skal du først afgøre, om VBA har noget, der opfylder dine behov. Hvis ikke, så tjek arbejdsarkfunktionerne. Hvis alt andet fejler, kan du muligvis skrive en brugerdefineret funktion ved at bruge VBA.