Även om VBA erbjuder ett anständigt utbud av inbyggda funktioner, kanske du inte alltid hittar exakt det du behöver. Som tur är kan du också använda de flesta av Excels kalkylbladsfunktioner i dina VBA-procedurer. De enda kalkylbladsfunktioner som du inte kan använda är de som har en motsvarande VBA-funktion. Du kan till exempel inte använda Excels RAND-funktion (som genererar ett slumptal) eftersom VBA har en motsvarande funktion: Rnd.
VBA gör Excels kalkylbladsfunktioner tillgängliga via WorksheetFunction-objektet, som finns i Application-objektet. Här är ett exempel på hur du kan använda Excels SUM-funktion i en VBA-sats:
Totalt = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Du kan utelämna antingen Application-delen eller WorksheetFunction-delen av uttrycket. I båda fallen räknar VBA ut vad du gör. Med andra ord, dessa tre uttryck fungerar alla exakt likadant:
Totalt = Application.WorksheetFunction.SUM(Range(“A1:A12”)) Totalt = KalkylbladFunction.SUM(Range(“A1:A12”)) Totalt = Application.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.
Här upptäcker du hur du använder kalkylbladsfunktioner i dina VBA-uttryck.
Hitta det maximala värdet i 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 arbetsbladet:
Använda en kalkylbladsfunktion i din VBA-kod.
Sub ShowMax() Dim TheMax As Double TheMax = WorksheetFunction.MAX(Range(“A:A”)) MsgBox TheMax Avsluta Sub
Du kan använda MIN-funktionen för att få det minsta värdet i ett intervall. Och som du kanske förväntar dig kan du använda andra kalkylbladsfunktioner på liknande sätt. Du kan till exempel använda funktionen LARGE för att bestämma det k: te största värdet i ett område. Följande uttryck visar detta:
SecondHighest = WorksheetFunction.LARGE(Range(“A:A”),2)
Observera att funktionen LARGE använder två argument. Det andra argumentet representerar den k: te delen — 2, i detta fall (det näst största värdet).
Beräkna en bolånebetalning
I nästa exempel används funktionen PMT-kalkylblad för att beräkna en bolånebetalning. Tre variabler används för att lagra data som skickas till Pmt-funktionen som argument. En meddelanderuta visar den beräknade betalningen.
Sub PmtCalc() Dim InRate As Double Dim LoanAmt As Double Dimperioder lika långa IntRate = 0,0625 / 12 Perioder = 30 * 12 LoanAmt = 150 000 MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt) Avsluta Sub
Som följande sats visar kan du också infoga värdena direkt som funktionsargument:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Men att använda variabler för att lagra parametrarna gör koden lättare att läsa och ändra vid behov.
Använder en uppslagsfunktion
Följande exempel använder VBA:s InputBox- och MsgBox-funktioner, plus Excels VLOOKUP-funktion. Den frågar efter ett artikelnummer och får sedan priset från en uppslagstabell. Nedan heter intervall A1:B13 Prislista.
Sortimentet, som heter PriceList, innehåller priser på delar.
Sub GetPrice()
Dim PartNum Som Variant
Dim Pris Som Dubbel
PartNum = InputBox(”Ange artikelnumret”)
Sheets ("Priser"). Aktivera
Pris = WorksheetFunction.VLOOKUP(DelNum, Range(“Prislista”), 2, Falskt)
MsgBox PartNum & " kostnader " & Pris
Avsluta Sub
Så här fungerar GetPrice-proceduren:
VBA:s InputBox-funktion ber användaren om ett artikelnummer.
Artikelnumret som användaren anger tilldelas variabeln PartNum.
Nästa uttalande aktiverar kalkylbladet Priser, ifall det inte redan är det aktiva arket.
Koden använder VLOOKUP-funktionen för att hitta artikelnumret i tabellen.
Lägg märke till att argumenten du använder i den här satsen är desamma som de du skulle använda med funktionen i en kalkylbladsformel. Detta påstående tilldelar resultatet av funktionen till variabeln Pris.
Koden visar priset för delen via MsgBox-funktionen.
Denna procedur har ingen felhantering, och den misslyckas totalt om du anger ett icke-existerande artikelnummer. (Prova det.) Om detta var ett verkligt program som används i en verklig verksamhet, skulle du vilja lägga till några uttalanden som hanterar fel mer elegant.
Du kan inte använda dialogrutan Klistra in i Excel för att infoga en kalkylbladsfunktion i en VBA-modul. Ange i stället sådana funktioner på gammaldags sätt: för hand. Du kan dock använda dialogrutan Klistra in funktion för att identifiera funktionen 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 med alla kalkylbladsfunktioner. Skriv bara Application.WorksheetFunction , följt av en punkt. Då ser du en lista över de funktioner du kan använda. Om den här funktionen inte fungerar, välj VBE:s Verktyg → Alternativ, klicka på fliken Redigerare och placera en bock bredvid Auto List Members.
Få en lista över kalkylbladsfunktioner som du kan använda i din VBA-kod.
Nykomlingar på VBA blandar ofta ihop VBA:s inbyggda funktioner och Excels arbetsboksfunktioner. En bra regel att komma ihåg är att VBA inte försöker uppfinna hjulet på nytt. För det mesta duplicerar VBA inte Excel-kalkylbladsfunktioner.
För de flesta kalkylbladsfunktioner som inte är tillgängliga som metoder för WorksheetFunction-objektet kan du använda en motsvarande inbyggd VBA-operator eller funktion. Till exempel är MOD-kalkylbladsfunktionen inte tillgänglig i WorksheetFunction-objektet eftersom VBA har en motsvarighet: dess inbyggda Mod-operator.
Slutsats? Om du behöver använda en funktion, avgör 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 genom att använda VBA.
Lär dig att använda kommandona Gör om och Upprepa i Word 2016 för att effektivt hantera dina dokument. Dessa funktioner hjälper dig att enkelt ångra och återställa ändringar.
Hur man blockerar Microsoft Word från att öppna filer i skrivskyddat läge på Windows Microsoft Word öppnar filer i skrivskyddat läge, vilket gör det omöjligt att redigera dem? Oroa dig inte, metoderna finns nedan
Så här åtgärdar du fel vid utskrift av felaktiga Microsoft Word-dokument Fel vid utskrift av Word-dokument med ändrade teckensnitt, röriga stycken, saknad text eller förlorat innehåll är ganska vanligt. Men gör det inte
Om du har använt pennan eller överstrykningspennan för att rita på dina PowerPoint-bilder under en presentation, kan du spara ritningarna till nästa presentation eller radera dem så att nästa gång du visar den börjar du med rena PowerPoint-bilder. Följ dessa instruktioner för att radera ritningar med penna och överstrykningspenna: Radera linjer ett på […]
Stilbiblioteket innehåller CSS-filer, XSL-filer (Extensible Stylesheet Language) och bilder som används av fördefinierade mallsidor, sidlayouter och kontroller i SharePoint 2010. För att hitta CSS-filer i stilbiblioteket på en publiceringswebbplats: Välj Webbplatsåtgärder→Visa Allt webbplatsinnehåll. Innehållet på webbplatsen visas. Style-biblioteket ligger i […]
Överväldiga inte din publik med gigantiska siffror. I Microsoft Excel kan du förbättra läsbarheten för dina instrumentpaneler och rapporter genom att formatera dina siffror så att de visas i tusentals eller miljoner.
Lär dig hur du använder SharePoints sociala nätverksverktyg som låter individer och grupper kommunicera, samarbeta, dela och ansluta.
Julianska datum används ofta i tillverkningsmiljöer som en tidsstämpel och snabbreferens för ett batchnummer. Denna typ av datumkodning tillåter återförsäljare, konsumenter och serviceagenter att identifiera när en produkt tillverkades och därmed produktens ålder. Julianska datum används också i programmering, militären och astronomi. Annorlunda […]
Du kan skapa en webbapp i Access 2016. Så vad är en webbapp egentligen? Tja, webben betyder att den är online, och appen är bara en förkortning för "applikation". En anpassad webbapp är en onlinedatabasapplikation som nås från molnet med en webbläsare. Du bygger och underhåller webbappen i skrivbordsversionen […]
De flesta sidor i SharePoint 2010 visar en lista med navigeringslänkar i snabbstartsfältet till vänster på sidan. Snabbstartsfältet visar länkar till utvalt webbplatsinnehåll som listor, bibliotek, webbplatser och publiceringssidor. Snabbstartsfältet innehåller två mycket viktiga länkar: Länken Allt webbplatsinnehåll: […]
