Slik bruker du VBA-regnearkfunksjoner i Excel 2016 - dummies

Selv om VBA tilbyr et anstendig utvalg av innebygde funksjoner, kan du ikke alltid finne akkurat det du trenger. Heldigvis kan du også bruke de fleste av Excels regnearkfunksjoner i dine VBA-prosedyrer. De eneste regnearkfunksjonene du ikke kan bruke er de som har en tilsvarende VBA-funksjon. For eksempel kan du ikke bruke Excels RAND-funksjon (som genererer et tilfeldig tall) fordi VBA har en tilsvarende funksjon: Rnd.

VBA gjør at Excels regnearkfunksjoner er tilgjengelige gjennom WorksheetFunction-objektet, som finnes i applikasjonsobjektet. Her er et eksempel på hvordan du kan bruke Excels SUM-funksjon i en VBA-setning:

Totalt = Program. WorksheetFunction. SUM (Range ("A1: A12"))

Du kan utelate enten Programparten eller WorksheetFunction-delen av uttrykket. I begge tilfeller figurerer VBA ut hva du gjør. Med andre ord, disse tre uttrykkene virker alle nøyaktig det samme:

Totalt = Søknad. WorksheetFunction. SUM (Range ("A1: A12")) Totalt = ArbeidsarkFunksjon. SUM (Range ("A1: A12")) Totalt = Søknad. SUM (Range ("A1: A12"))

Min personlige preferanse er å bruke WorksheetFunction-delen bare for å gjøre det helt klart at koden bruker en Excel-funksjon.

Eksempler på regnearkfunksjonen

Her finner du hvordan du bruker regnearkfunksjoner i VBA-uttrykkene dine.

Finne den maksimale verdien i et område

Her er et eksempel som viser hvordan du bruker Excels MAX-regnearkfunksjon i en VBA-prosedyre. Denne prosedyren viser maksimumsverdien i kolonne A i det aktive regnearket:

Bruke et regnearksfunksjon i VBA-koden.

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

Du kan bruke MIN-funksjonen til å få den minste verdien i et område. Og som du kanskje forventer, kan du bruke andre regnearkfunksjoner på en lignende måte. For eksempel kan du bruke LARGE-funksjonen til å bestemme k den største verdien i et område. Følgende uttrykk demonstrerer dette:

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

Merk at den store funksjonen bruker to argumenter. Det andre argumentet representerer k delen 2, i dette tilfellet (den nest største verdien).

Beregning av boliglånsbetaling

I neste eksempel brukes PMT-regnearkfunksjonen til å beregne en boliglånsbetaling. Tre variabler brukes til å lagre dataene som sendes til Pmt-funksjonen som argumenter.En meldingsboks viser den beregnede betalingen.

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

Som følgende setning viser, kan du også sette inn verdiene direkte som funksjonsargumenter:

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

Ved å bruke variabler til å lagre parameterne, gjør koden imidlertid lettere å lese og modifisere, om nødvendig.

Bruke en oppslagsfunksjon

Følgende eksempel bruker VBAs InputBox- og MsgBox-funksjoner, samt Excels VLOOKUP-funksjon. Det ber om et delnummer og får deretter prisen fra et oppslagstabell. Nedenfor er utvalg A1: B13 kalt PriceList.

Utvalget, kalt PriceList, inneholder priser for deler.

Sub GetPrice () Dim PartNum Som Variant Dim Price Som Double PartNum = InputBox ("Skriv inn delenummeret") Ark ("Priser"). Aktiver pris = WorksheetFunction. VLSKOPP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "kostnader" & Pris End Sub

Slik fungerer GetPrice-prosedyren:

• VBAs InputBox-funksjon spør brukeren om et varenummer.

• Delnummeret brukeren legger inn er tildelt PartNum-variabelen.

• Den neste setningen aktiverer regnearket, bare hvis det ikke allerede er det aktive arket.

• Koden bruker VLOOKUP-funksjonen til å finne delenummeret i tabellen.

• Legg merke til at argumentene du bruker i denne setningen, er de samme som de du vil bruke med funksjonen i en regnearkformel. Denne setningen tilordner resultatet av funksjonen til prisvariabelen.

• Koden viser prisen for delen via MsgBox-funksjonen.

Denne prosedyren har ingen feilhåndtering, og det mislykkes dårlig hvis du oppgir et ikke-eksisterende delnummer. (Prøv det.) Hvis dette var en faktisk applikasjon som brukes i en faktisk bedrift, vil du legge til noen utsagn som omhandler feil mer grasiøst.

Angi regnearkfunksjoner

Du kan ikke bruke dialogboksen Excel Paste-funksjon for å sette inn en regnearkfunksjon i en VBA-modul. I stedet legg inn slike funksjoner den gammeldags måten: for hånd. Du kan imidlertid kan bruke dialogboksen Lim inn funksjon for å identifisere funksjonen du vil bruke og finne ut om dens argumenter.

Du kan også dra nytte av VBEs Auto List Members-alternativ, som viser en rullegardinliste over alle regnearkfunksjoner. Skriv bare Søknad. WorksheetFunction , etterfulgt av en periode. Deretter ser du en liste over funksjonene du kan bruke. Hvis denne funksjonen ikke virker, velger du VBE-verktøyet → Alternativer-kommandoen, klikker på fanen Editor og legger en sjekk ved siden av Auto List Members.

Få en liste over regnearksfunksjoner som du kan bruke i VBA-koden.

Mer om bruk av regnearkfunksjoner

Nykommere til VBA forveksler ofte VBAs innebygde funksjoner og Excels arbeidsbokfunksjoner. En god regel å huske er at VBA ikke prøver å gjenoppfinne hjulet.For det meste dupliserer ikke VBA Excel-regnearkfunksjoner.

For de fleste regnearkfunksjoner som ikke er tilgjengelige som metoder for arbeidsarksfunksjonobjektet, kan du bruke en ekvivalent VBA innebygd operatør eller funksjon. MOD-regnearkfunksjonen er for eksempel ikke tilgjengelig i WorksheetFunction-objektet fordi VBA har en ekvivalent: den innebygde Mod-operatøren.

Bottom line? Hvis du trenger å bruke en funksjon, må du først avgjøre om VBA har noe som tilfredsstiller dine behov. Hvis ikke, sjekk ut regnearkfunksjonene. Hvis alt annet feiler, kan du kanskje skrive en egendefinert funksjon ved å bruke VBA.