24 užitečných příkladů maker aplikace Excel pro začátečníky VBA (připraveno k použití)

Používání maker aplikace Excel může zrychlit práci a ušetřit vám spoustu času.

Jedním ze způsobů získání kódu VBA je zaznamenat makro a převzít kód, který generuje. Tento kód podle záznamu makra je však často plný kódu, který ve skutečnosti není potřeba. Makro rekordér má také určitá omezení.

Vyplatí se tedy mít sbírku užitečných kódů maker VBA, které můžete mít v zadní kapse a používat je, když je potřeba.

Zatímco psaní kódu makra aplikace Excel VBA může zpočátku nějakou dobu trvat, jakmile bude hotový, můžete jej mít k dispozici jako referenci a použít jej, kdykoli jej budete potřebovat příště.

V tomto rozsáhlém článku uvedu několik užitečných příkladů excelových maker, které často potřebuji a které mám schované v soukromém trezoru.

Tento tutoriál budu průběžně aktualizovat o další příklady maker. Pokud si myslíte, že by něco mělo být na seznamu, zanechte komentář.

Tuto stránku si můžete uložit do záložek pro budoucí použití.

Nyní, než se dostanu k příkladu makra a dám vám kód VBA, nejprve vám ukážu, jak tyto ukázkové kódy používat.

Použití příkladu makra kódu z Excelu

Zde jsou kroky, které musíte dodržet, abyste mohli použít kód z některého z příkladů:

• Otevřete sešit, ve kterém chcete použít makro.
• Podržte klávesu ALT a stiskněte F11. Tím se otevře editor VB.
• Klepněte pravým tlačítkem na libovolný objekt v Průzkumníkovi projektu.
• Přejděte na Vložit -> Modul.
• Zkopírujte a vložte kód do okna Kód modulu.

V případě, že příklad říká, že potřebujete vložit kód do okna kódu listu, dvakrát klikněte na objekt listu a zkopírujte vložení kódu do okna kódu.

Jakmile kód vložíte do sešitu, musíte jej uložit s příponou .XLSM nebo .XLS.

Jak spustit makro

Jakmile zkopírujete kód v editoru VB, postupujte podle následujících kroků ke spuštění makra:

• Přejděte na kartu Vývojář.
• Klikněte na makra.

• V dialogovém okně Makro vyberte makro, které chcete spustit.
• Klikněte na tlačítko Spustit.

V případě, že na pásu karet nemůžete najít kartu vývojáře, přečtěte si tento návod, jak se k ní dostat.

Související výukový program: Různé způsoby spuštění makra v aplikaci Excel.

V případě, že je kód vložen do okna kódu listu, nemusíte se starat o spuštění kódu. Automaticky se spustí, když dojde k zadané akci.

Nyní se podívejme na užitečné příklady maker, které vám mohou pomoci automatizovat práci a ušetřit čas.

Poznámka: Najdete mnoho příkladů apostrofu (‘) následovaného řádkem nebo dvěma. Toto jsou komentáře, které jsou při spuštění kódu ignorovány a jsou umístěny jako poznámky pro sebe/čtenáře.

V případě, že v článku nebo v kódu najdete nějakou chybu, buďte úžasní a dejte mi vědět.

Příklady maker aplikace Excel

V tomto článku jsou popsány níže uvedené příklady maker:

Odhalte všechny pracovní listy najednou

Pokud pracujete v sešitu, který má více skrytých listů, musíte tyto listy odkrýt jeden po druhém. V případě, že existuje mnoho skrytých listů, může to chvíli trvat.

Zde je kód, který odkryje všechny listy v sešitu.

'Tento kód odkryje všechny listy v sešitu Sub UnhideAllWoksheets () Dim ws As Worksheet For each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub

Výše uvedený kód používá smyčku VBA (pro každého) k procházení jednotlivými listy v sešitu. Potom změní viditelnou vlastnost listu na viditelnou.

Zde je podrobný návod, jak používat různé metody k odkrytí listů v aplikaci Excel.

Skrýt všechny pracovní listy kromě aktivního listu

Pokud pracujete na sestavě nebo na řídicím panelu a chcete skrýt celý list kromě toho, který obsahuje sestavu/řídicí panel, můžete použít tento kód makra.

`` Toto makro skryje veškerý list kromě aktivního listu Sub HideAllExceptActiveSheet () Dim ws As Worksheet for each ws In ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

Seřadit listy abecedně pomocí VBA

Pokud máte sešit s mnoha listy a chcete je seřadit podle abecedy, může se vám tento kód makra opravdu hodit. To může být případ, pokud máte názvy listů jako roky nebo jména zaměstnanců nebo názvy produktů.

`` Tento kód seřadí listy podle abecedy Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets (j) .Name <Sheets (i) .Name Then Sheets (j). Move before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Chraňte všechny pracovní listy najednou

Pokud máte v sešitu mnoho listů a chcete chránit všechny listy, můžete použít tento kód makra.

Umožňuje zadat heslo v kódu. Toto heslo budete potřebovat k odemknutí listu.

'Tento kód bude chránit všechny listy najednou Sub ProtectAllSheets () Dim ws As Worksheet Dim heslo As String heslo = "Test123"' nahradí Test123 heslem, které chcete Pro každé ws V pracovních listech ws.Chraňte heslo: = heslo Další ws End Sub

Odemkněte všechny pracovní listy najednou

Pokud máte některé nebo všechny listy chráněné, můžete pro jejich ochranu jednoduše použít mírnou úpravu kódu použitého k ochraně listů.

'Tento kód bude chránit všechny listy najednou Sub ProtectAllSheets () Dim ws As Worksheet Dim heslo As String heslo = "Test123"' nahradí Test123 heslem, které chcete Pro každé w V listech ws. Odemknout heslo: = heslo Další ws End Sub

Heslo musí být stejné jako heslo, které bylo použito k uzamčení listů. Pokud tomu tak není, zobrazí se chyba.

Odkryjte všechny řádky a sloupce

Tento kód makra odkryje všechny skryté řádky a sloupce.

To může být velmi užitečné, pokud získáte soubor od někoho jiného a chcete si být jisti, že neexistují žádné skryté řádky/sloupce.

'Tento kód odkryje všechny řádky a sloupce v Sub listu Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Slučte všechny sloučené buňky

Je běžnou praxí sloučit buňky, aby se z nich stala jedna. Zatímco to funguje, při sloučení buněk nebudete moci data třídit.

V případě, že pracujete s listem se sloučenými buňkami, použijte níže uvedený kód ke sloučení všech sloučených buněk najednou.

'Tento kód sloučí všechny sloučené buňky Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub

Všimněte si, že místo Sloučit a Vycentrovat doporučuji použít možnost Střed přes výběr.

Uložte sešit s časovým razítkem v názvu

Mnoho času možná budete muset vytvořit verze své práce. Jsou velmi užitečné v dlouhých projektech, kde pracujete se souborem v průběhu času.

Osvědčeným postupem je uložit soubor s časovými razítky.

Použití časových razítek vám umožní vrátit se k určitému souboru a zjistit, jaké změny byly provedeny nebo jaká data byla použita.

Zde je kód, který sešit automaticky uloží do zadané složky a při každém uložení přidá časové razítko.

'Tento kód uloží soubor s časovým razítkem v názvu Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub

Musíte zadat umístění složky a název souboru.

Ve výše uvedeném kódu je „C: UsersUsernameDesktop umístění složky, které jsem použil. Musíte zadat umístění složky, kam chcete soubor uložit. Jako předponu názvu souboru jsem také použil obecný název „Název sešitu“. Můžete určit něco, co souvisí s vaším projektem nebo společností.

Uložte každý list jako samostatný soubor PDF

Pokud pracujete s daty pro různé roky nebo divize nebo produkty, možná budete muset uložit různé listy jako soubory PDF.

I když to může být časově náročný proces, pokud se provádí ručně, VBA to může opravdu urychlit.

Zde je kód VBA, který uloží každý list jako samostatný PDF.

„Tento kód uloží každý pracovní list jako samostatný dílčí soubor PDF SaveWorkshetAsPDF () Dim ws As Worksheet for each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ws.Name &" .pdf "Next ws End Sub Sub

Ve výše uvedeném kódu jsem zadal adresu umístění složky, do které chci ukládat soubory PDF. Každý soubor PDF bude mít stejný název jako název listu. Toto umístění složky budete muset upravit (pokud vaše jméno není také Sumit a neuložíte jej do testovací složky na ploše).

Všimněte si, že tento kód funguje pouze pro listy (a ne pro listy grafů).

Uložte každý list jako samostatný soubor PDF

Zde je kód, který uloží celý váš sešit jako PDF do zadané složky.

„Tento kód uloží celý sešit jako PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ThisWorkbook.Name &" .pdf "End Sub

Chcete -li použít tento kód, budete muset změnit umístění složky.

Převeďte všechny vzorce na hodnoty

Tento kód použijte, pokud máte list, který obsahuje mnoho vzorců a chcete tyto vzorce převést na hodnoty.

'Tento kód převede všechny vzorce na hodnoty Sub ConvertToValues ​​() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Tento kód automaticky identifikuje, že jsou použity buňky, a převede jej na hodnoty.

Chraňte/zamkněte buňky pomocí vzorců

Pokud máte spoustu výpočtů a nechcete je omylem smazat nebo změnit, můžete chtít zamknout buňky pomocí vzorců.

Zde je kód, který uzamkne všechny buňky, které mají vzorce, zatímco všechny ostatní buňky nejsou uzamčeny.

`` Tento kód makra uzamkne všechny buňky pomocí vzorců Sub LockCellsWithFormulas () s ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub

Související výukový program: Jak zamknout buňky v aplikaci Excel.

Chraňte všechny pracovní listy v sešitu

Pomocí níže uvedeného kódu můžete chránit všechny listy v sešitu najednou.

'Tento kód ochrání všechny listy v sešitu Sub ProtectAllSheets () Dim ws As Worksheet for each ws In Worksheets ws.Protect Next ws End Sub

Tento kód projde všechny pracovní listy jeden po druhém a bude jej chránit.

V případě, že chcete zrušit ochranu všech listů, použijte v kódu místo ws.Unprotect místo ws.Protect.

Vložit řádek za každý další řádek ve výběru

Tento kód použijte, pokud chcete za každý řádek ve vybraném rozsahu vložit prázdný řádek.

'Tento kód vloží řádek za každý řádek ve výběru Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Vložte ActiveCell.Offset (2, 0). Vyberte Další i Konec Sub

Podobně můžete tento kód upravit tak, aby po každém sloupci ve vybraném rozsahu vložil prázdný sloupec.

Automaticky vložit datum a časové razítko do sousední buňky

Časové razítko je něco, co používáte, když chcete sledovat aktivity.

Můžete například chtít sledovat činnosti, jako například kdy byly vynaloženy konkrétní výdaje, kdy byla vytvořena prodejní faktura, kdy bylo zadání dat provedeno v buňce, kdy byla sestava naposledy aktualizována atd.

Tento kód použijte k vložení razítka data a času do sousední buňky při zadávání záznamu nebo při úpravách stávajícího obsahu.

'Tento kód vloží časové razítko do sousední buňky Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Format (Now (), "dd-mm-rrrr hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub

Všimněte si, že tento kód musíte vložit do okna s kódem listu (a ne do okna s kódem modulu, jak jsme to dosud dělali v jiných příkladech maker aplikace Excel). Chcete -li to provést, poklepejte v editoru VB na název listu, pro který chcete tuto funkci. Poté zkopírujte a vložte tento kód do okna kódu tohoto listu.

Tento kód také funguje, když je zadávání dat provedeno ve sloupci A (kód má řádek Target.Column = 1). Můžete to odpovídajícím způsobem změnit.

Ve výběru zvýrazněte alternativní řádky

Zvýraznění alternativních řádků může ohromně zvýšit čitelnost vašich dat. To může být užitečné, když potřebujete vytisknout a projít data.

Zde je kód, který okamžitě zvýrazní alternativní řádky ve výběru.

'Tento kód zvýrazní alternativní řádky ve výběru Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Pokud Next Myrow End Sub

Všimněte si, že jsem v kódu zadal barvu jako vbCyan. Můžete zadat i jiné barvy (například vbRed, vbGreen, vbBlue).

Zvýrazněte buňky chybně napsanými slovy

Excel nemá kontrolu pravopisu jako ve Wordu nebo PowerPointu. I když můžete kontrolu pravopisu spustit stisknutím klávesy F7, v případě pravopisné chyby neexistuje žádné vizuální znamení.

Pomocí tohoto kódu okamžitě zvýrazněte všechny buňky, ve kterých je pravopisná chyba.

'Tento kód zvýrazní buňky, které mají chybně napsaná slova Sub HighlightMisspelledCells () Dim cl jako rozsah pro každý cl v ActiveSheet.UsedRange, pokud není Application.CheckSpelling (slovo: = cl.Text) Potom cl.Interior.Color = vbRed Konec Pokud Další cl End Sub

Buňky, které jsou zvýrazněny, jsou buňky s textem, který aplikace Excel považuje za pravopisnou chybu. V mnoha případech také zvýrazní názvy nebo výrazy značek, kterým nerozumí.

Obnovte všechny kontingenční tabulky v sešitu

Pokud máte v sešitu více než jednu kontingenční tabulku, můžete tento kód použít k aktualizaci všech těchto kontingenčních tabulek najednou.

`` Tento kód obnoví všechny kontingenční tabulky v dílčím sešitu RefreshAllPivotTables () Dim PT jako kontingenční tabulku pro každý PT v ActiveSheet.PivotTables PT.RefreshTable Další PT End Sub

Zde si můžete přečíst více o aktualizaci kontingenčních tabulek.

Změňte velikost písmen vybraných buněk na velká písmena

Zatímco Excel má vzorce pro změnu velkých a malých písmen textu, můžete to udělat v jiné sadě buněk.

Pomocí tohoto kódu můžete okamžitě změnit velká a malá písmena textu ve vybraném textu.

`` Tento kód změní výběr na velká písmena Sub SubCaseCase () Dim Rng jako rozsah pro každý Rng ve výběru.Cells If Rng.HasFormula = False Then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub

Všimněte si, že v tomto případě jsem použil UCase k tomu, aby byl textový případ Upper. LCase můžete použít pro malá písmena.

Zvýrazněte všechny buňky pomocí komentářů

Pomocí níže uvedeného kódu zvýrazněte všechny buňky, které obsahují komentáře.

"Tento kód zvýrazní buňky, které mají komentáře" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub

V tomto případě jsem použil vbBlue, abych dal buňkám modrou barvu. Pokud chcete, můžete to změnit na jiné barvy.

Zvýrazněte prázdné buňky pomocí VBA

I když můžete zvýraznit prázdnou buňku podmíněným formátováním nebo pomocí dialogového okna Přejít na speciální, pokud to musíte dělat poměrně často, je lepší použít makro.

Po vytvoření můžete toto makro mít na panelu nástrojů Rychlý přístup nebo jej uložit do osobního sešitu maker.

Zde je kód makra VBA:

'Tento kód zvýrazní všechny prázdné buňky v datové sadě Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub

V tomto kódu jsem určil prázdné buňky, které mají být zvýrazněny červenou barvou. Můžete si vybrat další barvy, jako je modrá, žlutá, azurová atd.

Jak třídit data podle jednoho sloupce

Pomocí níže uvedeného kódu můžete data seřadit podle zadaného sloupce.

Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub

Všimněte si, že jsem vytvořil pojmenovaný rozsah s názvem „DataRange“ a použil jsem ho místo odkazů na buňky.

Zde jsou také použity tři klíčové parametry:

• Key1 - Toto je místo, na kterém chcete třídit datovou sadu. Ve výše uvedeném příkladu kódu budou data seřazeny podle hodnot ve sloupci A.
• Pořadí- Zde musíte určit, zda chcete data řadit vzestupně nebo sestupně.
• Záhlaví - Zde musíte určit, zda vaše data mají záhlaví nebo ne.

Přečtěte si více o tom, jak třídit data v aplikaci Excel pomocí VBA.

Jak třídit data podle více sloupců

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

Níže je uveden kód, který bude data třídit na základě více sloupců:

Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Záhlaví = xlAno. Použít Konec S Koncovou Sub

Všimněte si, že zde jsem určil první řazení podle sloupce A a poté podle sloupce B.

Výstupem by bylo něco, jak je uvedeno níže:

Jak získat z řetězce v Excelu pouze číselnou část

Pokud chcete z řetězce extrahovat pouze číselnou část nebo pouze textovou část, můžete ve VBA vytvořit vlastní funkci.

Tuto funkci VBA pak můžete použít v listu (stejně jako běžné funkce Excelu) a z řetězce extrahuje pouze číselnou nebo textovou část.

Něco, jak je uvedeno níže:

Níže je kód VBA, který vytvoří funkci pro extrahování číselné části z řetězce:

`` Tento kód VBA vytvoří funkci pro získání číselné části z řetězce. ) Pak Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Result End Function

Potřebujete místo v kódu v modulu a pak můžete použít funkci = GetNumeric v listu.

Tato funkce převezme pouze jeden argument, kterým je odkaz na buňku buňky, ze které chcete získat číselnou část.

Podobně níže je funkce, která vám poskytne pouze textovou část z řetězce v aplikaci Excel:

'Tento kód VBA vytvoří funkci pro získání textové části z řetězce. 1))) Potom Result = Result & Mid (CellRef, i, 1) Next i GetText = Result End Function

Toto jsou tedy některé z užitečných kódů maker aplikace Excel, které můžete použít při každodenní práci k automatizaci úkolů a ke zvýšení produktivity.