Funkce jako VLOOKUP, COUNTIF, SUMIF se nazývají funkce listu. Funkce, které jsou předdefinovány v Excelu a připraveny k použití na listu, jsou obecně funkce listu. Ve VBA nemůžete měnit ani vidět kód za těmito funkcemi.
Na druhou stranu uživatelem definované funkce a funkce specifické pro VBA, jako je MsgBox nebo InputBox, jsou funkce VBA.
Všichni víme, jak používat funkce VBA ve VBA. Ale co když chceme použít VLOOKUP ve VBA. Jak to uděláme? V tomto článku se na to přesně podíváme.
Použití funkcí listu ve VBA
K přístupu k funkci listu používáme třídu Application. Téměř všechny funkce listu jsou uvedeny ve třídě Application.WorksheetFunction. A pomocí tečkového operátoru ke všem můžete přistupovat.
Do kterékoli dílčí položky napište Application.WorksheetFunction. A začněte psát název funkce. Inteligence VBA zobrazí název funkcí dostupných k použití. Jakmile vyberete název funkce, požádá o proměnné, jako každá funkce v Excelu. Ale budete muset předat proměnné ve srozumitelném formátu VBA. Pokud například chcete předat rozsah A1: A10, budete jej muset předat jako objekt rozsahu jako Rozsah ("A1: A10").
Pojďme tedy použít některé funkce listu, abychom tomu lépe porozuměli.
Jak používat funkci VLOOKUP ve VBA
Abych ukázal, jak můžete ve VBA používat funkci VLOOKUP, mám zde ukázková data. Potřebuji ukázat jméno a město daného přihlašovacího ID ve zprávě pomocí VBA. Data jsou rozložena v rozsahu A1: K26.
Stisknutím ALT+F11 otevřete VBE a vložte modul.
Viz níže 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žití funkce VLOOKUP k získání města daného ID v tabulce city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & „Město:“ & město) End Sub
Když spustíte tento kód, získáte tento výsledek.
Můžete vidět, jak rychle VBA vytiskne výsledek do pole se zprávou. Nyní se podívejme na kód.
Jak to funguje?
1.
Dim loginID as String
Dim jméno, město As String
Nejprve jsme deklarovali dvě proměnné typu řetězce pro uložení výsledku vráceného funkcí VLOOKUP. Použil jsem proměnné typu řetězce, protože jsem si jist, že výsledkem vráceným VLOOKUP bude hodnota řetězce. Pokud se očekává, že funkce vašeho listu vrátí číslo, datum, rozsah atd. Typu hodnoty, použijte tento typ proměnné k uložení výsledku. Pokud si nejste jisti, jaký druh hodnoty funkce listu vrátí, použijte proměnné typu varianty.
2.
loginID = "AHKJ_1-3357042451"
Dále jsme použili proměnnou loginID k uložení vyhledávací hodnoty. Zde jsme použili pevně zakódovanou hodnotu. Můžete také použít reference. Například. Hodnotu Range ("A2"). Value můžete použít k dynamické aktualizaci vyhledávací hodnoty z rozsahu A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Zde používáme funkci VLOOKUP k získání. Nyní, když funkci opravíte a otevřete závorku, zobrazí se vám požadované argumenty, ale ne tak popisné, jak se zobrazuje v Excelu. Podívej se sám.
Musíte si pamatovat, jak a jakou proměnnou potřebujete použít. Vždy se můžete vrátit zpět do listu a zobrazit podrobnosti popisné proměnné.
Zde je vyhledávací hodnota Arg1. Pro Arg1 používáme loginID. Vyhledávací tabulka je Arg2. Pro Arg2 jsme použili Range („A1: K26“). Všimněte si toho, že jsme nepoužili přímo A2: K26 jako v Excelu. Index sloupců je Arg3. Pro Arg3 jsme použili 2, protože název je ve druhém sloupci. Typ vyhledávání je Arg4. Použili jsme 0 jako Arg4.
city = Application.WorksheetFunction.VLookup (přihlašovací ID, rozsah ("A1: K26"), 4, 0)
Podobně získáme název města.
4.
MsgBox ("Jméno:" & název & vbLf & "Město:" & město)
Nakonec vytiskneme jméno a město pomocí Messageboxu.
Proč používat funkci listu ve VBA?
Funkce listu mají sílu obrovských výpočtů a nebude chytré ignorovat sílu funkcí listu. Pokud například chceme standardní odchylku datové sady a chcete k tomu napsat celý kód, může vám to trvat hodiny. Ale pokud víte, jak použít funkci listu STDEV.P ve VBA, abyste získali výpočet najednou.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Použití více funkcí listu VBA
Řekněme, že potřebujeme použít indexovou shodu k načtení některých hodnot. Jak byste tedy vzali vzorec ve VBA. To je to, co tuším napíšete:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
To není špatné, ale je to zdlouhavé. Správný způsob použití více funkcí je použití bloku With. Viz následující pří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
Jak vidíte, použil jsem With block, abych řekl VBA, že budu používat vlastnosti a funkce Application.WorksheetFunction. Nepotřebuji to tedy definovat všude. Právě jsem použil tečkový operátor pro přístup k funkcím INDEX, MATCH, VLOOKUP a STDEV.P. Jakmile použijeme příkaz End With, nemůžeme přistupovat k funkcím bez použití plně kvalifikovaných názvů funkcí.
Pokud tedy musíte ve VBA používat více funkcí listu, použijte s block.
Ne všechny funkce listu jsou k dispozici prostřednictvím Application.WorksheetFunction
Některé funkce listu jsou přímo k dispozici pro použití ve VBA. Objekt Application.WorksheetFunction nemusíte používat.
Například funkce jako Len (), které se používají k získání počtu znaků v řetězci, vlevo, vpravo, uprostřed, oříznutí, odsazení atd. Tyto funkce lze přímo použít ve VBA. Zde je příklad.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Podívejte se, zde jsme použili funkci LEN bez použití objektu Application.WorksheetFunction.
Podobně můžete použít další funkce, jako je levý, pravý, střední, znak atd.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) Debug.Print (left (strng, 2)) Debug.Print (right (strng, 1)) Debug.Print (Mid (strng, 3, 2)) End Sub
Když spustíte výše uvedený dílčí díl, vrátí se:
5 On o ll
Takže ano, lidi, takto můžete použít funkci listu aplikace Excel ve VBA. Doufám, že jsem byl dostatečně vysvětlující a tento článek vám pomohl. Máte -li jakékoli dotazy týkající se tohoto článku nebo čehokoli jiného souvisejícího s VBA, zeptejte se v sekci komentáře níže. Do té doby si můžete přečíst další související témata níže.
Co je funkce CSng v aplikaci Excel VBA | Funkce SCng je funkce VBA, která převádí jakýkoli datový typ na číslo s plovoucí desetinnou čárkou s jednoduchou přesností („vzhledem k tomu, že jde o číslo“). Většinou používám funkci CSng k převodu textově formátovaných čísel na skutečná čísla.
Jak získat text a číslo obráceně prostřednictvím VBA v aplikaci Microsoft Excel | K obrácení čísla a textu používáme ve VBA smyčky a střední funkci. 1234 bude převedeno na 4321, „vy“ bude převedeno na „uoy“. Tady je úryvek.
Formátujte data pomocí vlastních formátů čísel pomocí VBA v aplikaci Microsoft Excel | Chcete -li změnit formát čísel konkrétních sloupců v aplikaci Excel, použijte tento úryvek VBA. Jedním kliknutím překrývá formát čísla ze zadaného do zadaného formátu.
Použití události změny listu ke spuštění makra po provedení jakékoli změny | Chcete -li tedy spustit makro vždy, když se list aktualizuje, používáme události pracovního listu VBA.
V případě změny provedené na listu ve specifikovaném rozsahu spusťte makro | Chcete -li spustit kód makra, když se změní hodnota v zadaném rozsahu, použijte tento kód VBA. Detekuje jakoukoli změnu provedenou v uvedeném rozsahu a spustí událost.
Nejjednodušší kód VBA pro zvýraznění aktuálního řádku a sloupce pomocí | Pomocí tohoto malého úryvku VBA zvýrazněte aktuální řádek a sloupec listu.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Funkce VLOOKUP v Excelu | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.