Obsah
I když neumíte programovat, existuje mnoho míst (knihy, webové stránky, fóra), kde najdete hotový kód makra VBA pro obrovské množství typických úloh v Excelu. Podle mých zkušeností většina uživatelů dříve nebo později shromáždí svou osobní sbírku maker za účelem automatizace rutinních procesů, ať už jde o převod vzorců na hodnoty, zobrazování součtů ve slovech nebo sčítání buněk podle barev. A zde nastává problém – kód makra ve Visual Basicu je potřeba někam uložit, aby mohl být později použit v práci.
Nejjednodušší možností je uložit kód makra přímo do pracovního souboru přechodem do editoru Visual Basic pomocí klávesové zkratky Další+F11 a přidání nového prázdného modulu přes nabídku Vložit – Modul:
Tato metoda má však několik nevýhod:
- Pokud existuje mnoho pracovních souborů a všude je potřeba makro, například makro pro převod vzorců na hodnoty, budete muset kód zkopírovat v každé knize.
- Nesmí být zapomenuto uložit soubor ve formátu s podporou maker (xlsm) nebo v binárním formátu knihy (xlsb).
- Při otevírání takového souboru makro ochrana pokaždé vydá varování, které je třeba potvrdit (dobře, nebo ochranu úplně deaktivovat, což nemusí být vždy žádoucí).
Elegantnějším řešením by bylo vytvořit váš vlastní doplněk (Excel Add-in) – samostatný soubor speciálního formátu (xlam) obsahující všechna vaše „oblíbená“ makra. Výhody tohoto přístupu:
- Bude to stačit připojte doplněk jednou v Excelu – a jeho postupy a funkce VBA můžete používat v libovolném souboru na tomto počítači. Opětovné ukládání pracovních souborů ve formátech xlsm a xlsb tedy není nutné, protože. zdrojový kód nebude uložen v nich, ale v souboru doplňku.
- Ochrana nebudou vás obtěžovat ani makra. doplňky jsou z definice důvěryhodné zdroje.
- Může udělat samostatná záložka na pásu karet Excelu s pěknými tlačítky pro spouštění doplňkových maker.
- Doplněk je samostatný soubor. Jeho snadno se přenáší z počítače do počítače, sdílet s kolegy nebo dokonce prodávat 😉
Pojďme si projít celý proces vytváření vlastního doplňku Microsoft Excel krok za krokem.
Krok 1. Vytvořte soubor doplňku
Otevřete Microsoft Excel s prázdným sešitem a uložte jej pod libovolným vhodným názvem (např MyExcelAddin) ve formátu doplňku s příkazem Soubor – Uložit jako nebo klíče F12, určující typ souboru Doplněk aplikace Excel:
Vezměte prosím na vědomí, že ve výchozím nastavení Excel ukládá doplňky do složky C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ale v zásadě můžete zadat jakoukoli jinou složku, která je pro vás vhodná.
Krok 2. Připojíme vytvořený doplněk
Nyní doplněk, který jsme vytvořili v posledním kroku MyExcelAddin musí být připojen k Excelu. Chcete-li to provést, přejděte do nabídky Soubor – Možnosti – Doplňky (Soubor — Možnosti — Doplňky), klikněte na tlačítko O Nás (Jít) ve spodní části okna. V okně, které se otevře, klikněte na tlačítko přezkoumání (Procházet) a zadejte umístění našeho souboru doplňku.
Pokud jste udělali vše správně, pak naše MyExcelAddin by se měly objevit v seznamu dostupných doplňků:
Krok 3. Přidejte do doplňku makra
Náš doplněk je propojený s Excelem a úspěšně funguje, ale zatím v něm není ani jedno makro. Pojďme to naplnit. Chcete-li to provést, otevřete editor jazyka Visual Basic pomocí klávesové zkratky Další+F11 nebo tlačítkem Visual Basic Karta vývojka (Vývojář). Pokud tabulátory vývojka není vidět, lze jej zobrazit skrz Soubor – Možnosti – Nastavení pásu karet (Soubor — Možnosti — Přizpůsobit pás karet).
V levém horním rohu editoru by mělo být okno Projekt (pokud není vidět, zapněte jej prostřednictvím nabídky Zobrazit — Project Explorer):
Toto okno zobrazuje všechny otevřené sešity a spuštěné doplňky aplikace Microsoft Excel, včetně toho našeho. VBAProject (MyExcelAddin.xlam) Vyberte jej myší a přes menu do něj přidejte nový modul Vložit – Modul. V tomto modulu uložíme kód VBA našich doplňkových maker.
Kód můžete buď napsat od začátku (pokud umíte programovat), nebo jej zkopírovat odněkud připravený (což je mnohem jednodušší). Pojďme pro testování zadat kód jednoduchého, ale užitečného makra do přidaného prázdného modulu:
Po zadání kódu nezapomeňte kliknout na tlačítko uložit (disketa) v levém horním rohu.
Naše makro VzorceToValues, jak si můžete snadno představit, převádí vzorce na hodnoty v předem zvoleném rozsahu. Někdy se tato makra také nazývají postupy. Chcete-li jej spustit, musíte vybrat buňky se vzorci a otevřít speciální dialogové okno makra ze záložky vývojka (Vývojář — Makra) nebo klávesovou zkratkou Další+F8. Normálně toto okno zobrazuje dostupná makra ze všech otevřených sešitů, ale doplňková makra zde nejsou vidět. Navzdory tomu můžeme do pole zadat název našeho postupu název makra (název makra)a potom klepněte na tlačítko Běh (běh) – a naše makro bude fungovat:
| |
Zde můžete také přiřadit klávesovou zkratku pro rychlé spuštění makra – za to je zodpovědné tlačítko parametry (Možnosti) v předchozím okně Makro:
Při přiřazování kláves mějte na paměti, že rozlišují velká a malá písmena a rozložení klávesnice. Pokud tedy přiřadíte kombinaci like Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally směnazískat velké písmeno.
Pro pohodlí můžeme také přidat tlačítko pro naše makro na panel nástrojů pro rychlý přístup v levém horním rohu okna. Chcete-li to provést, vyberte Soubor – Možnosti – Panel nástrojů Rychlý přístup (Soubor — Možnosti — Přizpůsobit panel nástrojů Rychlý přístup)a poté v rozevíracím seznamu v horní části okna možnost makra. Poté naše makro VzorceToValues lze umístit na panel pomocí tlačítka přidat (Add) a vyberte pro něj ikonu pomocí tlačítka Přeměna (Edit):
Krok 4. Přidejte funkce do doplňku
Ale makropostupy, jsou tu také funkční makra nebo jak se jim říká UDF (User Defined Function = uživatelsky definovaná funkce). Vytvořme samostatný modul v našem doplňku (příkaz menu Vložit – Modul) a vložte tam kód následující funkce:
Je snadné vidět, že tato funkce je potřebná k vytažení DPH z částky včetně DPH. Samozřejmě ne Newtonův binom, ale poslouží nám jako příklad pro ukázku základních principů.
Všimněte si, že syntaxe funkce se liší od procedury:
- konstrukce se používá Funkce…. End Function místo toho Sub … Konec Sub
- za názvem funkce jsou její argumenty uvedeny v závorkách
- v těle funkce se provedou potřebné výpočty a následně se výsledek přiřadí proměnné s názvem funkce
Všimněte si také, že tato funkce není potřeba a nelze ji spustit jako předchozí proceduru makra prostřednictvím dialogového okna makra a tlačítko Běh. Taková funkce makra by měla být použita jako standardní funkce listu (SUM, IF, SVYHLEDAT…), tj. stačí zadat do libovolné buňky a jako argument uvést hodnotu částky s DPH:
… nebo zadejte pomocí standardního dialogového okna pro vložení funkce (tlačítko fx v řádku vzorců) výběrem kategorie Definováno uživatelem (definováno uživatelem):
Jediným nepříjemným momentem je zde absence obvyklého popisu funkce ve spodní části okna. Chcete-li jej přidat, budete muset provést následující:
- Otevřete Editor jazyka Visual Basic pomocí klávesové zkratky Další+F11
- Vyberte doplněk na panelu Projekt a stiskněte klávesu F2otevřete okno Prohlížeč objektů
- Vyberte svůj doplňkový projekt z rozevíracího seznamu v horní části okna
- Klepněte pravým tlačítkem myši na funkci, která se zobrazí, a vyberte příkaz Nemovitosti.
- V okně zadejte popis funkce Popis
- Uložte soubor doplňku a restartujte excel.
Po restartu by funkce měla zobrazit popis, který jsme zadali:
Krok 5. Vytvořte doplňkovou kartu v rozhraní
Posledním, sice ne povinným, ale příjemným dotykem bude vytvoření samostatné záložky s tlačítkem pro spuštění našeho makra, které se objeví v rozhraní Excelu po připojení našeho doplňku.
Informace o kartách, které se zobrazují ve výchozím nastavení, jsou obsaženy v knize a musí být naformátovány ve speciálním kódu XML. Nejjednodušší způsob, jak takový kód napsat a upravit, je pomocí speciálních programů – editorů XML. Jedním z nejpohodlnějších (a bezplatných) je program Maxima Novikova Ribbon XML Editor.
Algoritmus pro práci s ním je následující:
- Zavřete všechna okna aplikace Excel, aby při úpravě kódu XML doplňku nedošlo ke konfliktu souborů.
- Spusťte program Ribbon XML Editor a otevřete v něm náš soubor MyExcelAddin.xlam
- S tlačítkem Karty v levém horním rohu přidejte fragment kódu pro novou kartu:
- Musíte zadat prázdné uvozovky id naší kartě a skupině (jakékoli jedinečné identifikátory) a v štítek – názvy naší karty a skupiny tlačítek na ní:
- S tlačítkem tlačítko na levém panelu přidejte prázdný kód pro tlačítko a přidejte k němu značky:
— štítek je text na tlačítku
— imageMso — toto je podmíněný název obrázku na tlačítku. Použil jsem ikonu červeného tlačítka s názvem AnimationCustomAddExitDialog. Názvy všech dostupných tlačítek (a je jich několik stovek!) lze nalézt na velkém množství stránek na internetu, pokud vyhledáte klíčová slova „imageMso“. Pro začátek můžete jít sem.
- onAction – to je název procedury zpětného volání – speciálního krátkého makra, které spustí naše hlavní makro VzorceToValues. Tento postup můžete nazvat, jak chcete. Přidáme o něco později.
- Správnost všeho provedeného můžete zkontrolovat pomocí tlačítka se zeleným zaškrtnutím v horní části panelu nástrojů. Na stejném místě klikněte na tlačítko s disketou pro uložení všech změn.
- Zavřete Ribbon XML Editor
- Otevřete Excel, přejděte do editoru Visual Basic a přidejte do našeho makra proceduru zpětného volání KillFormulastakže spustí naše hlavní makro pro nahrazení vzorců hodnotami.
- Uložíme změny a po návratu do Excelu zkontrolujeme výsledek:
To je vše – doplněk je připraven k použití. Naplňte jej vlastními postupy a funkcemi, přidejte krásná tlačítka – a používání maker ve vaší práci bude mnohem jednodušší.
- Co jsou makra, jak je používat ve své práci, kde získat kód makra ve Visual Basicu.
- Jak vytvořit úvodní obrazovku při otevírání sešitu v Excelu
- Co je to osobní kniha maker a jak ji používat