Hur man använder VBA-kalkylfunktioner i Excel 2016 - dummies

Även om VBA erbjuder ett anständigt sortiment av inbyggda funktioner kan du inte alltid hitta exakt vad du behöver. Lyckligtvis kan du också använda de flesta av Excels kalkylbladsfunktioner i dina VBA-procedurer. Det enda kalkylbladsfunktionen som du inte kan använda är de som har en motsvarande VBA-funktion. Till exempel kan du inte använda Excels RAND-funktion (som genererar ett slumptal) eftersom VBA har en ekvivalent funktion: Rnd.

VBA gör Excels kalkylbladsfunktioner tillgängliga via WorksheetFunction-objektet, som finns i applikationsobjektet. Här är ett exempel på hur du kan använda Excels SUM-funktion i ett VBA-formulär:

Totalt = Application. WorksheetFunction. SUM (Räckvidd ("A1: A12"))

Du kan utelämna antingen Programdelen eller Verktygsfunktionsdelen av uttrycket. I båda fallen visar VBA ut vad du gör. Med andra ord fungerar dessa tre uttryck alla exakt lika:

Totalt = Ansökan. WorksheetFunction. SUM (Räckvidd ("A1: A12")) Totalt = Arbetsbladsfunktion. SUM (Räckvidd ("A1: A12")) Totalt = Ansökan. SUM (Range ("A1: A12"))

Min personliga preferens är att använda WorksheetFunction-delen bara för att göra det helt klart att koden använder en Excel-funktion.

Exempel på arbetsbladsfunktioner

Här upptäcker du hur man använder kalkylbladsfunktioner i dina VBA-uttryck.

Hitta det maximala värdet inom ett intervall

Här är ett exempel som visar hur du använder Excels MAX-kalkylbladsfunktion i en VBA-procedur. Denna procedur visar det maximala värdet i kolumn A i det aktiva kalkylbladet:

Använda en kalkylbladsfunktion i din VBA-kod.

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

Du kan använda MIN-funktionen för att få det minsta värdet inom ett intervall. Och som du kan förvänta dig kan du använda andra kalkylbladsfunktioner på ett liknande sätt. Till exempel kan du använda den stora funktionen för att bestämma k det största värdet inom ett intervall. Följande uttryck visar detta:

SecondHighest = WorksheetFunction. STOR (Område ("A: A"), 2)

Observera att den stora funktionen använder två argument. Det andra argumentet representerar k den andra delen - 2, i det här fallet (det näst största värdet).

Beräkning av hypotekslån

I nästa exempel används PMT-kalkylbladsfunktionen för att beräkna hypotekslån. Tre variabler används för att lagra data som överförs till Pmt-funktionen som argument.En meddelandebox visar den beräknade betalningen.

Sub PmtCalc () Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Long IntRate = 0. 0625/12 Perioder = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Periods, -LoanAmt) End Sub

Som följande uttalande visar kan du också infoga värdena direkt som funktionsargument:

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

Men med hjälp av variabler för att lagra parametrarna gör koden lättare att läsa och modifiera, om det behövs.

Använda en uppslagningsfunktion

Följande exempel använder VBA: s InputBox och MsgBox-funktioner, plus Excels VLOOKUP-funktion. Det ber om ett artikelnummer och får sedan priset från ett uppslagstabell. Nedan visas område A1: B13 PriceList.

Sortimentet heter PriceList, innehåller priser för delar.

Sub GetPrice () Dim PartNum Som Variant Dim Price Som Double PartNum = InputBox ("Enter the Part Number") Ark ("Priser"). Aktivera priset = Arbetsbladsfunktion. VLSKOPP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "costs" & Price End Sub

Så här fungerar GetPrice-proceduren:

• VBAs InputBox-funktion frågar användaren för ett artikelnummer.

• Delnumret som användaren anger har tilldelats variabeln PartNum.

• Nästa uttalande aktiverar regnearket, bara om det inte redan är det aktiva arket.

• Koden använder VLOOKUP-funktionen för att hitta partnumret i tabellen.

• Observera att argumenten du använder i detta uttalande är desamma som de som du skulle använda med funktionen i en kalkylbladformel. Detta uttalande tilldelar resultatet av funktionen till Price-variabeln.

• Koden visar priset för delen via MsgBox-funktionen.

Denna procedur har ingen felhantering, och det misslyckas om du anger ett existerande artikelnummer. (Prova det.) Om det här var en verklig applikation som används i ett verkligt företag, skulle du vilja lägga till några uttalanden som behandlar fel mer graciöst.

Ange arbetsbladsfunktioner

Du kan inte använda dialogrutan Excel Paste Function för att infoga en kalkylbladsfunktion i en VBA-modul. Istället skriver du in sådana funktioner på det gammaldags sättet: för hand. Du kan dock kan använda dialogrutan Klistra in funktion för att identifiera den funktion du vill använda och ta reda på dess argument.

Du kan också dra nytta av VBE: s Auto List Members-alternativ, som visar en rullgardinslista över alla arbetsbladsfunktioner. Skriv bara Application. Arbetsbladsfunktion följt av en period. Då ser du en lista över de funktioner du kan använda. Om den här funktionen inte fungerar väljer du VBE: s verktyg → Alternativ-kommandot, klickar på fliken Editor och ställer en check bredvid Auto List Members.

Få en lista över kalkylbladsfunktioner som du kan använda i din VBA-kod.

Mer om att använda kalkylbladsfunktioner

Nykomlingar till VBA förvirrar ofta VBA: s inbyggda funktioner och Excels arbetsboksfunktioner. En bra regel att komma ihåg är att VBA inte försöker återuppfinna hjulet.För det mesta kopierar VBA inte Excel-kalkylbladsfunktioner.

För de flesta kalkylbladsfunktioner som inte är tillgängliga som metoder i WorksheetFunction-objektet kan du använda en motsvarande VBA inbyggd operatör eller funktion. Funktionen MOD-kalkylblad är till exempel inte tillgänglig i WorksheetFunction-objektet eftersom VBA har en ekvivalent: den inbyggda Mod-operatören.

Bottom line? Om du behöver använda en funktion, bestäm först om VBA har något som uppfyller dina behov. Om inte, kolla in kalkylbladsfunktionerna. Om allt annat misslyckas kan du kanske skriva en anpassad funktion med hjälp av VBA.