Sådan bruges VBA-regnearkfunktioner i Excel 2016 - dummies

Selvom VBA tilbyder et anstændigt udvalg af indbyggede funktioner, finder du måske ikke altid præcis, hvad du har brug for. Heldigvis kan du også bruge de fleste af Excels regnearkfunktioner i dine VBA-procedurer. De eneste regnearkfunktioner, som du ikke kan bruge, er dem, der har en tilsvarende VBA-funktion. Du kan f.eks. Ikke bruge Excel's RAND-funktion (som genererer et tilfældigt tal), fordi VBA har en tilsvarende funktion: Rnd.

VBA gør Excels regnearkfunktioner tilgængelige via WorksheetFunction-objektet, som findes i applikationsobjektet. Her er et eksempel på, hvordan du kan bruge Excels SUM-funktion i en VBA-sætning:

Total = Application. WorksheetFunction. SUM (Område ("A1: A12"))

Du kan udelade enten Programmets del eller WorksheetFunction-delen af ​​udtrykket. I begge tilfælde viser VBA ud, hvad du laver. Med andre ord arbejder disse tre udtryk alle sammen nøjagtigt:

I alt = Ansøgning. WorksheetFunction. SUM (Range ("A1: A12")) I alt = WorksheetFunction. SUM (Range ("A1: A12")) I alt = Ansøgning. SUM (Range ("A1: A12"))

Min personlige præference er at bruge WorksheetFunction-delen kun for at gøre det helt klart, at koden bruger en Excel-funktion.

Eksempler på arbejdsarksfunktioner

Her finder du ud af, hvordan du bruger regnearkfunktioner i dine VBA-udtryk.

Find den maksimale værdi i en rækkevidde

Her er et eksempel, der viser, hvordan man bruger Excel's MAX-regnearkfunktion i en VBA-procedure. Denne procedure viser den maksimale værdi i kolonne A i det aktive regneark:

Brug af et regneark-funktion i din VBA-kode.

Sub ShowMax () Dim TheMax As Double TheMax = WorksheetFunction. MAX (Range ("A: A")) MsgBox TheMax End Sub

Du kan bruge MIN-funktionen til at få den mindste værdi i en rækkevidde. Og som du måske forventer, kan du bruge andre regnearksfunktioner på en lignende måde. Du kan f.eks. Bruge den store funktion til at bestemme k den største værdi i en rækkevidde. Følgende udtryk viser dette:

SecondHighest = WorksheetFunction. STOR (Range ("A: A"), 2)

Bemærk, at den store funktion bruger to argumenter. Det andet argument repræsenterer k den del - 2, i dette tilfælde (den næststørste værdi).

Beregning af realkreditbetaling

I det næste eksempel bruges PMT-regnearkfunktionen til at beregne en realkreditbetaling. Tre variabler bruges til at gemme dataene, der sendes til Pmt-funktionen som argumenter.En meddelelsesboks viser den beregnede betaling.

Sub PmtCalc () Dim IntRate Som Double Dim LoanAmt Som Double Dim Perioder Så Lang IntRate = 0. 0625/12 Perioder = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Perioder, -LoanAmt) End Sub

Som følgende sætning viser, kan du også indsætte værdierne direkte som funktionsargumenterne:

MsgBox WorksheetFunction. PMT (0, 0625/12, 360, -150000)

Brug af variabler til at gemme parametrene gør koden lettere at læse og ændre, hvis det er nødvendigt.

Brug af en opslagsfunktion

Følgende eksempel bruger VBAs InputBox- og MsgBox-funktioner samt Excel's VLOOKUP-funktion. Det beder om et varenummer og får derefter prisen fra et opslagstabell. Nedenfor er rækkevidde A1: B13 betegnet PriceList.

Sortimentet, der hedder PriceList, indeholder priser for dele.

Sub GetPrice () Dim PartNum Som Variant Dim Price Som Double PartNum = InputBox ("Enter the Part Number") Sheets ("Priser"). Aktivér pris = Arbejdsarksfunktion. VLOOKUP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "costs" & Price End Sub

Sådan fungerer GetPrice-proceduren:

• VBAs InputBox-funktion beder brugeren om et varenummer.

• Delenummeret, som brugeren indtaster, er tildelt til PartNum-variablen.

• Den næste sætning aktiverer Priser regnearket, bare hvis det ikke allerede er det aktive ark.

• Koden bruger VLOOKUP-funktionen til at finde partnummeret i tabellen.

• Bemærk, at de argumenter, du bruger i denne erklæring, er de samme som dem, du vil bruge med funktionen i en regnearkformel. Denne sætning tildeler resultatet af funktionen til prisvariablen.

• Koden viser prisen for delen via MsgBox-funktionen.

Denne procedure har ingen fejlhåndtering, og det mislykkes mishandlet, hvis du indtaster et ikke-eksisterende partnummer. (Prøv det.) Hvis dette var en faktisk applikation, der bruges i en egentlig virksomhed, vil du gerne tilføje nogle udsagn, der omhandler fejl mere yndefuldt.

Indtastning af regnearkfunktioner

Du kan ikke bruge dialogboksen Excel Paste-funktion til at indsætte en regnearkfunktion i et VBA-modul. I stedet indtaste sådanne funktioner den gammeldags måde: for hånd. Du kan dog kan bruge dialogboksen Indsæt funktion til at identificere den funktion, du vil bruge, og finde ud af om dens argumenter.

Du kan også udnytte VBE's Auto List Members-valgmulighed, som viser en rulleliste over alle regnearkfunktioner. Skriv blot Application. Arbejdsarksfunktion efterfulgt af en periode. Så ser du en liste over de funktioner, du kan bruge. Hvis denne funktion ikke virker, skal du vælge VBE's Funktioner → Indstillinger kommando, klikke på fanen Editor og placere en check ud for Auto List Members.

Få en liste over regnearksfunktioner, som du kan bruge i din VBA-kode.

Mere om brugen af ​​regnearkfunktioner

Nybegyndere til VBA forvirrer ofte VBAs indbyggede funktioner og Excels arbejdsbogsfunktioner. En god regel at huske er, at VBA ikke forsøger at genopfinde hjulet.For det meste duplikerer VBA ikke Excel-regnearkfunktioner.

For de fleste regnearksfunktioner, der ikke er tilgængelige som metoder i WorksheetFunction-objektet, kan du bruge en tilsvarende VBA-indbygget operatør eller funktion. Funktionen MOD-regneark er f.eks. Ikke tilgængelig i WorksheetFunction-objektet, fordi VBA har et tilsvarende: den indbyggede Mod-operatør.

nederste linje? Hvis du skal bruge en funktion, skal du først afgøre, om VBA har noget, der opfylder dine behov. Hvis ikke, skal du tjekke regnearkets funktioner. Hvis alt andet fejler, kan du muligvis skrive en brugerdefineret funktion ved hjælp af VBA.