Selv om VBA tilbyr en anstendig utvalg av innebygde funksjoner, du kan ikke alltid finne akkurat det du trenger. Heldigvis kan du også bruke de fleste av Excel regneark funksjoner i VBA-prosedyrer. De eneste regnearkfunksjoner som du ikke kan bruke er de som har en tilsvarende VBA-funksjon. For eksempel kan du ikke bruke Excel funksjonen TILFELDIG (som genererer et tilfeldig tall) fordi VBA har en tilsvarende funksjon. Rnd
VBA gjør Excel regnearkfunksjoner tilgjengelige gjennom WorksheetFunction objekt, som finnes i program gjenstand. Her er et eksempel på hvordan du kan bruke Excel SUMMER-funksjonen i en VBA uttalelse:
Total = Application.WorksheetFunction.SUM (Range ( "A1: A12")) Du kan utelate enten Application del eller WorksheetFunction del av uttrykket . I begge tilfeller tallene VBA ut hva du gjør. Med andre ord, disse tre uttrykkene alle arbeide nøyaktig det samme:
Total = Application.WorksheetFunction.SUM (Range ( "A1: A12")) Totalt = WorksheetFunction.SUM (Range ( "A1: A12")) Totalt = Application.SUM (Range ( "A1: A12"))
Min personlige preferanse er å bruke WorksheetFunction del bare for å gjøre det helt klart at koden bruker en Excel-funksjon
regneark funksjon eksempler <. br>
Her oppdager du hvordan du bruker regnearkfunksjoner i dine VBA-uttrykk.
Finne den maksimale verdien i en rekke
Her er et eksempel som viser hvordan du bruker Excel MAX regnearkfunksjon i en VBA prosedyre. Denne fremgangsmåten viser den maksimale verdien i kolonne A i det aktive regnearket:
Bruke et regneark funksjon i VBA code.Sub ShowMax () Dim TheMax Som Double TheMax = WorksheetFunction.MAX (Range ( "A: A")) MsgBox TheMaxEnd Sub
Du kan bruke MIN-funksjonen for å 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 den store funksjonen til å bestemme k
th største verdien i et område. Følgende uttrykk viser dette:
SecondHighest = WorksheetFunction.LARGE (Range ( "A: A"), 2)
Legg merke til at den store funksjonen bruker to argumenter. Det andre argumentet representerer k
th del -. 2, i dette tilfellet (den nest største verdien)
Beregne et boliglån betaling
Det neste eksemplet bruker PMT regnearkfunksjonen til å beregne et boliglån betaling. Tre variabler brukes til å lagre dataene som sendes til Pmt funksjonen som argumenter. En meldingsboks viser beregnede betaling
Sub PmtCalc () Dim INTRATE Som Double Dim LoanAmt Som Double Dim Perioder så lenge INTRATE = 0,0625 /12 Perioder = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.PMT (INTRATE, Perioder,. - LoanAmt) End Sub
som følgende uttalelse viser, du kan også sette inn verdiene direkte som funksjonsargumenter:
MsgBox WorksheetFunction.PMT (0,0625 /12, 360, -150 000)
Men ved hjelp av variabler for å lagre parametrene gjør koden lettere å lese og endre om nødvendig.
Ved hjelp av en oppslagsfunksjon
følgende eksempel bruker VBA er InputBox og MsgBox funksjoner, pluss Excel FINN.RAD-funksjonen. Den ber om en del tall og deretter får prisen fra en oppslagstabell. Nedenfor området A1:. Er B13 heter prisliste
rekkevidde, heter prisliste, inneholder priser for parts.Sub GetPrice () Dim PartNum Som Variant Dim Pris As Double PartNum = InputBox ( "Skriv inn delenummer") Sheets ( " priser ".) Aktiver Pris = WorksheetFunction.VLOOKUP (PartNum, Range (" prisliste "), 2, False) MsgBox PartNum & "Kostnader" & PriceEnd Sub
Her er hvordan GetPrice prosedyren fungerer.
Delenummeret brukeren taster er tilordnet til PartNum variable .
den neste setningen aktiverer Prisene regneark, bare i tilfelle det ikke allerede er det aktive arket.
den koden bruker FINN.RAD-funksjonen til å finne delenummeret i bord.
Legg merke til at argumentene du bruker i denne uttalelsen er de samme som du ville bruke med funksjonen i et regneark formel. Denne uttalelsen tildeler resultatet av funksjonen til prisen variabelen.
Denne prosedyren doesn 't har noen feilhåndtering, og det mislykkes miserably hvis du skriver inn et ikke-eksisterende delenummer. (Prøv det.) Hvis dette var en faktisk program som brukes i en faktisk virksomhet, ville du ønsker å legge til noen utsagn som omhandler feil mer elegant.
Legge inn regnearkfunksjoner
Du kan " t bruke dialogboksen Lim inn funksjon i Excel boksen for å sette inn et regneark funksjon i en VBA-modul. I stedet, gå inn i slike funksjoner den gammeldagse måten: hånd. Men du kan
bruke dialogboksen Lim inn funksjon boksen for å identifisere den funksjonen du vil bruke, og finne ut om argumentene.
Du kan også dra nytte av VBE Auto List medlemmer alternativet, som viser en drop-down liste over alle regnearkfunksjoner. Bare skriv Application.WorksheetFunction
, etterfulgt av en periode. Deretter kan du se en liste over funksjoner du kan bruke. Hvis denne funksjonen ikke fungerer, velger VBE Verktøy → Alternativer-kommandoen, klikker du på kategorien Editor, og plassere en sjekk ved siden av Auto medlemmer av listen.
Får en liste over regnearkfunksjoner som du kan bruke i VBA-kode.
Mer om bruk av regnearkfunksjoner
nykommere til VBA ofte forvirre VBA innebygde funksjoner og Excel arbeidsbok funksjoner. En god regel å huske er at VBA ikke prøver å gjenoppfinne hjulet. For det meste, ikke VBA ikke duplisere Excel-regnearkfunksjoner.
For de fleste regnearkfunksjoner som ikke er tilgjengelige som metoder for WorksheetFunction objektet, kan du bruke en tilsvarende VBA innebygd operatør eller funksjon. For eksempel er MOD regnearkfunksjonen ikke er tilgjengelig i WorksheetFunction objektet fordi VBA har en tilsvarende. Den innebygde Mod operatør
Ergo? Hvis du trenger å bruke en funksjon, må du først finne ut om VBA har noe som dekker dine behov. Hvis ikke, sjekk ut regnearkfunksjoner. Hvis alt annet mislykkes, kan du være i stand til å skrive en egendefinert funksjon ved hjelp av VBA.