Čeprav VBA ponuja dostojen nabor vgrajenih funkcij, morda ne boste vedno našli točno tistega, kar potrebujete. Na srečo lahko večino funkcij Excelovega delovnega lista uporabite tudi v svojih postopkih VBA. Edine funkcije delovnega lista, ki jih ne morete uporabiti, so tiste, ki imajo enakovredno funkcijo VBA. Na primer, ne morete uporabiti Excelove funkcije RAND (ki generira naključno število), ker ima VBA enakovredno funkcijo: Rnd.
VBA omogoča Excelove funkcije delovnega lista na voljo prek predmeta WorksheetFunction, ki je vsebovan v objektu Application. Tukaj je primer, kako lahko uporabite Excelovo funkcijo SUM v stavku VBA:
Skupaj = Application.WorksheetFunction.SUM(Obseg(“A1:A12”))
Del izraza lahko izpustite bodisi del aplikacije bodisi del funkcije delovnega lista. V obeh primerih VBA ugotovi, kaj počnete. Z drugimi besedami, vsi ti trije izrazi delujejo popolnoma enako:
Skupaj = Application.WorksheetFunction.SUM(Obseg(“A1:A12”))
Skupaj = Funkcija delovnega lista.SUM(Obseg(“A1:A12”))
Skupaj = aplikacija.SUM(obseg(“A1:A12”))
Moja osebna želja je, da uporabim del WorksheetFunction samo zato, da je popolnoma jasno, da koda uporablja funkcijo Excel.
Primeri funkcij delovnega lista
Tukaj odkrijete, kako uporabljati funkcije delovnega lista v izrazih VBA.
Iskanje največje vrednosti v območju
Tukaj je primer, ki prikazuje, kako uporabiti Excelovo funkcijo delovnega lista MAX v postopku VBA. Ta postopek prikaže največjo vrednost v stolpcu A aktivnega delovnega lista:
Uporaba funkcije delovnega lista v vaši kodi VBA.
Sub ShowMax()
Zatemni TheMax kot dvojno
TheMax = WorksheetFunction.MAX(Razpon(“A:A”))
MsgBox TheMax
Končni sub
S funkcijo MIN lahko dobite najmanjšo vrednost v obsegu. In kot bi lahko pričakovali, lahko na podoben način uporabite druge funkcije delovnega lista. S funkcijo LARGE lahko na primer določite k -to največjo vrednost v obsegu. Naslednji izraz to dokazuje:
SecondHighest = WorksheetFunction.LARGE(Razpon(“A:A”),2)
Upoštevajte, da funkcija LARGE uporablja dva argumenta. Drugi argument predstavlja k- ti del — 2, v tem primeru (druga največja vrednost).
Izračun hipotekarnega plačila
Naslednji primer uporablja funkcijo delovnega lista PMT za izračun hipotekarnega plačila. Za shranjevanje podatkov, ki so posredovani funkciji Pmt kot argumenti, se uporabljajo tri spremenljivke. V oknu s sporočilom se prikaže izračunano plačilo.
Sub PmtCalc()
Dim IntRate As Double
Dim LoanAmt kot dvojno
Tako dolga zatemnjena obdobja
IntRate = 0,0625 / 12
Obdobja = 30 * 12
Znesek posojila = 150000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)
Končni sub
Kot kaže naslednja izjava, lahko vrednosti vstavite tudi neposredno kot argumente funkcije:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Vendar pa uporaba spremenljivk za shranjevanje parametrov olajša branje in po potrebi spreminjanje kode.
Uporaba funkcije iskanja
Naslednji primer uporablja funkcije VBA InputBox in MsgBox ter Excelovo funkcijo VLOOKUP. Zahteva številko dela, nato pa dobi ceno iz iskalne tabele. Spodaj je razpon A1:B13 poimenovan Cenik.
Razpon, imenovan Cenik, vsebuje cene delov.
Sub GetPrice()
Dim PartNum kot različica
Dim Price As Double
PartNum = InputBox (»vnesite številko dela«)
Listi (“Cene”). Aktivirajte
Cena = WorksheetFunction.VLOOKUP(PartNum, Range(“PriceList”), 2, False)
MsgBox PartNum & "stroški" & Cena
Končni sub
Postopek GetPrice deluje tako:
-
Funkcija VBA InputBox od uporabnika zahteva številko dela.
-
Številka dela, ki jo vnese uporabnik, je dodeljena spremenljivki PartNum.
-
Naslednji stavek aktivira delovni list Cene, samo v primeru, da še ni aktiven list.
-
Koda uporablja funkcijo VLOOKUP za iskanje številke dela v tabeli.
-
Upoštevajte, da so argumenti, ki jih uporabljate v tem stavku, enaki tistim, ki bi jih uporabili s funkcijo v formuli delovnega lista. Ta stavek dodeli rezultat funkcije spremenljivki Price.
-
Koda prikaže ceno za del prek funkcije MsgBox.
Ta postopek nima nikakršnega ravnanja z napakami in ne uspe, če vnesete neobstoječo številko dela. (Poskusite.) Če bi bila to dejanska aplikacija, ki se uporablja v dejanskem poslu, bi želeli dodati nekaj stavkov, ki bolj elegantno obravnavajo napake.
Vnos funkcij delovnega lista
Za vstavljanje funkcije delovnega lista v modul VBA ne morete uporabiti pogovornega okna Funkcija lepljenja Excel. Namesto tega vnesite takšne funkcije na staromoden način: ročno. Vendar pa lahko uporabite pogovorno Funkcija okno Prilepi opredeliti funkcijo, ki jo želite uporabiti, in izvedeti o svojih argumentov.
Izkoristite lahko tudi možnost samodejnega seznama članov VBE, ki prikaže spustni seznam vseh funkcij delovnega lista. Samo vnesite Application.WorksheetFunction , ki ji sledi pika. Nato vidite seznam funkcij, ki jih lahko uporabljate. Če ta funkcija ne deluje, izberite ukaz VBE Orodja → Možnosti, kliknite zavihek Urejevalnik in potrdite polje Samodejni člani seznama.
Pridobivanje seznama funkcij delovnega lista, ki jih lahko uporabite v kodi VBA.
Več o uporabi funkcij delovnega lista
Novinci v VBA pogosto zamenjujejo vgrajene funkcije VBA in funkcije delovnega zvezka Excel. Dobro pravilo, ki si ga morate zapomniti, je, da VBA ne poskuša na novo izumiti kolesa. VBA večinoma ne podvaja funkcij Excelovega delovnega lista.
Za večino funkcij delovnega lista, ki niso na voljo kot metode predmeta WorksheetFunction, lahko uporabite enakovredni vgrajeni operator ali funkcijo VBA. Funkcija delovnega lista MOD na primer ni na voljo v objektu WorksheetFunction, ker ima VBA enakovreden: njegov vgrajeni operater Mod.
Spodnja črta? Če želite uporabiti funkcijo, najprej ugotovite, ali ima VBA nekaj, kar ustreza vašim potrebam. Če ne, si oglejte funkcije delovnega lista. Če vse drugo ne uspe, boste morda lahko napisali funkcijo po meri z uporabo VBA.