Funkcijos, tokios kaip VLOOKUP, COUNTIF, SUMIF, vadinamos darbalapio funkcijomis. Paprastai funkcijos, kurios yra iš anksto nustatytos „Excel“ ir paruoštos naudoti darbalapyje, yra darbalapio funkcijos. VBA negalite pakeisti ar matyti šių funkcijų kodo.
Kita vertus, vartotojo apibrėžtos funkcijos ir funkcijos, būdingos VBA, pvz., „MsgBox“ arba „InputBox“, yra VBA funkcijos.
Visi žinome, kaip naudotis VBA funkcijomis VBA. Bet ką daryti, jei norime naudoti VLOOKUP VBA. Kaip mes tai darome? Šiame straipsnyje mes išnagrinėsime būtent tai.
Darbo lapo funkcijų naudojimas VBA
Norėdami pasiekti darbalapio funkciją, naudojame programų klasę. Beveik visos darbalapio funkcijos yra išvardytos „Application.WorksheetFunction“ klasėje. Naudodami taškų operatorių, galite juos visus pasiekti.
Bet kuriame poskyryje parašykite Application.WorksheetFunction. Ir pradėkite rašyti funkcijos pavadinimą. VBA intellisense bus rodomas galimų naudoti funkcijų pavadinimas. Kai pasirinksite funkcijos pavadinimą, ji paprašys kintamųjų, kaip ir bet kuri „Excel“ funkcija. Tačiau turėsite perduoti kintamuosius suprantamu VBA formatu. Pvz., Jei norite praeiti diapazoną A1: A10, turėsite jį perduoti kaip diapazono objektą, pvz., Diapazoną („A1: A10“).
Taigi, norėdami geriau suprasti, naudokime kai kurias darbalapio funkcijas.
Kaip naudotis VLOOKUP funkcija VBA
Norėdami parodyti, kaip galite naudoti VLOOKUP funkciją VBA, čia turiu pavyzdinių duomenų. Man reikia parodyti prisijungimo ID vardą ir miestą pranešimų laukelyje naudojant VBA. Duomenys platinami diapazone A1: K26.
Paspauskite ALT+F11, kad atidarytumėte VBE ir įdėtumėte modulį.
Žiūrėkite žemiau esantį kodą.
Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Naudojant funkciją VLOOKUP, norint gauti nurodyto ID pavadinimą lentelės pavadinime = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26") ), 2, 0) 'Naudojant funkciją VLOOKUP, norint gauti nurodytą miesto miestą lentelėje city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "Miestas:" ir miestas) Pabaiga
Kai paleisite šį kodą, gausite šį rezultatą.
Galite pamatyti, kaip greitai VBA spausdina rezultatą į pranešimų laukelį. Dabar panagrinėkime kodą.
Kaip tai veikia?
1.
Pritemdyti prisijungimo ID kaip eilutę
Prastas pavadinimas, miestas kaip eilutė
Pirmiausia paskelbėme du eilutės tipo kintamuosius, kad išsaugotume VLOOKUP funkcijos grąžintą rezultatą. Aš naudojau eilutės tipo kintamuosius, nes esu tikras, kad VLOOKUP grąžintas rezultatas bus eilutės reikšmė. Jei tikimasi, kad jūsų darbalapio funkcija grąžins vertės, datos, diapazono ir kt. Tipą, naudokite tokio tipo kintamąjį, kad išsaugotumėte rezultatą. Jei nesate tikri, kokią vertę grąžins darbalapio funkcija, naudokite variantų tipo kintamuosius.
2.
loginID = "AHKJ_1-3357042451"
Toliau paieškos vertei išsaugoti panaudojome kintamąjį loginID. Čia mes panaudojome koduotą reikšmę. Taip pat galite naudoti nuorodas. Pavyzdžiui. Galite naudoti diapazoną („A2“). Vertė, skirta dinamiškai atnaujinti paieškos vertę iš diapazono A2.
3.
name = Application.WorksheetFunction.VLookup (prisijungimo ID, diapazonas („A1: K26“), 2, 0)
Čia mes naudojame funkciją VLOOKUP, kad gautume. Dabar, kai pataisysite funkciją ir atidarysite skliaustelius, ji parodys reikiamus argumentus, bet ne tokius aprašomuosius, kaip rodoma „Excel“. Pasižiūrėk pats.
Turite prisiminti, kaip ir kokį kintamąjį naudoti. Visada galite grįžti į darbalapį, kad pamatytumėte aprašomojo kintamojo informaciją.
Čia paieškos vertė yra Arg1. „Arg1“ naudojame prisijungimo ID. Peržiūros lentelė yra Arg2. „Arg2“ naudojome diapazoną („A1: K26“). Atminkite, kad mes nenaudojome tiesiogiai A2: K26, kaip tai darome „Excel“. Stulpelių indeksas yra Arg3. „Arg3“ naudojome 2, nes pavadinimas yra antrame stulpelyje. Ieškos tipas yra Arg4. Mes naudojome 0 kaip Arg4.
city = Application.WorksheetFunction.VLookup (prisijungimo ID, diapazonas („A1: K26“), 4, 0)
Panašiai gauname miesto pavadinimą.
4.
„MsgBox“ („Name:“ & name & vbLf & „City:“ & city)
Galiausiai spausdiname vardą ir miestą naudodami „Messagebox“.
Kodėl VBA naudoti darbalapio funkciją?
Skaičiuoklės funkcijos turi didžiulius skaičiavimus ir nebus protinga ignoruoti darbalapio funkcijų galią. Pavyzdžiui, jei norime standartinio duomenų rinkinio nuokrypio ir norite parašyti visą kodą, tai gali užtrukti valandas. Bet jei žinote, kaip VBA naudoti darbalapio funkciją STDEV.P, kad gautumėte skaičiavimą vienu ypu.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Diapazonas („A1: K26“)) Pabaiga
Kelių darbalapio funkcijų naudojimas VBA
Tarkime, kad norėdami gauti kai kurias vertes, turime naudoti indekso atitiktį. Dabar kaip suformuluotumėte formulę VBA. Štai ką, manau, parašysite:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) Pabaiga
Tai nėra klaidinga, bet ilga. Tinkamas būdas naudoti kelias funkcijas yra naudojant bloką Su. Žr. Toliau pateiktą pavyzdį:
Sub IndMtch () Su Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (skaičiai) Pabaiga su pabaiga
Kaip matote, aš naudoju „With block“, norėdamas pasakyti VBA, kad naudosiu „Application.WorksheetFunction“ ypatybes ir funkcijas. Taigi man nereikia jo visur apibrėžti. Aš tiesiog naudoju taškų operatorių, kad pasiekčiau INDEX, MATCH, VLOOKUP ir STDEV.P funkcijas. Kai naudosime „End With“ teiginį, negalėsime pasiekti funkcijų nenaudodami visiškai kvalifikuotų funkcijų pavadinimų.
Taigi, jei VBA turite naudoti kelias darbalapio funkcijas, naudokite su bloku.
Ne visos darbalapio funkcijos pasiekiamos naudojant programą.WorksheetFunction
Kai kurias darbalapio funkcijas galima tiesiogiai naudoti VBA. Jums nereikia naudoti „Application.WorksheetFunction“ objekto.
Pavyzdžiui, tokios funkcijos kaip Len (), kuri naudojama norint gauti simbolių skaičių eilutėje, kairėje, dešinėje, viduryje, apipjaustyti, kompensuoti ir pan. Šios funkcijos gali būti tiesiogiai naudojamos VBA. Štai pavyzdys.
„GetLen“ () Strng = „Sveiki“ derinimo derinys. Spausdinti (Len (strng)) Pabaiga
Žiūrėkite, čia mes naudojome LEN funkciją nenaudodami „Application.WorksheetFunction“ objekto.
Panašiai galite naudoti kitas funkcijas, pvz., Kairę, dešinę, vidurinę, char ir kt.
Sub GetLen () Strng = "Sveiki" Debug.Print (Len (strng)) Debug.Print (kairė (strng, 2)) Debug.Print (dešinė (strng, 1)) Debug.Print (Mid (strng, 3, 2)) Pabaiga
Kai paleisite aukščiau pateiktą antrinį elementą, jis grįš:
5 Jis bus
Taigi taip, vaikinai, taip galite naudoti „Excel“ darbalapio funkciją VBA. Tikiuosi, kad buvau pakankamai aiškus ir šis straipsnis jums padėjo. Jei turite klausimų apie šį straipsnį ar ką nors kitą susijusį VBA, paklauskite žemiau esančiame komentarų skyriuje. Iki tol galite skaityti apie kitas susijusias temas žemiau.
Kas yra „CSng“ funkcija „Excel VBA“ | Funkcija SCng yra VBA funkcija, konvertuojanti bet kokio tipo duomenis į vieno tikslumo slankiojo kablelio skaičių („atsižvelgiant į tai, kad tai yra skaičius“). Aš dažniausiai naudoju CSng funkciją, kad teksto formatuoti skaičiai būtų paversti tikraisiais skaičiais.
Kaip gauti tekstą ir skaičių atvirkščiai per VBA „Microsoft Excel“ | Norėdami pakeisti skaičių ir tekstą, VBA naudojame kilpas ir vidurio funkciją. 1234 bus paverstas į 4321, „jūs“ - į „uoy“. Štai fragmentas.
Formatuokite duomenis pasirinktiniais skaičių formatais naudodami „Microsoft Excel“ VBA | Norėdami pakeisti konkrečių „Excel“ stulpelių skaičių formatą, naudokite šį VBA fragmentą. Tai vienu paspaudimu paverčia nurodyto skaičiaus formatą į nurodytą formatą.
Darbo lapo keitimo įvykio naudojimas makrokomandai paleisti, kai atliekami bet kokie pakeitimai | Taigi, norėdami paleisti makrokomandą, kai lapas atnaujinamas, naudojame VBA darbalapio įvykius.
Vykdykite makrokomandą, jei lape bus atlikti pakeitimai nurodytame diapazone | Jei norite paleisti makrokomandą, kai pasikeičia nurodyto diapazono vertė, naudokite šį VBA kodą. Jis aptinka bet kokius pakeitimus, atliktus nurodytame diapazone, ir suaktyvina įvykį.
Paprasčiausias VBA kodas, skirtas paryškinti dabartinę eilutę ir stulpelį | Naudokite šį mažą VBA fragmentą, kad paryškintumėte dabartinę lapo eilutę ir stulpelį.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
„VLOOKUP“ funkcija „Excel“ | Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
COUNTIF „Excel 2016“ | Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.