Selv om VBA tilbyr et anstendig utvalg av innebygde funksjoner, finner du kanskje ikke alltid 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 Excels regnearkfunksjoner tilgjengelige gjennom WorksheetFunction-objektet, som finnes i Application-objektet. Her er et eksempel på hvordan du kan bruke Excels SUM-funksjon i en VBA-setning:
Totalt = Application.WorksheetFunction.SUM(Range(“A1:A12”))
Du kan utelate enten Application-delen eller WorksheetFunction-delen av uttrykket. I begge tilfeller finner VBA ut hva du gjør. Med andre ord, disse tre uttrykkene fungerer alle nøyaktig det samme:
Totalt = Application.WorksheetFunction.SUM(Range(“A1:A12”)) Totalt = RegnearkFunksjon.SUM(Område(“A1:A12”)) Totalt = Application.SUM(Range(“A1:A12”))
Min personlige preferanse er å bruke WorksheetFunction-delen bare for å gjøre det helt klart at koden bruker en Excel-funksjon.
Her finner du ut hvordan du bruker regnearkfunksjoner i VBA-uttrykkene dine.
Finne maksimumsverdien i et område
Her er et eksempel som viser hvordan du bruker Excels MAX-regnearkfunksjon i en VBA-prosedyre. Denne prosedyren viser maksimalverdien i kolonne A i det aktive regnearket:
Bruke en regnearkfunksjon i VBA-koden din.
Sub ShowMax() Dim TheMax As Double TheMax = WorksheetFunction.MAX(Range(“A:A”)) MsgBox TheMax End 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å lignende måte. Du kan for eksempel bruke LARGE-funksjonen til å bestemme den k'te største verdien i et område. Følgende uttrykk demonstrerer dette:
SecondHighest = WorksheetFunction.LARGE(Range(“A:A”),2)
Legg merke til at LARGE-funksjonen bruker to argumenter. Den andre argumentet representerer den k- te del - 2, i dette tilfellet (den nest største verdi).
Beregning av boliglånsbetaling
Det neste eksemplet bruker 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 InRate As Double Dim LoanAmt As Double Dunkle perioder så lenge IntRate = 0,0625 / 12 Perioder = 30 * 12 LoanAmt = 150 000 MsgBox WorksheetFunction.PMT(IntRate, Periods, -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)
Men å bruke variabler for å lagre parameterne gjør koden lettere å lese og endre om nødvendig.
Bruke en oppslagsfunksjon
Følgende eksempel bruker VBAs InputBox- og MsgBox-funksjoner, pluss Excels VLOOKUP-funksjon. Den ber om et delenummer og får deretter prisen fra en oppslagstabell. Nedenfor er området A1:B13 kalt Prisliste.
Serien, kalt Prisliste, inneholder priser på deler.
Sub GetPrice()
Dim PartNum Som Variant
Dim Pris Som Dobbel
PartNum = InputBox(“Skriv inn delenummeret”)
Ark ("Priser"). Aktiver
Pris = ArbeidsarkFunksjon.VLOOKUP(DeleNum, Range(“Prisliste”), 2, False)
MsgBox PartNum & " kostnader " & pris
End Sub
Slik fungerer GetPrice-prosedyren:
VBAs InputBox-funksjon ber brukeren om et delenummer.
Delenummeret brukeren angir er tilordnet PartNum-variabelen.
Den neste setningen aktiverer regnearket Priser, i tilfelle det ikke allerede er det aktive arket.
Koden bruker VLOOKUP-funksjonen for å finne delenummeret i tabellen.
Legg merke til at argumentene du bruker i denne setningen er de samme som du ville brukt med funksjonen i en regnearkformel. Denne setningen tildeler resultatet av funksjonen til prisvariabelen.
Koden viser prisen for delen via MsgBox-funksjonen.
Denne prosedyren har ingen feilhåndtering, og den mislykkes totalt hvis du oppgir et ikke-eksisterende delenummer. (Prøv det.) Hvis dette var et faktisk program som brukes i en faktisk virksomhet, vil du legge til noen utsagn som håndterer feil mer elegant.
Du kan ikke bruke dialogboksen innlimingsfunksjon i Excel til å sette inn en regnearkfunksjon i en VBA-modul. Skriv heller inn slike funksjoner på gammeldags måte: for hånd. Du kan imidlertid bruke dialogboksen Lim inn funksjon for å identifisere funksjonen du vil bruke og finne ut om argumentene.
Du kan også dra nytte av VBEs Auto List Members-alternativ, som viser en rullegardinliste over alle regnearkfunksjoner. Bare skriv Application.WorksheetFunction , etterfulgt av et punktum. Deretter ser du en liste over funksjonene du kan bruke. Hvis denne funksjonen ikke fungerer, velg VBEs Verktøy → Alternativer-kommandoen, klikk på Editor-fanen og sett av ved siden av Auto List Members.
Få en liste over regnearkfunksjoner som du kan bruke i VBA-koden.
Nykommere til VBA blander ofte VBAs innebygde funksjoner og Excels arbeidsbokfunksjoner. En god huskeregel er at VBA ikke prøver å finne opp hjulet på nytt. For det meste dupliserer ikke VBA Excel-regnearkfunksjoner.
For de fleste regnearkfunksjoner som ikke er tilgjengelige som metoder for WorksheetFunction-objektet, kan du bruke en tilsvarende innebygd VBA-operatør eller funksjon. MOD-regnearkfunksjonen er for eksempel ikke tilgjengelig i WorksheetFunction-objektet fordi VBA har en ekvivalent: den innebygde Mod-operatoren.
Bunnlinjen? Hvis du trenger å bruke en funksjon, må du først finne ut om VBA har noe som dekker dine behov. Hvis ikke, sjekk ut regnearkfunksjonene. Hvis alt annet mislykkes, kan du kanskje skrive en egendefinert funksjon ved å bruke VBA.
Hvordan blokkere Microsoft Word fra å åpne filer i skrivebeskyttet modus på Windows Microsoft Word åpner filer i skrivebeskyttet modus, noe som gjør det umulig å redigere dem? Ikke bekymre deg, metodene er nedenfor
Slik fikser du feil ved utskrift av feil Microsoft Word-dokumenter Feil ved utskrift av Word-dokumenter med endrede fonter, rotete avsnitt, manglende tekst eller tapt innhold er ganske vanlig. Men ikke gjør det
Hvis du har brukt pennen eller merkepennen til å tegne på PowerPoint-lysbildene dine under en presentasjon, kan du lagre tegningene til neste presentasjon eller slette dem, slik at du neste gang du viser dem starter med rene PowerPoint-lysbilder. Følg disse instruksjonene for å slette penn- og merkepenntegninger: Slette linje én på […]
Stilbiblioteket inneholder CSS-filer, Extensible Stylesheet Language-filer (XSL) og bilder som brukes av forhåndsdefinerte mastersider, sideoppsett og kontroller i SharePoint 2010. For å finne CSS-filer i stilbiblioteket til et publiseringsnettsted: Velg Site Actions→ View Alt innhold på nettstedet. Innholdet på nettstedet vises. Style-biblioteket ligger i […]
Ikke overveld publikum med gigantiske tall. I Microsoft Excel kan du forbedre lesbarheten til dashbordene og rapportene dine ved å formatere tallene dine slik at de vises i tusenvis eller millioner.
Lær hvordan du bruker SharePoints sosiale nettverksverktøy som lar enkeltpersoner og grupper kommunisere, samarbeide, dele og koble til.
Julianske datoer brukes ofte i produksjonsmiljøer som et tidsstempel og hurtigreferanse for et batchnummer. Denne typen datokoding lar forhandlere, forbrukere og serviceagenter identifisere når et produkt ble laget, og dermed alderen på produktet. Julianske datoer brukes også i programmering, militæret og astronomi. Forskjellig […]
Du kan lage en nettapp i Access 2016. Så hva er en nettapp egentlig? Vel, nettet betyr at det er online, og appen er bare en forkortelse for "applikasjon". En Custom Web App er en online databaseapplikasjon som du får tilgang til fra skyen ved hjelp av en nettleser. Du bygger og vedlikeholder nettappen i skrivebordsversjonen […]
De fleste sidene i SharePoint 2010 viser en liste over navigasjonskoblinger på hurtigstartlinjen langs venstre side av siden. Hurtigstartlinjen viser koblinger til innhold på nettstedet som lister, biblioteker, nettsteder og publiseringssider. Hurtigstartlinjen inneholder to svært viktige lenker: Linken for alt nettstedinnhold: […]
For enkle problemer finner Solver i Excel vanligvis raskt de optimale Solver-variabelverdiene for objektivfunksjonen. Men i noen tilfeller har Solver problemer med å finne Solver-variabelverdiene som optimerer objektivfunksjonen. I disse tilfellene viser Solver vanligvis en melding eller en feilmelding som beskriver eller diskuterer problemet som […]
