Výukový program pre Excel VBA - Ako písať kód do tabuľky pomocou jazyka Visual Basic

Úvod

Toto je návod na písanie kódu v tabuľkových procesoroch Excel pomocou jazyka Visual Basic for Applications (VBA).

Excel je jedným z najobľúbenejších produktov spoločnosti Microsoft. V roku 2016 povedal generálny riaditeľ spoločnosti Microsoft: „Premýšľajte o svete bez Excelu. To je pre mňa jednoducho nemožné.“ No, možno svet nemôže myslieť bez Excelu.

  • V roku 1996 bolo viac ako 30 miliónov používateľov programu Microsoft Excel (zdroj).
  • V súčasnosti má Microsoft Excel podľa odhadov 750 miliónov používateľov. To je o niečo viac ako populácia v Európe a 25x viac používateľov ako v roku 1996.

Sme jedna veľká šťastná rodina!

V tomto výučbe sa dozviete o VBA a o tom, ako písať kód do tabuľky programu Excel pomocou jazyka Visual Basic.

Predpoklady

Na pochopenie tejto príručky nepotrebujete žiadne predchádzajúce skúsenosti s programovaním. Budete však potrebovať:

  • Základné až mierne znalosti jazyka Microsoft Excel
  • Ak chcete postupovať podľa príkladov VBA v tomto článku, budete potrebovať prístup k programu Microsoft Excel, najlepšie k najnovšej verzii (2019), ale Excel 2016 a Excel 2013 budú fungovať dobre.
  • Ochota skúšať nové veci

Učebné ciele

V priebehu tohto článku sa dozviete:

  1. Čo je VBA
  2. Prečo by ste používali VBA
  3. Ako nastaviť, aby sa v programe Excel písalo VBA
  4. Ako vyriešiť niektoré problémy z reálneho sveta pomocou VBA

Dôležité koncepty

Tu je niekoľko dôležitých konceptov, ktoré by ste mali poznať, aby ste tomuto kurzu úplne porozumeli.

Objekty : Excel je objektovo orientovaný, čo znamená, že všetko je objekt - okno programu Excel, zošit, hárok, graf, bunka. VBA umožňuje používateľom manipulovať a vykonávať akcie s objektmi v programe Excel.

Ak nemáte žiadne skúsenosti s objektovo orientovaným programovaním a jedná sa o úplne nový koncept, na chvíľu ho nechajte vstúpiť!

Postupy : postup je veľká časť kódu VBA napísaná v editore jazyka Visual Basic, ktorá slúži na vykonanie úlohy. Niekedy sa to označuje aj ako makro (viac o makrách nižšie). Existujú dva typy postupov:

  • Podprogramy: skupina príkazov VBA, ktorá vykonáva jednu alebo viac akcií
  • Funkcie: skupina príkazov VBA, ktorá vykonáva jednu alebo viac akcií a vracia jednu alebo viac hodnôt

Poznámka: Vo vnútri podprogramov môžete mať funkcie. Uvidíš neskôr.

Makrá : Ak ste strávili nejaký čas učením pokročilejších funkcií Excelu, pravdepodobne ste sa už stretli s konceptom makra. Používatelia programu Excel môžu zaznamenávať makrá pozostávajúce z používateľských príkazov / klávesových skratiek / kliknutí a prehrávať ich rýchlosťou blesku pri opakovaných úlohách. Zaznamenané makra generujú kód VBA, ktorý potom môžete preskúmať. Je vlastne celkom zábavné zaznamenať jednoduché makro a potom sa pozrieť na kód VBA.

Majte na pamäti, že niekedy môže byť jednoduchšie a rýchlejšie zaznamenať makro, ako ručne kódovať postup VBA.

Napríklad možno pracujete v projektovom manažmente. Raz týždenne musíte zmeniť surovú exportovanú správu z vášho systému riadenia projektu na nádherne naformátovanú a čistú správu pre vedenie. Názvy nadrozpočtových projektov musíte naformátovať tučným červeným textom. Zmeny formátovania môžete zaznamenať ako makro a spustiť ich kedykoľvek budete musieť vykonať zmenu.

Čo je to VBA?

Visual Basic for Applications je programovací jazyk vyvinutý spoločnosťou Microsoft. Každý softvérový program v balíku Microsoft Office je dodávaný s jazykom VBA bez ďalších nákladov. VBA umožňuje používateľom balíka Microsoft Office vytvárať malé programy, ktoré fungujú v rámci softvérových programov balíka Microsoft Office.

Predstavte si VBA ako pec na pizzu v reštaurácii. Reštaurácia je Excel. Kuchyňa je vybavená štandardnými komerčnými spotrebičmi, ako sú veľké chladničky, sporáky a bežné rúry na pečenie - to sú všetko štandardné funkcie programu Excel.

Čo však v prípade, že si chcete pripraviť pizzu na drevo? To sa nedá urobiť v štandardnej komerčnej pekárskej peci. VBA je pec na pizzu.

Mňam

Prečo používať VBA v programe Excel?

Pretože pizza na drevo je najlepšia!

Ale vážne.

Mnoho ľudí trávi v rámci svojej práce veľa času v programe Excel. Čas v programe Excel sa tiež pohybuje inak. Podľa okolností sa 10 minút v programe Excel môže cítiť ako večnosť, ak nie ste schopní urobiť to, čo potrebujete, alebo 10 hodín môže uplynúť veľmi rýchlo, ak je všetko v poriadku. Čo je, kedy by ste si mali položiť otázku, prečo preboha trávim 10 hodín v programe Excel?

Niekedy sú tie dni nevyhnutné. Ak však trávite v programe Excel každý deň 8-10 hodín opakovaním úloh, opakovaním rovnakých procesov, pokusom o upratovanie po iných používateľoch súboru alebo dokonca aktualizáciou ďalších súborov po vykonaní zmien v súbore Excel, postup VBA by pre vás mohol byť riešením.

Používanie VBA by ste mali zvážiť, ak potrebujete:

  • Automatizujte opakujúce sa úlohy
  • Vytvorte jednoduché spôsoby, ako môžu používatelia interagovať s vašimi tabuľkami
  • Manipulujte s veľkým množstvom údajov

Príprava nastavenia na písanie VBA v programe Excel

Tabuľka pre vývojárov

Ak chcete písať VBA, musíte na pás s nástrojmi pridať kartu Vývojár, aby sa vám páska zobrazovala takto.

Pridanie karty Vývojár na pás s nástrojmi:

  1. Na karte Súbor prejdite na Možnosti> Prispôsobiť pásku s nástrojmi.
  2. V časti Prispôsobenie pásky a v časti Hlavné karty začiarknite políčko Vývojár.

Po zobrazení karty zostane karta Vývojár viditeľná, pokiaľ nezrušíte začiarknutie políčka alebo nebudete musieť program Excel preinštalovať. Ďalšie informácie nájdete v dokumentácii pomoci spoločnosti Microsoft.

Editor VBA

Prejdite na kartu Vývojár a kliknite na tlačidlo Visual Basic. Zobrazí sa nové okno - toto je editor jazyka Visual Basic. Na účely tohto tutoriálu sa musíte oboznámiť s tablou Prieskumník projektu a tablou Vlastnosti.

Príklady Excel VBA

Najprv si vytvoríme súbor, ktorý budeme hrať.

  1. Otvorte nový súbor programu Excel
  2. Uložte ho ako zošit s povolenými makrami (. Xlsm)
  3. Vyberte kartu Vývojár
  4. Otvorte editor VBA

Poďme na rock and roll s niekoľkými jednoduchými príkladmi, ktoré vám umožnia písať kód v tabuľke pomocou jazyka Visual Basic.

Príklad č. 1: Zobrazenie správy, keď používatelia otvoria zošit programu Excel

V editore VBA vyberte Vložiť -> Nový modul

Napíšte tento kód do okna modulu (nelepte!):

Sub Auto_Open ()

MsgBox („Vitajte v zošite XYZ.“)

Koniec Sub

Uložte, zatvorte zošit a znova ho otvorte. Toto dialógové okno by sa malo zobraziť.

Ta da!

Ako to robí?

V závislosti od vašej znalosti programovania môžete mať nejaké odhady. Nie je to nijako zvlášť zložité, ale deje sa toho naozaj veľa:

  • Sub (skratka pre „Subrutine): pamätajte si od začiatku„ na skupinu vyhlásení VBA, ktorá vykonáva jednu alebo viac akcií. “
  • Auto_otvorenie: toto je konkrétny podprogram. Automaticky spustí váš kód, keď sa otvorí súbor Excel - to je udalosť, ktorá spustí postup. Auto_Open sa spustí, len keď je zošit otvorený manuálne; nespustí sa, ak je zošit otvorený pomocou kódu z iného zošita (Workbook_Open to urobí, dozviete sa viac o rozdiele medzi týmito dvoma).
  • V predvolenom nastavení je prístup k podprogramu verejný. To znamená, že tento podprogram môže používať ktorýkoľvek iný modul. Všetky príklady v tejto príručke budú verejné podprogramy. V prípade potreby môžete podprogramy vyhlásiť za súkromné. To môže byť v niektorých situáciách potrebné. Získajte viac informácií o modifikátoroch prístupu k podprogramom.
  • msgBox: toto je funkcia - skupina príkazov VBA, ktorá vykonáva jednu alebo viac akcií a vracia hodnotu. Vrátenou hodnotou je správa „Vitajte v zošite XYZ.“

Stručne povedané, jedná sa o jednoduchý podprogram, ktorý obsahuje funkciu.

Kedy by som to mohol použiť?

Možno máte veľmi dôležitý súbor, ku ktorému sa pristupuje zriedka (napríklad raz za štvrťrok), ale ktorý sa každý deň automaticky aktualizuje pomocou iného postupu VBA. Ak je k nej prístup, poskytuje ju veľa ľudí na viacerých oddeleniach v celej spoločnosti.

  • Problém: Keď používatelia pristupujú k súboru, sú väčšinou zmätení o účele tohto súboru (prečo existuje), o tom, ako sa tak často aktualizuje, kto ho spravuje a ako by s ním mali interagovať. Noví najímatelia majú vždy veľa otázok a tieto otázky musíte neustále opakovať.
  • Riešenie: vytvorte používateľskú správu, ktorá obsahuje stručnú odpoveď na každú z týchto často odpovedaných otázok.

Príklady zo skutočného sveta

  • Pomocou funkcie MsgBox môžete zobraziť správu, keď dôjde k nejakej udalosti: používateľ zatvorí zošit programu Excel, používateľ vytlačí, do zošita sa pridá nový hárok atď.
  • Pomocou funkcie MsgBox môžete zobraziť správu, keď používateľ potrebuje pred zatvorením zošita programu Excel splniť podmienku
  • Na získanie informácií od používateľa použite funkciu InputBox

Príklad č. 2: Umožniť používateľovi vykonať iný postup

V editore VBA vyberte Vložiť -> Nový modul

Napíšte tento kód do okna modulu (nelepte!):

Sub UserReportQuery ()

Dim UserInput As Long

Stlmiť odpoveď ako celé číslo

UserInput = vbYesNo

Odpoveď = MsgBox („Spracovať prehľad XYZ?“, UserInput)

Ak odpoveď = vbYes, potom ProcessReport

Koniec Sub

Podprocesná správa ()

MsgBox („Ďakujeme za spracovanie správy XYZ.“)

Koniec Sub

Uložte a prejdite späť na kartu Vývojár v programe Excel a vyberte možnosť „Tlačidlo“. Kliknite na bunku a tlačidlu priraďte makro UserReportQuery.

Teraz kliknite na tlačidlo. Táto správa by mala zobrazovať:

Kliknite na „áno“ alebo stlačte kláves Enter.

Ešte raz, tada!

Upozorňujeme, že sekundárny podprogram, ProcessReport, môže byť akýkoľvek . V príklade 3 ukážem viac možností. Ale najprv...

Ako to robí?

Tento príklad nadväzuje na predchádzajúci príklad a má pomerne veľa nových prvkov. Poďme na nové veci:

  • Dim UserInput As Long: Dim je skratka pre „dimenziu“ a umožňuje deklarovať názvy premenných. V tomto prípade je UserInput názov premennej a Long je dátový typ. V jednoduchej angličtine tento riadok znamená „Tu je premenná s názvom„ UserInput “a jedná sa o typ premennej Long.“
  • Dim Answer As Integer: deklaruje inú premennú nazvanú „Answer“ s dátovým typom Integer. Viac informácií o typoch údajov nájdete tu.
  • UserInput = vbYesNo: priradí hodnotu premennej. V takom prípade vbYesNo, ktoré zobrazuje tlačidlá Áno a Nie. Existuje veľa typov tlačidiel, viac sa dozviete tu.
  • Odpoveď = MsgBox („Spracovať správu XYZ?“, UserInput): priradí hodnote premennej Answer funkciu MsgBox a premennú UserInput. Áno, premenná v rámci premennej.
  • Ak Answer = vbYes Then ProcessReport: toto je výraz „If“, podmienený výrok, ktorý nám umožňuje povedať, či je x pravda, potom urobte y. V takom prípade, ak používateľ vybral „Áno“, vykonajte podprogram ProcessReport.

Kedy by som to mohol použiť?

To by sa dalo využiť mnohými a mnohými spôsobmi. Hodnota a všestrannosť tejto funkcie je viac definovaná tým, čo robí sekundárny podprogram.

Napríklad môžete mať súbor, ktorý sa používa na generovanie 3 rôznych týždenných správ. Tieto správy sú formátované dramaticky rôznymi spôsobmi.

  • Problém: Zakaždým, keď je potrebné vygenerovať jeden z týchto prehľadov, používateľ otvorí súbor a zmení formátovanie a grafy; tak ďalej a tak ďalej. Tento súbor sa rozsiahlo upravuje najmenej trikrát týždenne a jeho vykonanie trvá minimálne 30 minút.
  • Riešenie: Vytvorte 1 tlačidlo pre každý typ prehľadu, ktoré automaticky preformátuje potrebné komponenty prehľadov a vygeneruje potrebné grafy.

Príklady zo skutočného sveta

  • Vytvorte dialógové okno pre používateľa na automatické vyplnenie určitých informácií viacerými listami
  • Pomocou funkcie InputBox získate informácie od používateľa, ktoré sa potom vyplnia do viacerých hárkov

Príklad č. 3: Pridajte čísla do rozsahu pomocou slučky For-Next

Pre slučky sú veľmi užitočné, ak potrebujete vykonávať opakujúce sa úlohy na konkrétnom rozsahu hodnôt - poliach alebo rozsahoch buniek. V jednoduchej angličtine slučka hovorí „pre každé x, urob y“.

V editore VBA vyberte Vložiť -> Nový modul

Napíšte tento kód do okna modulu (nelepte!):

Príklad vedľajšej slučky ()

Dim X ako celé číslo

Pre X = 1 až 100

Rozsah („A“ a X). Hodnota = X

Ďalej X

Koniec Sub

Uložte a prejdite späť na kartu Vývojár v programe Excel a kliknite na tlačidlo Makrá. Spustite makro LoopExample.

Malo by sa to stať:

Atď., Až do 100. riadku.

Ako to robí?

  • Dim X As Integer: deklaruje premennú X ako dátový typ Integer.
  • Pre X = 1 až 100: toto je začiatok slučky For. Jednoducho povedané, povie to slučke, aby sa opakovala, kým X = 100. X je počítadlo . Smyčka sa bude vykonávať až do X = 100, vykoná sa naposledy a potom sa zastaví.
  • Range ("A" & X) .Value = X: toto deklaruje rozsah slučky a to, čo má byť do tohto rozsahu. Pretože X = 1 spočiatku, prvá bunka bude A1, a v tom okamihu slučka vloží X do tejto bunky.
  • Ďalej X: toto povie slučke, aby sa znovu spustila

Kedy by som to mohol použiť?

Smyčka For-Next je jednou z najsilnejších funkcií VBA; existuje veľa potenciálnych prípadov použitia. Toto je zložitejší príklad, ktorý by vyžadoval viac vrstiev logiky, ale komunikuje svet možností v cykloch For-Next.

Možno máte zoznam všetkých výrobkov predávaných vo vašej pekárni v stĺpci A, typ produktu v stĺpci B (koláče, šišky alebo vdolky), náklady na prísady v stĺpci C a priemerné trhové náklady na každý typ produktu v ďalší list.

Musíte zistiť, aká by mala byť maloobchodná cena každého produktu. Myslíte si, že by to mali byť náklady na ingrediencie plus 20%, ale podľa možnosti aj 1,2% pod priemerom trhu. Smyčka For-Next by vám umožnila vykonať tento typ výpočtu.

Príklady zo skutočného sveta

  • Pomocou slučky s vnoreným príkazom if môžete pridať konkrétne hodnoty do samostatného poľa, iba ak spĺňajú určité podmienky
  • Vykonajte matematické výpočty pre každú hodnotu v rozsahu, napr. Vypočítajte ďalšie poplatky a pridajte ich k hodnote
  • Prelistujte každý znak v reťazci a extrahujte všetky čísla
  • Náhodne vyberte počet hodnôt z poľa

Záver

Teraz, keď sme hovorili o pizzi a muffinoch a ach, o tom, ako písať kód VBA do tabuliek programu Excel, urobme kontrolu učenia. Zistite, či môžete odpovedať na tieto otázky.

  • Čo je to VBA?
  • Ako sa nastavím na začatie používania jazyka VBA v programe Excel?
  • Prečo a kedy by ste použili VBA?
  • Aké sú problémy, ktoré by som mohol vyriešiť pomocou VBA?

Ak máte nestranný názor na to, ako môžete na tieto otázky odpovedať, bolo to úspešné.

Či už ste príležitostným používateľom alebo pokročilým používateľom, dúfam, že tento výukový program poskytol užitočné informácie o tom, čo je možné dosiahnuť pomocou trochy kódu v tabuľkových procesoroch programu Excel.

Šťastné programovanie!

Učebné zdroje

  • Programovanie Excel VBA pre figuríny, John Walkenbach
  • Začíname s dokumentáciou VBA, Microsoft
  • Výučba jazyka VBA v programe Excel, Lynda

Trochu o mne

Som Chloe Tucker, umelkyňa a vývojárka v Portlande v Oregone. Ako bývalý pedagóg neustále hľadám prienik medzi učením a výučbou alebo technológiami a umením. Oslovte ma na Twitteri @_chloetucker a pozrite sa na môj web na adrese chloe.dev.