Hoci VBA ponúka slušný sortiment vstavaných funkcií, nemusíte vždy nájsť presne to, čo potrebujete. Našťastie môžete vo svojich postupoch VBA použiť aj väčšinu funkcií pracovného hárka programu Excel. Jediné funkcie hárka, ktoré nemôžete použiť, sú tie, ktoré majú ekvivalentnú funkciu VBA. Napríklad nemôžete použiť funkciu RAND programu Excel (ktorá generuje náhodné číslo), pretože VBA má ekvivalentnú funkciu: Rnd.
VBA sprístupňuje funkcie pracovného hárka programu Excel prostredníctvom objektu WorksheetFunction, ktorý je obsiahnutý v objekte Application. Tu je príklad, ako môžete použiť funkciu SUM programu Excel vo príkaze VBA:
Celkom = Application.WorksheetFunction.SUM(Rozsah(“A1:A12”))
Vo výraze môžete vynechať časť Application alebo WorksheetFunction. V oboch prípadoch VBA zistí, čo robíte. Inými slovami, všetky tieto tri výrazy fungujú úplne rovnako:
Celkom = Application.WorksheetFunction.SUM(Rozsah(“A1:A12”))
Celkom = Funkcia hárka.SUM(Rozsah(“A1:A12”))
Celkom = Aplikácia.SÚČET(Rozsah(“A1:A12”))
Mojou osobnou preferenciou je použiť časť WorksheetFunction, aby bolo úplne jasné, že kód používa funkciu Excel.
Príklady funkcií pracovného listu
Tu zistíte, ako používať funkcie pracovného hárka vo výrazoch VBA.
Nájdenie maximálnej hodnoty v rozsahu
Tu je príklad, ktorý ukazuje, ako používať funkciu pracovného hárka Excelu MAX v procedúre VBA. Tento postup zobrazí maximálnu hodnotu v stĺpci A aktívneho pracovného hárka:
Použitie funkcie pracovného hárka v kóde VBA.
Sub ShowMax()
Dim TheMax As Double
TheMax = WorksheetFunction.MAX(Rozsah(“A:A”))
MsgBox TheMax
End Sub
Na získanie najmenšej hodnoty v rozsahu môžete použiť funkciu MIN. A ako sa dalo očakávať, podobným spôsobom môžete použiť aj ďalšie funkcie pracovného hárka. Môžete napríklad použiť funkciu LARGE na určenie k -tej najväčšej hodnoty v rozsahu. Dokazuje to nasledujúci výraz:
Druhý najvyšší = Funkcia pracovného hárka.LARGE(Rozsah(“A:A”),2)
Všimnite si, že funkcia LARGE používa dva argumenty. Druhý argument predstavuje k -tu časť — v tomto prípade 2 (druhá najväčšia hodnota).
Výpočet splátky hypotéky
Nasledujúci príklad používa funkciu pracovného hárka PMT na výpočet splátky hypotéky. Tri premenné sa používajú na ukladanie údajov, ktoré sú odovzdané funkcii Pmt ako argumenty. V okne so správou sa zobrazí vypočítaná platba.
Sub PmtCalc()
Dim IntRate As Double
Dim LoanAmt As Double
Dim Periods As Long
IntRate = 0,0625 / 12
Obdobia = 30 * 12
Pôžička = 150 000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)
End Sub
Ako ukazuje nasledujúci príkaz, hodnoty môžete vložiť aj priamo ako argumenty funkcie:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Použitie premenných na ukladanie parametrov však uľahčuje čítanie a úpravu kódu, ak je to potrebné.
Použitie funkcie vyhľadávania
V nasledujúcom príklade sú použité funkcie InputBox a MsgBox VBA plus funkcia VLOOKUP Excelu. Vyzve na zadanie čísla dielu a potom získa cenu z vyhľadávacej tabuľky. Nižšie je rozsah A1:B13 pomenovaný Cenník.
Sortiment s názvom Cenník obsahuje ceny dielov.
Sub GetPrice()
Dim PartNum ako variant
Znížiť cenu ako dvojitú
PartNum = InputBox(“Zadajte číslo dielu”)
Tabuľky („Ceny“). Aktivovať
Cena = WorksheetFunction.VLOOKUP(PartNum, Range(“Cenník”), 2, False)
MsgBox PartNum & „costs“ & Price
End Sub
Postup GetPrice funguje takto:
-
Funkcia InputBox VBA žiada používateľa o číslo dielu.
-
Číslo dielu, ktoré používateľ zadá, je priradené k premennej PartNum.
-
Nasledujúci príkaz aktivuje pracovný hárok Ceny, len v prípade, že to ešte nie je aktívny hárok.
-
Kód používa funkciu VLOOKUP na nájdenie čísla dielu v tabuľke.
-
Všimnite si, že argumenty, ktoré používate v tomto príkaze, sú rovnaké ako tie, ktoré by ste použili s funkciou vo vzorci pracovného hárka. Tento príkaz priradí výsledok funkcie do premennej Cena.
-
Kód zobrazí cenu dielu cez funkciu MsgBox.
Tento postup neobsahuje žiadne spracovanie chýb a zlyhá, ak zadáte neexistujúce číslo dielu. (Vyskúšajte.) Ak by toto bola skutočná aplikácia, ktorá sa používa v skutočnom podnikaní, mali by ste pridať niekoľko vyhlásení, ktoré sa s chybami vyrovnávajú elegantnejšie.
Zadávanie funkcií pracovného hárka
Dialógové okno funkcie prilepenia programu Excel nemôžete použiť na vloženie funkcie pracovného hárka do modulu VBA. Namiesto toho zadajte tieto funkcie staromódnym spôsobom: ručne. Môžete však použiť dialógové okno Prilepiť funkciu na identifikáciu funkcie, ktorú chcete použiť, a na zistenie jej argumentov.
Môžete tiež využiť možnosť automatického zoznamu členov VBE, ktorá zobrazuje rozbaľovací zoznam všetkých funkcií pracovného hárka. Stačí zadať Application.WorksheetFunction , za ktorým nasleduje bodka. Potom uvidíte zoznam funkcií, ktoré môžete použiť. Ak táto funkcia nefunguje, vyberte príkaz Nástroje → Možnosti vo VBE, kliknite na kartu Editor a začiarknite položku Auto List Members.
Získanie zoznamu funkcií pracovného hárka, ktoré môžete použiť vo svojom kóde VBA.
Viac o používaní funkcií pracovného hárka
Nováčikovia vo VBA si často mýlia vstavané funkcie VBA a funkcie zošita Excelu. Je dobré si zapamätať pravidlo, že VBA sa nesnaží znovu vynájsť koleso. Vo väčšine prípadov VBA neduplikuje funkcie pracovného hárka programu Excel.
Pre väčšinu funkcií pracovného hárka, ktoré nie sú dostupné ako metódy objektu WorksheetFunction, môžete použiť ekvivalentný vstavaný operátor alebo funkciu VBA. Napríklad funkcia pracovného hárka MOD nie je dostupná v objekte WorksheetFunction, pretože VBA má ekvivalent: vstavaný operátor Mod.
Spodná čiara? Ak potrebujete použiť funkciu, najprv zistite, či má VBA niečo, čo vyhovuje vašim potrebám. Ak nie, pozrite si funkcie pracovného hárka. Ak všetko ostatné zlyhá, možno budete môcť napísať vlastnú funkciu pomocou VBA.