24 užitočných príkladov makier Excel pre začiatočníkov VBA (pripravené na použitie)

Používanie makier v Exceli môže urýchliť vašu prácu a ušetriť veľa času.

Jedným zo spôsobov, ako získať kód VBA, je zaznamenať makro a získať kód, ktorý vygeneruje.Kód makro rekordéra je však často plný kódu, ktorý v skutočnosti nie je potrebný.Makrorekordér má tiež určité obmedzenia.

Preto sa oplatí mať užitočnú sadu VBA makier, ktoré môžete mať v zadnom vrecku a použiť ich, keď ich budete potrebovať.

Hoci kódovanie makra Excel VBA môže spočiatku trvať nejaký čas, po dokončení ho môžete použiť ako referenciu a použiť ho nabudúce, keď ho budete potrebovať.

V tomto rozsiahlom článku uvediem niekoľko užitočných príkladov makier Excel, ktoré často potrebujem a uchovávam ich vo svojom súkromnom trezore.

Tento návod budem naďalej aktualizovať o ďalšie príklady makier.Prosím, zanechajte komentár, ak si myslíte, že by niečo malo byť na zozname.

Túto stránku si môžete uložiť ako záložku pre budúce použitie.

Teraz, skôr ako sa pustím do príkladov makier a poskytnem vám kód VBA, dovoľte mi najprv ukázať vám, ako použiť vzorový kód.

Použitie kódu z príkladu makra Excel

Ak chcete použiť kód z ktoréhokoľvek z príkladov, postupujte takto:

• Otvorte zošit, v ktorom chcete použiť makro.
• Podržte kláves ALT a stlačte F11.Toto sa otvoríEditor VB.
• Kliknite pravým tlačidlom myši na ľubovoľný objekt v Prieskumníkovi projektu.
• Prejdite na Vložiť -> Moduly.
• Skopírujte a prilepte kód do okna kódu modulu.

Ak príklad hovorí, že musíte vložiť kód do okna kódu pracovného hárka, dvakrát kliknite na objekt hárka a skopírujte a vložte kód do okna kódu.

Po vložení kódu do zošita je potrebné ho uložiť s príponou .XLSM alebo .XLS.

Ako spustiť makro

Po skopírovaní kódu v editore VB sú kroky na spustenie makra nasledovné:

• Prejdite na kartu vývojár.
• Kliknite na položku Makro.

• V dialógovom okne Makrá vyberte makro, ktoré chcete spustiť.
• Kliknite na tlačidlo Spustiť.

Ak nemôžete nájsť kartu vývojára na páse s nástrojmi, prečítajte si tento návod a zistite, ako ju získať.

Ak je kód vložený do okna kódu pracovného hárka, nemusíte si robiť starosti so spustením kódu.Spustí sa automaticky, keď nastane zadaná akcia.

Teraz sa pozrime na užitočné príklady makier, ktoré vám môžu pomôcť zautomatizovať prácu a ušetriť čas.

Poznámka: Nájdete veľa apostrofov ('), za ktorými nasleduje jeden alebo dva riadky.Toto sú komentáre, ktoré sa pri spustení kódu ignorujú a umiestnia sa ako komentáre seba/čitatelov.

Ak nájdete nejaké chyby v článku alebo kóde, dajte mi vedieť.

Príklad makra programu Excel

Tento článok popisuje nasledujúce príklady makier:

Zobrazte všetky pracovné hárky naraz

Ak pracujete na zošite s viacerými skrytými hárkami, musíte tieto hárky jeden po druhom odkryť.Ak existuje veľa skrytých listov, môže to chvíľu trvať.

Tu je kód na odkrytie všetkých hárkov v zošite.

'Tento kód odkryje všetky hárky v zošite Sub UnhideAllWoksheets() Dim ws As Worksheet For Every ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

Vyššie uvedený kód používa slučku VBA (pre každého) na iteráciu cez každý pracovný hárok v zošite.Potom zmení viditeľnú vlastnosť listu na viditeľnú.

Tu je podrobný návod, ako odkryť pracovný hárok v Exceli pomocou rôznych metód.

Skryť všetky pracovné hárky okrem aktívneho pracovného hárka

Tento kód makra je možné použiť, ak pracujete na zostave alebo dashboarde a chcete skryť všetky hárky okrem hárka, ktorý obsahuje zostavu alebo dashboard.

'Toto makro skryje celý pracovný hárok okrem aktívneho hárka 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

Zoraďte pracovný hárok podľa abecedy pomocou VBA

Tento kód makra sa môže hodiť, ak máte zošit s mnohými hárkami a chcete tieto hárky zoradiť podľa abecedy.To sa môže stať, ak máte názov listu ako rok alebo meno zamestnanca alebo názov produktu.

'Tento kód zoradí hárky abecedne 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

Chráňte všetky pracovné hárky naraz

Ak máte v zošite veľa hárkov a chcete chrániť všetky hárky, môžete použiť tento kód makra.

Umožňuje vám zadať heslo v kóde.Toto heslo budete potrebovať na odblokovanie hárku.

'Tento kód ochráni všetky hárky naraz Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'nahraďte Test123 heslom, ktoré chcete Pre každý ws In Worksheets ws.Protect password:=password Next ws End Sub

Zrušte ochranu všetkých pracovných hárkov naraz

Ak ste chránili niektoré alebo všetky svoje pracovné hárky, môžete ich zrušiť iba malou úpravou kódu použitého na ochranu pracovných hárkov.

'Tento kód ochráni všetky hárky naraz Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'nahraďte Test123 heslom, ktoré chcete Pre každý ws In Worksheets ws.Unprotect password:=password Next ws End Sub

Upozorňujeme, že heslo musí byť rovnaké ako na uzamknutie hárka.Ak nie, zobrazí sa chyba.

Odkryť všetky riadky a stĺpce

Tento kód makra zobrazí všetky skryté riadky a stĺpce.

To môže byť užitočné, ak preberáte súbor od niekoho iného a chcete sa uistiť, že neobsahuje žiadne skryté riadky/stĺpce.

'Tento kód odkryje všetky riadky a stĺpce v Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Zrušte zlúčenie všetkých zlúčených buniek

Spájanie buniek do jednej je bežnou praxou.Kým to funguje, nebudete môcť zoradiť údaje, keď sa bunky zlúčia.

Ak používate pracovný hárok so zlúčenými bunkami, použite kód nižšie na zrušenie zlúčenia všetkých zlúčených buniek naraz.

'Tento kód zruší zlúčenie všetkých zlúčených buniek Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Všimnite si, že namiesto možnosti „Zlúčiť a vycentrovať“ odporúčam použiť možnosť „Výber na stred“.

Uložiť zošit s časovou pečiatkou v názve

Mnohokrát môže byť potrebné vytvoriť pracovnú verziu.Sú skvelé pre dlhodobé projekty, ktoré sa zaoberajú súbormi v priebehu času.

Osvedčeným postupom je uložiť súbor s časovou pečiatkou.

Použitie časovej pečiatky vám umožní vrátiť sa k súboru, aby ste videli, aké zmeny boli vykonané alebo aké údaje boli použité.

Tu je kód, ktorý automaticky uloží zošit do určeného priečinka a pri ukladaní pridá časovú pečiatku.

'Tento kód uloží súbor s časovou pečiatkou vo svojom názve Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & časová pečiatka End Sub

Musíte zadať umiestnenie priečinka a názov súboru.

Vo vyššie uvedenom kóde „C:UsersUsernameDesktop je umiestnenie priečinka, ktoré používam.Musíte zadať umiestnenie priečinka, do ktorého chcete súbor uložiť.Tiež som použil všeobecný názov "WorkbookName" ako predponu súboru.Môžete špecifikovať obsah súvisiaci s vaším projektom alebo spoločnosťou.

Uložte každý pracovný hárok ako samostatný súbor PDF

Ak používate údaje z rôznych rokov alebo oddelení alebo produktov, možno budete musieť uložiť rôzne pracovné hárky ako súbory PDF.

Aj keď to môže byť časovo náročný proces, ak sa vykonáva manuálne, VBA veci urýchli.

Tu je kód VBA, ktorý uloží každý pracovný hárok ako samostatný súbor PDF.

'Tento kód uloží každý pracovný hárok ako samostatný PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

Vo vyššie uvedenom kóde som zadal adresu umiestnenia priečinka, kam chcem uložiť PDF.Každé PDF bude mať rovnaký názov ako hárok.Budete musieť upraviť umiestnenie tohto priečinka (pokiaľ sa tiež nevoláte Sumit a neuložili ste ho do testovacieho priečinka na pracovnej ploche).

Upozorňujeme, že tento kód funguje iba pre pracovné hárky (nie pre hárky s grafmi).

Uložte každý pracovný hárok ako samostatný súbor PDF

Tu je kód na uloženie celého zošita ako PDF do určeného priečinka.

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

Ak chcete použiť tento kód, musíte zmeniť umiestnenie priečinka.

Previesť všetky vzorce na hodnoty

Tento kód použite, keď máte pracovný hárok s množstvom vzorcov a chcete tieto vzorce previesť na hodnoty.

'Tento kód prevedie všetky vzorce na hodnoty ​​Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Tento kód automaticky rozpozná použitú bunku a prevedie ju na hodnotu.

Chráňte/uzamknite bunky pomocou vzorcov

Keď máte veľa výpočtov a nechcete ich náhodne vymazať alebo zmeniť, možno budete chcieť uzamknúť bunky pomocou vzorcov.

Tu je kód, ktorý uzamkne všetky bunky pomocou vzorcov a všetky ostatné bunky ponechá odomknuté.

'Tento kód makra uzamkne všetky bunky pomocou vzorcov Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Súvisiaci návod: Ako uzamknúť bunky v Exceli.

Chráňte všetky listy v zošite

Na ochranu všetkých hárkov v zošite naraz použite nasledujúci kód.

'Tento kód ochráni všetky hárky v zošite Sub ProtectAllSheets() Dim ws As Worksheet For Every ws In Worksheets ws. Protect Next ws End Sub

Tento kód prejde postupne všetkými hárkami a ochráni ich.

Ak chcete zrušiť ochranu všetkých hárkov, použite v kóde ws.Unprotect namiesto ws.Protect.

Vložte riadok za každý riadok výberu

Tento kód použite, ak chcete vložiť prázdny riadok za každý riadok vo vybratom rozsahu.

'Tento kód vloží riadok za každý riadok vo výbere 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 Ďalej a Koniec

Opäť môžete tento kód upraviť tak, aby sa za každý stĺpec vo vybranom rozsahu vložil prázdny stĺpec.

Automaticky vložiť dátum a časovú pečiatku do susedných buniek

Časové pečiatky sú to, čo používate, keď chcete sledovať aktivitu.

Môžete napríklad chcieť sledovať aktivity, ako napríklad kedy vznikol konkrétny výdavok, kedy bola vytvorená predajná faktúra, kedy boli zadané údaje do bunky, kedy bola zostava naposledy aktualizovaná atď.

Tento kód použite na vloženie dátumovej a časovej pečiatky do susedných buniek pri písaní alebo úprave existujúceho obsahu.

'Tento kód vloží časovú pečiatku do susednej bunky 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-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Upozorňujeme, že tento kód musíte vložiť do okna kódu pracovného hárka (nie do okna kódu modulu, ako sme to doteraz robili v iných príkladoch makier programu Excel).Ak to chcete urobiť, v editore VB dvakrát kliknite na názov hárka, pre ktorý chcete túto funkciu.Potom skopírujte a prilepte tento kód do okna kódu na tomto hárku.

Tento kód funguje aj vtedy, keď sa údaje zadávajú do stĺpca A (všimnite si, že kód má Target.Column = 1 riadok).Podľa toho ho môžete zmeniť.

Zvýraznite alternatívne riadky vo výbere

Zvýraznenie striedajúcich sa čiar môže výrazne zlepšiť čitateľnosť vašich údajov.To môže byť užitočné, keď si ho potrebujete vytlačiť a prezerať údaje.

Tu je kód, ktorý okamžite zvýrazní alternatívne riadky vo výbere.

'Tento kód by zvýraznil alternatívne riadky vo výbere Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Výber pre každý Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Ak Next Myrow End Sub

Všimnite si, že som v kóde špecifikoval farbu ako vbCyan.Môžete zadať aj iné farby (napr. vbRed, vbGreen, vbBlue).

Zvýraznite nesprávne napísané bunky

Excel nemá kontrolu pravopisu vo Worde alebo PowerPointe.Aj keď môžete spustiť kontrolu pravopisu stlačením klávesu F7, neexistuje žiadna vizuálna pomôcka pre pravopisné chyby.

Použite tento kód na okamžité zvýraznenie všetkých nesprávne napísaných buniek.

'Tento kód zvýrazní bunky, ktoré obsahujú nesprávne napísané slová Sub HighlightMissspelCells() Dim cl Ako rozsah pre každý cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

Upozorňujeme, že zvýraznené bunky sú bunky s textom, ktorý Excel považuje za nesprávne napísaný.V mnohých prípadoch tiež zvýrazňuje názvy alebo výrazy značky, ktorým nerozumie.

Obnovte všetky kontingenčné tabuľky v zošite

Ak máte v zošite viacero kontingenčných tabuliek, tento kód môžete použiť na obnovenie všetkých naraz.

'Tento kód obnoví celú kontingenčnú tabuľku v zošite Sub RefreshAllPivotTables() Dim PT ako kontingenčnú tabuľku pre každý PT v ActiveSheet.PivotTables PT.RefreshTable Ďalší PT End Sub

Viac o obnovovaní kontingenčných tabuliek si môžete prečítať tu.

Zmeňte veľkosť písmen vo vybratých bunkách na veľké

Aj keď má Excel vzorce na zmenu malých a veľkých písmen v texte, umožňuje vám to urobiť v inej skupine buniek.

Použite tento kód na okamžitú zmenu veľkosti písmen textu vo vybratom texte.

'Tento kód zmení výber na veľké písmená Sub ChangeCase() Dim Rng As Range pre každý Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Všimnite si, že v tomto prípade používam UCase na nastavenie veľkých písmen.Pre malé písmená môžete použiť LCase.

Zvýraznite všetky bunky s komentármi

Pomocou nasledujúceho kódu zvýraznite všetky bunky s komentármi.

"Tento kód zvýrazní bunky s komentármi" Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

V tomto prípade používam vbBlue, aby bunka získala modrú farbu.V prípade potreby ho môžete zmeniť na inú farbu.

Zvýraznite prázdne bunky pomocou VBA

Aj keď môžete použiť podmienené formátovanie alebo použiť dialógové okno Prejsť na špeciálne na zvýraznenie prázdnych buniek, ak to musíte robiť často, je najlepšie použiť makro.

Po vytvorení môžete toto makro mať na paneli s nástrojmi Rýchly prístup alebo si ho uložiť do osobného zošita makier.

Tu je kód makra VBA:

'Tento kód zvýrazní všetky prázdne bunky v množine údajov Sub HighlightBlankCells() Dim Dataset ako množina údajov množina údajov = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

V tomto kóde som špecifikoval prázdne bunky zvýraznené červenou farbou.Môžete si vybrať iné farby, ako je modrá, žltá, azúrová atď.

Ako zoradiť údaje podľa jedného stĺpca

Na zoradenie údajov podľa určeného stĺpca môžete použiť nasledujúci kód.

Sub SortDataHeader() 
Rozsah("DataRange").Kľúč zoradenia1:=Rozsah("A1"), Poradie1:=xlVzostupne, Hlavička:=xlÁno 
End Sub

Všimnite si, že som vytvoril pomenovaný rozsah s názvom „DataRange“ a použil som ho namiesto odkazu na bunku.

Používajú sa tu aj tri kľúčové parametre:

• Key1 – Toto je kľúč, podľa ktorého chcete triediť množinu údajov.Vo vyššie uvedenom vzorovom kóde budú údaje zoradené na základe hodnôt v stĺpci A.
• Poradie1 – Tu musíte určiť, či chcete údaje zoradiť vzostupne alebo zostupne.
• Hlavička – Tu musíte určiť, či vaše údaje majú hlavičku alebo nie.

Prečítajte si viac o tom, ako triediť údaje v Exceli pomocou VBA.

Ako triediť údaje podľa viacerých stĺpcov

Predpokladajme, že máte množinu údajov, ktorá vyzerá takto:

Tu je kód na triedenie údajov na základe viacerých stĺpcov:

Sub SortMultipleColumns() s ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1") :C13") .Header = xlYes .Apply End With End Sub

Všimnite si, že tu som určil, aby sa triedilo najskôr podľa stĺpca A a potom podľa stĺpca B.

Výstup bude vyzerať takto:

Ako získať iba číselnú časť z reťazca v Exceli

Ak chcete z reťazca iba extrahovať číselnú časť alebo textovú časť, môžete vo VBA vytvoriť vlastnú funkciu.

Potom môžete použiť túto funkciu VBA v pracovnom hárku (rovnako ako bežná funkcia Excelu) a z reťazca vytiahne iba číslo alebo textovú časť.

Ako je ukázané nižšie:

Tu je kód VBA, ktorý vytvorí funkciu, ktorá extrahuje číselnú časť z reťazca:

'Tento kód VBA vytvorí funkciu na získanie číselnej časti z reťazca Funkcia GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Potom Výsledok = Výsledok & Stred (CellRef, i, 1) Ďalej i GetNumeric = Funkcia konca výsledku

Kód musíte vložiť do modulu a potom môžete použiť funkciu =GetNumeric v pracovnom hárku.

Táto funkcia bude mať iba jeden parameter, odkaz na bunku, z ktorej sa získa číselná časť.

Podobne funkcia nižšie získa iba textovú časť z reťazca v Exceli:

'Tento kód VBA vytvorí funkciu na získanie textovej časti z reťazca Funkcia GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Potom Výsledok = Výsledok a stred (CellRef, i, 1) Ďalej i GetText = Funkcia konca výsledku

Toto je niekoľko užitočných kódov makier programu Excel, ktoré môžete použiť vo svojej každodennej práci na automatizáciu úloh a zvýšenie produktivity.