Funkcie ako VLOOKUP, COUNTIF, SUMIF sa nazývajú funkcie pracovného hárka. Funkcie, ktoré sú preddefinované v programe Excel a pripravené na použitie na pracovnom hárku, sú vo všeobecnosti funkciami pracovného hárka. Vo VBA nemôžete meniť ani zobrazovať kód za týmito funkciami.
Na druhej strane užívateľom definované funkcie a funkcie špecifické pre VBA ako MsgBox alebo InputBox sú funkcie VBA.
Všetci vieme, ako používať funkcie VBA vo VBA. Ale čo keď chceme použiť VLOOKUP vo VBA. Ako to urobíme? V tomto článku sa budeme zaoberať presne tým.
Použitie funkcií pracovného hárka vo VBA
Na prístup k funkcii pracovného hárka používame triedu Application. Takmer všetky funkcie pracovného hárka sú zaradené do triedy Application.WorksheetFunction. A pomocou bodového operátora máte prístup ku všetkým.
Do ľubovoľného pododseku napíšte Application.WorksheetFunction. A začnite písať názov funkcie. Inteligencia VBA zobrazí názov funkcií dostupných na použitie. Keď vyberiete názov funkcie, bude požadovať premenné, ako každá funkcia v programe Excel. Ale budete musieť odovzdať premenné v zrozumiteľnom formáte VBA. Ak napríklad chcete odovzdať rozsah A1: A10, budete ho musieť odovzdať ako objekt rozsahu, ako je napríklad rozsah („A1: A10“).
Poďme teda použiť niektoré funkcie pracovného hárka, aby sme to lepšie pochopili.
Ako používať funkciu VLOOKUP vo VBA
Na ukážku toho, ako môžete vo VBA používať funkciu VLOOKUP, tu uvádzam ukážkové údaje. Potrebujem ukázať meno a mesto daného prihlasovacieho ID v okne správy pomocou VBA. Údaje sa šíria v rozsahu A1: K26.
Stlačením klávesov ALT+F11 otvorte VBE a vložte modul.
Pozrite sa na nižšie uvedený kód.
Sub WsFuncitons () Dim loginID as String Dim name, city As String loginID = "AHKJ_1-3357042451" "Using VLOOKUP function to get name of given id in table name = Application.WorksheetFunction.VLookup (loginID, Range (" A1: K26 " ), 2, 0) 'Použitie funkcie VLOOKUP na získanie mesta s daným ID v tabuľke city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & „Mesto:“ & mesto) End Sub
Keď spustíte tento kód, získate tento výsledok.
Môžete vidieť, ako rýchlo VBA vytlačí výsledok do poľa so správou. Teraz sa pozrime na kód.
Ako to funguje?
1.
Dim loginID ako reťazec
Dim názov, mesto As String
Najprv sme deklarovali dve premenné typu reťazec na uloženie výsledku vráteného funkciou VLOOKUP. Použil som premenné typu reťazec, pretože som si istý, že výsledkom vráteným nástrojom VLOOKUP bude hodnota reťazca. Ak sa od funkcie vášho pracovného hárka očakáva, že vráti číslo, dátum, rozsah atď., Použite tento druh premennej na uloženie výsledku. Ak si nie ste istí, aký druh hodnoty vráti funkcia pracovného hárka, použite premenné typu variant.
2.
loginID = "AHKJ_1-3357042451"
Ďalej sme použili premennú loginID na uloženie vyhľadávanej hodnoty. Tu sme použili napevno zadanú hodnotu. Môžete tiež použiť referencie. Napríklad. Hodnotu Range ("A2"). Value môžete použiť na dynamickú aktualizáciu vyhľadávanej hodnoty z rozsahu A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Tu používame funkciu VLOOKUP na získanie. Teraz, keď funkciu napravíte a otvoríte zátvorku, zobrazí sa vám požadované argumenty, ale nie také popisné, ako sa zobrazujú v programe Excel. Presvedčte sa sami.
Musíte si zapamätať, ako a akú premennú musíte použiť. Kedykoľvek sa môžete vrátiť späť do pracovného hárka, kde nájdete podrobnosti popisných premenných.
Tu je hodnota vyhľadávania Arg1. Pre Arg1 používame prihlasovacie ID. Vyhľadávacia tabuľka je Arg2. Pre Arg2 sme použili rozsah („A1: K26“). Všimnite si toho, že sme nepoužili priamo A2: K26 ako v programe Excel. Index stĺpcov je Arg3. Pre Arg3 sme použili 2, pretože názov je v druhom stĺpci. Typ vyhľadávania je Arg4. Použili sme 0 ako Arg4.
mesto = Application.WorksheetFunction.VLookup (prihlasovacie ID, rozsah ("A1: K26"), 4, 0)
Podobne dostaneme názov mesta.
4.
MsgBox ("Name:" & name & vbLf & "City:" & city)
Nakoniec vytlačíme meno a mesto pomocou programu Messagebox.
Prečo používať funkciu pracovného hárka vo VBA?
Funkcie pracovného hárka majú silu obrovských výpočtov a nebude múdre ignorovať výkon funkcií pracovného hárka. Ak napríklad chceme štandardnú odchýlku množiny údajov a ak na to chceme napísať celý kód, môže ti to trvať hodiny. Ak však viete, ako používať funkciu listu STDEV.P vo VBA na získanie výpočtu jedným ťahom.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Použitie viacerých funkcií pracovného hárka VBA
Povedzme, že na získanie niektorých hodnôt musíme použiť indexovú zhodu. Ako by ste teraz sprísnili vzorec vo VBA. Toto hádam napíšeš:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Nie je to zlé, ale je to zdĺhavé. Správny spôsob použitia viacerých funkcií je použitie bloku With. Pozrite sa na nasledujúci príklad:
Sub IndMtch () With Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (numbers) End With End Sub
Ako vidíte, pomocou bloku With som VBA oznámil, že budem používať vlastnosti a funkcie aplikácie Application.WorksheetFunction. Nepotrebujem to teda definovať všade. Na prístup k funkciám INDEX, MATCH, VLOOKUP a STDEV.P som práve použil bodkový operátor. Keď použijeme príkaz End With, nebudeme môcť pristupovať k funkciám bez použitia plne kvalifikovaných názvov funkcií.
Ak teda musíte vo VBA používať viac funkcií pracovného hárka, použite príkaz block.
Nie všetky funkcie pracovného hárka sú k dispozícii prostredníctvom aplikácie.WorksheetFunction
Niektoré funkcie pracovného hárka sú priamo dostupné na použitie vo VBA. Objekt Application.WorksheetFunction nemusíte používať.
Napríklad funkcie ako Len (), ktoré sa používajú na získanie počtu znakov v reťazci, vľavo, vpravo, v strede, orezania, ofsetu atď. Tieto funkcie je možné priamo použiť vo VBA. Tu je príklad.
Sub GetLen () Strng = „Ahoj“ Debug.Print (Len (strng)) Koniec Sub
Pozrite sa, tu sme použili funkciu LEN bez použitia objektu Application.WorksheetFunction.
Podobne môžete používať aj ďalšie funkcie ako ľavý, pravý, stredný, znak atď.
Sub GetLen () Strng = "Ahoj" Debug.Print (Len (strng)) Debug.Print (vľavo (strng, 2)) Debug.Print (vpravo (strng, 1)) Debug.Print (stred (strng, 3, 2)) End Sub
Keď spustíte vyššie uvedený podsek, vráti sa:
5 On o ll
Takže áno, chlapci, takto môžete použiť funkciu pracovného hárka programu Excel vo VBA. Dúfam, že som bol dostatočne vysvetľujúci a tento článok vám pomohol. Ak máte akékoľvek otázky týkajúce sa tohto článku alebo čohokoľvek iného súvisiaceho s VBA, opýtajte sa ich v sekcii komentárov nižšie. Do tej doby si môžete prečítať o ďalších súvisiacich témach nižšie.
Čo je funkcia CSng v programe Excel VBA | Funkcia SCng je funkcia VBA, ktorá prevádza ľubovoľný typ údajov na číslo s pohyblivou rádovou čiarkou s jednoduchou presnosťou („vzhľadom na to, že ide o číslo“). Väčšinou používam funkciu CSng na prevod čísel formátovaných v texte na skutočné čísla.
Ako získať text a číslo opačne prostredníctvom VBA v programe Microsoft Excel | Na obrátenie čísla a textu používame vo VBA slučky a strednú funkciu. 1234 sa prevedie na 4321, „vy“ sa zmení na „uoy“. Tu je úryvok.
Formátujte údaje pomocou vlastných formátov čísel pomocou jazyka VBA v programe Microsoft Excel | Na zmenu formátu čísla konkrétnych stĺpcov v programe Excel použite tento útržok VBA. Jedným kliknutím prekryje formát čísla zo zadaného do zadaného formátu.
Použitie udalosti zmeny pracovného hárka na spustenie makra po vykonaní akejkoľvek zmeny | Na spustenie makra vždy, keď sa aktualizuje hárok, preto používame udalosti pracovného hárka VBA.
V prípade zmeny vykonanej na hárku v určenom rozsahu spustite makro | Na spustenie kódu makra pri zmene hodnoty v určenom rozsahu použite tento kód VBA. Zistí všetky zmeny vykonané v uvedenom rozsahu a spustí udalosť.
Najjednoduchší kód VBA na zvýraznenie aktuálneho použitia riadkov a stĺpcov | Pomocou tohto malého útržku VBA zvýraznite aktuálny riadok a stĺpec listu.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
