Makro rekordér Excelu má veľa výkonu, ale má svoje obmedzenia. Ako je uvedené v inom článku, záznamník makier často zaznamenáva nepotrebný kód a nemôže zaznamenávať napríklad logiku alebo interakcie s inými programami. Použitie dlhších makier môže byť tiež náročné - môže sa stať, že vopred napíšete svoje akcie, aby ste sa vyhli nákladným chybám.
Cieľom tohto článku je pomôcť vám začať kódovať makrá od začiatku vo VBA. Naučíte sa, kde sú uložené makrá, napíšete základné makro a naučíte sa základy programovania vo VBA pomocou premenných, logiky a slučiek.
Začíname
VBA a editor jazyka Visual Basic
VBA alebo Visual Basic for Applications je jazyk, v ktorom sú makrá napísané. Všetky makrá sú uložené ako kód VBA, či už sú ručne kódované alebo sú vytvorené pomocou záznamníka makier.
K všetkému kódu VBA v zošite máte prístup pomocou editora jazyka Visual Basic. Jedná sa o špeciálny textový editor a debugger, ktorý je vstavaný vo všetkých kancelárskych aplikáciách vrátane Excelu. Tento editor zvyčajne otvoríte pomocou ALT+F11 klávesová skratka v Exceli, ale máte k nej prístup aj z Excelu Vývojár kartu, ak ju máte povolenú.
Prieskumník projektu
The Prieskumník projektu je okno vo vnútri editora VB, ktoré vám ukazuje všetky položky, v ktorých môže byť kód VBA. Ak sa vám toto okno nezobrazuje, stlačte F5 aby sa zobrazil alebo vyberte Prieskumník projektu z vyhliadka Ponuka.
Dvojitým kliknutím na položku v programe Project Explorer sa zobrazí kód danej položky. V programe Project Explorer sa môže objaviť niekoľko typov položiek:
- Pracovné zošity
- Listy
- UserForms
- Moduly triedy
- Moduly (v týchto položkách sú uložené makrá)
Aj keď všetky tieto typy položiek môžu obsahovať kód VBA, osvedčeným postupom je kódovanie makier v moduloch.
Vytvorenie prvého makra
Použitie zoznamu makier
Zoznam makier zobrazuje všetky makrá vo vašom zošite. V tomto zozname môžete upraviť existujúce makro alebo vytvoriť nové.
Ak chcete vytvoriť nové makro pomocou zoznamu makier:
- Vyberte kartu Vývojár a kliknite na Makrá (alebo stlačte ALT+F8)
- Zadajte nový názov makra a potom kliknite na „Vytvoriť“
Po kliknutí na „Vytvoriť“ sa zobrazí editor VB so zobrazením novovytvoreného makra. Excel v prípade potreby vytvorí nový modul pre makro.
Ručne v editore VB
Nové makro môžete pridať ručne bez zoznamu makier. Toto je lepšia možnosť, ak chcete určiť modul, v ktorom je makro uložené.
Manuálne pridanie makra:
- Otvorte editor VB (ALT+F11)
- Buď:
- Kliknutím na tlačidlo pridajte nový modul Vložiť> Modul v ponuke (modul sa automaticky otvorí)
-
- ALEBO dvojitým kliknutím na existujúci modul v programe Project Explorer ho otvoríte
- V module zadajte kód pre vaše nové makro
Sub MyMacro () Koniec sub
Tieto dva riadky označujú začiatok a koniec makra s názvom „MyMacro“ (všimnite si povinné zátvorky). Toto sa zobrazí v dialógovom okne „Zobraziť makrá“ v programe Excel a je možné ho priradiť k tlačidlu (aj keď zatiaľ nič nerobí).
Pridajte do makra nejaký kód
Teraz pridajme nejaký kód medzi riadky „Sub“ a „End Sub“, aby toto makro skutočne niečo urobilo:
Rozsah Sub MyMacro () („A1“). Hodnota = „Hello World!“ Koniec pod
Základné štruktúry kódu
Objekt rozsahu
Excel VBA používa objekt rozsahu na reprezentáciu buniek v hárku. Vo vyššie uvedenom príklade je objekt Range vytvorený pomocou kódu Rozsah („A1“) aby ste získali prístup k hodnote bunky A1.
Objekty rozsahu sa primárne používajú na nastavenie hodnôt buniek:
Rozsah („A1“). Hodnota = 1
Rozsah („A1“). Hodnota = „prvá bunka“
Všimnite si, že pri definovaní hodnôt buniek ako čísel stačí zadať číslo, ale pri zadávaní textu musíte text obklopiť úvodzovkami.
Rozsahy je možné použiť aj na prístup k mnohým vlastnostiam buniek, ako sú ich písmo, ohraničenie, vzorce a ďalšie.
Písmo bunky môžete napríklad nastaviť na tučné takto:
Rozsah („A1“). Font.Bold = True
Môžete tiež nastaviť vzorec bunky:
Rozsah („A1“). Vzorec = „= súčet (A2: A10)“
V programe Excel môžete vybrať blok buniek pomocou kurzora (povedzme od A1 do D10) a nastaviť ich všetky na tučné. Objekty rozsahu môžu pristupovať k blokom buniek takto:
Rozsah („A1: D10“). Font.Bold = True
Môžete tiež odkazovať na niekoľko buniek/blokov naraz:
Rozsah („A1: D10, A12: D12, G1“). Font.Bold = True
Formát je rovnaký ako formát, ktorý by ste použili pri výbere buniek pre vzorec SUM () v programe Excel. Každý blok je oddelený čiarkou a bloky sú označené bunkami vľavo hore a vpravo dole oddelenými dvojbodkou.
Nakoniec, objekty Range majú vstavané metódy na vykonávanie bežných operácií s pracovným listom. Môžete napríklad chcieť skopírovať niektoré údaje z jedného miesta na druhé. Tu je príklad:
Rozsah („A1: D10“). Rozsah kopírovania („F1“). Rozsah PasteSpecial xlPasteValues („F1“). PasteSpecial xlPasteFormats
Skopíruje bunky A1: D10 do schránky a potom sa vykoná príkaz PasteSpecial () začínajúci v bunke C1 - rovnako ako by ste to urobili ručne v programe Excel. Tento príklad ukazuje, ako použiť PasteSpecial () na prilepenie iba hodnôt a formátov - pre všetky možnosti, ktoré by ste videli v dialógovom okne Prilepiť špeciálne, existujú parametre.
Tu je príklad prilepenia „Všetko“ do iného pracovného hárka:
Rozsah („A1: D10“). Kopírujte listy („List2“). Rozsah („A1“). PasteSpecial xlPasteAll
Ak vyhlásenia
S Ak vyhlásenie, môžete spustiť časť kódu iba vtedy, ak je určité tvrdenie pravdivé.
Bunku môžete napríklad označiť ako tučnú a zafarbiť na červeno, ale iba vtedy, ak je hodnota v bunke menšia ako 100.
Ak je rozsah („A4“). Hodnota <100 potom rozsah („A4“). Písmo. Tučné = True Range („A4“). Interior.Color = vbČervený koniec Ak
Správna štruktúra príkazu If je nasledovná (hranaté zátvorky označujú voliteľné súčasti):
Ak potom
[Ináč vtedy]
[Inak]
Koniec Ak
Môžete zahrnúť toľko Ináč Ak bloky, ako chcete testovať viac podmienok. Môžete tiež pridať príponu Inak blok, ktorý sa spustí iba vtedy, ak nie sú splnené žiadne ďalšie podmienky v príkaze If.
Tu je ďalší príklad založený na predchádzajúcom, kde je bunka formátovaná niekoľkými rôznymi spôsobmi v závislosti od hodnoty:
Ak je rozsah („A4“). Hodnota <100 a potom rozsah („A4“). Písmo.Bold = True Range („A4“). Interior.Color = vbRed ElseIf Range („A4“). Hodnota <200 Then Range ( "A4"). Písmo.Bold = nepravdivý rozsah ("A4"). Interior.Color = vbŽltý alebo iný rozsah ("A4"). Font.Bold = nepravdivý rozsah ("A4"). Interior.Color = vbZelený koniec, ak
Vo vyššie uvedenom príklade je bunka zrušená tučným písmom v blokoch ElseIf, kde hodnota nie je nižšia ako 100. Môžete hniezdo Ak sa vyhlásenia vyhnú duplikácii kódu, postupujte takto:
Ak je rozsah („A4“). Hodnota <100, potom rozsah („A4“). Písmo.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' rozbalenie písma iba raz, ak rozsah („A4“). Hodnota <200 potom rozsah („A4“). Interior.Color = vbŽltý rozsah („A4“). Interior.Color = vbZelený koniec, ak koniec, ak
Premenné
A Variabilné je časť pamäte používaná na ukladanie dočasných informácií počas spusteného makra. Často sa používajú v slučkách ako iterátory alebo na uloženie výsledku operácie, ktorú chcete v makre použiť niekoľkokrát.
Tu je príklad premennej a ako ju môžete použiť:
Sub ExtractSerialNumber () Dim strSerial As String 'Toto je deklarácia premennej' As As String 'znamená, že táto premenná má obsahovať text' nastavujúci predstierané sériové číslo: Rozsah ("A4"). Hodnota = “sériové# 804567-88 ”„ Analyzujte sériové číslo z bunky A4 a priraďte ho k premennej strSerial = stred (rozsah („A4“). Hodnota, 9) 'teraz použite premennú dvakrát, namiesto toho, aby ste museli sériové číslo analyzovať dvakrát Rozsah („ B4 ”). Hodnota = strSerial MsgBox strSerial End Sub
V tomto základnom prípade sa premenná „strSerial“ používa na extrahovanie sériového čísla z bunky A4 pomocou funkcie Mid () a potom sa použije na dvoch ďalších miestach.
Štandardný spôsob vyhlásiť premenná je nasledovná:
Dim akékoľvek meno [Ako typ]
- akékoľvek meno je názov, ktorý ste sa rozhodli zadať svojej premennej
- typ je dátový typ premennej
Stránka „[As typ] ”Časť môže byť vynechaná - ak áno, premenná je deklarovaná ako typ Variant, ktorý môže obsahovať akýkoľvek druh údajov. Typy variantov, aj keď sú úplne platné, by ste sa mali vyhýbať, pretože ak si nedáte pozor, môžu viesť k neočakávaným výsledkom.
Existujú pravidlá pre názvy premenných. Musia začínať písmenom alebo podčiarkovníkom, nesmú mať medzery, bodky, čiarky, úvodzovky ani znaky „! @ & $ #“.
Tu je niekoľko príkladov deklarácií premenných:
Dim strFilename As String 'dobrý názov štýlu - popisný a používa predponu Dim i As Long' zlý názov štýlu - prijateľný iba pre niektorých iterátorov Dim SaleCena ako dvojitý 'v poriadku názov štýlu - popisný, ale nepoužíva predponu Dim iCounter' v poriadku meno - nie je príliš popisný, používa predponu, žiadny typ údajov
Všetky tieto príklady používajú mierne odlišné schémy pomenovania, ale všetky sú platné. Nie je zlé predponovať názov premennej krátkou formou jeho dátového typu (podľa niektorých z týchto príkladov), pretože váš kód je na prvý pohľad čitateľnejší.
VBA obsahuje veľa základov dátové typy. Medzi najobľúbenejšie patria:
- Reťazec (slúži na uchovávanie textových údajov)
- Dlho (používa sa na uloženie celých čísel, t. j. bez desatinných miest)
- Dvojité (používa sa na uchovávanie čísel s pohyblivou rádovou čiarkou, t. j. desatinných miest)
Úplný zoznam vnútorných typov údajov VBA nájdete tu: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Premenné objektu rozsahu
Je možné vytvárať aj premenné, ktoré odkazujú na objekty rozsahu. Je to užitočné, ak chcete vo svojom kóde odkazovať na konkrétny rozsah na niekoľkých miestach - v prípade, že potrebujete rozsah zmeniť, stačí ho zmeniť iba na jednom mieste.
Keď vytvoríte premennú objektu Range, musíte ju „nastaviť“ na inštanciu rozsahu. Napríklad:
Dim rMyRange As Range Set rMyRange = Range (“A1: A10; D1: J10”)
Vynechanie príkazu „Set“ pri priradení premennej Range bude mať za následok chybu.
Slučky
Smyčky sú bloky, ktoré opakujú kód v nich určitý počet krát. Sú užitočné pri znižovaní množstva kódu, ktorý musíte napísať, a umožňujú vám napísať jeden kus kódu, ktorý vykonáva rovnaké akcie pre mnoho rôznych súvisiacich položiek.
For-Next
A For-Next blok je slučka, ktorá sa opakuje určitý počet krát. Ako premennú používa premennú iterátor aby sa spočítalo, koľkokrát sa spustilo, a túto premennú iterátora je možné použiť vo vnútri slučky. Vďaka tomu sú slučky For-Next veľmi užitočné pri iterácii cez bunky alebo polia.
Tu je príklad, ktorý prechádza bunkami v riadkoch 1 až 100, stĺpcom 1 a nastavuje ich hodnoty na hodnotu premennej iterátora:
Dim i As long for i = 1 to 100 Cells (i, 1). Value = i Next i
Riadok „Pre i = 1 až 100“ znamená, že slučka začína od 1 a končí po 100. Môžete nastaviť ľubovoľné počiatočné a koncové číslo; pre tieto čísla môžete použiť aj premenné.
Štandardne sa slučky For-Next počítajú od 1. Ak chcete počítať od iného čísla, môžete slučku napísať s explicitným Krok klauzula:
Pre i = 5 až 100 Krok 5
Táto slučka začína na 5, potom pridá 5 k „i“ vždy, keď sa slučka opakuje (takže „i“ bude 10 pri druhom opakovaní, 15 pri treťom atď.).
Použitím Krok, Môžete tiež urobiť počítanie slučky dozadu:
Pre i = 100 až 1 Krok -1
Môžete tiež hniezdo Slučky For-Next. Každý blok vyžaduje svoju vlastnú premennú, s ktorou môžete počítať, ale tieto premenné môžete použiť kdekoľvek. Tu je príklad toho, ako je to užitočné v programe 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í prechádzať riadkami aj stĺpcami.
POZOR: aj keď je to dovolené, NIKDY by ste nemali modifikovať premennú iterátora v bloku For-Next, pretože tento iterátor používa na sledovanie cyklu. Úprava iterátora môže spôsobiť nekonečnú slučku a zablokovanie makra. Napríklad:
Pre i = 1 až 100 i = 1 Ďalej i
V tejto slučke sa „I“ nikdy nedostane nad 2, kým sa nevynuluje na 1, a slučka sa bude navždy opakovať.
Pre každý
Pre každý bloky sú veľmi podobné blokom For-Next, ibaže nepoužívajú počítadlo na určenie, koľkokrát sa opakujú. Namiesto toho blok For-each vezme „zbierku“ objektov (ako je rozsah buniek) a spustí sa toľkokrát, koľkokrát sa v tejto zbierke nachádzajú objekty.
Tu je príklad:
Dim r as Range for every r In Range ("A15: J54") If r.Value> 0 Then r.Font.Bold = True End If Next r
Všimnite si použitie premennej objektu Range „r“. Toto je premenná iterátora používaná v slučke For -Every - zakaždým, keď v cykle prejde, „r“ získa odkaz na ďalšiu bunku v rozsahu.
Výhodou použitia cyklov For-Each v programe Excel VBA je, že môžete prechádzať všetkými bunkami v rozsahu bez vnorených slučiek. To môže byť užitočné, ak potrebujete prejsť všetkými bunkami v komplexnom rozsahu ako Rozsah („A1: D12, J13, M1: Y12“).
Jednou z nevýhod slučiek For-Each je, že nemáte žiadnu kontrolu nad poradím, v ktorom sa bunky spracovávajú. Hoci v praxi Excel bude cyklicky prechádzať bunkami, teoreticky mohol spracovať bunky v úplne náhodnom poradí. Ak potrebujete spracovať bunky v konkrétnom poradí, mali by ste namiesto toho použiť slučky For-Next.
Do-Loop
Kým bloky For-Next používajú počítadlá, aby vedeli, kedy zastaviť, Do-Loop bloky bežia, kým nie je splnená podmienka. Na tento účel použite príponu Do klauzula na začiatku alebo na konci bloku, ktorá testuje podmienku a spôsobí, že sa slučka zastaví, keď je táto podmienka splnená.
Príklad:
Dim str As String str = "Buffalo" Vykonajte do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Rozsah slučiek ("A1"). Hodnota = str
V tejto slučke je „Buffalo“ zreťazený na „str“ vždy v slučke, kým sa nezhoduje s očakávanou vetou. V tomto prípade sa test vykoná na začiatku cyklu - ak 'str' už bola očakávaná veta (čo nie je, pretože sme to tak nespustili, ale ak), slučka by sa ani nespustila .
Cyklus môžete nechať spustiť aspoň raz tak, že posuniete klauzulu Before na koniec takto:
Do str = str & "" & "Buffalo" opakujte do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Vo svojom makre môžete použiť ktorúkoľvek verziu, ktorá má zmysel.
POZOR: môžete spôsobiť nekonečnú slučku s blokom Do-Loop, ak podmienka Nikdy nie je splnená. Kód vždy píšte tak, aby pri použití tohto typu slučky bola podmienka Kým určite splnená.
Čo bude ďalej?
Keď pochopíte základy, prečo neskúsiť naučiť sa pokročilejšie techniky? Náš tutoriál na https://easyexcel.net/excel/learn-vba-tutorial/ bude stavať na všetkom, čo ste sa tu naučili, a rozšíri vaše schopnosti pomocou udalostí, UserForms, optimalizácie kódu a mnohých ďalších!
