Makro rekordér Excelu má spoustu výkonu, ale má svá omezení. Jak je popsáno v jiném článku, záznam makra často zaznamenává nepotřebný kód a nemůže zaznamenávat věci, jako je logika nebo interakce s jinými programy. Může být také obtížné je použít pro delší makra - může se stát, že si předem vytvoříte scénář svých akcí, abyste se vyhnuli nákladným chybám.
Tento článek si klade za cíl pomoci vám začít s kódováním maker od nuly ve VBA. Dozvíte se, kde jsou uložena makra, napíšete základní makro a naučíte se základy programování ve VBA pomocí proměnných, logiky a smyček.
Začínáme
VBA a editor jazyka
VBA nebo Visual Basic for Applications je jazyk, ve kterém jsou makra napsána. Všechna makra jsou uložena jako kód VBA, ať už jsou ručně kódována nebo vytvořena pomocí záznamníku maker.
Ke všemu kódu VBA v sešitu můžete přistupovat pomocí editoru jazyka Visual Basic. Jedná se o speciální textový editor a debugger, který je integrován do všech kancelářských aplikací, včetně Excelu. Tento editor obvykle otevřete pomocí ALT+F11 klávesovou zkratku v Excelu, ale můžete k ní také přistupovat z Excelu Vývojář kartu, pokud ji máte povolenou.
Průzkumník projektu
The Průzkumník projektů je okno uvnitř editoru VB, které vám ukazuje všechny položky, které v sobě mohou mít kód VBA. Pokud toto okno nevidíte, stiskněte F5 aby se zobrazil nebo vyberte Průzkumník projektů z Pohled Jídelní lístek.
Poklepáním na položku v Průzkumníku projektů se zobrazí kód dané položky. V Průzkumníku projektů se může objevit několik typů položek:
- Sešity
- Pracovní listy
- UserForms
- Moduly třídy
- Moduly (v těchto položkách jsou uložena makra)
Přestože všechny tyto typy položek mohou obsahovat kód VBA, osvědčeným postupem je kódování maker v modulech.
Vytvoření prvního makra
Pomocí seznamu maker
Seznam maker zobrazuje všechna makra ve vašem sešitu. Z tohoto seznamu můžete upravit stávající makro nebo vytvořit nové.
Chcete -li vytvořit nové makro pomocí seznamu maker:
- Vyberte kartu Vývojář a klikněte na Makra (nebo stiskněte ALT+F8)
- Zadejte nový název makra a poté klikněte na „Vytvořit“
Po kliknutí na „Vytvořit“ se zobrazí editor VB, který ukazuje nově vytvořené makro. Excel v případě potřeby vytvoří nový modul pro makro.
Ručně v editoru VB
Nové makro můžete přidat ručně bez seznamu maker. Toto je lepší možnost, pokud chcete určit modul, ve kterém je makro uloženo.
Ruční přidání makra:
- Otevřete editor VB (ALT+F11)
- Buď:
- Přidejte nový modul kliknutím Vložit> Modul v nabídce (modul se automaticky otevře)
-
- NEBO poklepáním na existující modul v Průzkumníku projektů jej otevřete
- V modulu zadejte kód pro vaše nové makro
Sub MyMacro () Konec Sub
Tyto dva řádky označují začátek a konec makra s názvem „MyMacro“ (všimněte si závorek, které jsou povinné). To se zobrazí v dialogu „Zobrazit makra“ v aplikaci Excel a lze jej přiřadit tlačítku (i když zatím nic nedělá).
Přidejte nějaký kód do makra
Nyní přidejme nějaký kód mezi řádky „Sub“ a „End Sub“, aby toto makro skutečně něco udělalo:
Rozsah Sub MyMacro () („A1“). Hodnota = „Hello World!“ End Sub
Základní struktury kódu
Objekt rozsahu
Excel VBA používá objekt rozsahu k reprezentaci buněk na listu. Ve výše uvedeném příkladu je objekt Range vytvořen pomocí kódu Rozsah („A1“) pro přístup k hodnotě buňky A1.
Objekty rozsahu se primárně používají k nastavení hodnot buněk:
Rozsah („A1“). Hodnota = 1
Rozsah („A1“). Hodnota = „První buňka“
Všimněte si, že při definování hodnot buněk jako čísel stačí zadat číslo, ale při zadávání textu musíte text obklopit uvozovkami.
Rozsahy lze také použít k přístupu k mnoha vlastnostem buněk, jako je jejich písmo, ohraničení, vzorce a další.
Například můžete nastavit písmo buňky na tučné takto:
Rozsah („A1“). Font.Bold = True
Můžete také nastavit vzorec buňky:
Rozsah („A1“). Vzorec = „= Součet (A2: A10)“
V Excelu můžete vybrat blok buněk kurzorem (řekněme od A1 do D10) a nastavit je na tučné. Objekty rozsahu mohou přistupovat k blokům buněk takto:
Rozsah („A1: D10“). Font.Bold = True
Můžete také odkazovat na několik buněk/bloků najednou:
Rozsah („A1: D10, A12: D12, G1“). Font.Bold = True
Formát je stejný jako formát, který byste použili při výběru buněk pro vzorec SUM () v aplikaci Excel. Každý blok je oddělen čárkou a bloky jsou označeny buňkami vlevo nahoře a vpravo dole oddělenými dvojtečkou.
Nakonec objekty Range mají vestavěné metody k provádění běžných operací na listu. Můžete například chtít zkopírovat některá data z jednoho místa na druhé. Zde je příklad:
Rozsah („A1: D10“). Rozsah kopírování („F1“). Rozsah PasteSpecial xlPasteValues („F1“). PasteSpecial xlPasteFormats
To zkopíruje buňky A1: D10 do schránky a poté provede PasteSpecial () začínající v buňce C1 - stejně jako byste to udělali ručně v aplikaci Excel. Všimněte si, že tento příklad ukazuje, jak použít PasteSpecial () k vložení pouze hodnot a formátů - pro všechny možnosti, které byste viděli v dialogu Vložit jinak, existují parametry.
Zde je příklad vložení „Vše“ do jiného listu:
Rozsah („A1: D10“). Kopírování listů („List2“). Rozsah („A1“). PasteSpecial xlPasteAll
Pokud prohlášení
S If prohlášení, můžete spustit část kódu pouze „pokud“ je určité tvrzení pravdivé.
Například můžete chtít buňku zvýraznit tučně a vybarvit ji na červeno, ale pouze „pokud“ je hodnota v buňce menší než 100.
Pokud je rozsah („A4“). Hodnota <100, pak rozsah („A4“). Písmo. Tučné = True Range („A4“). Interior.Color = vbČervený konec Pokud
Správná struktura příkazu If je následující (hranaté závorky označují volitelné součásti):
Pokud Pak
[Jinak Pokud]
[Jiný]
Konec If
Můžete zahrnout tolik Jinak bloky, jak chcete testovat více podmínek. Můžete také přidat Jiný blok, který běží pouze v případě, že nejsou splněny žádné další podmínky v příkazu If.
Zde je další příklad založený na předchozím, kde je buňka formátována několika různými způsoby v závislosti na hodnotě:
Pokud je rozsah („A4“). Hodnota <100 pak rozsah („A4“). Písmo.Bold = True Range („A4“). Interior.Color = vbRed ElseIf Range („A4“). Hodnota <200 Then Range ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vbYellow Else Range ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End If
Ve výše uvedeném příkladu je buňka zrušena tučně v blocích ElseIf, kde hodnota není nižší než 100. Můžete hnízdo Pokud se příkazy vyhýbají duplikaci kódu, postupujte takto:
Pokud je rozsah („A4“). Hodnota <100, pak rozsah („A4“). Font.Bold = True Range („A4“). Interior.Color = vbRed Else Range („A4“). Font.Bold = False ' rozbalení písma pouze jednou If Range ("A4"). Hodnota <200 Then Range ("A4"). Interior.Color = vbYellow Else Range ("A4"). Interior.Color = vbGreen End If End If
Proměnné
A Variabilní je část paměti používaná k ukládání dočasných informací, když je spuštěno makro. Často se používají ve smyčkách jako iterátory nebo k uchovávání výsledku operace, kterou chcete v makru použít několikrát.
Zde je příklad proměnné a jak ji můžete použít:
Sub ExtractSerialNumber () Dim strSerial As String 'Toto je deklarace proměnné' As As String 'znamená, že tato proměnná má obsahovat text' nastavení předstíraného sériového čísla: Rozsah ("A4"). Hodnota = “pořadové# 804567-88 ”„ Rozdělte sériové číslo z buňky A4 a přiřaďte jej k proměnné strSerial = Mid (rozsah („A4“). Hodnota, 9) 'nyní použijte proměnnou dvakrát, místo abyste museli sériové číslo analyzovat dvakrát Rozsah („ B4 ”). Hodnota = strSerial MsgBox strSerial End Sub
V tomto základním příkladu je proměnná „strSerial“ použita k extrahování sériového čísla z buňky A4 pomocí funkce Mid () a poté je použita na dalších dvou místech.
Standardní způsob prohlásit proměnná je následující:
Ztlumit jakékoli jméno [Tak jako typ]
- jakékoli jméno je název, který se rozhodnete proměnné zadat
- typ je datový typ proměnné
„[As typ] ”Část lze vynechat - pokud ano, proměnná je deklarována jako typ varianty, který může obsahovat jakýkoli druh dat. Ačkoli jsou varianty variant naprosto platné, je třeba se jim vyhnout, protože pokud si nedáte pozor, mohou vést k neočekávaným výsledkům.
Existují pravidla pro názvy proměnných. Musí začínat písmenem nebo podtržítkem, nesmí mít mezery, tečky, čárky, uvozovky ani znaky „! @ & $ #“.
Zde je několik příkladů deklarací proměnných:
Dim strFilename As String 'good name style - descriptive and uses prefix Dim i As Long' bad name style - přijatelné pouze u některých iterátorů Dim SalePrice As Double 'v pořádku název stylu - popisný, ale nepoužívá předponu Dim iCounter' v pořádku jméno - není příliš popisný, používá předponu, žádný datový typ
Všechny tyto příklady používají mírně odlišná schémata pojmenování, ale všechna jsou platná. Předponovat název proměnné krátkou formou datového typu (podle některých z těchto příkladů) není špatný nápad, protože váš kód je na první pohled čitelnější.
VBA obsahuje spoustu základních typy dat. Mezi nejoblíbenější patří:
- Tětiva (slouží k uchovávání textových dat)
- Dlouho (slouží k uložení celých čísel, tj. bez desetinných míst)
- Dvojnásobek (používá se k uložení čísel s plovoucí desetinnou čárkou, tj. desetinná místa)
Úplný seznam vnitřních datových typů VBA naleznete zde: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Proměnné objektu rozsahu
Je možné vytvářet také proměnné, které odkazují na objekty rozsahu. To je užitečné, pokud chcete v kódu odkazovat na konkrétní rozsah na několika místech - pokud tedy potřebujete rozsah změnit, stačí jej změnit pouze na jednom místě.
Když vytvoříte objektovou proměnnou Range, musíte ji „nastavit“ na instanci Range. Například:
Dim rMyRange As Range Set rMyRange = Range (“A1: A10; D1: J10”)
Vynechání příkazu „Set“ při přiřazování proměnné Range bude mít za následek chybu.
Smyčky
Smyčky jsou bloky, které v sobě určitý početkrát opakují kód. Jsou užitečné pro snížení množství kódu, který musíte napsat, a umožňují vám napsat jeden kus kódu, který provádí stejné akce u mnoha různých souvisejících položek.
Pro další
A Pro další block je smyčka, která se opakuje určitý početkrát. Používá proměnnou jako iterátor spočítat, kolikrát byla spuštěna, a tuto proměnnou iterátoru lze použít uvnitř smyčky. Díky tomu jsou smyčky For-Next velmi užitečné pro iteraci přes buňky nebo pole.
Zde je příklad, který prochází buňkami v řádcích 1 až 100, sloupci 1 a nastavuje jejich hodnoty na hodnotu proměnné iterátoru:
Dim i As Long For i = 1 až 100 buněk (i, 1). Hodnota = i Další i
Řádek „Pro i = 1 až 100“ znamená, že smyčka začíná od 1 a končí po 100. Můžete nastavit libovolná počáteční a koncová čísla; pro tato čísla můžete také použít proměnné.
Ve výchozím nastavení se smyčky For-Next počítají podle 1. Pokud chcete počítat s jiným číslem, můžete smyčku napsat s explicitním Krok doložka:
Pro i = 5 až 100 Krok 5
Tato smyčka začne v 5, poté přidá 5 k „i“ pokaždé, když se smyčka opakuje (takže „i“ bude 10 při druhém opakování, 15 při třetím atd.).
Použitím Krok, můžete také provést smyčku zpětně:
Pro i = 100 až 1 Krok -1
Můžete také hnízdo Smyčky For-Next. Každý blok vyžaduje svou vlastní proměnnou, se kterou je třeba počítat, ale tyto proměnné můžete použít kdekoli chcete. Zde je příklad toho, jak je to užitečné v aplikaci Excel VBA:
Dim i As Long, j As Long For i = 1 to 100 For j = 1 to 100 Cells (i, j). Value = i * j Next j Next i
To vám umožní procházet řádky i sloupce.
VAROVÁNÍ: ačkoli je to povoleno, NIKDY byste neměli měnit proměnnou iterátoru uvnitř bloku For-Next, protože tento iterátor používá ke sledování smyčky. Úprava iterátoru může způsobit nekonečnou smyčku a zablokování makra. Například:
Pro i = 1 až 100 i = 1 Další i
V této smyčce se „I“ nikdy nedostane nad 2, než bude resetováno na 1, a smyčka se bude navždy opakovat.
Pro každého
Pro každého bloky jsou velmi podobné blokům For-Next, kromě toho, že nepoužívají čítač k určení, kolikrát zacyklí. Místo toho blok For-Each vezme „sbírku“ objektů (jako je rozsah buněk) a spustí se tolikrát, kolik objektů v této kolekci je.
Zde je příklad:
Dim r as Range for each r In Range ("A15: J54") If r.Value> 0 Then r.Font.Bold = True End If Next r
Všimněte si použití proměnné objektu Range „r“. Toto je proměnná iterátoru použitá ve smyčce For -Every - pokaždé, když smyčkou projdete, „r“ získá odkaz na další buňku v rozsahu.
Výhodou použití smyček For-Each v aplikaci Excel VBA je, že můžete procházet všemi buňkami v rozsahu bez vnořených smyček. To může být užitečné, pokud potřebujete procházet všemi buňkami v komplexním rozsahu jako Rozsah („A1: D12, J13, M1: Y12“).
Jednou z nevýhod smyček For-Each je, že nemáte žádnou kontrolu nad pořadím, ve kterém jsou buňky zpracovávány. Ačkoli v praxi Excel bude procházet buňkami v pořadí, teoreticky mohl zpracovat buňky ve zcela náhodném pořadí. Pokud potřebujete zpracovat buňky v určitém pořadí, měli byste místo toho použít smyčky For-Next.
Do-Loop
Zatímco bloky For-Next používají čítače, aby věděly, kdy zastavit, Do-Loop bloky běží, dokud není splněna podmínka. K tomu použijete Dokud klauzule na začátku nebo na konci bloku, která testuje podmínku a způsobí, že se smyčka zastaví, když je tato podmínka splněna.
Příklad:
Dim str As String str = "Buffalo" Do Do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Hodnota = str
V této smyčce je „Buffalo“ zřetězeno na „str“ pokaždé ve smyčce, dokud neodpovídá očekávané větě. V tomto případě je test proveden na začátku smyčky - pokud 'str' již byla očekávaná věta (což není, protože jsme ji tak nezačali, ale pokud), smyčka by se ani nespustila .
Smyčku můžete spustit alespoň jednou přesunutím klauzule Before na konec takto:
Do smyčky str = str & "" & "Buffalo" Do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Ve svém makru můžete použít kteroukoli verzi, která má smysl.
VAROVÁNÍ: můžete způsobit nekonečnou smyčku s blokem Do-Loop, pokud podmínka dokud není nikdy splněna. Vždy pište svůj kód tak, aby při použití tohoto typu smyčky byla podmínka Before určitě splněna.
Co bude dál?
Jakmile pochopíte základy, proč nezkusit naučit se pokročilejší techniky? Náš tutoriál na https://easyexcel.net/excel/learn-vba-tutorial/ bude stavět na všem, co jste se zde naučili, a rozšíří vaše dovednosti pomocí Událostí, UserForms, optimalizace kódu a mnoha dalších!
