Jak vložit hotové makro do sešitu? Jak povolit makra v Excelu. Makra v Excelu: Příklady

Málokdo ví, že první verze populární produkt společnosti Microsoft Excel se objevil v roce 1985. Od té doby prošel několika úpravami a je žádaný miliony uživatelů po celém světě. Mnoho lidí přitom pracuje jen s malým zlomkem možností tohoto tabulkového procesoru a ani si neuvědomují, jak by jim dovednost programování v Excelu mohla usnadnit život.

Co je VBA

Programování v Excelu se provádí pomocí programovacího jazyka Visual Basic for Application, který je původně zabudován do toho nejznámějšího tabulkový procesor od společnosti Microsoft.

Odborníci označují jeho výhody za srovnatelnou snadnost vývoje. Jak ukazuje praxe, základy VBA zvládnou i uživatelé, kteří nemají profesionální programátorské dovednosti. Mezi funkce jazyka VBA patří spouštění skriptu v prostředí kancelářských aplikací.

Nevýhodou programu jsou problémy spojené s kompatibilitou různé verze. Jsou způsobeny skutečností, že programový kód VBA odkazuje funkčnost, které jsou přítomny v nová verze produkt, ale ve starém chybí. Mezi nevýhody patří také příliš vysoká otevřenost kódu pro změny. od outsidera. Nicméně Microsoft Office, stejně jako IBM Lotus Symphony, umožňují uživateli zašifrovat počáteční kód a nastavit heslo pro jeho zobrazení.

Objekty, kolekce, vlastnosti a metody

Právě těmto pojmům musí rozumět ti, kdo se chystají pracovat v prostředí VBA. Nejprve musíte pochopit, co je objekt. V Excelu je to list, sešit, buňka a rozsah. Tyto objekty mají zvláštní hierarchii, tzn. jsou si navzájem podřízeny.

Hlavní je Aplikace, která odpovídá většině Excel program. Pak přijdou Pracovní sešity, Pracovní listy a také Rozsah. Chcete-li například získat přístup k buňce A1 na konkrétním listu, musíte zadat cestu, která bere v úvahu hierarchii.

Pokud jde o pojem „kolekce“, jedná se o skupinu objektů stejné třídy, která má v záznamu tvar ChartObjects. Její jednotlivé prvky jsou také předměty.

Dalším pojmem jsou vlastnosti. Jsou nezbytnou vlastností každého předmětu. Například pro rozsah je to Hodnota nebo Vzorec.

Metody jsou příkazy, které označují, co je třeba udělat. Při psaní kódu ve VBA musí být odděleny od objektu tečkou. Například, jak bude ukázáno později, velmi často se při programování v Excelu používá příkaz Cells(1,1).Select. To znamená, že musíte vybrat buňku se souřadnicemi

Spolu s ním se často používá Selection.ClearContents. Jeho provedení znamená vymazání obsahu vybrané buňky.

Jak začít

Poté je třeba přejít do aplikace VB, pro kterou stačí použít kombinaci kláves „Alt“ a „F11“. Dále:

• v panelu nabídek v horní části okna klikněte na ikonu vedle ikony Excel;
• vyberte příkaz Mudule;
• uložit kliknutím na ikonu s obrázkem;
• napište, řekněme, náčrt kódu.

Vypadá to takto:

podprogram()

„Náš kód

Všimněte si, že řádek „Náš kód“ bude zvýrazněn jinou barvou (zeleně), důvodem je apostrof na začátku řádku, který označuje, že následuje komentář.

Nyní můžete napsat libovolný kód a vytvořit si ho pro sebe nový nástroj PROTI VBA Excel(viz příklady programů níže). Pro ty, kteří jsou obeznámeni se základy Visual Basicu, to bude samozřejmě mnohem jednodušší. Nicméně i ti, kteří je nemají, se na přání budou moci dostat do pohodlí dostatečně rychle.

Makra v Excelu

Tento název skrývá programy napsané v jazyce Visual Basic for Application. Programování v Excelu je tedy vytváření maker s správný kód. Díky této schopnosti se tabulka Microsoftu sama vyvíjí a přizpůsobuje se potřebám konkrétního uživatele. Poté, co jste přišli na to, jak vytvořit moduly pro psaní maker, můžete začít uvažovat konkrétní příklady programy VBA Vynikat. Nejlepší je začít s nejzákladnějšími kódy.

Příklad 1

Úkol: napište program, který zkopíruje hodnotu obsahu jedné buňky a následně zapíše do druhé.

Pro tohle:

• otevřete kartu "Zobrazit";
• přejděte na ikonu "Makra";
• klikněte na "Zaznamenat makro";
• vyplňte otevřený formulář.

Pro zjednodušení ponechte v poli „Název makra“ „Macro1“ a do pole „Klávesová zkratka“ vložte např. hh (to znamená, že program spustíte bleskovým příkazem „Ctrl + h“). Stiskněte Enter.

Nyní, když záznam makra již začal, obsah buňky se zkopíruje do jiné. Návrat k původní ikoně. Klikněte na "Zaznamenat makro". Tato akce znamená konec programu.

• znovu přejděte na řádek "Makra";
• vyberte ze seznamu "Makro 1";
• klikněte na "Spustit" (stejná akce se spustí spuštěním klávesové zkratky "Ctrl + hh").

V důsledku toho dojde k akci, která byla provedena během záznamu makra.

Má smysl vidět, jak kód vypadá. Chcete-li to provést, přejděte zpět na řádek „Makra“ a klikněte na „Upravit“ nebo „Zadat“. Díky tomu se ocitnou v prostředí VBA. Samotný kód makra je ve skutečnosti umístěn mezi řádky Sub Macro1() a End Sub.

Pokud bylo provedeno kopírování například z buňky A1 do buňky C1, bude jeden z řádků kódu vypadat jako Range(“C1”). Vyberte. V překladu to vypadá jako “Range(“C1”).Select, jinými slovy skočí do VBA Excel, do buňky C1.

Příkaz ActiveSheet.Paste dokončí aktivní část kódu. Znamená to zaznamenat obsah vybrané buňky (v tento případ A1) do zvýrazněné buňky C1.

Příklad 2

Smyčky VBA vám pomohou vytvářet různá makra v aplikaci Excel.

Smyčky VBA vám pomohou vytvořit různá makra. Předpokládejme, že existuje funkce y=x + x 2 + 3x 3 - cos(x). Chcete-li získat jeho plán, musíte vytvořit makro. To lze provést pouze pomocí smyček VBA.

Vezměte x1=0 a x2=10 pro počáteční a konečné hodnoty argumentu funkce. Kromě toho musíte zadat konstantu - hodnotu pro krok změny argumentu a počáteční hodnotu pro čítač.

Všechny příklady makra VBA Soubory Excel se vytvářejí stejným postupem jako výše. V tomto konkrétním případě kód vypadá takto:

podprogram()

krok = 0,1

Do Zatímco x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Buňky (i, 1). Hodnota = x1 (hodnota x1 se zapíše do buňky se souřadnicemi (i,1))

Buňky (i, 2). Hodnota = y (hodnota y se zapíše do buňky se souřadnicemi (i,2))

i = i + 1 (počítadlo aktivní);

x1 = x1 + shag (argument se změní o hodnotu kroku);

endsub.

V důsledku spuštění tohoto makra v Excelu získáme dva sloupce, z nichž první obsahuje hodnoty pro x a druhý pro y.

Poté se na nich sestaví graf způsobem, který je standardní pro Excel.

Příklad 3

K implementaci smyček ve VBA Excel 2010, stejně jako v jiných verzích, spolu s již danou konstrukcí Do While se používá For.

Zvažte program, který vytvoří sloupec. V každé jeho buňce budou zapsány druhé mocniny čísla odpovídajícího řádku. Použití konstrukce For vám umožní napsat jej velmi krátce, bez použití počítadla.

Nejprve musíte vytvořit makro, jak je popsáno výše. Dále napíšeme samotný kód. Věříme, že nás zajímají hodnoty pro 10 buněk. Kód vypadá takto.

Pro i = 1 až 10 Další

Příkaz se překládá do „lidského“ jazyka jako „Opakujte od 1 do 10 v krocích po jedné“.

Pokud je úkolem získat sloupec se čtverci, například všechna lichá čísla z rozsahu od 1 do 11, pak napíšeme:

Pro i = 1 až 10 krok 1 Dále.

Tady krok je krok. V tomto případě se rovná dvěma. Ve výchozím nastavení absence tohoto slova v cyklu znamená, že krok je jediný.

Získané výsledky je nutné uložit do buněk s číslem (i,1). Potom při každém spuštění smyčky se zvýšením i o hodnotu kroku automaticky vzroste i číslo řádku. Kód bude tedy optimalizován.

Obecně bude kód vypadat takto:

podprogram()

Pro i = 1 až 10 Krok 1 (můžete napsat jednoduše Pro i = 1 až 10)

Buňky (i, 1). Hodnota = i ^ 2

Další (v jistém smyslu funguje jako počítadlo a znamená další začátek smyčky)

endsub.

Pokud je vše provedeno správně, včetně záznamu a spuštění makra (viz pokyny výše), pak při každém jeho vyvolání se získá sloupec daná velikost(v tomto případě sestávající z 10 buněk).

Příklad 4

V každodenním životě je poměrně často potřeba učinit jedno nebo druhé rozhodnutí v závislosti na nějaké situaci. Bez nich se ve VBA Excel neobejdete. Příklady programů, kde je další průběh algoritmu zvolen a není předem určen, nejčastěji používají konstrukci If ... Then (např. těžké případy) If …Ten …END If.

Zvážit konkrétní případ. Předpokládejme, že potřebujete vytvořit makro pro "Excel", aby byla buňka se souřadnicemi (1,1) zapsána:

1, pokud je argument kladný;

0, pokud je argument null;

-1, pokud je argument záporný.

Začíná vytváření takového makra pro Excel standardním způsobem pomocí "horkého" Klávesy Alt a F11. Dále je napsán následující kód:

podprogram()

x= Cells(1, 1).Value (tento příkaz přiřadí x hodnotu obsahu buňky na souřadnicích (1, 1))

Pokud x>0, pak Cells(1, 1).Hodnota = 1

Pokud x=0, pak Cells(1, 1).Hodnota = 0

Pokud x<0 Then Cells(1, 1).Value = -1

endsub.

Zbývá spustit makro a získat požadovanou hodnotu pro argument v Excelu.

Funkce VBA

Jak jste si mohli všimnout, programování v nejslavnější tabulce Microsoftu není tak těžké. Zvláště pokud se naučíte používat funkce VBA. Celkem má tento programovací jazyk, vytvořený speciálně pro psaní aplikací v Excelu a Wordu, asi 160 funkcí. Lze je rozdělit do několika velkých skupin. Tento:

• Matematické funkce. Když je aplikují na argument, získají hodnotu kosinu, přirozeného logaritmu, celé části atd.
• finanční funkce. Díky jejich přítomnosti a využití programování v Excelu můžete získat efektivní nástroje pro účetnictví a finanční výpočty.
• Funkce zpracování pole. Patří mezi ně Array, IsArray; lBound; UBound.
• Funkce VBA Excel pro řádek. To je poměrně velká skupina. Obsahuje například funkce Space pro vytvoření řetězce s počtem mezer rovným celočíselnému argumentu nebo Asc pro převod znaků na kód ANSI. Všechny jsou široce používány a umožňují pracovat s řádky v Excelu a vytvářet aplikace, které práci s těmito tabulkami výrazně usnadní.
• Funkce převodu datových typů. Například CVar vrátí hodnotu argumentu Expression tím, že jej převede na datový typ Variant.
• Funkce pro práci s daty. Výrazně rozšiřují ty standardní.Funkce WeekdayName tedy vrací název (celý nebo částečný) dne v týdnu jeho číslem. Ještě užitečnější je časovač. Udává počet sekund, které uplynuly od půlnoci do určitého bodu dne.
• Funkce pro převod číselného argumentu do různých číselných soustav. Například Oct udává osmičkovou reprezentaci čísla.
• Formátovací funkce. Nejdůležitější z nich je Formát. Vrací hodnotu typu Variant s výrazem naformátovaným podle pokynů uvedených v deklaraci formátu.
• atd.

Studium vlastností těchto funkcí a jejich aplikace výrazně rozšíří záběr Excelu.

Příklad 5

Zkusme přejít k řešení složitějších problémů. Například:

Je uveden papírový doklad zprávy o skutečné výši nákladů podniku. Požadované:

• vytvořit jeho šablonovou část pomocí tabulky Excel;
• vytvořte VBA program, který si vyžádá počáteční data k jejich vyplnění, provede potřebné výpočty a vyplní jimi odpovídající buňky šablony.

Zvažme jedno z řešení.

Vytvořte šablonu

Všechny akce se provádějí na standardním listu v Excelu. Volné buňky jsou vyhrazeny pro zadání údajů o názvu spotřebitelské společnosti, výši nákladů, jejich výši a obratu. Vzhledem k tomu, že počet společností (společností), pro které je sestava sestavován, není pevně daný, nejsou buňky pro zadávání hodnot na základě výsledků a celé jméno specialisty předem rezervovány. Pracovní list dostane nový název. Například "Օtchet".

Proměnné

Chcete-li napsat program, který automaticky vyplní šablonu, musíte zvolit notaci. Budou použity pro proměnné:

• NN - číslo aktuálního řádku tabulky;
• TP a TF - plánovaný a skutečný obrat;
• SF a SP - skutečná a plánovaná výše nákladů;
• IP a IF - plánovaná a skutečná úroveň nákladů.

Označme stejnými písmeny, ale s "prefixem" Itog akumulaci součtu na daném sloupci. Například ItogTP odkazuje na sloupec tabulky s názvem „plánovaný obrat“.

Řešení problému pomocí programování VBA

Pomocí zavedeného zápisu získáme vzorce pro odchylky. Pokud je nutné provést výpočet v %, máme (F - P) / P * 100 a ve výši - (F - P).

Výsledky těchto výpočtů lze nejlépe ihned zapsat do příslušných buněk excelové tabulky.

Pro skutečné a předpovědní součty se získá pomocí vzorců ItogP=ItogP + P a ItogF=ItogF+ F.

Pro odchylky použijte = (ItogF - ItogP) / ItogP * 100, pokud se výpočet provádí v procentech, a v případě celkové hodnoty - (ItogF - ItogP).

Výsledky se opět ihned zapisují do příslušných buněk, není tedy potřeba je přiřazovat k proměnným.

Před spuštěním vytvořeného programu je potřeba sešit uložit např. pod názvem "Sestava1.xls".

Tlačítko "Vytvořit tabulku přehledů" stačí stisknout pouze 1krát po zadání informací v záhlaví. Je třeba si uvědomit další pravidla. Zejména na tlačítko "Přidat řádek" je nutné kliknout pokaždé po zadání hodnot pro každou aktivitu do tabulky. Po zadání všech údajů je třeba kliknout na tlačítko "Dokončit" a poté se přepnout do okna "Excel".

Nyní víte, jak řešit problémy pro Excel pomocí maker. Schopnost používat vba excel (viz příklady programů výše) může být potřeba i pro práci v prostředí aktuálně nejpopulárnějšího textového editoru „Word“. Tlačítka menu můžete vytvářet zejména psaním, jak je znázorněno na samém začátku článku, nebo psaním kódu, díky kterému lze mnoho operací s textem provádět stisknutím funkčních kláves nebo přes záložku „Zobrazit“ a ikonu "Makra".

Pokud nejste silní v programování, ale zároveň potřebujete nějaké implementovatúkol, který přesahuje standardní funkce a příkazy MS Excel, můžete hledat řešení na internetu. Řešení je s největší pravděpodobností ve formě kódu VBA, který musíte zkopírovat a vložit do sešitu, pak nějak zajistit, aby vám tento kód fungoval, v tomto článku vám řeknu, jak na to.

Zvažte dva příklady:

1. Hledání a využití týmu

Potřebujeme například příkaz, který by do vybraných buněk vložil číslo „1“. Spustíme vyhledávač, zadáme hledaný výraz, získáme výsledky, začneme procházet, najdeme kód v něčem takovém:

Sub Insert1() Dim q As Object On Error Pokračovat Další sada q = Buňky pro každý q Ve výběru q = 1 Další q End Sub

Vyberte tento kód (bez číslování řádků, počínaje slovem Sub) a stiskněte ctrl+c. Přejděte do sešitu MS Excel a stiskněte klávesovou zkratku Alt+F11, budete mít otevřené okno editoru VBA:

V levém okně "Projekt - Projekt VBA" vyberte (klikněte) náš sešit, do kterého chcete vložit makro, například "VBAProject (Sešit2)":

V položce nabídky „Vložit“ vyberte „Modul“:

V levém okně "Projekt - VBA Project" byste měli mít novou složku "Modules" a v ní nový objekt "Module1":

Přesuňte kurzor do pravého vstupního pole "Velké" a stiskněte ctrl+v, zkopírované makro se vloží do modulu:

Makra v MS Excel lze vkládat na následující místa:

• V Modulu obvykle vkládají kód maker, která se spustí stisknutím tlačítka uživatelem (jako např. v našem případě) nebo kód funkcí (vzorce);
• Do Listu obvykle vloží kód maker, k jejichž spuštění by mělo dojít automaticky v závislosti na akcích uživatele nebo změnách dat v listu (data se změnila, makro bylo provedeno);
• Do Sešitu většinou vloží kód maker, k jejichž spuštění by mělo dojít automaticky v závislosti na akcích prováděných na knize (souboru). Například makro, které se spustí při otevření nebo zavření sešitu nebo při jeho uložení;
• Součástí uživatelského formuláře mohou být i makra.

Osoba, která kód publikuje, obvykle uvádí, kam má být vložen, do modulu, listu nebo sešitu.

Pro vložení kódu do listu vyberte odpovídající list v levém okně editoru VBA, dvakrát na něj klikněte levým tlačítkem myši, přesuňte kurzor do pravého vstupního pole a vložte kód.

Chcete-li vložit kód do knihy, vyberte „Tato kniha“:

Pojďme trénovat. Vložte níže uvedený kód do "Sheet1".

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox("Cell " & Target.Address & " = 2") End If a: Exit Sub End Sub

Toto makro zobrazí informační zprávu, pokud do libovolné buňky listu zadáte "2".

Vraťte se do sešitu, přejděte na "List1" a do buňky "A1" zadejte číslo "2" a stiskněte Vstupte, po kterém by se měla zobrazit následující zpráva:

Pokud vidíte tuto zprávu, udělali jste vše správně. Pokud ne, pak jste kód vložili někam na špatné místo, zkuste to znovu.

Při vkládání kódu je třeba dávat pozor, kam jej vkládáte. To lze provést pohledem na to, co je napsáno v názvu okna editoru VBA:

2. Hledání a používání funkce

Na internetu najdeme VBA kód funkce, která počítá například počet slov v buňce:

Veřejná funkce CountWordsInCell(Cell As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") CountWordsInCell = UBound(q) + 1 End Function

Zkopírujte kód, stiskněte klávesovou zkratku Alt+F11 otevře se editor VBA:

Do naší knihy přidáme nový modul a do tohoto modulu vložíme zkopírovaný kód:

Zavřete editor VBA a přejděte do sešitu. Vytvořte nový list (volitelné) v buňce A1 zadejte text "máma umývala rám." Stojíme v buňce, ve které chceme získat výsledek (počet slov), v nabídce "Vzorce" klikněte na tlačítko "Vložit funkci":

V okně "Vložit funkci", které se otevře, v poli "Kategorie" vyberte "Definováno uživatelem"

V seznamu dostupných funkcí vyberte „Počet slov v buňce“ a klikněte na „OK“:

Zadejte požadované argumenty a klikněte na „OK“:

Dostáváme výsledek:

Důležité:

Pokud sešit, do kterého jste makro vložili, neuložíte jako „sešit s podporou maker“, všechny moduly s makry se smažou a celou tuto práci budete muset později zopakovat.

Pokud při vkládání makra do modulu vidíte místo nějakého textu červené znaky " ???????? "

To znamená, že máte problém s kódováním, který je pozorován při kopírování textu v azbuce z některých prohlížečů. Chcete-li tento problém vyřešit, zkuste zkopírovaný kód vložit do prázdného listu MS Excel jako „Text Unicode“. Chcete-li to provést, přejděte do sešitu MS Excel, vyberte nebo vytvořte prázdný list, postavte se do buňky "A1" a stiskněte klávesové zkratky Ctrl+Alt+V. Měla by se objevit nabídka "Vložit jinak", vyberte "Text Unicode" a klikněte na "OK".

Kód bude nutné vložit do listu bez otazníků:

Poté znovu zkopírujte kód vložený do listu a vložte jej do modulu.

Pokud v editoru VBA nevidíte okno "Projekt - Projekt VBA", přejděte na kartu nabídky "Zobrazit" a v seznamu vyberte "Projektový průzkumník" nebo stiskněte klávesovou zkratku ctrl+r:

Makro pro zvýraznění na každém listu v aktivním sešitu buňky A1. Posouvá také obrazovku.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Vyberte Application.ScreenUpdating = True End Sub

Makro pro kopírování aktuálního listu zadaným počtem opakování. Užitečné pro testování některých maker - provedli změny, zkontrolovali je na kopii dat. Došly kopie – spusťte makro znovu

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Zadejte počet kopií aktuálního listu") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets) .Count) ActiveSheet .Name = "Kopírovat" & j Další j Application.ScreenUpdating = True End Sub

Vytvářejte listy s názvy ze zadaného rozsahu na listu

Sub CreateFromList() Ztlumit buňku jako rozsah pro každou buňku ve výběru Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Další buňka End Sub

Markros odesílá e-maily se zpožděním. Upravené makro z knihy Johna Walkenbacha Professional VBA Programming

Sub SendMail() Dim OutApp as Object Dim OutApp As Object Dim OutMail as Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Pokračovat dále s OutMail .To = " [e-mail chráněný]" .Subject = "Přehled prodeje" .Přílohy.Přidat "C:\Test.txt" .Body = "Tělo zprávy" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00 " .send ".Display pro vytvoření e-mailu a jeho otevření End With On Error Přejít na 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub

Mírně upravené makro obsahu od Nikolaje Pavlova.
Pokud kniha již obsahuje list Obsah, makro navrhne jeho odstranění. Pokud ne, vytvoří list "Obsah" a vloží odkazy s názvy listů

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False S ActiveWorkbook For Every Worksheet In ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" Then Answer = MsgBox("There is a sheet v sešitu s názvem Obsah. Delete it?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array (1)).Select Sheets.Add Sheets(1).Name = "Obsah" S ActiveWorkbook Pro každý list V ActiveWorkbook.Worksheets If sheet.Name<>"Obsah" Poté nastavte buňku = Worksheets(1).Buňky(list.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = list.Name End If Další list End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Třídění listů z průvodců VBA. Makro také třídí skryté listy. Nebude fungovat, pokud má kniha ochranu struktury

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " si zapamatujte stav viditelnosti každého listu a proveďte vše viditelné Pro každý iSht v ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Další S ActiveWorkbook " seřadit viditelné listy For i = 1 To .Sheets.Count - 1 For j = i + 1 To .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Potom .Sheets(j).Přesunout před:=.Sheets(i) Další j Další i Konec " obnovit počáteční stav viditelnosti každého listu Pro každý iSht v ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Další Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Importujte sloupce "Pole1" a "Pole2" z listu "List1" souboru Excel "C:\Manager.xls" přes ADODB připojení a vložte obsah počínaje buňkou A1 aktuálního listu

Tabulky Excel jsou jedním z nejoblíbenějších způsobů ukládání a organizace informací. Pokud implementujete makra v Excelu, pak se možnosti programu mohou téměř zdvojnásobit. Makra například pomáhají automatizovat rutinní procesy a urychlují monotónní práci. Často je používají specialisté, kteří překládají sestavy z 1C do Excelu. S pomocí vloženého kódu je lze snadno převést do jediné standardní šablony.

Co jsou makra Excelu?

Makra se používají nejen v tabulkách, ale v celém Microsoft Office. Jsou to kód generovaný pomocí Visual Basic for Applications. Pokud jste částečně obeznámeni s webovým programováním, můžete nakreslit analogii s Javascriptem. Makra Excelu dělají v podstatě totéž, co Javascript dělá s daty HTML.

Malá část toho, co makro dokáže v kancelářských aplikacích:

Zarovnejte dokument podle stylu a naformátujte jej;
. provádět operace s číselnými a textovými daty;
. uchýlit se k pomoci externích souborů (text, databáze a další);
. vytvářet nové dokumenty od začátku;
. kombinovat několik akcí a provádět je automaticky.

Chcete-li s tabulkou pracovat, můžete si ji vytvořit sami nebo si stáhnout hotovou verzi z internetu. Makra je třeba stahovat pouze z důvěryhodných stránek, jinak si můžete do Excelu zanést virus.

Jaká jsou nebezpečí maker v Excelu?

Poškozené soubory na PC;
. ukrást důvěrná data.

K zavlečení viru dochází při provozu jedné z kancelářských aplikací – Wordu nebo Excelu. Poté, co přestanete pracovat, virus v nich začne pracovat autonomně a infikuje s ním celý systém.

Dalším způsobem, jak škodlivé makro funguje, je zprostředkování vstřikování hrozeb do systému. V tomto případě se jedná o jakousi bránu pro zavedení a generování trojského softwaru. Nebude již řízen upraveným makrem, ale samotným operačním systémem, a proto se hrozba rozšiřuje. Ne vždy je možné takové viry vyléčit, někdy musíte kompletně změnit OC Windows nebo pevný disk PC (v závislosti na úkolech, které si hacker při psaní kódu nastavil).

Můžete si vybrat jakýkoli způsob ochrany proti takovým makrům. Zvýšení úrovně zabezpečení aplikace Excel je nejoblíbenější, ale v tomto případě již program nemusí podporovat užitečná makra vašeho autora. Nedůvěřujte souborům z neznámých zdrojů, protože mohou být infikovány. I makro získané z PC vašeho kolegy může být plné nebezpečí. Pokud vám přítel dal soubor, který podporuje použití maker, s nimiž budete pracovat, pak než povolíte systému jej používat, určete, jak toto makro obdržel.

Nejlepší možností je naučit se umění vytvářet makra pro vaše potřeby. V tomto případě se můžete plně zaručit za jejich kvalitu a bezpečnost.

Jak zaznamenat makra v Excelu

Nejjednodušší způsob, jak vytvořit makro, je programovat přímo v Excelu. K tomu budete potřebovat tabulku se zadanými údaji a trochu znalostí, které umožní záznam takového kódu v programu.

Vytváření začíná nabídkou "Zobrazit". V něm je třeba vybrat pozici "Makra" a příkaz "Nahrát makro". Dále musíte zadat název bez mezer, například "Formát" (pokud bude kód zodpovědný za změnu formátu textu nebo buněk).

Poté budou všechny vaše akce automaticky zaznamenány a převedeny do kódu makra. V dolní části dokumentu se objeví tlačítko s malým čtverečkem („Stop“), kliknutím na něj zastavíte nahrávání. Takže můžete automaticky zaznamenávat makra v Excelu, jejichž příklady můžete analyzovat později v procesu učení.

Jak povolit makra v Excelu

Chcete-li zaznamenané makro zahrnout do jiných dokumentů, musíte je po vymazání tabulky uložit. Formát dokumentu musí být nastaven na XLTM (který podporuje makra).

Chcete-li při příštím přístupu k dokumentu povolit makra v aplikaci Excel, musíte nejprve povolit jejich použití v rozbalovací horní liště. Poté je potřeba importovat data, která budou makrem upravovat, z běžné CSV tabulky. Na konci importu musíte znovu přejít do nabídky „Zobrazit“, tam vybrat „Makra“ a v seznamu najít název vámi vytvořeného kódu. Na konci klikněte na tlačítko "Spustit". Poté uvidíte, jak program podle dříve zadaného algoritmu provádí výpočty a formátuje dokument.

Jak zobrazit programový kód makra?

Všechna makra vytvořená v Excelu jsou psána automaticky, bez lidského zásahu. Program samostatně sestaví programový kód, který lze otevřít z tabulky voláním pomocí nabídky.

Chcete-li to provést, musíte postupně provádět příkazy:

Rozbalte kartu "Zobrazit";
. otevřete "Makra";
. vyberte název požadovaného makra;
. vyvolejte okno s jeho kódem pomocí tlačítka "Změnit".

V novém dialogovém okně můžete upravit a změnit makro. Uvidíte tam kód, který program automaticky zaznamenal, a libovolnou akci můžete přidat ručně. Po uložení informací při importu nových dat bude makro fungovat jinak.

Jak spustit makro po kliknutí na buňku

Řekněme, že chcete, aby se makro spustilo, když je vybrána buňka A1. Aby buňka vybraná v Excelu spustila makro, musíte do prvního modulu přidat kód programu:

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
If Target.Address = "$A$1" Pak: Zavolejte module1
konec sub

Nejlepší způsob, jak se naučit makra v tutoriálu Excel. Takových příkladů je nejeden a pro ty, kteří se se všemi možnostmi stolu teprve seznamují, je to nejlepší způsob, jak se naučit plně využívat jeho funkčnost.

Kde se mohu naučit pracovat s makry

Existuje několik způsobů, jak se naučit vytvářet složitější makra. Měly by se lišit logikou a jasně nakonfigurovaným sledem akcí. Taková makra se vytvářejí pouze ručním psaním kódu VBA. Chcete-li vytvořit složitá makra, budete muset strávit nějaký čas učením se složitosti programovacího jazyka Visual Basic.

Nyní, abyste mohli studovat všechny možnosti Excelu, bylo vytvořeno mnoho speciálních kurzů, které trvají několik týdnů, ale k jejich účasti budete muset cestovat do školicího centra nebo navštěvovat webináře (což není vždy vhodné kvůli časovému posunu). Ale efektivnější a jednodušší způsob, jak se naučit vytvářet makra v Excelu, je tutoriál s příklady. Můžete si to procvičit, kdykoli budete chtít, a podrobné návody vám pomohou vyvinout různé možnosti: od nejjednodušších kódů po ty nejsložitější. Microsoft Excel je univerzální tabulkový editor, se kterým můžete řešit mnoho složitých úkolů.

Příklady

Existují kódy, které umožňují automatizovat různé úkoly. Takže jako příklad zvažte makro vložení řádku. Vypadá to takto:

Dílčí makro1()

Začal jsem vytvářet tento web jako poznámkový blok o pohodlných funkcích Excelu. Kdykoli si můžete obnovit své znalosti o konkrétní funkci programu tím, že přejdete na web, a navíc můžete poslat odkaz na článek přátelům nebo kolegům, kteří požádají o pomoc s Excelem. Návštěvnost webu se poměrně rychle rozrostla a jak vidím, zaznamenané informace jsou užitečné nejen pro mě a pár dalších lidí, ale i pro mnoho lidí, které neznám a jsou vysoce hodnoceny vyhledávači. Pokud jsem si jistý ve schopnostech Excelu, pak se nemohu nazývat specialistou na VBA na vysoké úrovni. Proto bych rád vytvořil velký pomocný článek, kam si budu zapisovat užitečná makra, která často používám. Jsem si jistý, že taková sbírka funkcí VBA se bude hodit nejen mně.

Tato stránka již obsahuje několik článků o VBA, včetně , ale myslím, že by bylo užitečné mít na jednom místě spoustu často používaných kódů.

Vypněte zobrazování makro akcí na obrazovce jednoduchou funkcí. Tím se nejen vizuálně zlepší provádění, ale také se mnohem rychleji spustí makro.

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

Nezapomeňte funkci zahrnout na konec makra

Jak odstranit výběr kopie po spuštění makra?

Pokud přidělíte rozsah pro kopírování a vkládání do makra. Nezapomeňte na konci makra zakázat výběr.

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

Užitečná makra. Jak najít poslední řádek nebo sloupec rozsahu

Tato konstrukce vám pomůže snadno najít číslo posledního řádku nebo sloupce vyplněného rozsahu. Zvláště vhodné je použití v cyklech, nemusíte nastavovat 1000 řádků cyklu s okrajem, Excel sám najde, kde je konec rozsahu pomocí této konstrukce:

Sub makros1() Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "Zjistí číslo posledního řádku vyplněného rozsahu nLastCol= Cells(1, Columns.Count ).End(xlToLeft ).Column "Najde poslední vyplněné číslo sloupce rozsahu End Sub

Navíc proměnnou rovnou deklaruji jako Long (délka 2 147 483 647), abych se nedostal do situace, kdy oblíbené Integer nemusí stačit (32 767) pro velké tabulky.

Měli byste také věnovat pozornost tomu, že vyhledávání probíhá v prvním sloupci nebo prvním řádku, vyberte požadované číslo pro správnou definici.

Pro smyčku a kontrolu stavu ve smyčce

Pokud jste našli poslední čísla řádků a sloupců rozsahů, můžete je použít v smyčkách. Například musíte projít první sloupec a spočítat počet prázdných buněk.

Sub makros1() Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Count Variable Dim i As Long "Loop Variable mLastRow = Cells(Rows.Count, 1).End(xlUp).Row Kol = 0 For i = 1 To mLastRow If Cells(i, 1).Value = "" Then Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Zde je také použit Msgbox, s touto funkcí můžete zobrazit data v samostatném okně. Pro můj příklad by to vypadalo takto:

Počítání času provedení makra

MsgBox dává následující výsledek:

Je velmi vhodné sledovat dobu provádění maker pro následnou optimalizaci.

Jsou to užitečná makra, která používám velmi často. Tento seznam budu postupně rozšiřovat. Veškerá efektivní automatizace!