Ako písať makrá pre Excel. Makrá v Exceli - Návod na použitie. Pridanie tlačidiel makier na karty
Téma 2.3. Prezentačný softvér a základy kancelárskeho programovania
Téma 2.4. Systémy správy databáz a expertné systémy
2.4.11. Databáza školení s hlavným tlačidlom formulár "Training_students" - Stiahnuť
Programovanie VBA a makrá
2.3. Prezentačný softvér a základy kancelárskeho programovania
2.3.7. Základy programovania v Office
BASIC vizuálny programovací jazyk pre aplikácie: Visual Basic for Applications (VBA)
VBA je podmnožinou vizuálneho programovacieho jazyka Visual Basic (VB), ktorý obsahuje takmer všetky nástroje na vytváranie aplikácií VB.
VBA sa líši od programovacieho jazyka VB tým, že jazyk VBA je navrhnutý na priamu prácu s objektmi balíka Office a nemožno ho použiť na vytvorenie projektu nezávislého od aplikácií balíka Office. Vo VBA je teda programovacím jazykom VB a programovacie prostredie je implementované vo forme VB editora, ktorý je možné aktivovať z akejkoľvek aplikácie MS Office.
Napríklad, ak chcete otvoriť editor VBA z PowerPointu, musíte spustiť príkaz Nástroje / Makro / Editor VBA. Z editora sa môžete vrátiť do aplikácie výberom príkazu Microsoft PowerPoint v ponuke Zobraziť alebo stlačením kombinácie klávesov Alt + F11.
Pomocou vstavanej sady ovládacích prvkov editora VBA a editora formulárov môže používateľ vytvárať používateľské rozhranie pre projekt, s ktorým sa pracuje formulár obrazovky. Ovládacie prvky sú objekty a každý objekt má definovanú množinu možných udalostí (napríklad kliknutie alebo dvojité kliknutie myšou, stlačenie klávesu, potiahnutie objektu atď.).
Každá udalosť sa prejavuje určitými programovými akciami (reakcie, reakcie). Používateľský formulár vám umožňuje vytvárať dialógové okná aplikácie. Programovací jazyk VBA sa používa na písanie programového kódu, napríklad na vytváranie používateľských funkcií v Exceli.
Skutočnosť, že programovací systém VBA je navrhnutý pre prácu s objektmi Office, umožňuje jeho efektívne využitie na automatizáciu vývojových aktivít rôzne druhy Dokumenty.
Pozrime sa na algoritmus na vytváranie používateľských funkcií vo VBA:
1. Otvorte okno editora kódu VBA spustením príkazu Tools / Macro / Visual Basic Editor alebo stlačením Alt+F11.
2. Vyberte položku ponuky editora Vložiť / Modul.
3. Ďalej vykonajte Vložiť / Postup. V dialógovom okne Pridať procedúru, ktoré sa otvorí, zadajte názov funkcie (napríklad SUM5) a nastavte prepínače: Typ – do polohy Funkcia; Rozsah (Scope) - do pozície Public (General) a kliknite na OK.
Ryža. 1.
4. V okne editora pre programovanie VBA sa zobrazí šablóna funkcie: nadpis - Verejná funkcia SUM5() a koniec - End Function, medzi ktoré je potrebné umiestniť kód tela funkcie.
Ryža. 2.
5. Ďalej zadáme zoznam parametrov funkcie, napríklad v zátvorkách uvedieme (x, y, z, i, j), typ údajov (pre presné výpočty) a typ hodnoty vrátenej funkciou (my v tomto príklade ho nezadáte). Okrem toho uvedieme telo funkcie, napríklad SUM5 = x + y + z + i + j. Výsledkom je nasledujúci text programu:
Verejná funkcia SUM5(x, y, z, i, j)
SUM5 = x + y + z + i + j
End Function
6. Návrat k oknu Excel aplikácie, v ktorom napríklad potrebujeme sčítať päť hodnôt.
7. Vykonajte „Vložiť/Funkcia“ av okne sprievodcu funkciou, ktoré sa otvorí, vyberte kategóriu „Definované používateľom“ av okne „Vybrať funkciu“ vyberte položku SUM5 a kliknite na tlačidlo OK.
Ryža. 3.
Ryža. 4.
Makrá
Vytvorenie makra
Vytvorme makro na automatické sčítanie dvoch čísel v bunkách A1, B1 a výsledok umiestnime do bunky C1, ako aj vyplníme bunku C1 tyrkysovou farbou.
Algoritmus na vytvorenie makra pre úlohu:
1. Zvoľte Nástroje/Makro, Spustiť nahrávanie.
2. Do poľa Názov makra zadajte názov makra. Prvý znak názvu makra musí byť písmeno. V názve makra nie sú povolené medzery; Ako oddeľovač slov môžete použiť podčiarkovníky.
3. Ak chcete spustiť makro pomocou klávesovej skratky, zadajte písmeno do poľa Klávesová skratka. Môžete použiť kombináciu CTRL+ písmeno (pre malé písmená) alebo CTRL+SHIFT+ písmeno (pre veľké písmená), kde písmeno je ľubovoľné písmeno na klávesnici. Nevyberajte štandardnú klávesovú skratku, pretože vybratá klávesová skratka prepíše štandardné klávesové skratky Microsoft Excel vtedy s touto knihou.
4. V poli "Uložiť" vyberte zošit, do ktorého chcete makro uložiť. Uložte makro do "Tento zošit". Na tvorenie stručný popis makro, zadajte požadovaný text do poľa Popis. Snímka obrazovky zobrazuje príklad vyplnenia dialógového okna „Nahrať makro“.
Ryža. 5.
5. Kliknite na tlačidlo OK.
6. Vykonajte makropríkazy, ktoré chcete zaznamenať.
Ryža. 6.
7. Kliknite na tlačidlo Zastaviť nahrávanie na plávajúcom paneli nástrojov OS (Zastaviť nahrávanie) alebo Nástroje / Makro / Zastaviť makro.
Keď makro dokončí záznam, zobrazí sa v zozname makier pod svojím názvom.
Editor VBA automaticky zaznamenal postupnosť makropríkazov alebo programu na základe vykonaných akcií (obr. 7.).
Ryža. 7.
Ak chcete volať makro, musíte vykonať príkaz Nástroje / Makro / Makrá. Potom ho v dialógovom okne so zoznamom makier nájdete podľa názvu a kliknite na tlačidlo Spustiť.
Priradenie tlačidla na paneli nástrojov na spustenie makra
Makro je možné spustiť pomocou tlačidla na vstavanom paneli nástrojov, aby ste to urobili, musíte urobiť:
- V ponuke Nástroje vyberte položku Nastavenia.
- V dialógovom okne Nastavenia vyberte kartu Príkazy a v zozname Kategórie vyberte možnosť Makrá a v zozname Príkazy vyberte „Vlastné tlačidlo“.
- Zo zoznamu Príkazy potiahnite myšou vlastné tlačidlo na panel s nástrojmi.
- Kliknite pravým tlačidlom myši na toto tlačidlo a vyberte položku Priradiť makro obsahové menu.
- Do poľa Názov makra zadajte názov makra.
Priradenie oblasti grafického objektu na spustenie makra:
- Vytvorte grafický objekt.
- Použite kontextové menu na vybraný grafický objekt.
- Z kontextovej ponuky vyberte príkaz Priradiť makro.
- V zobrazenom dialógovom okne Priradiť makro k objektu zadajte názov makra do poľa Názov makra a kliknite na tlačidlo OK.
Úprava makra sa vykonáva pomocou editora VBA, na to musíte urobiť nasledovné:
- Vyberte príkaz Nástroje / Makrá / Makrá.
- V zozname Názov vyberte názov makra, ktoré chcete zmeniť.
- Kliknite na tlačidlo Upraviť, otvorí sa okno Visual Basic, v ktorom môžete upravovať príkazy vybraného makra napísaného vo Visual Basicu.
Odstránenie makra:
- V ponuke Nástroje vyberte položku Makrá a potom položku Makrá.
- V zozname makier v aktuálnom zošite musíte vybrať makro, ktoré chcete odstrániť, a kliknúť na tlačidlo Odstrániť.
Premenovanie makra
Ak chcete premenovať makro, musíte vstúpiť do režimu úpravy makra a zmeniť názov v texte programu. Nový názov automaticky nahradí starý v zoznamoch makier a klávesové skratky zavolajú makro s novým názvom.
Najprv trochu o terminológii.
Makro- toto je kód napísaný vo vstavanom jazyku Excelu VBA(Vizuálny základ pre aplikáciu). Makrá je možné vytvárať buď manuálne, alebo ich zaznamenávať automaticky pomocou takzvaného makrorekordéra.
Makrorekordér je nástroj v Exceli, ktorý zaznamenáva všetko, čo robíte v Exceli krok za krokom a prevádza to do kódu VBA. Makrorekordér vytvára veľmi podrobný kód (ako uvidíme neskôr), ktorý môžete v prípade potreby neskôr upraviť.
Zaznamenané makro je možné spustiť neobmedzený počet krát a Excel zopakuje všetky zaznamenané kroky. To znamená, že aj keď o VBA nič neviete, niektoré úlohy môžete automatizovať tak, že si svoje kroky jednoducho zaznamenáte a neskôr ich znova použijete.
Teraz sa poďme ponoriť a uvidíme, ako zaznamenať makro v Exceli.
Zobrazte kartu Vývojár na páse s ponukami
Pred zaznamenaním makra musíte pridať kartu Vývojár na pás s nástrojmi ponuky Excel. Ak to chcete urobiť, postupujte takto:
V dôsledku toho sa na páse s ponukami zobrazí karta „Vývojár“.
Nahrávanie makra v Exceli
Teraz si napíšme veľmi jednoduché makro, ktoré vyberie bunku a zadá do nej text, napríklad „Excel“.
Tu sú kroky na zaznamenanie takéhoto makra:
Gratulujem! Práve ste zaznamenali svoje prvé makro v Exceli. Hoci makro nerobí nič užitočné, pomôže nám pochopiť, ako funguje záznam makier v Exceli.
Teraz sa pozrime na kód, ktorý zaznamenal makrorekordér. Ak chcete otvoriť editor kódu, postupujte podľa týchto krokov:
Uvidíte, že po kliknutí na tlačidlo Spustiť sa do bunky A2 vloží text „Excel“ a vyberie sa bunka A3. To sa deje v milisekundách. Ale v skutočnosti makro vykonávalo zaznamenané akcie postupne.
Poznámka. Makro môžete spustiť aj pomocou klávesovej skratky Ctrl + Shift + N (podržte klávesy Ctrl a Shift a potom stlačte kláves N). Ide o rovnaký štítok, ktorý sme priradili makru, keď sme ho zaznamenávali.
Čo zaznamenáva makro?
Teraz poďme do editora kódu a uvidíme, čo máme.
Tu sú kroky na otvorenie editora VB v Exceli:
- V skupine Kód kliknite na tlačidlo Visual Basic.
Môžete tiež použiť kombináciu klávesov Alt + F11 a prejsť do editora kódu VBA.
- Panel s ponukami: Obsahuje príkazy, ktoré môžete použiť pri práci s editorom VB.
- Panel s nástrojmi- vyzerá ako panel rýchly prístup v Exceli. Môžete pridať ďalšie nástroje, ktoré často používate.
- Project Explorer- Tu Excel uvádza zoznam všetkých zošitov a všetkých objektov v každom zošite. Napríklad, ak máme pracovný zošit s 3 pracovnými hárkami, zobrazí sa v Project Explorer. Nachádza sa tu niekoľko ďalších objektov, ako sú moduly, používateľské formuláre a moduly tried.
- Okno s kódom- samotný kód VBA sa nachádza v tomto okne. Pre každý objekt uvedený v prieskumníkovi projektu existuje okno kódu, ako sú pracovné hárky, zošity, moduly atď. V tomto návode uvidíme, že zaznamenané makro je v okne kódu modulu.
- Okno vlastností- v tomto okne môžete vidieť vlastnosti každého objektu. Toto okno často používam na označenie objektov alebo zmenu ich vlastností.
- Okamžité okno(okno náhľadu) - V počiatočnej fáze ho nebudete potrebovať. Je to užitočné, keď chcete otestovať kroky alebo počas ladenia. V predvolenom nastavení sa nezobrazuje a môžete ho zobraziť kliknutím na kartu Zobraziť a výberom možnosti Okamžité okno.
Keď sme zaznamenali makro „EnterText“, v editore VB sa udiali nasledujúce veci:
- Bol pridaný nový modul.
- Makro bolo zaznamenané s názvom, ktorý sme zadali - "InputText"
- Do okna kódu bol pridaný nový postup.
Ak teda dvakrát kliknete na modul (v našom prípade modul 1), zobrazí sa okno s kódom, ako je uvedené nižšie.
Tu je kód zaznamenaný makrorekordérom:
SubTextEnter() " "TextEnter Makro " " Rozsah("A2").Vyberte ActiveCell.FormulaR1C1 = "Excel" Rozsah("A3").Vyberte End Sub
Vo VBA sa nevykoná žiadny riadok, ktorý nasleduje po " (apostrof). Toto je komentár, ktorý slúži len na informačné účely. Ak odstránite prvých päť riadkov tohto kódu, makro bude stále fungovať.
Teraz si prejdeme každý riadok kódu a popíšeme čo a prečo.
Kód začína s Sub nasleduje názov makra a prázdne zátvorky. Sub je skratka pre podprogram. Každý podprogram (nazývaný aj procedúra) vo VBA začína na Sub a končí End Sub.
- Rozsah("A2").Vybrať - Tento riadok vyberie bunku A2.
- ActiveCell.FormulaR1C1 = "Excel" - Tento riadok zadáva text "Excel" do aktívnej bunky. Keďže sme ako prvý krok vybrali bunku A2, stáva sa našou aktívnou bunkou.
- Rozsah("A3").Vybrať - vyberte bunku A3. To sa stane, keď po zadaní textu stlačíme kláves Enter, výsledkom čoho je výber bunky A3.
Dúfajme, že máte nejaké základné znalosti o tom, ako zaznamenať makro v programe Excel.
Upozorňujeme, že kód zaznamenaný cez záznamník makier vo všeobecnosti nie je efektívny alebo optimalizovaný kód. Makrorekordér často pridáva zbytočné kroky navyše. To však neznamená, že nemusíte používať záznamník makier. Pre tých, ktorí sa práve učia VBA, môže byť záznam makier skvelým spôsobom, ako analyzovať a pochopiť, ako všetko funguje vo VBA.
Absolútny a relatívny záznam makra
Viete už o absolútnych a relatívnych odkazoch v Exceli? Ak na zaznamenanie makra použijete absolútny odkaz, kód VBA bude vždy odkazovať na tie isté bunky, ktoré ste použili vy. Napríklad, ak vyberiete bunku A2 a zadáte text "Excel", potom zakaždým - bez ohľadu na to, kde sa nachádzate v pracovnom hárku a bez ohľadu na to, ktorá bunka je vybratá, váš kód vloží text "Excel" do bunky A2.
Ak na zaznamenanie makra použijete možnosť relatívnej referencie, VBA sa nenaviaže na konkrétnu adresu bunky. V tomto prípade sa program „pohne“ vzhľadom na aktívnu bunku. Povedzme napríklad, že ste už vybrali bunku A1 a začnete zaznamenávať makro v režime relatívneho prepojenia. Teraz vyberte bunku A2, zadajte Excelový text a stlačte Enter. Ak teraz spustíte toto makro, nevráti sa do bunky A2, ale presunie sa relatívne k aktívnej bunke. Napríklad, ak je vybratá bunka B3, presunie sa do B4, napíše text "Excel" a potom sa presunie do bunky K5.
Teraz zaznamenajme makro v režime relatívneho prepojenia:
Makro v režime relatívneho prepojenia sa uloží.
Teraz urobte nasledovné.
- Vyberte ľubovoľnú bunku (okrem A1).
- Prejdite na kartu „Vývojár“.
- V skupine Kód kliknite na tlačidlo Makrá.
- V dialógovom okne Makro kliknite na uložené makro Relatívne referencie.
- Kliknite na tlačidlo "Spustiť".
Ako si všimnete, makro nezapísalo text "Excel" do buniek A2. Stalo sa to preto, že ste makro zaznamenali v relatívnom referenčnom režime. Kurzor sa teda pohybuje relatívne k aktívnej bunke. Napríklad, ak to urobíte, keď je vybratá bunka B3, prejde do textu programu Excel - bunka B4 a nakoniec sa vyberie bunka B5.
Tu je kód, ktorý zaznamenal makrorekordér:
Všimnite si, že kód neodkazuje na bunky B3 alebo B4. Makro používa Activecell na odkaz na aktuálnu bunku a posun relatívne k tejto bunke.
Ignorujte časť kódu Range("A1"). Toto je jeden z tých prípadov, keď makrorekordér pridá nepotrebný kód, ktorý neslúži žiadnemu účelu a možno ho odstrániť. Bez neho bude kód fungovať dobre.
Čo nemôžete robiť s makrorekordérom?
Makrorekordér je skvelý pre vás v Exceli a zaznamenáva vaše presné kroky, ale nemusí byť pre vás tou správnou voľbou, keď potrebujete urobiť viac.
- Nemôžete spustiť kód bez výberu objektu. Napríklad, ak chcete, aby makro prešlo na ďalší pracovný hárok a zvýraznilo všetky vyplnené bunky v stĺpci A bez toho, aby opustilo aktuálny pracovný hárok, záznam makier to nebude môcť urobiť. V takýchto prípadoch musíte kód upraviť manuálne.
- Pomocou záznamníka makier nemôžete vytvoriť vlastnú funkciu. Pomocou jazyka VBA môžete vytvárať vlastné funkcie, ktoré možno použiť v hárku ako bežné funkcie.
- Pomocou makrorekordéra nemôžete vytvárať slučky. Ale môžete zaznamenať jednu akciu a pridať slučku ručne v editore kódu.
- Nemôžete analyzovať podmienky: môžete skontrolovať podmienky v kóde pomocou záznamníka makier. Ak píšete kód VBA ručne, môžete použiť príkazy IF Then Else na vyhodnotenie podmienky a spustenie kódu, ak je pravda (alebo iný kód, ak je nepravda).
Rozšírenie súborov programu Excel, ktoré obsahujú makrá
Keď zaznamenáte makro alebo manuálne zapíšete kód VBA v Exceli, musíte súbor uložiť s príponou súboru s povoleným makrom (.xlsm).
Pred Excelom 2007 stačil jeden formát súboru – .xls. Od roku 2007 je však .xlsx zavedený ako štandardná prípona súboru. Súbory uložené ako .xlsx nemôžu obsahovať makro. Ak teda máte súbor s príponou .xlsx a napíšete/nahráte makro a uložíte ho, upozorní vás na jeho uloženie vo formáte s povoleným makrom a zobrazí nasledujúce dialógové okno:
Ak vyberiete Nie, Excel uloží súbor vo formáte s povoleným makrom. Ak však kliknete na Áno, Excel automaticky odstráni všetok kód z vášho zošita a uloží súbor ako zošit vo formáte .xlsx. Ak teda máte v zošite makro, musíte ho uložiť vo formáte .xlsm, aby ste toto makro uložili.
čo je makro? Makro je uložená postupnosť akcií alebo program vytvorený vo VBA (Visual Basic for Application). Často kladené otázky ako napísať makro v Exceli? Len.
Tie. ak potrebujeme vykonať rovnaké akcie niekoľkokrát, môžeme si tieto akcie zapamätať a spustiť ich jedným tlačidlom. V tomto článku vás nebudem nútiť učiť sa VBA a ani navrhovať veľa štandardných makier. V skutočnosti môže každý vytvoriť/napísať makro v Exceli. Na tento účel existuje najzaujímavejšia a najneobvyklejšia funkcia programu Excel - Makrorekordér(zaznamenávanie vašich akcií vo forme kódu). Tie. môžete zaznamenať svoje akcie, napríklad na video, a preložiť ich do kódu (sekvencie).
Stručne povedané, ak vykonávate rovnaké akcie každý deň, stojí za to zistiť, ako tento proces automatizovať. Čítajte ďalej a dozviete sa, ako napísať makro v Exceli?
1.Povoliť makrá
Menu (okrúhle tlačidlo vľavo hore) - Možnosti programu Excel— Centrum dôveryhodnosti — Nastavenia centra dôveryhodnosti — Nastavenia makra. Začiarknite políčko „Povoliť všetky makrá“.
Alebo urobte to isté na karte Vývojár
2. Aktivujte ponuku vývojára pre rýchla práca s makrami
Menu (okrúhle tlačidlo vľavo hore) - Možnosti programu Excel - Všeobecné - Zobraziť kartu Vývojár.
Aby sa vytvorené makro uložilo do zošita, musíte súbor uložiť do .xlsm alebo .xlsb. Kliknite na položku Uložiť ako – zošit programu Excel s povoleným makrom alebo binárny zošit.
Ako teda napísať makro v Exceli?
Je to jednoduché. Poďme do Excelu. V spodnej časti stránky pod štítkami hárkov sa nachádza tlačidlo „Nahrať makro“.
Kliknite na tlačidlo a otvorí sa okno „Nahrať makro“. Makro pomenujeme ako chceme v poli Názov makra:. Nastavíme klávesovú kombináciu, ktorú budeme neskôr používať na jej volanie (voliteľné).
Kliknite na tlačidlo OK. Makrorekordér spustil nahrávanie.
Všetky. Teraz vykonáme akcie, ktoré si musíme zapamätať ako makro. Napríklad musíme vymazať jeden riadok a zafarbiť bunku A1 žltou.
Počas týchto manipulácií sa namiesto tlačidla „Nahrať makro“ pod listami rozsvieti štvorec, kliknutím na ktorý zastavíte vykonávanie makra.
Ako spustiť to, čo sa stalo? Kliknite na tlačidlo, zobrazí sa okno Vybrať makro, vyberte požadované makro a potom kliknite na tlačidlo Spustiť.
Ako môžem vidieť, čo sa stalo? Kliknite na tlačidlo. Vyberte požadované makro a kliknite na Upraviť. Otvorí sa okno Záznam makra (okno VBA).
Kód makra by mal vyzerať asi takto.
Sub Example1() " Example1 Macro Rows("2:2").Vyberte Selection.Delete Shift:=xlUp Range("A1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub‘ je symbol komentára, t.j. tento riadok nie je zahrnutý v kóde. Makro musí mať názov (Sub Example1()) a koncovku (End Sub).
Môžete si prečítať, ako vytvoriť tlačidlo pre makro.
Takže si môžete zaznamenať akúkoľvek svoju činnosť, aby ste ju mohli opakovať toľkokrát, koľkokrát chcete!
Môžete si prečítať, ako uložiť makro pre všetky knihy v počítači.
Ak to chcete urobiť, použite Osobné.
Môžete si prečítať, ako spustiť makro podľa podmienky
Veľa šťastia, som si istý, že to bude veľmi užitočné!
Zdieľajte náš článok na svojich sociálnych sieťach:Pri tabuľkách alebo iných objektoch balíka Office často nastávajú situácie, keď potrebujete vykonať rovnaké akcie. To zaberá nielen čas navyše, ale je to aj veľmi nepríjemné. Našťastie existujú makrá na vyriešenie tohto problému.
Prostredie VBA je určené na vytváranie makier, ale nemusíte byť programátor alebo študovať Visual Basic for Application, aby ste sa ich naučili písať. Na tento účel existujú špeciálne vyvinuté nástroje, ktoré vytvárajú kód VBA na váš príkaz bez toho, aby od vás vyžadovali ďalšie znalosti. Zvládnuť tento jazyk však nie je také ťažké.
Makrá sa vytvárajú v aplikáciách pomocou nástrojov na nahrávanie. V tomto prípade je určitej kombinácii kláves priradená požadovaná postupnosť akcií. Otvorte aplikáciu balíka Office. Vyberte fragment, ktorý chcete formátovať.
Vyberte položku ponuky „Nástroje“ -> „Makro“ -> „Spustiť nahrávanie“ (v Office 2007 – „Zobraziť“ -> „Makrá“ -> „Nahrať makro“). V zobrazenom okne „Zaznamenať makro“ nastavte názov nového makra; štandardne je to „Makro1“, ale je lepšie ho pomenovať, najmä ak bude makier niekoľko. Maximálna veľkosť polia názvu – 255 znakov, nie sú povolené znaky bodky a medzery.
Rozhodnite sa o výbere tlačidla alebo kombinácie klávesov, ktoré bude vaše makro používať v budúcnosti. Pokúste sa vybrať najvhodnejšiu možnosť, najmä ak je určená na časté používanie. V poli „Priradiť makro“ vyberte príslušnú položku: „tlačidlo“ alebo „klávesy“.
Ak vyberiete "tlačidlo", otvorí sa okno "Rýchle nastavenia". Keď vyberiete „klávesy“, stačí zadať kombináciu na klávesnici. Skontrolujte "Aktuálne kombinácie", aby ste sa vyhli opakovaniu. Kliknite na "Priradiť".
Makro vytvorené vo Worde a PowerPointe bude v budúcnosti platné pre všetky dokumenty. Aby bolo excelové makro dostupné pre všetky dokumenty, uložte ho do súboru personal.xls, ktorý sa spustí automaticky pri otvorení aplikácie. Vykonajte príkaz „Window“ -> „Display“ a v zobrazenom okne vyberte riadok s názvom súboru personal.xls.
Do poľa Popis zadajte popis makra. Kliknite na tlačidlo OK a vrátite sa späť do dokumentu, ale teraz môžete na kurzore myši vidieť ikonu nahrávania. Formátujte text podľa poradia akcií, ktoré chcete automatizovať. Buďte veľmi opatrní a nerobte zbytočné akcie, pretože makro ich všetky zaznamená, čo ovplyvní čas jeho vykonania v budúcnosti.
Vykonajte príkaz „Nástroje“ -> „Makro“ -> „Zastaviť nahrávanie“. Vytvorili ste objekt VBA bez toho, aby ste sami napísali jediný riadok kódu. Ak však stále potrebujete vykonať zmeny manuálne, zadajte objekt cez sekciu „Makrá“, príkaz „Zmeniť“ alebo pomocou kombinácie klávesov Alt+F8.
Málokto vie, že prvá verzia obľúbeného produktu Microsoft Excel sa objavila v roku 1985. Odvtedy prešiel niekoľkými úpravami a je žiadaný medzi miliónmi používateľov po celom svete. Mnoho ľudí zároveň pracuje len s malým zlomkom možností tohto tabuľkového procesora a ani si neuvedomujú, ako by im schopnosť programovania v Exceli mohla uľahčiť život.
Čo je VBA
Programovanie v Exceli sa vykonáva pomocou programovacieho jazyka Visual Basic for Application, ktorý bol pôvodne zabudovaný do najznámejšieho tabuľkového procesora od spoločnosti Microsoft.
Odborníci pripisujú jej prednostiam relatívnu ľahkosť učenia. Ako ukazuje prax, aj používatelia, ktorí nemajú profesionálne programovacie zručnosti, môžu zvládnuť základy VBA. Medzi funkcie VBA patrí vykonávanie skriptov v prostredí kancelárskych aplikácií.
Nevýhodou programu sú problémy spojené s kompatibilitou rôznych verzií. Sú spôsobené tým, že pristupuje programový kód VBA funkčnosť, ktoré sú prítomné v Nová verzia produkt, ale v starom chýba. Medzi nevýhody patrí aj príliš vysoká otvorenosť kódu pre zmeny zo strany cudzinca. Napriek tomu Microsoft Office, ako aj IBM Lotus Symphony umožňujú používateľovi zašifrovať počiatočný kód a nastaviť heslo na jeho zobrazenie.
Objekty, kolekcie, vlastnosti a metódy
Toto sú pojmy, ktoré musia pochopiť tí, ktorí sa chystajú pracovať v prostredí VBA. Najprv musíte pochopiť, čo je objekt. V Exceli sú tieto funkcie hárok, zošit, bunka a rozsah. Tieto objekty majú špeciálnu hierarchiu, t.j. poslúchať jeden druhého.
Hlavná je aplikácia, ktorá zodpovedá program Excel. Potom prídu Pracovné zošity, Pracovné hárky a Rozsah. Napríklad, ak chcete získať prístup k bunke A1 na konkrétnom hárku, musíte zadať cestu, ktorá zohľadňuje hierarchiu.
Pokiaľ ide o pojem „kolekcia“, ide o skupinu objektov rovnakej triedy, ktorá má v zázname tvar ChartObjects. Jej jednotlivými prvkami sú aj predmety.
Ďalším pojmom sú vlastnosti. Sú nevyhnutnou charakteristikou každého objektu. Napríklad pre rozsah je to Hodnota alebo Vzorec.
Metódy sú príkazy, ktoré naznačujú, čo je potrebné urobiť. Pri písaní kódu vo VBA musia byť oddelené od objektu bodkou. Napríklad, ako sa ukáže neskôr, pri programovaní v Exceli sa veľmi často používa príkaz Cells(1,1).Select. To znamená, že musíte vybrať bunku so súradnicami
Spolu s ním sa často používa Selection.ClearContents. Vykonanie znamená vymazanie obsahu vybranej bunky.
Ako začať
Potom musíte prejsť do aplikácie VB, pre ktorú stačí použiť kombináciu klávesov „Alt“ a „F11“. ďalej:
- v paneli s ponukami v hornej časti okna kliknite na ikonu vedľa ikony Excel;
- vyberte príkaz Mudule;
- uložiť kliknutím na ikonu s obrázkom;
- píšu, povedzme, návrh kódexu.
Vyzerá to takto:
Podprogram ()
„Náš kód
Upozorňujeme, že riadok „Náš kód“ bude zvýraznený inou farbou (zelenou), dôvodom je apostrof umiestnený na začiatku riadku, ktorý označuje, že nasleduje komentár.
Teraz môžete napísať ľubovoľný kód a vytvoriť si nový nástroj VBA Excel(pozri príklady programov nižšie). Samozrejme, pre tých, ktorí sú oboznámení so základmi Visual Basicu, to bude oveľa jednoduchšie. Avšak aj tí, ktorí ich nemajú, si na to môžu dostatočne rýchlo zvyknúť, ak chcú.
Makrá v Exceli
Tento názov skrýva programy napísané vo Visual Basic for Application. Programovanie v Exceli je teda vytváranie makier s požadovaný kód. Vďaka tejto funkcii sa tabuľkový procesor Microsoft vyvíja sám, prispôsobuje sa požiadavkám konkrétneho používateľa. Keď ste prišli na to, ako vytvoriť moduly na písanie makier, môžete začať uvažovať konkrétne príklady Programy VBA Excel. Najlepšie je začať s najzákladnejšími kódmi.
Príklad 1
Úloha: napíšte program, ktorý skopíruje hodnotu obsahu jednej bunky a následne ju zapíše do druhej.
Pre to:
- otvorte kartu „Zobraziť“;
- prejdite na ikonu „Makrá“;
- kliknite na „Zaznamenať makro“;
- vyplňte formulár, ktorý sa otvorí.
Pre jednoduchosť ponechajte v poli „Názov makra“ „Macro1“ a do poľa „Klávesová skratka“ vložte napríklad hh (to znamená, že program spustíte rýchlym príkazom „Ctrl+h“). Stlačte Enter.
Teraz, keď sa už makro nahrávanie začalo, obsah bunky sa skopíruje do inej. Vráťte sa na pôvodnú ikonu. Kliknite na „Zaznamenať makro“. Táto akcia znamená koniec programu.
- vráťte sa na riadok „Makrá“;
- zo zoznamu vyberte „Makro 1“;
- kliknite na „Spustiť“ (rovnaká akcia sa spustí spustením kombinácie kláves „Ctrl+hh“).
Výsledkom je akcia, ktorá bola vykonaná počas zaznamenávania makra.
Má zmysel vidieť, ako kód vyzerá. Ak to chcete urobiť, vráťte sa do riadku „Makrá“ a kliknite na „Zmeniť“ alebo „Prihlásiť sa“. Vďaka tomu sa ocitnú v prostredí VBA. Samotný kód makra sa v skutočnosti nachádza medzi riadkami Sub Macro1() a End Sub.
Ak sa skopírovanie vykonalo napríklad z bunky A1 do bunky C1, potom jeden z riadkov kódu bude vyzerať ako Range(“C1”). Vyberte. V preklade to vyzerá ako „Range(“C1”).Select, inými slovami, ide do VBA Excel, do bunky C1.
Aktívna časť kódu je doplnená príkazom ActiveSheet.Paste. Znamená to zapísanie obsahu vybranej bunky (v tomto prípade A1) do vybranej bunky C1.
Príklad 2
Slučky VBA vám pomáhajú vytvárať rôzne makrá v Exceli.
Slučky VBA vám pomáhajú vytvárať rôzne makrá. Predpokladajme, že máme funkciu y=x + x 2 + 3x 3 - cos(x). Ak chcete získať jeho graf, musíte vytvoriť makro. Toto je možné vykonať iba pomocou slučiek VBA.
Počiatočné a konečné hodnoty argumentu funkcie sú x1=0 a x2=10. Okrem toho musíte zadať konštantu - hodnotu pre krok zmeny argumentu a počiatočnú hodnotu pre počítadlo.
Všetky príklady makier VBA Excel sú vytvorené pomocou rovnakého postupu, ako je uvedené vyššie. V tomto konkrétnom prípade kód vyzerá takto:
Podprogram ()
krok = 0,1
Urobte Kým x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)
y=x1 + x1^2 + 3*x1^3 - Cos(x1)
Bunky (i, 1). Hodnota = x1 (hodnota x1 sa zapíše do bunky so súradnicami (i,1))
Bunky (i, 2). Hodnota = y (hodnota y sa zapíše do bunky so súradnicami (i,2))
i = i + 1 (počítadlo je účinné);
x1 = x1 + shag (argument sa mení o hodnotu kroku);
End Sub.
Výsledkom spustenia tohto makra v Exceli sú dva stĺpce, z ktorých prvý obsahuje hodnoty pre x a druhý pre y.
Potom sa z nich zostaví graf štandardným spôsobom pre Excel.
Príklad 3
Na implementáciu slučiek vo VBA Excel 2010, rovnako ako v iných verziách, spolu s už daným konštruktom Do While sa používa For.
Zvážte program, ktorý vytvorí stĺpec. V každej z jeho buniek sa zapíšu druhé mocniny príslušného čísla riadku. Použitie konštrukcie For vám umožní napísať ho veľmi stručne, bez použitia počítadla.
Najprv musíte vytvoriť makro, ako je opísané vyššie. Ďalej si zapíšeme samotný kód. Predpokladáme, že nás zaujímajú hodnoty pre 10 buniek. Kód vyzerá takto.
Pre i = 1 až 10 Ďalej
Príkaz je preložený do „ľudského“ jazyka ako „Opakujte od 1 do 10 v krokoch po jednej“.
Ak je úlohou získať stĺpec so štvorcami, napríklad všetkých nepárnych čísel z rozsahu od 1 do 11, potom napíšeme:
Pre i = 1 až 10 krok 1 Ďalej.
Tu je krok krokom. V tomto prípade sa rovná dvom. Štandardne absencia tohto slova v cykle znamená, že krok je jediný.
Získané výsledky sa musia uložiť do očíslovaných buniek (i,1). Potom, vždy, keď sa slučka spustí, s nárastom i o krok, sa číslo riadku automaticky zvýši. Týmto spôsobom bude kód optimalizovaný.
Celkovo bude kód vyzerať takto:
Podprogram ()
Pre i = 1 až 10 Krok 1 (môžete jednoducho napísať Pre i = 1 až 10)
Bunky(i, 1). Hodnota = i ^ 2 (t. j. hodnota štvorca i sa zapíše do bunky (i,1)
Ďalej (v istom zmysle hrá úlohu počítadla a znamená ďalší začiatok slučky)
End Sub.
Ak je všetko vykonané správne, vrátane záznamu a spustenia makra (pozri pokyny vyššie), potom keď ho zavoláte, zakaždým dostanete stĺpec zadanej veľkosti (v tomto prípade pozostávajúci z 10 buniek).
Príklad 4
IN Každodenný život Pomerne často vzniká potreba urobiť jedno alebo druhé rozhodnutie v závislosti od nejakej podmienky. Vo VBA Excel sa bez nich nezaobídete. Príklady programov, kde je ďalší priebeh vykonávania algoritmu vopred zvolený a nie vopred určený, najčastejšie používajú konštrukciu If ...Then (pre zložité prípady) If ...Then ...END If.
Zoberme si konkrétny prípad. Predpokladajme, že potrebujete vytvoriť makro pre Excel, aby bolo v bunke so súradnicami (1,1) napísané nasledovné:
1, ak je argument kladný;
0, ak je argument nulový;
-1, ak je argument záporný.
Začína sa vytváranie takéhoto makra pre Excel štandardným spôsobom pomocou klávesových skratiek Alt a F11. Ďalej je napísaný nasledujúci kód:
Podprogram ()
x= Cells(1, 1).Value (tento príkaz priradí x hodnotu obsahu bunky na súradniciach (1, 1))
Ak x>0, potom bunky(1, 1).Hodnota = 1
Ak x=0, potom bunky(1, 1).Hodnota = 0
Ak x<0 Then Cells(1, 1).Value = -1
End Sub.
Zostáva len spustiť makro a získať požadovanú hodnotu pre argument v Exceli.
Funkcie VBA
Ako ste si už mohli všimnúť, programovanie v najznámejšom tabuľkovom procesore Microsoftu nie je až také náročné. Najmä ak sa naučíte používať funkcie VBA. Celkovo má tento programovací jazyk vytvorený špeciálne na písanie aplikácií v Exceli a Worde asi 160 funkcií. Možno ich rozdeliť do niekoľkých veľkých skupín. toto:
- Matematické funkcie. Ak ich aplikujú na argument, získajú hodnotu kosínusu, prirodzeného logaritmu, celočíselnej časti atď.
- Finančné funkcie. Vďaka ich dostupnosti a programovaniu v Exceli získate efektívne nástroje na účtovné a finančné kalkulácie.
- Funkcie spracovania poľa. Patria sem Array, IsArray; LBound; UBound.
- Funkcie Excel VBA pre reťazec. Ide o pomerne veľkú skupinu. Patria sem napríklad funkcie Space na vytvorenie reťazca s počtom medzier rovným celočíselnému argumentu alebo Asc na konverziu znakov na kód ANSI. Všetky sú široko používané a umožňujú vám pracovať s riadkami v Exceli a vytvárať aplikácie, ktoré výrazne uľahčujú prácu s týmito tabuľkami.
- Funkcie konverzie dátových typov. Napríklad CVar vráti hodnotu argumentu Expression tak, že ho skonvertuje na typ údajov Variant.
- Funkcie pre prácu s dátumami. Výrazne rozširujú tie štandardné.Funkcia WeekdayName teda vracia názov (úplný alebo čiastočný) dňa v týždni jeho číslom. Ešte užitočnejší je časovač. Udáva počet sekúnd, ktoré uplynuli od polnoci do konkrétneho bodu dňa.
- Funkcie na prevod číselného argumentu do rôznych číselných sústav. Napríklad Oct vypíše čísla v osmičkovej sústave.
- Funkcie formátovania. Najdôležitejším z nich je Formát. Vráti hodnotu Variant s výrazom naformátovaným podľa pokynov uvedených v deklarácii formátu.
- atď.
Štúdium vlastností týchto funkcií a ich aplikácia výrazne rozšíri rozsah Excelu.
Príklad 5
Skúsme prejsť k riešeniu zložitejších problémov. Napríklad:
Je uvedený papierový dokument, ktorý uvádza skutočnú úroveň nákladov podniku. Požadovaný:
- vytvoriť jeho šablónovú časť pomocou tabuľky Excel;
- vytvorte program VBA, ktorý si vyžiada počiatočné údaje na vyplnenie, vykoná potrebné výpočty a vyplní nimi príslušné bunky šablóny.
Zoberme si jednu z možností riešenia.
Vytvorte šablónu
Všetky akcie sa vykonávajú na štandardnom hárku v Exceli. Voľné bunky sú vyhradené na zadávanie údajov o názve spotrebiteľskej spoločnosti, výške nákladov, ich výške a obrate. Keďže počet spoločností (spoločností), pre ktoré sa správa zostavuje, nie je pevne stanovený, bunky na zadávanie hodnôt na základe výsledkov a mena špecialistu nie sú vopred rezervované. Pracovný list dostane nový názov. Napríklad „správy“.
Premenné
Ak chcete napísať program na automatické vyplnenie šablóny, musíte vybrať notácie. Tieto budú použité pre premenné:
- NN - číslo aktuálneho riadku tabuľky;
- TP a TF - plánovaný a skutočný obrat obchodu;
- ŠF a SP - skutočná a plánovaná výška nákladov;
- IP a IF - plánovaná a skutočná úroveň nákladov.
Na označenie celkového súčtu pre tento stĺpec použite rovnaké písmená, ale s „predponou“ Itog. Napríklad ItogTP - odkazuje na stĺpec tabuľky s názvom „plánovaný obrat“.
Riešenie problému pomocou programovania vo VBA
Pomocou zavedených zápisov získame vzorce pre odchýlky. Ak potrebujete vypočítať v %, máme (F - P) / P * 100 a celkovo - (F - P).
Výsledky týchto výpočtov je najlepšie zadať priamo do príslušných buniek v excelovskej tabuľke.
Pre skutočné a predpovedané súčty sa získajú pomocou vzorcov ItogP=ItogP + P a ItogF=ItogF+ F.
Pre odchýlky použite = (ItogF - ItogP) / ItogP * 100, ak sa výpočet vykonáva ako percento av prípade celkovej hodnoty - (ItogF - ItogP).
Výsledky sa opäť ihneď zapisujú do príslušných buniek, takže nie je potrebné ich priraďovať k premenným.
Pred spustením vytvoreného programu je potrebné uložiť zošit napríklad pod názvom „Report1.xls“.
Tlačidlo „Vytvoriť tabuľku prehľadov“ stačí stlačiť raz po zadaní informácií v hlavičke. Existujú aj ďalšie pravidlá, ktoré by ste mali poznať. Predovšetkým je potrebné kliknúť na tlačidlo „Pridať riadok“ vždy po zadaní hodnôt pre každý typ aktivity do tabuľky. Po zadaní všetkých údajov musíte kliknúť na tlačidlo „Dokončiť“ a potom sa prepnúť do okna programu Excel.
Teraz viete, ako vyriešiť problémy programu Excel pomocou makier. Schopnosť používať vba excel (pozri príklady programov vyššie) môže byť potrebná aj na prácu v momentálne najpopulárnejšom textovom editore Word. Tlačidlá ponuky môžete vytvárať najmä písaním, ako je uvedené na úplnom začiatku článku, alebo písaním kódu, vďaka čomu je možné vykonať veľa operácií s textom stlačením funkčných kláves alebo cez kartu „Zobraziť“ a ikonu „Makrá“.
