V tomto tutoriálu se seznámíme s funkcí Excel VBA
1) Co je to Visual Basic v Excelu?
2) Jak používat VBA v Excelu?
3) Jak vytvořit uživatelem definovanou funkci?
4) Jak psát makro?
Jak napsat kód VBA
Excel poskytuje uživateli rozsáhlou sbírku hotových funkcí, což je více než dost na uspokojení průměrného uživatele. Mnoho dalších lze přidat instalací různých doplňků, které jsou k dispozici. Většiny výpočtů lze dosáhnout pomocí toho, co je k dispozici, ale netrvá dlouho a zjistíte, že si přejete, aby existovala funkce, která vykonává konkrétní práci, a v seznamu nemůžete najít nic vhodného. Potřebujete UDF. UDF (User Defined Function) je jednoduše funkce, kterou si sami vytvoříte pomocí VBA. UDF se často říká „vlastní funkce“. UDF může zůstat v modulu kódu připojeném k sešitu, v takovém případě bude vždy k dispozici, když je tento sešit otevřený. Případně si můžete vytvořit vlastní doplněk obsahující jednu nebo více funkcí, které můžete do Excelu nainstalovat stejně jako komerční doplněk. K UDF lze přistupovat také pomocí kódových modulů. Vývojáři často vytvářejí UDF tak, aby fungovaly pouze v rámci kódu procedury VBA a uživatel si nikdy není vědom jejich existence. Jako každá funkce, i UDF může být tak jednoduchý nebo složitý, jak chcete. Začněme snadným…
Funkce pro výpočet plochy obdélníku
Ano, vím, že byste to mohli udělat ve své hlavě! Koncept je velmi jednoduchý, takže se můžete soustředit na techniku. Předpokládejme, že potřebujete funkci pro výpočet plochy obdélníku. Prohlížíte si kolekci funkcí Excelu, ale žádná není vhodná. Toto je výpočet, který je třeba provést:
OBLAST = DÉLKA x ŠÍŘKA
Otevřete nový sešit a poté otevřete editor jazyka (Nástroje> Makro> Editor jazyka Visual Basic nebo ALT+F11).
Budete potřebovat modul, do kterého budete psát svoji funkci, takže si vyberte Vložit> Modul. Do prázdného typu modulu: Funkční oblast a stiskněte VSTUP. Editor jazyka Visual Basic dokončí řádek za vás a přidá řádek End Function, jako byste vytvářeli podprogram. Zatím to vypadá takto …
Funkční oblast () Koncová funkce
Umístěte kurzor mezi závorky za „Oblast“. Pokud jste někdy přemýšleli, k čemu jsou závorky, chystáte se to zjistit! Specifikujeme „argumenty“, které bude naše funkce trvat (např argument je informace potřebná k provedení výpočtu). Typ Délka jako dvojnásobek, šířka jako dvojnásobek a klikněte na prázdný řádek pod ním. Všimněte si toho, že při psaní se objeví rolovací pole se seznamem všech věcí odpovídajících tomu, co píšete.
Tato funkce se nazývá Auto List Members. Pokud se nezobrazí, je vypnutý (zapněte jej v Nástroje> Možnosti> Editor) nebo jste mohli udělat chybu při psaní dříve. Je to velmi užitečná kontrola vaší syntaxe. Najděte požadovanou položku a dvojitým kliknutím ji vložte do kódu. Můžete to ignorovat a psát, pokud chcete. Váš kód nyní vypadá takto…
Funkční oblast (délka jako dvojnásobek, šířka jako dvojnásobek) Koncová funkce
Deklarace datového typu argumentů není povinná, ale dává smysl. Mohl jsi napsat Délka šířka a nechal to tak, ale varování Excelu, jaký datový typ očekávat, pomůže vašemu kódu běžet rychleji a zachytí chyby ve vstupu. The dvojnásobek datový typ odkazuje na číslo (které může být velmi velké) a umožňuje zlomky. Nyní k samotnému výpočtu. Na prázdném řádku nejprve stiskněte TAB klíč k odsazení kódu (usnadňuje jeho čtení) a psaní Plocha = Délka * Šířka. Zde je vyplněný kód…
Funkční oblast (délka jako dvojnásobek, šířka jako dvojnásobek) plocha = délka * šířka koncová funkce
Během psaní si všimnete, že se objevila další funkce nápovědy editoru jazyka Visual Basic, Auto Rychlé informace…
Tady to není relevantní. Jeho účelem je pomoci vám psát funkce ve VBA tím, že vám řekne, jaké argumenty jsou vyžadovány. Svou funkci můžete hned vyzkoušet. Přepněte do okna aplikace Excel a zadejte údaje o délce a šířce do samostatných buněk. Ve třetí buňce zadejte svou funkci, jako by to byla jedna z vestavěných. V tomto příkladu buňka A1 obsahuje délku (17) a buňka B1 šířku (6,5). V C1 jsem zadal = plocha (A1, B1) a nová funkce vypočítala plochu (110,5)…
Někdy mohou být argumenty funkce volitelné. V tomto případě bychom mohli udělat Šířka argument nepovinný. Předpokládejme, že obdélník se stane čtvercem se stejnou délkou a šířkou. Abychom zachránili uživatele před zadáváním dvou argumentů, mohli bychom ho nechat zadat pouze délku a funkci použít tuto hodnotu dvakrát (tj. Vynásobit délku x délku). Funkce tedy ví, kdy to dokáže, musíme zahrnout znak IF prohlášení aby to pomohlo se rozhodnout. Změňte kód tak, aby vypadal takto…
Funkční oblast (délka jako dvojnásobek, volitelná šířka jako varianta) Pokud chybí
Všimněte si toho, že datový typ pro Šířku byl změněn na Varianta povolit hodnoty null. Funkce nyní umožňuje uživateli zadat pouze jeden argument, např. = plocha (A1).Příkaz IF ve funkci zkontroluje, zda byl zadán argument Šířka, a podle toho vypočítá …
Funkce pro výpočet spotřeby paliva
Rád sleduji spotřebu paliva svého auta, takže když si koupím palivo, zaznamenám si počet najetých kilometrů a kolik paliva je potřeba k naplnění nádrže. Zde se ve Velké Británii palivo prodává v litrech. Milometr automobilu (OK, je to tedy počítadlo kilometrů) zaznamenává vzdálenost v mílích. A protože jsem příliš starý a hloupý na to, abych se změnil, rozumím pouze MPG (míle na galon). Pokud si myslíte, že je to všechno trochu smutné, co s tím. Když se vrátím domů, otevřu Excel a zadám data do listu, který mi vypočítá MPG a mapuje výkon auta. Výpočet je počet mil, které auto najelo od posledního plnění, děleno počtem galonů použitého paliva…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL
ale protože palivo přichází v litrech a v galonu je 4,546 litru…
MPG = (MILES THIS FILL - MILES LAST FILL) / LITRY PALIVA x 4,546
Takto jsem napsal funkci…
Funkce MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Liters * 4.546 End Function
a takhle to vypadá na listu…
Ne všechny funkce provádějí matematické výpočty. Zde je jeden, který poskytuje informace…
Funkce, která dává název dne
Často se mě ptají, zda existuje funkce data, která udává den v týdnu jako text (např. Pondělí). Odpověď je ne*, ale je docela snadné jej vytvořit. (*Dodatek: Řekl jsem ne? Podívejte se na poznámku níže a podívejte se na funkci, kterou jsem zapomněl!). Excel má funkci WEEKDAY, která vrací den v týdnu jako číslo od 1 do 7. Můžete si vybrat, který den je 1, pokud se vám nelíbí výchozí (neděle). V níže uvedeném příkladu funkce vrací „5“, což, jak náhodou vím, znamená „čtvrtek“.
Ale nechci vidět číslo, chci vidět „čtvrtek“. Výpočet jsem mohl upravit přidáním funkce VLOOKUP, která odkazovala na tabulku někde obsahující seznam čísel a odpovídající seznam názvů dnů. Nebo bych mohl mít celou věc samostatně uzavřenou s více vnořenými příkazy IF. Příliš komplikované! Odpovědí je vlastní funkce…
Funkce DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Případ 5 DayName = "Čtvrtek" Případ 6 DayName = "Pátek" Případ 7 DayName = "Sobota" Konec Vyberte Konec Funkce
Nazval jsem svou funkci „DayName“ a vyžaduje to jediný argument, který nazývám „InputDate“, což (samozřejmě) musí být datum. Zde je návod, jak to funguje…
- První řádek funkce deklaruje proměnnou, kterou jsem nazval „DayNumber“, což bude celé číslo (tj. Celé číslo).
- Další řádek funkce přiřadí této proměnné hodnotu pomocí funkce WEEKDAY v Excelu. Hodnota bude číslo mezi 1 a 7. Přestože výchozí hodnota je 1 = neděle, pro přehlednost jsem ji přesto zahrnul.
- Nakonec a Prohlášení o případu zkoumá hodnotu proměnné a vrací příslušný kus textu.
Takto to vypadá na pracovním listu …
Přístup k vašim vlastním funkcím
Pokud je k sešitu připojen modul kódu VBA, který obsahuje vlastní funkce, lze tyto funkce snadno řešit ve stejném sešitu, jak je ukázáno v příkladech výše. Název funkce používáte, jako by to byla jedna z vestavěných funkcí aplikace Excel.
Můžete také najít funkce uvedené v Průvodci funkcemi (někdy se nazývá nástroj Vložit funkci). Pomocí průvodce vložte funkci normálním způsobem (Vložit> Funkce).
Procházejte seznamem kategorií funkcí, které chcete najít Definováno uživatelem a jeho výběrem zobrazíte seznam dostupných UDF…
Můžete vidět, že uživatelem definovaným funkcím chybí jakýkoli jiný popis než neužitečná zpráva „Žádná pomoc není k dispozici“, ale můžete přidat krátký popis …
Ujistěte se, že jste v sešitu, který obsahuje funkce. Jít do Nástroje> Makro> Makra. Zde neuvidíte své funkce, ale Excel o nich ví! V Název makra v horní části dialogového okna, zadejte název funkce a poté klikněte na dialogové okno Možnosti knoflík. Pokud je tlačítko zašedlé, buď jste špatně zadali název funkce, nebo jste ve špatném sešitu, nebo neexistuje! Tím se otevře další dialog, do kterého můžete zadat krátký popis funkce. Klikněte OK pro uložení popisu a (zde je matoucí bit) klikněte zrušení zavřete dialogové okno Makro. Nezapomeňte uložit sešit obsahující tuto funkci. Až příště přejdete na Průvodce funkcemi, váš UDF bude mít popis …
Stejně jako makra lze uživatelem definované funkce použít v jakémkoli jiném sešitu, pokud je sešit, který je obsahuje, otevřený. Není však dobré to dělat. Zadání funkce do jiného sešitu není jednoduché. K názvu funkce musíte přidat název hostitelského sešitu. To není obtížné, pokud se spoléháte na Průvodce funkcemi, ale nešikovné při ručním zápisu. Průvodce funkcemi zobrazuje úplná jména všech UDF v jiných sešitech…
Pokud otevřete sešit, ve kterém jste funkci používali, v době, kdy je sešit obsahující funkci uzavřen, zobrazí se v buňce, ve které jste funkci použili, chybová zpráva. Excel na to zapomněl! Otevřete sešit hostitelské funkce, přepočítejte a vše je opět v pořádku. Naštěstí existuje lepší způsob.
Pokud chcete zapsat funkce definované uživatelem pro použití ve více než jednom sešitu, je nejlepší metodou vytvořit aplikaci Excel Přidat. Zjistěte, jak to provést v kurzu Sestavte doplněk aplikace Excel.
Dodatek
Opravdu bych to měl vědět lépe! Nikdy, nikdy neříkej nikdy! Když jsem vám řekl, že neexistuje funkce, která by poskytovala název dne, nyní jsem si vzpomněl na tu, která může. Podívejte se na tento příklad…
Funkce TEXT vrací hodnotu buňky jako text v konkrétním číselném formátu. Takže v příkladu jsem si mohl vybrat = TEXT (A1, "ddd") vrátit „Čt“, = TEXT (A1, "mmmm") vrátit „září“ atd. Nápověda k Excelu obsahuje další příklady způsobů, jak tuto funkci používat.
Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku.
Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mailový web