Funkcije, kot so VLOOKUP, COUNTIF, SUMIF, imenujemo funkcije delovnega lista. Na splošno so funkcije, ki so vnaprej določene v Excelu in pripravljene za uporabo na delovnem listu, funkcije delovnega lista. Kode za temi funkcijami v VBA ne morete spremeniti ali videti.
Po drugi strani so uporabniško definirane funkcije in funkcije, značilne za VBA, kot sta MsgBox ali InputBox, funkcije VBA.
Vsi vemo, kako uporabljati funkcije VBA v VBA. Kaj pa, če želimo uporabiti VLOOKUP v VBA. Kako to naredimo? V tem članku bomo natančno to raziskali.
Uporaba funkcij delovnega lista v VBA
Za dostop do funkcije delovnega lista uporabljamo razred Application. Skoraj vse funkcije delovnega lista so navedene v razredu Application.WorksheetFunction. Z uporabo operaterja dot lahko dostopate do vseh.
V katero koli podrejeno datoteko napišite Application.WorksheetFunction. In začnite pisati ime funkcije. Inteligenca VBA bo prikazala imena funkcij, ki so na voljo za uporabo. Ko izberete ime funkcije, bo zahteval spremenljivke, tako kot katera koli funkcija v Excelu. Spremenljivke pa boste morali posredovati v razumljivi obliki VBA. Na primer, če želite prenesti obseg A1: A10, ga boste morali prenesti kot predmet obsega, kot je obseg ("A1: A10").
Zato za boljše razumevanje uporabimo nekatere funkcije delovnega lista.
Kako uporabljati funkcijo VLOOKUP v VBA
Za prikaz, kako lahko uporabite funkcijo VLOOKUP v VBA, imam tukaj vzorčne podatke. V polju za sporočila z uporabo VBA moram prikazati ime in mesto danega ID -ja za prijavo. Podatki so razporejeni v območju A1: K26.
Pritisnite ALT+F11, da odprete VBE in vstavite modul.
Oglejte si spodnjo kodo.
Sub WsFuncitons () Zatemni loginID kot niz Nizko ime, mesto As String loginID = "AHKJ_1-3357042451" 'S funkcijo VLOOKUP dobite ime danega ID-ja v ime tabele = Application.WorksheetFunction.VLookup (loginID, obseg ("A1: K26" ), 2, 0) 'Uporaba funkcije VLOOKUP za pridobitev mesta danega ID -ja v mestu tabele = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "Mesto:" & mesto) End Sub
Ko zaženete to kodo, boste dobili ta rezultat.
Vidite lahko, kako hitro VBA natisne rezultat v polje za sporočila. Zdaj pa preučimo kodo.
Kako deluje?
1.
Zatemni loginID kot niz
Zatemnjeno ime, mesto As String
Najprej smo razglasili dve spremenljivki vrste niza za shranjevanje rezultata, ki ga vrne funkcija VLOOKUP. Uporabil sem spremenljivke vrste niza, ker sem prepričan, da bo rezultat, ki ga vrne VLOOKUP, vrednost niza. Če se pričakuje, da bo funkcija vašega delovnega lista vrnila številko, datum, obseg itd. Vrednost, uporabite takšno spremenljivko za shranjevanje rezultata. Če niste prepričani, kakšno vrednost bo vrnila funkcija delovnega lista, uporabite spremenljivke vrste variante.
2.
loginID = "AHKJ_1-3357042451"
Nato smo za shranjevanje iskalne vrednosti uporabili spremenljivko loginID. Tu smo uporabili trdo kodirano vrednost. Uporabite lahko tudi reference. Na primer. Uporabite lahko obseg ("A2"). Vrednost za dinamično posodabljanje iskalne vrednosti iz območja A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, obseg ("A1: K26"), 2, 0)
Tukaj uporabljamo funkcijo VLOOKUP, da dobimo. Zdaj, ko poravnate funkcijo in odprete oklepaj, vam bo prikazala potrebne argumente, vendar ne tako opisne, kot je prikazano v Excelu. Prepričajte se sami.
Zapomniti si morate, kako in katero spremenljivko morate uporabiti. Vedno se lahko vrnete na delovni list in si ogledate podrobnosti opisne spremenljivke.
Tu je iskalna vrednost Arg1. Za Arg1 uporabljamo loginID. Iskalna tabela je Arg2. Za Arg2 smo uporabili Range ("A1: K26"). Upoštevajte, da A2: K26 nismo uporabljali neposredno kot v Excelu. Indeks stolpcev je Arg3. Za Arg3 smo uporabili 2, saj je ime v drugem stolpcu. Vrsta iskanja je Arg4. Kot Arg4 smo uporabili 0.
city = Application.WorksheetFunction.VLookup (loginID, obseg ("A1: K26"), 4, 0)
Podobno dobimo tudi ime mesta.
4.
MsgBox ("Ime:" & ime & vbLf & "Mesto:" & mesto)
Nazadnje natisnemo ime in mesto z uporabo sporočila.
Zakaj uporabljati funkcijo delovnega lista v VBA?
Funkcije delovnega lista imajo moč ogromnih izračunov in ne bo pametno prezreti moči funkcij delovnega lista. Na primer, če želimo standardni odklon nabora podatkov in želite za to napisati celotno kodo, lahko traja nekaj ur. Če pa veste, kako uporabiti funkcijo delovnega lista STDEV.P v VBA, da dobite izračun naenkrat.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Uporaba več funkcij delovnega lista VBA
Recimo, da moramo za pridobitev nekaterih vrednosti uporabiti ujemanje indeksa. Zdaj, kako bi zapisali formulo v VBA. Mislim, da boš napisal tole:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
To ni narobe, vendar je dolgotrajno. Pravilen način uporabe več funkcij je uporaba bloka With. Oglejte si spodnji primer:
Sub IndMtch () Z Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (številke) Končaj z End Sub
Kot lahko vidite, sem z blokom With povedal VBA, da bom uporabljal lastnosti in funkcije Application.WorksheetFunction. Zato mi ga ni treba povsod opredeliti. Ravnokar sem uporabil operater dot za dostop do funkcij INDEX, MATCH, VLOOKUP in STDEV.P. Ko uporabimo stavek End With, ne moremo dostopati do funkcij brez uporabe popolnoma kvalificiranih imen funkcij.
Če morate torej v VBA uporabiti več funkcij delovnega lista, uporabite z blokom.
Vse funkcije delovnega lista niso na voljo v programu Application.WorksheetFunction
Nekatere funkcije delovnega lista so neposredno na voljo za uporabo v VBA. Ni vam treba uporabljati predmeta Application.WorksheetFunction.
Na primer funkcije, kot je Len (), ki se uporablja za pridobivanje števila znakov v nizu, levo, desno, sredi, obrezovanje, odmik itd. Te funkcije je mogoče neposredno uporabljati v VBA. Tukaj je primer.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Glej, tukaj smo uporabili funkcijo LEN brez uporabe predmeta Application.WorksheetFunction.
Podobno lahko uporabite tudi druge funkcije, kot so levo, desno, sredina, znak itd.
Sub GetLen () Strng = "Pozdravljeni" Debug.Print (Len (strng)) Debug.Print (levo (strng, 2)) Debug.Print (desno (strng, 1)) Debug.Print (sredina (strng, 3, 2)) End Sub
Ko zaženete zgornji podmeni, se bo vrnilo:
5 On bo
Torej, fantje, tako lahko uporabite funkcijo delovnega lista programa Excel v VBA. Upam, da sem bil dovolj razložen in da vam je ta članek pomagal. Če imate kakršna koli vprašanja v zvezi s tem člankom ali karkoli drugega, povezanega z VBA, vprašajte v spodnjem razdelku za komentarje. Do takrat lahko spodaj preberete o drugih sorodnih temah.
Kaj je funkcija CSng v Excelu VBA | Funkcija SCng je funkcija VBA, ki pretvori kateri koli tip podatkov v eno natančno število s plavajočo vejico ("glede na to, da je to število"). Večinoma uporabljam funkcijo CSng za pretvorbo besedilno oblikovanih številk v dejanske številke.
Kako pridobiti besedilo in številko v obratni smeri prek VBA v Microsoft Excelu | Za obrnitev števila in besedila uporabljamo zanke in funkcijo mid v VBA. 1234 se pretvori v 4321, "ti" pa v "uoy". Tukaj je odlomek.
Oblikujte podatke s številčnimi oblikami po meri z uporabo VBA v Microsoft Excelu | Če želite spremeniti obliko števila določenih stolpcev v Excelu, uporabite ta delček VBA. Zajema obliko zapisa števila, ki je podana določeni obliki, z enim klikom.
Uporaba dogodka spremembe delovnega lista za zagon makra, ko pride do kakršne koli spremembe | Zato za zagon makra vsakič, ko se list posodobi, uporabljamo dogodke delovnega lista VBA.
Zaženite makro, če je na listu v določenem obsegu prišlo do kakršne koli spremembe | Če želite zagnati kodo makra, ko se vrednost v določenem obsegu spremeni, uporabite to kodo VBA. Zazna vsako spremembo v določenem obsegu in sproži dogodek.
Najpreprostejša koda VBA za označevanje trenutne vrstice in stolpca | S tem majhnim delčkom VBA označite trenutno vrstico in stolpec lista.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.