Sada makier pre excel. VBA a makrá

Práca s knihami

Makro 1. Vytvorte nový zošit od začiatku
Makro 2. Uložte zošit pri zmene konkrétnej bunky / rozsahu
Makro 3. Pred zatvorením zošit uložte
Makro 4. Ochrana pracovného hárka v zošite pred zatvorením
Makro 5. Odstránenie ochrany z hárka pri otváraní súboru programu Excel
Makro 6. Otvorte knihu na požadovanom hárku
Makro 7. Otvorenie konkrétneho užívateľom definovaného zošita
Makro 8. Zistite, či je kniha otvorená
Makro 9. Zistite, či kniha v priečinku existuje
Makro 10. Aktualizujte všetky odkazy v otvorených zošitoch
Makro 11. Zatvorte všetky knihy naraz
Makro 12. Otvorte všetky zošity v priečinku
Makro 13. Vytlačte všetky knihy v priečinku
Makro 14. Nedovoľujeme zatvoriť knihu, kým nezaplní bunku
Makro 15. Vytvorte záložnú kópiu aktuálneho zošita s dnešným dátumom

Práca s listami

Makro 16. Pridajte nový pracovný hárok a zadajte názov
Makro 17. Odstráňte všetky hárky okrem aktívneho
Makro 18. Skryť všetko okrem aktívneho pracovného hárka
Makro 19. Zobrazte všetky listy knihy
Makro 20. Pohyblivé pracovné listy
Makro 21. Zoraďte listy podľa názvu
Makro 22. Skupina hárkov podľa farby štítka
Makro 23. Skopírujte list do nového zošita
Makro 24. Vytvorte nový zošit pre každý list
Makro 25. Tlačte listy
Makro 26. Chráňte všetky listy
Makro 27. Odstráňte ochranu zo všetkých listov
Makro 28. Vytvorte obsah
Makro 29. Dvojitým kliknutím zmeníte mierku obrázka pracovného hárka
Makro 30. Vyberte stĺpec aktívneho riadka

Zvýraznenie a úprava rozsahov

Makro 31. Výber a formátovanie rozsahu
Makro 32. Vytvorte a vyberte pomenované rozsahy
Makro 33. Hľadajte v rade buniek
Makro 34. Výber a formátovanie rozsahov
Makro 35. Vkladanie prázdnych riadkov do rozsahu
Makro 36. Zobraziť všetky skryté riadky a stĺpce
Makro 37. Odstráňte prázdne riadky
Makro 38. Odstraňujú sa prázdne stĺpce
Makro 39. Vyberte a naformátujte všetky vzorce v zošite
Makro 40. Nájdite a vyberte prvý prázdny riadok alebo stĺpec
Makro 41. Naneste dodatočnú farbu výplne
Makro 42. Zoraďte rozsahy dvojitým kliknutím
Makro 43. Obmedzenie rozsahu posúvania v konkrétnej oblasti
Makro 44. Automaticky nastaví tlačiteľnú oblasť listu

Práca s údajmi

Makro 45. Skopírujte a prilepte rozsah
Makro 46. Premeňte všetky vzorce v rozsahu na hodnoty
Makro 47. Konvertujte textové hodnoty na číselné
Makro 48. Previesť pomlčky na mínus
Makro 49. Odstráňte nadbytočné medzery zo všetkých buniek v rozsahu
Makro 50. Odrezať zľava 5 znakov v každej bunke rozsahu
Makro 51. Pridajte chýbajúce nuly do bunky
Makro 52. Prázdne bunky nahraďte nulou
Makro 53. Pridajte text na začiatok alebo na koniec bunky
Makro 54. Vytvorte makro transformácie údajov
Makro 55. Vymazanie údajov (netlačiteľné znaky)
Makro 56. Vyberte duplikáty v rozsahu údajov
Makro 57. Skryť duplicitné riadky
Makro 58. Selektívne skryte šípky automatického filtra
Makro 59. Skopírujte filtrované riadky do nového zošita
Makro 60. Vytvorte nový hárok pre každú položku v automatickom filtri
Makro 61. V stavovom riadku zobrazte filtrované stĺpce

Práca s kontingenčnými tabuľkami

Makro 62: Vytvorte spätnú kompatibilitu kontingenčnej tabuľky
Makro 63. Obnovte všetky kontingenčné tabuľky v zošite
Makro 64. Vytvorte „inventár“ všetkých kontingenčných tabuliek knihy
Makro 65. Vytvorte všetky kontingenčné tabuľky pomocou rovnakej vyrovnávacej pamäte údajov
Makro 66. Skryť všetky medzisúčty v kontingenčnej tabuľke
Makro 67. Zmeňte názvy údajov všetkých polí súhrnu
Makro 68. Vynútené sčítanie všetkých údajov v súhrne
Makro 69. Na všetky údajové položky použite formát čísla
Makro 70. Zoraďte kontingenčné polia podľa abecedy
Makro 71. Na položky údajov použite vlastné triedenie
Makro 72: Na kontingenčnú tabuľku sme vložili ochranu
Makro 73: Aplikujte obmedzenia kontingenčného poľa
Makro 74. Automatické vymazanie hárkov podrobných údajov
Makro 75: Vytlačte kontingenčnú tabuľku pre každú položku filtra
Makro 76. Vytvorte nový súbor pre každý prvok filtra
Makro 77. Pripravte rozsah údajov pre kontingenčnú tabuľku

Práca s tabuľkami a grafmi

Makro 78: Zmeňte veľkosť grafov v pracovnom hárku
Makro 79. Spojte graf s konkrétnym rozsahom
Makro 80. Vytvorte množinu roztrúsených diagramov
Makro 81. Vytlačte všetky grafy na pracovný hárok
Makro 82. Označte najlepšie a najhoršie hodnoty na
Makro 83. Rovnaké farby pre hodnoty v rôznych grafoch
Makro 84. Priraďte farbu grafov k farbe rozsahov

Odosielanie e -mailov z Excelu

Makro 85: Odoslanie aktívneho zošita poštou (príloha)
Makro 86: Odoslanie rozsahu hodnôt ako prílohy
Makro 87. Odošlite jeden list ako prílohu
Makro 88. Pošlite e -mail s odkazom na naše súbory
Makro 89: Odosielanie e -mailov s pridaním adries do nášho zoznamu kontaktov
Makro 90. Uložte všetky prílohy do samostatného priečinka
Makro 91. Ukladanie konkrétnych príloh do priečinka

Interoperabilita s inými aplikáciami balíka Office

Makro 92. Spustite žiadosť o prístup z Excelu



Makro 96. Kompaktná databáza programu Access z Excelu
Makro 97: Odosielanie údajov programu Excel do dokumentu programu Word
Makro 98. Zlúčenie s dokumentom programu Word
Makro 99. Odosielanie údajov programu Excel do prezentácie programu PowerPoint
Makro 100. Odosielanie grafov programu Excel na prezentáciu v programe PowerPoint
Makro 101. Premeňte zošit na prezentáciu v programe PowerPoint

Interoperabilita s inými aplikáciami balíka Office
naučte sa pracovať s Wordom, Accessom a PowerPointom
Makro 92. Spustite žiadosť o prístup z Excelu
Makro 93. Spustite Accessové makro z Excelu
Makro 94. Otvorte správu o prístupe z Excelu
Makro 95. Otvorenie prístupového formulára z Excelu

Začal som vytvárať túto stránku ako zápisník praktických funkcií Excelu. Kedykoľvek môžete obnoviť svoje znalosti o konkrétnej funkcii programu tak, že prejdete na web, a navyše môžete poslať odkaz na článok priateľom alebo kolegom, ktorí požiadajú o pomoc pomocou Excelu. Návštevnosť tejto stránky sa rýchlo rozrástla a ako vidím, zaznamenané informácie sú užitočné nielen pre mňa a niekoľko ďalších ľudí, ale aj pre mnohých ľudí, ktorých nepoznám, a sú veľmi hodnotené vyhľadávacími nástrojmi. Ak som si istý schopnosťami Excelu, nemôžem sa nazývať špecialistom na VBA na vysokej úrovni. Preto by som rád vytvoril skvelý pomocný článok, kde budem písať užitočné makrá, ktoré často používam. Som si istý, že takáto zbierka funkcií VBA bude užitočná nielen pre mňa.

Stránka už obsahuje niekoľko článkov o VBA vrátane, ale myslím si, že bude užitočné uchovávať gadgety kódu, ktoré sa často používajú, na jednom mieste.

Jednoduchou funkciou vypnite zobrazenie akcií makra na obrazovke. To zlepší nielen vizuálny výkon, ale aj výrazne urýchli vykonanie makra.

Sub Makros1 () Application.ScreenUpdating = 0 "Váš kód Application.ScreenUpdating = 1 End Sub

Nezabudnite zahrnúť funkciu na koniec makra

Ako odstrániť výber kópie po spustení makra?

Ak ste vo svojom makre všetci takí, určte rozsah na kopírovanie a vkladanie. Nezabudnite vypnúť výber na konci makra

Sub Makros1 () "Váš kód Application.CutCopyMode = 0 End Sub

Užitočné makrá. Ako nájsť posledný riadok alebo stĺpec v rozsahu

Táto konštrukcia vám pomôže ľahko nájsť číslo posledného riadka alebo stĺpca vyplneného rozsahu. Je obzvlášť vhodné používať v slučkách, nemusíte zadávať 1 000 riadkov slučky s okrajom, Excel sa ocitne tam, kde koniec rozsahu používa nasledujúcu konštrukciu:

Sub makros1 () Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells (Rows.Count, 1). End (xlUp) .Row "Finds the last row number of the full range nLastCol = Cells (1, Columns.Count). End (xlToLeft) .Column "Zistí číslo posledného vyplneného stĺpca v rozsahu End Sub

Navyše premennú okamžite vyhlásim za Long (dĺžka 2 147 483 647), aby som sa nedostal do situácie, kde populárne Integer nemusí stačiť (32 767) na veľké tabuľky.

Malo by sa tiež poznamenať, že vyhľadávanie prebieha v prvom stĺpci alebo prvom riadku, pre správnu definíciu vyberte požadované číslo.

Pre slučku a kontrolu stavu v slučke

Ak nájdete posledné čísla riadkov a stĺpcov rozsahov, môžete ich použiť v slučkách. Napríklad musíte prejsť prvým stĺpcom a spočítať počet prázdnych buniek.

Sub makros1 () Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Variabilné množstvo Dim i As Long" Cyklus premennej mLastRow = bunky (riadky. Počet, 1). Koniec (xlUp). Riadok Kol = 0 pre i = 1 To mLastRow If Cells (i, 1) .Value = "" Then Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Tu sa Msgbox používa aj s touto funkciou, údaje môžete zobrazovať v samostatnom okne. V mojom prípade to bude vyzerať takto:

Počítanie času vykonania makra

Sub makros1 () TimeStart = Now TimeFinish = Now MsgBox "Time:" & Format (TimeFinish - TimeStart, "h: mm: ss") End Sub

MsgBox produkuje tento výstup:

Je veľmi výhodné sledovať dobu vykonávania makier pre následnú optimalizáciu.

Toto je niekoľko užitočných makier, ktoré používam veľmi často. Tento zoznam budem postupne rozširovať. Celá efektívna automatizácia!

Podeľte sa o náš článok na svojich sociálnych sieťach:

Príručka obsahuje makrá na nasledujúce témy:
Spustenie makra s vyhľadaním bunky.
Pri otváraní zošita spustite makro.
Spustenie makra pri písaní do bunky „2“.
Spustenie makra po stlačení klávesu „Enter“.
Na panel pridajte vlastnú kartu „Doplnky“ (formát bunky).
Práca so súbormi (t. J. Výmena údajov s TXT, RTF, XLS atď.).
Kontrola existencie súboru na zadanej ceste.
Vyhľadajte požadovaný súbor.
Automatizácia mazania súborov.
Voľný text na stavovom riadku.
Obnovenie stavového riadka.
Posúvací riadok v stavovom riadku.

Rýchlo zmeňte názov okna.
Zmena názvu okna (skrytie názvu súboru).
Vráti sa k pôvodnému nadpisu.
Čo je momentálne otvorené.
Práca s textovými súbormi.
Písanie a čítanie textového súboru.
Spracovanie viacerých textových súborov.
Určenie konca riadka textového súboru.
Kopírovanie z textového súboru do programu Excel.

Skopírujte obsah do textového súboru.
Export údajov do formátu txt.
Export údajov do html.
Import údajov, ktoré vyžadujú viac ako 256 stĺpcov.
Vytvárajte záložné kópie cenných súborov.
Počítanie počtu otvorení súborov.
Výstup cesty k súboru do aktívnej bunky.
Kopírovanie obsahu súboru RTF do programu Excel.
Kopírovanie údajov z uzavretého zošita.
Extrahovanie údajov z uzavretého súboru.
Vyhľadajte slovo v súboroch.
Vytvorte textový súbor a zadajte doň text.
Vytvorte textový súbor a zadajte text (detekcia konca súboru).
Vytvárajte dokumenty programu Word z tabuľky programu Excel.
Príkazy na vytváranie a odstraňovanie adresárov.
Získava sa aktuálny adresár.
Zmena adresára.

Zobraziť všetky súbory v adresári.
Zobraziť všetky súbory v adresári.
Pracovný priestor programu Microsoft Excel.
Pracovný zošit.
Počet názvov zošitov.
Ochrana zošita.
Zákaz tlače knihy.
Otvorenie knihy (alebo textových súborov).
Otvorenie knihy a pridanie textu do bunky A1.
Koľko kníh je otvorených.
Zatváranie všetkých kníh.
Zatvorenie zošita iba vtedy, ak je splnená podmienka.
Uloží zošit s názvom, ktorý predstavuje aktuálny dátum.
Či bol zošit uložený.
Vytvorte knihu z jedného listu.
Vytvorte knihu.
Odstránenie nepotrebných mien.
Rýchla reprodukcia zošita.
Triediť listy.
Nájdenie maximálnej hodnoty na všetkých listoch knihy.
Pracovný list.
Kontrola, či je pracovný list chránený.
Zoznam triedených listov.
Vytvorte nový list.
Vytvorte nový list.
Odstránenie listov v závislosti od dátumu.
Kopírovanie listu v knihe.
Kopírovanie listu do nového zošita (vytvorený).
Presuňte list v knihe.
Presuňte viac listov do nového zošita.
Nahradiť existujúci súbor.
„Prevrátenie“ knihy.
Vložte hlavičku a pätu s názvom knihy, listu a aktuálnym dátumom.
Existuje list?
Existuje list?
Zobrazuje počet hárkov v aktívnom zošite.
Zobrazuje počet hárkov v aktívnom zošite ako hypertextové odkazy.
Zobrazuje názvy aktívnych hárkov jeden po druhom.
Zobrazí názov a čísla hárkov aktuálnej knihy.
Urobte list neviditeľným.
Koľko strán je na všetkých listoch?
Bunka a rozsah (stĺpce a riadky).
Skopírujte riadky na iný list.
Skopírujte stĺpce do iného listu.
Spočíta počet buniek, ktoré obsahujú zadané hodnoty.
Spočíta počet buniek v rozsahu, ktoré obsahujú zadané hodnoty.
Počíta počet viditeľných buniek v rozsahu.
Určuje počet buniek v rozsahu a súčet ich hodnôt.
Počítanie počtu buniek.
Automatický prepočet údajov tabuľky pri zmene jej hodnôt.
Zadávanie údajov do buniek.
Zadávanie údajov pomocou vzorcov.
Sériové zadávanie údajov.
Zadávanie textových údajov do buniek.
Výstup do buniek názvu knihy, listu a počtu listov.
Odstraňujú sa prázdne riadky.
Odstraňujú sa prázdne riadky.
Odstraňujú sa prázdne riadky.
Podmienečné odstránenie riadka.
Odstránenie skrytých čiar.
Odstráňte použité skryté čiary alebo čiary s nulovou výškou.
Odstránenie duplikátov pomocou masky.

Vyberie rozsah nad aktuálnou bunkou.
Vyberte bunku a zadajte tam číslo.
Zvýraznenie záporných hodnôt.
Prideľovanie rozsahu a používanie absolútnych adries.

Výber buniek podľa medzier.
Výber z viacerých rozsahov.
Pohyb bunkami.
Nájde najbližšiu prázdnu bunku v stĺpci.
Nájdenie maximálnej hodnoty.
Vyhľadajte a nahraďte podľa vzoru.
Vyhľadajte hodnotu a výsledok zobrazte v samostatnom okne.

Hľadajte so zvýraznením nájdených údajov.
Hľadajte podľa podmienok v rozsahu.
Nájdite poslednú prázdnu bunku v rozsahu.
Nájdite poslednú prázdnu bunku v stĺpci.
Nájde poslednú prázdnu bunku v reťazci.
Nájde modrú bunku v rozsahu.
Nájde zápornú hodnotu v rozsahu a zvýrazní sa modrou farbou.
Zistí existenciu hodnoty v stĺpci.
Vyhľadajte zhody v rozsahu.
Vyhľadajte bunku v rozsahu.
Vyhľadajte bunku v rozsahu.
Nájdenie približnej hodnoty v rozsahu.
Nájde začiatok a koniec rozsahu, ktorý obsahuje údaje.
Vyhľadajte začiatok údajov.
Automatické nahradenie hodnôt.
Rýchle plnenie rozsahu (pole).
Výplň v intervaloch (pole).
Vyplnenie určeného rozsahu (pole).
Plnenie rozsahu (pole).
Výpočet súčtu prvých hodnôt rozsahu.
Umiestnenie v komore elektronických hodín.
„Alarm“.
Dekorácia hornej a dolnej hranice rozsahu.
Aktívna adresa bunky.
Súradnice aktívnej bunky.
Vzorec aktívnej bunky.
Získajte vzorec z bunky.
Dátový typ bunky.
Zobrazí adresu na konci rozsahu.
Získavanie informácií o vybranom rozsahu.
Vezmite slovo s 13 znakmi do bunky.
Vytvorenie meniteľného zoznamu (tabuľky).
Kontrola prázdnej hodnoty.
Priesečník buniek.
Vynásobte vybraný rozsah číslom.
Súčasné násobenie všetkých údajov v rozsahu.
Delenie rozsahu o.
Štvorčeky každej bunky v rozsahu.
Sumarizujte údaje iba o viditeľných bunkách.
Súčet buniek s číselnými hodnotami.
Pri súčte je kurzor v rozsahu.

Časové rozlíšenie úroku v závislosti od sumy.
Časové rozlíšenie úroku v závislosti od sumy.
Súhrnný príklad výpočtu provízie.
Pohyb na diaľku.
Odsadenie od vybratej bunky.
Prejdite cez bunky v stĺpci.
Vytvorte výplň pre rozsah.
Výber parametra bunky.
Rozdelenie rozsahu.
Kombinácia údajov o rozsahu.
Kombinácia údajov o rozsahu.
Zistite maximálny stĺpec alebo riadok.
Obmedzenie možných hodnôt rozsahu.
Testovanie rýchlosti rozsahov čítania a zápisu.
Pri výbere bunky otvorte MsgBox.
Skrývanie radu.
Skrytie viacerých riadkov.
Skrytie stĺpca.
Skrytie viacerých stĺpcov.
Skryť riadok podľa názvu bunky.
Skrytie viacerých riadkov podľa adries buniek.
Skryť stĺpec podľa názvu bunky.
Skrytie viacerých stĺpcov podľa adries buniek.
Blikanie buniek.
Práca s poznámkami.
Zobrazí všetky poznámky v pracovnom hárku.
Funkcia extrakcie komentárov.
Zoznam poznámok chránených listov.

Zoznam poznámok v samostatnom zozname.
Zoznam poznámok v samostatnom zozname.

Počítanie počtu poznámok.
Počítanie poznámok.
Vyberte bunky s poznámkami.
Zobrazí všetky poznámky.
Zmeňte farbu poznámok.
Pridávanie poznámok.
Podmienené pridávanie komentárov k rozsahu.
Prenos komentára do a z bunky.

Prenos hodnôt z bunky do komentára.
Vlastné karty na páse s nástrojmi.
Pridanie panela s nástrojmi.
Pridanie tlačidla na panel s nástrojmi.
Panel s jedným tlačidlom.
Panel s dvoma tlačidlami.
Vytvorte panel vpravo.

Vytvorenie vlastnej ponuky (možnosť 1).
Vytvorenie vlastnej ponuky (možnosť 2).
Vytvorenie vlastnej ponuky (možnosť 3).
Vytvorenie vlastnej ponuky (možnosť 4).
Vytvorenie vlastnej ponuky (možnosť 5).
Vytvorenie vlastnej ponuky (možnosť 6).
Vytvorenie zoznamu položiek v hlavnom menu Excel.
Vytvorenie zoznamu položiek kontextového menu.
Zobrazí panel s nástrojmi za konkrétnych podmienok.
Skrytie a zobrazenie panelov s nástrojmi.
Vytvoriť popis pre moje tlačidlá.
Vytvorí ponuku na základe údajov z pracovného hárka.
Vytvorenie kontextového menu.
Uzamknutie kontextovej ponuky.
Pridanie príkazu do ponuky Nástroje.
Pridanie príkazu do ponuky Zobraziť.
Vytvorenie panelu so zoznamom.
Karikatúra s asistentom v hlavnej úlohe.
Doplnenie pomocníka o text, názov, tlačidlo a ikonu.
Nové možnosti asistenta.
Pomocou asistenta vyberte farbu výplne.
DIALÓGOVÉ OKNÁ.
Funkcia INPUTBOX (prostredníctvom zadávania hodnôt).
Ukážka hovoru.
Konfigurácia zadávania údajov v dialógovom okne.

Otvorí dialógové okno (Otvoriť súbor).
Otvorí dialógové okno (Print).
Ďalšie dialógové okná.
Hovor prehliadača z Excelu.
Dialógové okno zadávania údajov.
Dialógové okno nastavení písma.
Základné hodnoty.
Formátovanie textu. Tabuľky. HRANICE A VÝPLŇ.
Zobrazí zoznam dostupných písem.
Výber všetkých čísel z textu.
Veľké písmeno iba na začiatku textu.
Počítanie počtu opakovaní hľadaného textu.
Výber ľubovoľného prvku z textu.
Zobrazenie textu dozadu.
Anglický text - veľkými písmenami.
Spustenie tabuľky symbolov z Excelu.
informácie o používateľovi, počítači, tlačiarni atď.
Získajte používateľské meno.
Rozlíšenie displeja monitora.
Získajte informácie o používanej tlačiarni.
Zobrazte informácie o diskoch vo svojom počítači.
UŽÍVATEĽSKÉ FORMY.
SCHÉMA.
Zostavenie diagramu pomocou makra.
Uloženie diagramu do samostatného súboru.
Zostavenie a odstránenie diagramu kliknutím na tlačidlo.
V samostatnom okne zobrazí zoznam diagramov.
Použitie náhodnej farebnej palety.
Účinok transparentnosti grafu.
Zostavte graf na základe údajov z viacerých pracovných hárkov.
Vytvorte štítky pre údaje grafu.
RÔZNE PROGRAMY.
Program na skladanie krížoviek.
Vytvorte obal DVD.
Hra na mínovom poli.
Hra „Hádaj zviera“.
Výpočet založený na bunkách určitej farby.
OSTATNÉ FUNKCIE A MAKRÁ.
Volanie funkčných klávesov.
Výpočet aritmetického priemeru.
Premena čísel na peniaze.
Hľadaj najbližší pondelok.
Počítanie počtu celých rokov.
Vážený priemer výpočtu.
Premeňte číslo mesiaca na jeho názov.
Použitie relatívnych odkazov.
Previesť tabuľku programu Excel do formátu HTML.
Generátor náhodných čísel.
Náhodné čísla sú založené na rozsahu.
Použitie funkcie bez jej zadania do bunky.
Počítanie pomenovaných predmetov.
Zapnite automatický filter pomocou makra.
Vytvorenie plazivej čiary.
Vytvorenie bežiaceho obrázku.
Rotujúce automatické tvary.
Vyvolávanie tabuľky farieb.
Vytvorenie kalkulačky.
Odmietnutie priezviska, mena a priezviska.
DÁTUM A ČAS.
Zobrazenie dátumu a času.
Zobrazenie dátumu a času.
Získanie systémového dátumu.
Extrahujte dátum a hodiny.
Funkcia DateFull. Použitá verzia MS Office nie je špecifikovaná.

Málokto vie, že prvá verzia obľúbeného produktu Microsoft Excel sa objavila v roku 1985. Od tej doby prešiel niekoľkými úpravami a je v dopyte miliónov používateľov po celom svete. Mnoho ľudí zároveň pracuje iba s malým zlomkom možností tohto tabuľkového procesora a ani nevie, ako by im schopnosť programovať v programe Excel mohla uľahčiť život.

Čo je to VBA

Programovanie v programe Excel sa vykonáva pomocou programovacieho jazyka Visual Basic for Application, ktorý je pôvodne vstavaný do najznámejšieho tabuľkového procesora od spoločnosti Microsoft.

Odborníci pripisujú jeho výhodám relatívnu jednoduchosť vývoja. Ako ukazuje prax, základy VBA zvládnu aj používatelia, ktorí nemajú profesionálne znalosti programovania. K zvláštnostiam VBA patrí spustenie skriptu v prostredí kancelárskych aplikácií.

Nevýhodou programu sú problémy spojené s kompatibilitou rôznych verzií. Sú spôsobené tým, že programový kód VBA odkazuje na funkčnosť, ktorá je prítomná v novej verzii produktu, ale nie v starej. K nevýhodám patrí aj príliš vysoká otvorenosť kódu pre zmenu neoprávnenou osobou. Microsoft Office a IBM Lotus Symphony však umožňujú používateľovi zašifrovať jadro a nastaviť heslo na jeho zobrazenie.

Objekty, zbierky, vlastnosti a metódy

Toto sú koncepty, ktorým musia porozumieť tí, ktorí sa chystajú pracovať v prostredí VBA. Najprv musíte pochopiť, čo je to predmet. V programe Excel je to hárok, zošit, bunka a rozsah. Tieto objekty majú špeciálnu hierarchiu, t.j. navzájom sa poslúchať.

Tou hlavnou je Aplikácia, ktorá zodpovedá samotnému programu Excel. Nasledujú zošity, pracovné listy a rozsah. Ak chcete napríklad odkazovať na bunku A1 na konkrétnom hárku, musíte zadať cestu na základe hierarchie.

Pokiaľ ide o koncept „zbierky“, ide o skupinu predmetov rovnakej triedy, ktorá má v zázname tvar ChartObjects. Jeho jednotlivými prvkami sú tiež objekty.

Ďalším konceptom sú vlastnosti. Sú nevyhnutnou charakteristikou akéhokoľvek predmetu. 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. Ako napríklad bude ukázané neskôr, príkaz Bunky (1,1). Výber je veľmi často používaný pri programovaní v Exceli. To znamená, že musíte vybrať bunku so súradnicami

Často sa s ním používa Selection.ClearContents. Jeho vykonanie znamená vymazanie obsahu vybratej bunky.

Ako začať

Potom musíte prejsť na aplikáciu VB, pre ktorú stačí použiť kombináciu klávesov „Alt“ a „F11“. Ďalej:

• na paneli s ponukami umiestnenom v hornej časti okna kliknite na ikonu vedľa ikony programu Excel;
• vyberte tím Mudule;
• uložte kliknutím na ikonu s obrázkom;
• napíšte, povedzme, náčrt kódu.

Vyzerá to takto:

Podprogram ()

„Náš kód

Upozorňujeme, že riadok „Náš kód“ bude zvýraznený inou farbou (zelenou). Dôvod je v apostrofe na začiatku riadka, čo znamená, že nasleduje komentár.

Teraz môžete vo VBA Excel napísať ľubovoľný kód a vytvoriť si nový nástroj (pozri príklady programov nižšie). Samozrejme, tí, ktorí poznajú základy jazyka Visual Basic, to budú mať oveľa jednoduchšie. Na ten si však dostatočne rýchlo zvyknú aj tí, ktorí ich nemajú, ak chcú.

Makrá programu Excel

Tento názov skrýva programy napísané v jazyku Visual Basic for Application. Programovanie v programe Excel je teda o vytváraní makier so správnym kódom. Vďaka tejto funkcii sa tabuľkový procesor Microsoft vyvíja sám a prispôsobuje sa požiadavkám konkrétneho používateľa. Keď prídete na to, ako vytvoriť moduly pre písanie makier, môžete sa začať venovať konkrétnym príkladom programov 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 potom zapíše do inej.

Pre to:

• otvorte kartu „Zobraziť“;
• prejdite na ikonu „Makrá“;
• kliknite na „Nahrať makro“;
• vyplňte otvorený formulár.

Pre jednoduchosť nechajte „Macro1“ v poli „Názov makra“ a do poľa „Klávesová skratka“ zadajte napríklad hh (to znamená, že program je možné spustiť príkazom „bleskovo“ Ctrl + h)). Stlačte Enter.

Keď sa záznam makra už spustil, obsah jednej bunky sa skopíruje do druhej. Vráti sa k pôvodnej ikone. Kliknite na „Nahrať makro“. Táto akcia znamená koniec programu.

• znova prejdite na riadok „Makrá“;
• v zozname vyberte „Makro 1“;
• kliknite na „Spustiť“ (rovnaká akcia sa spustí spustením klávesovej skratky „Ctrl + hh“).

V dôsledku toho dôjde k akcii, ktorá bola vykonaná počas záznamu makra.

Má zmysel vidieť, ako kód vyzerá. Ak to chcete urobiť, vráťte sa na riadok „Makrá“ a kliknite na „Zmeniť“ alebo „Enter“. Výsledkom je, že sa ocitnú v prostredí VBA. Samotný kód makra sa v skutočnosti nachádza medzi riadkami Sub Macro1 () a End Sub.

Ak sa kopírovalo napríklad z bunky A1 do bunky C1, potom jeden z riadkov kódu bude vyzerať ako Rozsah („C1“). Vyberte. V preklade to vyzerá ako „Rozsah („ C1 “). Vyberte„, inými slovami, prepne sa na VBA Excel, do bunky C1.

Príkaz ActiveSheet.Paste ukončí aktívnu časť kódu. Znamená to zapísať obsah vybratej bunky (v tomto prípade A1) do vybranej bunky C1.

Príklad 2

Slučky VBA vám pomôžu vytvárať rôzne makrá v Exceli.

Slučky VBA vám pomôžu vytvoriť rôzne makrá. Predpokladajme, že existuje funkcia y = x + x 2 + 3x 3 - cos (x). Na získanie grafu je potrebné vytvoriť makro. To je možné vykonať iba pomocou slučiek VBA.

Pre počiatočnú a konečnú hodnotu argumentu funkcie vezmite 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 ukážkové makrá VBA Excel sú vytvorené rovnakým spôsobom, ako je uvedené vyššie. V tomto konkrétnom prípade kód vyzerá takto:

Podprogram ()

súlož = 0,1

Vykonajte 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 do bunky zapíše so súradnicami (i, 2))

i = i + 1 (počítadlo je aktívne);

x1 = x1 + súlož (argument sa zmení podľa veľkosti kroku);

Koniec pod.

Výsledkom spustenia tohto makra v programe Excel sú dva stĺpce, z ktorých prvý obsahuje hodnoty pre x a druhý pre y.

Potom je graf vykreslený pomocou nich štandardným spôsobom pre Excel.

Príklad 3

Na implementáciu slučiek vo VBA Excel 2010, ako v iných verziách, sa používa už uvedená konštrukcia Do While, používa sa For.

Zvážte program, ktorý vytvorí stĺpec. Každá bunka bude obsahovať štvorce čísla zodpovedajúceho riadka. Použitie konštrukcie For vám umožní napísať ju veľmi skoro, bez použitia počítadla.

Najprv musíte vytvoriť makro, ako je popísané vyššie. Ďalej 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 prírastkoch po jednej“.

Ak je úlohou získať stĺpec so štvorcami, napríklad všetky nepárne čísla z rozsahu od 1 do 11, napíšeme:

Pre i = 1 až 10 krok 1 Ďalej.

Tu je krok krokom. V tomto prípade sa rovná dvom. Absencia tohto slova v slučke štandardne znamená, že krok je jeden.

Získané výsledky je potrebné uložiť do buniek s číslom (i, 1). Potom sa pri každom začiatku cyklu so zvýšením i o veľkosť kroku automaticky zvýši aj číslo riadku. Kód bude teda optimalizovaný.

Vo všeobecnosti 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 plní úlohu počítadla a znamená ďalší začiatok cyklu)

Koniec pod.

Ak je všetko vykonané správne, vrátane záznamu a spustenia makra (pozri pokyny vyššie), potom sa pri každom volaní získa stĺpec špecifikovanej veľkosti (v tomto prípade pozostávajúci z 10 buniek).

Príklad 4

V každodennom živote je často potrebné urobiť to alebo ono rozhodnutie v závislosti od nejakej podmienky. Vo VBA Excel sa bez nich nezaobídete. Príklady programov, kde je zvolený ďalší priebeh vykonávania algoritmu a nie je vopred definovaný, najčastejšie používajú konštrukciu If ... Then (pre zložité prípady) If ... Then ... END If.

Uvažujme o konkrétnom prípade. Predpokladajme, že musíte vytvoriť makro pre Excel, aby bola napísaná bunka so súradnicami (1,1):

1, ak je argument kladný;

0, ak je argument null;

-1, ak je argument záporný.

Vytvorenie takéhoto makra pre Excel sa začína štandardným spôsobom pomocou klávesových skratiek Alt a F11. Ďalej je napísaný nasledujúci kód:

Podprogram ()

x = Bunky (1, 1). Hodnota (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

Koniec pod.

Zostáva spustiť makro a získať požadovanú hodnotu pre argument v programe Excel.

Funkcie VBA

Ako ste si mohli všimnúť, programovanie v najslávnejšom tabuľkovom procesore spoločnosti Microsoft nie je také ťažké. Zvlášť, 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, zhruba 160 funkcií. Môžu byť rozdelené do niekoľkých veľkých skupín. To:

• Matematické funkcie. Ich aplikovaním na argument získate hodnotu kosínu, prirodzeného logaritmu, celočíselnej časti atď.
• Finančné funkcie. Vďaka ich prítomnosti a používaniu programovania v Exceli môžete získať efektívne nástroje pre účtovníctvo a finančné výpočty.
• Funkcie spracovania polí. Patria sem Array, IsArray; LViazaný; UBound.
• VBA Excel funkcie pre reťazec. Jedná sa o pomerne veľkú skupinu. Zahŕňa napríklad funkcie Space na vytvorenie reťazca s počtom medzier rovných celočíselnému argumentu alebo Asc na preklad znakov do kódu ANSI. Všetky z nich sú široko používané a umožňujú vám pracovať s riadkami v programe Excel a vytvárať aplikácie, ktoré výrazne uľahčujú prácu s týmito tabuľkami.
• Funkcie prevodu dátových typov. CVar napríklad vráti hodnotu argumentu Výraz tým, že ho prevedie na údajový typ Variant.
• Funkcie pre prácu s dátumami. Výrazne rozširujú tie štandardné. Napríklad funkcia WeekdayName vracia podľa názvu názov (úplný alebo čiastočný) dňa v týždni. Časovač je ešte užitočnejší. Udáva počet sekúnd, ktoré uplynuli od polnoci do konkrétneho bodu v dni.
• Funkcie na prevod numerického argumentu na rôzne číselné systémy. Napríklad Oct vydáva číslo v osmičkovej reprezentácii.
• Funkcie formátovania. Najdôležitejším z nich je formát. Vráti variant s výrazom naformátovaným podľa pokynov uvedených v popise formátu.
• atď.

Štúdium vlastností týchto funkcií a ich aplikácie výrazne rozšíri rozsah Excelu.

Príklad 5

Skúsme prejsť na riešenie zložitejších problémov. Napríklad:

Daný papierový dokument správy o skutočnej úrovni nákladov podniku. Požadovaný:

• vyvinúť svoju šablónovú časť pomocou tabuľkového procesora Excel;
• vytvorte program VBA, ktorý vyžiada počiatočné údaje na jeho vyplnenie, vykoná potrebné výpočty a vyplní ich do príslušných buniek šablóny.

Uvažujme o jednom z riešení.

Vytvorenie šablóny

Všetky akcie sa vykonávajú na štandardnom hárku programu Excel. Voľné bunky sú vyhradené na zadávanie údajov o názve spotrebiteľskej spoločnosti, výške nákladov, ich úrovni a obrate. Pretože počet spoločností (spoločností), pre ktoré sa správa zostavuje, nie je pevný, bunky na zadávanie hodnôt na základe výsledkov a celého 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ť označenia. Budú použité pre premenné:

• NN - číslo aktuálneho riadka tabuľky;
• TP a TF - plánovaný a skutočný obrat;
• SF a SP - skutočné a plánované náklady;
• IP a IF - plánovaná a skutočná úroveň nákladov.

Označme akumuláciu súčtu pre tento stĺpec rovnakými písmenami, ale s „predponou“ Itog. Napríklad ItogTP - odkazuje na stĺpec tabuľky s názvom „Predpokladaný obrat“.

Riešenie problému pomocou programovania VBA

Použitím zavedených označení získame vzorce pre odchýlky. Ak chcete počítať v%, máme (F - P) / P * 100 a celkom - (F - P).

Výsledky týchto výpočtov je najlepšie zadať priamo do príslušných buniek v tabuľke programu Excel.

Výsledky skutočnosti a predpovede sa získavajú 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 v percentách, a v prípade celkovej hodnoty - (ItogF - ItogP).

Výsledky sú opäť okamžite zapísané do zodpovedajúcich buniek, takže nie je potrebné ich priraďovať k premenným.

Pred spustením vytvoreného programu musíte zošit uložiť napríklad pod názvom „Report1.xls“.

Tlačidlo „Vytvoriť tabuľku prehľadov“ je potrebné stlačiť iba 1 krát po zadaní informácií v hlavičke. Je potrebné si uvedomiť aj ďalšie pravidlá. Najmä tlačidlo „Pridať riadok“ musí byť stlačené 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 prejsť na okno „Excel“.

Teraz viete, ako riešiť úlohy programu Excel pomocou makier. Na prácu v prostredí v súčasnosti najpopulárnejšieho textového editora „Word“ môže byť potrebná aj schopnosť používať vba excel (viď príklady programov vyššie). Najmä môžete vytvárať tlačidlá ponuky písaním, ako je to uvedené na úplnom začiatku článku, alebo napísaním kódu, vďaka ktorému je možné mnoho operácií s textom vykonávať stlačením klávesov povinnosti alebo prostredníctvom karty Zobraziť a ikony Makrá .

Tabuľky programu Excel sú jedným z najobľúbenejších spôsobov ukladania a organizovania informácií. Ak do Excelu vložíte makrá, možnosti programu je možné rozšíriť takmer dvakrát. Makrá napríklad pomáhajú zautomatizovať rutinné procesy a urýchliť opakujúcu sa prácu. Často ich používajú špecialisti, ktorí prekladajú správy z 1C do Excelu. Pomocou vstavaného kódu je možné ich jednoducho zredukovať na jednu štandardnú šablónu.

Čo sú to makrá programu Excel?

Makrá sa používajú nielen v tabuľkách, ale v celom balíku Microsoft Office. Sú generované kódom pomocou programu Visual Basic for Applications. Ak ste čiastočne oboznámení s webovým programovaním, môžete nakresliť analógiu s jazykom Javascript. Makrá Excelu robia zhruba to isté ako Javascript s údajmi HTML.

Malá časť toho, čo môže makro robiť v kancelárskych aplikáciách:

Zarovnajte dokument štýlovo a naformátujte ho;
... vykonávať operácie s číselnými a textovými údajmi;
... uchýliť sa k používaniu externých súborov (text, databázy a ďalšie);
... vytvárať nové dokumenty od začiatku;
... skombinujte niekoľko akcií a vykonajte ich automaticky.

Ak chcete pracovať s tabuľkou, môžete si ju vytvoriť sami alebo si stiahnuť hotovú verziu z internetu. Makrá musíte sťahovať iba z dôveryhodných stránok, pretože v opačnom prípade môžete do programu Excel vložiť vírus.

Čo môže byť nebezpečné na makrách v programe Excel?

Poškodiť súbory vo vašom počítači;
... ukradnúť dôverné údaje.

Vírus sa zavádza, keď je spustená jedna z kancelárskych aplikácií - Word alebo Excel. Potom, čo prestanete pracovať, vírus spustí autonómnu prevádzku a nakazí ním celý systém.

Ďalším spôsobom, akým škodlivé makro funguje, je sprostredkovať šírenie hrozieb do systému. V tomto prípade ide o akúsi bránu pre zavedenie a generovanie trójskeho softvéru. Už ho nebude ovládať upravené makro, ale samotný operačný systém, a preto je hrozba stále ambicióznejšia. Vyliečiť takéto vírusy nie je vždy možné, niekedy je potrebné úplne zmeniť Windows OC alebo pevný disk PC (v závislosti od úloh, ktoré si hacker pri písaní kódu nastavil).

Môžete si vybrať ľubovoľný spôsob ochrany pred takýmito makrami. Najpopulárnejšie je zvýšiť úroveň zabezpečenia programu Excel, ale v tomto prípade program môže prestať podporovať užitočné makrá vášho autora. Nedôverujte súborom z neznámych zdrojov, pretože môžu byť infikované. Aj makro z kolegovho počítača môže byť nebezpečné. Ak vám priateľ poskytol súbor, ktorý podporuje používanie makier na prácu, skôr, ako ho systém dovolí používať, objasnite, ako toto makro prijal.

Najlepšou možnosťou je samoštúdium umenia vytvárania makier pre vaše potreby. V takom prípade môžete plne zaručiť ich kvalitu a bezpečnosť.

Ako zaznamenávať makrá v programe Excel

Makro najľahšie vytvoríte programovaním priamo v programe Excel. Na to budete potrebovať tabuľku so zadanými údajmi a trochu znalostí, aby ste mohli v programe zaznamenať takýto kód.

Vytváranie začína ponukou „Zobraziť“. V ňom musíte vybrať položku „Makrá“ a príkaz „Nahrať makro“. Ďalej musíte zadať názov bez medzier, napríklad „Formát“ (ak bude kód zodpovedný za zmenu formátu textu alebo buniek).

Potom sa všetky vaše akcie automaticky zaznamenajú a skonvertujú na kód makra. V spodnej časti dokumentu sa zobrazí tlačidlo s malým štvorcom („Zastaviť“), kliknutím na ktoré môžete zastaviť nahrávanie. V Exceli teda môžete automaticky písať makrá, ktorých príklady môžete v procese učenia ďalej analyzovať.

Ako povoliť makrá v programe Excel

Aby ste mohli zaznamenané makro zahrnúť do iných dokumentov, musíte ho po vyčistení tabuľky uložiť. Formát dokumentu musí byť zadaný ako XLTM (podporuje makrá).

Ak chcete pri najbližšom prístupe k dokumentu povoliť makrá v programe Excel, musíte najskôr povoliť makrá v rozbaľovacom hornom riadku. Potom musíte importovať údaje, ktoré budú upravované makrom z bežnej tabuľky CSV. Po dokončení importu sa musíte vrátiť späť do ponuky „Zobraziť“, vybrať tam „Makrá“ a v zozname nájsť názov kódu, ktorý ste zadali. Na konci kliknite na tlačidlo „Vykonať“. Potom uvidíte, ako program podľa algoritmu, ktorý ste zadali vyššie, vykonáva výpočty a formátovanie dokumentov.

Ako môžem vidieť kód programu makra?

Všetky makrá vytvorené v programe Excel sú napísané automaticky, bez ľudského zásahu. Program nezávisle skladá kód programu, ktorý je možné otvoriť z tabuľky zavolaním pomocou ponuky.

Aby ste to urobili, musíte postupne vykonávať príkazy:

Rozbaľte kartu „Zobraziť“;
... otvorte „Makrá“;
... vyberte názov požadovaného makra;
... vyvolajte okno s jeho kódom pomocou tlačidla „Zmeniť“.

V novom dialógovom okne môžete makro upravovať a meniť. Uvidíte tam kód, ktorý program zaznamenal automaticky, a akúkoľvek akciu môžete pridať ručne. Po uložení informácií pri importe nových údajov bude makro fungovať inak.

Ako spustiť makro kliknutím na bunku

Povedzme, že chcete, aby sa makro spustilo, keď vyberiete bunku A1. Aby mohla bunka vybraná v programe Excel spustiť makro, musíte do prvého modulu pridať kód programu:

Súkromný čiastkový pracovný hárok_Voľba výberu (ByVal Target As Range)
Ak Target.Address = "$ A $ 1" Potom: Zavolajte modul1
Koniec pod

Najlepším spôsobom, ako vám pomôcť naučiť sa makrá v programe Excel, je návod. Existuje viac ako jeden taký príklad a pre tých, ktorí sa len zoznamujú so všetkými možnosťami tabuľky, je to najlepší spôsob, ako sa naučiť naplno využívať jej funkčnosť.

Kde sa môžem naučiť pracovať s makrami

Existuje niekoľko spôsobov, ako sa naučiť vytvárať zložitejšie makrá. Mali by sa líšiť v logike a dobre definovanej postupnosti akcií. Takéto makrá sú vytvárané iba ručným písaním kódu VBA. Na vytváranie komplexných makier budete musieť stráviť nejaký čas učením sa zložitosti programovacieho jazyka Visual Basik.

Teraz, aby ste si preštudovali všetky možnosti Excelu, bolo vytvorených mnoho špeciálnych kurzov, ktoré trvajú niekoľko týždňov, ale musíte sa zúčastniť ich školenia alebo sa zúčastniť webinárov (čo nie je vždy vhodné kvôli časovému rozdielu). Efektívnejším a jednoduchším spôsobom, ako sa naučiť vytvárať makrá v programe Excel, je sprievodca pre samoukov s príkladmi. Môžete si to precvičiť kedykoľvek, vám to vyhovuje, a podrobné návody vám pomôžu vyvinúť rôzne možnosti: od najjednoduchších kódov po najzložitejšie. Microsoft Excel je všestranný tabuľkový editor, ktorý je možné použiť na riešenie mnohých zložitých úloh.

Príklady

Existujú kódy, ktoré vám umožňujú automatizovať rôzne úlohy. Vezmime si ako príklad makro na vkladanie riadkov. Vyzerá to takto:

Pod makro1 ()