Používanie makier Excelu môže urýchliť prácu a ušetriť vám veľa času.
Jeden zo spôsobov, ako získať kód VBA, je zaznamenať makro a prevziať kód, ktorý generuje. Tento kód zapisovača makier je však často plný kódu, ktorý v skutočnosti nie je potrebný. Makro rekordér má tiež určité obmedzenia.
Oplatí sa teda mať zbierku užitočných VBA makrokódov, ktoré môžete mať vo svojom zadnom vrecku a v prípade potreby ich použiť.
Pri písaní kódu makra programu Excel VBA môže spočiatku nejaký čas trvať, keď ho budete mať hotový, môžete ho nechať k dispozícii ako referenciu a použiť ho, kedykoľvek ho budete najbližšie potrebovať.
V tomto rozsiahlom článku uvediem niekoľko užitočných príkladov excelových makier, ktoré často potrebujem a ktoré mám ukryté vo svojom súkromnom trezore.
Tento tutoriál budem aktualizovať o ďalšie príklady makier. Ak si myslíte, že by niečo malo byť na zozname, zanechajte komentár.
Túto stránku si môžete uložiť ako záložku pre budúce použitie.
Teraz, než sa dostanem k príkladu makra a poskytnem vám kód VBA, najskôr vám ukážem, ako používať tieto ukážkové kódy.
Použitie príkladu makra kódu z Excelu
Tu sú kroky, ktoré musíte dodržať, aby ste mohli použiť kód z ktoréhokoľvek z príkladov:
- Otvorte zošit, v ktorom chcete použiť makro.
- Podržte kláves ALT a stlačte kláves F11. Tým sa otvorí editor VB.
- Kliknite pravým tlačidlom myši na ľubovoľný objekt v Prieskumníkovi projektu.
- Prejdite na položku Vložiť -> Modul.
- Skopírujte a prilepte kód do okna Kód modulu.
V prípade, že príklad hovorí, že musíte vložiť kód do okna kódu pracovného hárka, dvakrát kliknite na objekt pracovného hárka a skopírujte vloženie kódu do okna s kódom.
Keď kód vložíte do zošita, musíte ho uložiť s príponou .XLSM alebo .XLS.
Ako spustiť makro
Keď skopírujete kód do editora VB, postupujte podľa týchto krokov k spusteniu makra:
- Prejdite na kartu Vývojár.
- Kliknite na položku Makrá.
- V dialógovom okne Makro vyberte makro, ktoré chcete spustiť.
- Kliknite na tlačidlo Spustiť.
V prípade, že na páse s nástrojmi nemôžete nájsť kartu vývojára, prečítajte si tento návod a zistite, ako ju získať.
Súvisiaci návod: Rôzne spôsoby spustenia makra v programe Excel.
V prípade, že je kód vložený do okna kódu pracovného hárka, nemusíte si so spustením kódu robiť starosti. Automaticky sa spustí, keď dôjde k zadanej akcii.
Teraz sa dostaneme k užitočným príkladom makier, ktoré vám môžu pomôcť automatizovať prácu a ušetriť čas.
Poznámka: Nájdete mnoho prípadov apostrofu (‘), za ktorými nasleduje riadok alebo dva. Toto sú komentáre, ktoré sú pri spustení kódu ignorované a sú umiestnené ako poznámky pre seba/čitateľa.
V prípade, že v článku alebo kóde nájdete akúkoľvek chybu, buďte úžasní a dajte mi vedieť.
Príklady makra programu Excel
V tomto článku sú popísané nižšie uvedené príklady makier:
Odkryte všetky pracovné listy naraz
Ak pracujete v zošite, ktorý má viacero skrytých hárkov, musíte tieto listy odkryť jeden po druhom. V prípade, že existuje veľa skrytých hárkov, môže to chvíľu trvať.
Tu je kód, ktorý odkryje všetky pracovné hárky v zošite.
„Tento kód odkryje všetky listy v zošite Sub UnhideAllWoksheets () Dim ws as Worksheet for each 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 prechádzanie všetkými pracovnými listami v zošite. Potom zmení viditeľnú vlastnosť pracovného hárka na viditeľný.
Tu je podrobný návod, ako používať rôzne metódy na odkrytie hárkov v programe Excel.
Skryť všetky pracovné hárky okrem aktívneho hárka
Ak pracujete na zostave alebo informačnom paneli a chcete skryť všetok pracovný hárok okrem toho, ktorý obsahuje prehľad/informačný panel, môžete použiť tento kód makra.
„Toto makro skryje všetok 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é listy podľa abecedy pomocou jazyka VBA
Ak máte zošit s mnohými pracovnými listami a chcete ich zoradiť podľa abecedy, tento kód makra vám môže prísť skutočne vhod. To môže byť prípad, ak máte názvy hárkov ako roky alebo mená zamestnancov alebo názvy produktov.
„Tento kód zoradí pracovné listy podľa 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 into: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Chráňte všetky pracovné listy naraz
Ak máte v pracovnom zošite veľa pracovných hárkov a chcete chrániť všetky hárky, môžete použiť tento kód makra.
Umožňuje vám zadať heslo do kódu. Toto heslo budete potrebovať na zrušenie ochrany pracovného hárka.
„Tento kód ochráni všetky listy naraz. Koniec pod
Zrušte ochranu všetkých pracovných hárkov naraz
Ak máte niektoré alebo všetky pracovné hárky chránené, môžete na ich odomknutie použiť miernu úpravu kódu použitého na ochranu hárkov.
„Tento kód ochráni všetky listy naraz. Koniec pod
Heslo musí byť rovnaké ako heslo, ktoré bolo použité na uzamknutie pracovných hárkov. Ak nie, zobrazí sa chyba.
Odkryť všetky riadky a stĺpce
Tento kód makra odkryje všetky skryté riadky a stĺpce.
To môže byť veľmi užitočné, ak dostanete súbor od niekoho iného a chcete sa uistiť, že neexistujú žiadne skryté riadky/stĺpce.
"Tento kód odkryje všetky riadky a stĺpce v stĺpci pracovného hárka Un UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Zrušte zlúčenie všetkých zlúčených buniek
Je bežnou praxou zlúčiť bunky do jedného celku. Aj keď to funguje, pri zlúčení buniek nebudete môcť údaje triediť.
V prípade, že pracujete s pracovným listom so zlúčenými bunkami, pomocou nižšie uvedeného kódu zlúčte všetky zlúčené bunky naraz.
„Tento kód zruší zlúčenie všetkých zlúčených buniek Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Všimnite si toho, že namiesto Zlúčiť a Vycentrovať odporúčam použiť možnosť Stredový krížový výber.
Uložte zošit s časovou pečiatkou v názve
Veľa času budete možno potrebovať na vytvorenie verzií svojej práce. Sú celkom nápomocné v dlhých projektoch, kde pracujete so súborom v priebehu času.
Osvedčeným postupom je uložiť súbor s časovými pečiatkami.
Použitie časových pečiatok vám umožní vrátiť sa k určitému súboru a zistiť, aké zmeny boli vykonané alebo aké údaje boli použité.
Tu je kód, ktorý zošit automaticky uloží do zadaného priečinka a pri každom uložení pridá časovú pečiatku.
„Tento kód uloží súbor s časovou pečiatkou v názve 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 zadať umiestnenie priečinka a názov súboru.
Vo vyššie uvedenom kóde „C: UsersUsernameDesktop je umiestnenie priečinka, ktoré som použil. Musíte zadať umiestnenie priečinka, do ktorého chcete súbor uložiť. Ako predponu názvu súboru som tiež použil všeobecný názov „WorkbookName“. Môžete zadať niečo súvisiace s vašim projektom alebo spoločnosťou.
Uložte každý pracovný hárok ako samostatný súbor PDF
Ak pracujete s údajmi za rôzne roky alebo divízie alebo produkty, 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 ručne, VBA ho môže skutočne 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ý podradený dokument PDF SaveWorkshetAsPDF () Dim ws as Worksheet for each ws in Worksheets ws.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ws.Name &" .pdf "Next ws End Sub Sub
Vo vyššie uvedenom kóde som zadal adresu umiestnenia priečinka, do ktorého chcem ukladať súbory PDF. Každý súbor PDF bude mať rovnaký názov ako pracovný list. Toto umiestnenie priečinka budete musieť upraviť (pokiaľ vaše meno nie je tiež Sumit a neukladáte ho do testovacieho priečinka na ploche).
Tento kód funguje iba pre pracovné hárky (a nie pre hárky grafov).
Uložte každý pracovný hárok ako samostatný súbor PDF
Tu je kód, ktorý uloží celý váš zošit ako PDF do zadaného priečinka.
„Tento kód uloží celý zošit ako čiastkový dokument PDF SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF,„ C: UsersSumitDesktopTest “& ThisWorkbook.Name &„ .pdf “End Sub
Na použitie tohto kódu budete musieť zmeniť umiestnenie priečinka.
Premeňte všetky vzorce na hodnoty
Tento kód použite, ak máte pracovný hárok, ktorý obsahuje veľa vzorcov a chcete tieto vzorce previesť na hodnoty.
"Tento kód prevedie všetky vzorce na hodnoty Sub ConvertToValues () s ActiveSheet.UsedRange .Value = .Value End With End Sub
Tento kód automaticky identifikuje, že sa používajú bunky, a skonvertuje ho na hodnoty.
Chráňte/uzamknite bunky pomocou vzorcov
Bunky môžete zamknúť pomocou vzorcov, ak máte veľa výpočtov a nechcete ich omylom odstrániť alebo zmeniť.
Tu je kód, ktorý uzamkne všetky bunky so vzorcami, zatiaľ čo všetky ostatné bunky nie sú uzamknuté.
"Tento kód makra uzamkne všetky bunky pomocou vzorcov Sub LockCellsWithFormulas () s 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 programe Excel.
Chráňte všetky pracovné listy v zošite
Nasledujúci kód použite na ochranu všetkých pracovných hárkov v zošite naraz.
„Tento kód ochráni všetky listy v zošite Sub ProtectAllSheets () Dim ws as Worksheet for each ws In Worksheets ws.Protect Next ws End Sub
Tento kód postupne prejde všetkými pracovnými listami a ochráni ho.
V prípade, že chcete zrušiť ochranu všetkých pracovných hárkov, použite namiesto kódu ws.Unprotect namiesto ws.Protect v kóde.
Do výberu vložte riadok za každý ďalší riadok
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 výberu Sub InsertAlternateRows () Dim rng Ako rozsah 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 i Koniec pod
Podobne môžete tento kód upraviť tak, aby bol za každý stĺpec vo vybratom rozsahu vložený prázdny stĺpec.
Automaticky vložiť dátum a časovú pečiatku do susednej bunky
Časová pečiatka je niečo, čo používate, keď chcete sledovať aktivity.
Môžete napríklad sledovať činnosti, ako napríklad kedy boli vynaložené konkrétne výdavky, kedy bola vytvorená predajná faktúra, kedy bolo zadanie údajov vykonané v bunke, kedy bola zostava naposledy aktualizovaná atď.
Tento kód použite na vloženie pečiatky dátumu a času do susednej bunky pri zadávaní záznamu 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-rrrr 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 (a nie do okna s kódom modulu, ako sme to urobili doteraz v iných príkladoch makier programu Excel). Ak to chcete urobiť, v editore VB dvakrát kliknite na názov listu, pre ktorý chcete túto funkciu. Potom skopírujte a prilepte tento kód do okna kódu tohto listu.
Tento kód funguje aj vtedy, keď sa zadávanie údajov vykonáva v stĺpci A (kód má riadok Target.Column = 1). Môžete to zodpovedajúcim spôsobom zmeniť.
Vo výbere zvýraznite alternatívne riadky
Zvýraznenie alternatívnych riadkov môže ohromne zvýšiť čitateľnosť vašich údajov. To môže byť užitočné, keď si potrebujete vytlačiť a prejsť údaje.
Tu je kód, ktorý okamžite zvýrazní alternatívne riadky vo výbere.
„Tento kód zvýrazní alternatívne riadky vo výbere 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 Ak Next Myrow End Sub
Všimnite si toho, že som v kóde zadal farbu ako vbCyan. Môžete zadať aj ďalšie farby (napríklad vbRed, vbGreen, vbBlue).
Zvýraznite bunky nesprávne napísanými slovami
Excel nemá kontrolu pravopisu ako vo Worde alebo PowerPointe. Kontrolu pravopisu môžete spustiť stlačením klávesu F7, v prípade pravopisnej chyby však neexistuje žiadne vizuálne upozornenie.
Tento kód použite na okamžité zvýraznenie všetkých buniek, v ktorých je pravopisná chyba.
„Tento kód zvýrazní bunky, ktoré majú nesprávne napísané slová. cl End Sub
Bunky, ktoré sú zvýraznené, sú bunky s textom, ktorý Excel považuje za pravopisnú chybu. V mnohých prípadoch by tiež zvýraznil názvy alebo výrazy značiek, ktorým nerozumie.
Obnovte všetky kontingenčné tabuľky v zošite
Ak máte v zošite viac ako jednu kontingenčnú tabuľku, môžete tento kód použiť na aktualizáciu všetkých týchto kontingenčných tabuliek naraz.
„Tento kód obnoví všetku kontingenčnú tabuľku v podzostave zošita RefreshAllPivotTables () Dim PT ako kontingenčnú tabuľku pre každý PT v programe ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Tu si môžete prečítať viac o osviežujúcich kontingenčných tabuľkách.
Zmeňte veľkosť písmen vybratých buniek na veľké písmená
Aj keď Excel obsahuje vzorce na zmenu veľkých a malých písmen textu, umožňuje vám to urobiť v inej skupine buniek.
Tento kód použite na okamžitú zmenu veľkých a malých písmen textu vo vybratom texte.
„Tento kód zmení výber na Sub veľké písmena ChangeCase () Dim Rng ako rozsah pre každé Rng vo výbere. Zruší, ak Rng.HasFormula = False Then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Všimnite si toho, že v tomto prípade som použil UCase na zväčšenie textu na veľké písmená. LCase môžete použiť pre malé písmená.
Zvýraznite všetky bunky pomocou komentárov
Pomocou nižšie uvedeného kódu zvýraznite všetky bunky, v ktorých sú komentáre.
"Tento kód zvýrazní bunky, ktoré majú komentáre" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
V tomto prípade som použil vbBlue na dodanie buniek modrej farby. Ak chcete, môžete to zmeniť na iné farby.
Zvýraznite prázdne bunky pomocou VBA
Aj keď môžete prázdnu bunku zvýrazniť podmieneným formátovaním alebo pomocou dialógového okna Prejsť na špeciálne, ak to musíte robiť dosť často, je lepšie použiť makro.
Toto makro môžete mať po vytvorení na paneli s nástrojmi Rýchly prístup alebo 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 as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks). Interior.Color = vbRed End Sub
V tomto kóde som určil prázdne bunky, ktoré sa majú zvýrazniť červenou farbou. Môžete si vybrať ďalšie farby, ako je modrá, žltá, azúrová atď.
Ako zoradiť údaje podľa jedného stĺpca
Nasledujúci kód môžete použiť na zoradenie údajov podľa zadaného stĺpca.
Podrozsah SortDataHeader () ("DataRange"). Triediaci kľúč1: = rozsah ("A1"), poradie1: = xlAscending, hlavička: = xlAno Koniec
Všimnite si toho, že som vytvoril pomenovaný rozsah s názvom „DataRange“ a použil som ho namiesto odkazov na bunky.
Tu sa používajú aj tri kľúčové parametre:
- Key1 - Toto je miesto, na ktorom chcete triediť množinu údajov. Vo vyššie uvedenom vzorovom kóde budú údaje zoradené na základe hodnôt v stĺpci A.
- Poradie- Tu musíte zadať, či chcete údaje zoradiť vzostupne alebo zostupne.
- Hlavička - Tu musíte zadať, či majú vaše údaje hlavičky alebo nie.
Prečítajte si viac o tom, ako triediť údaje v programe Excel pomocou jazyka VBA.
Ako zoradiť údaje podľa viacerých stĺpcov
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Nasleduje kód, ktorý zoradí údaje na základe viacerých stĺpcov:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Záhlavie = xlÁno. Použiť koniec s koncovým podč
Všimnite si toho, že som tu určil prvé zoradenie podľa stĺpca A a potom podľa stĺpca B.
Výstupom by bolo niečo uvedené nižšie:
Ako získať iba číselnú časť z reťazca v programe Excel
Ak chcete z reťazca extrahovať iba numerickú časť alebo iba textovú časť, môžete vo VBA vytvoriť vlastnú funkciu.
Túto funkciu VBA potom môžete použiť v pracovnom hárku (rovnako ako bežné funkcie programu Excel) a z reťazca extrahuje iba numerickú alebo textovú časť.
Niečo, ako je uvedené nižšie:
Nasleduje kód VBA, ktorý vytvorí funkciu na extrahovanie numerickej časti z reťazca:
„Tento kód VBA vytvorí funkciu na získanie numerickej časti z reťazca. ) Potom Výsledok = Výsledok a stred (CellRef, i, 1) Ďalej i GetNumeric = Funkcia ukončenia výsledku
Potrebujete miesto v kóde v module a potom môžete v hárku použiť funkciu = GetNumeric.
Táto funkcia bude používať iba jeden argument, ktorým je odkaz na bunku, z ktorej chcete získať číselnú časť.
Podobne je tu funkcia, ktorá vám poskytne iba textovú časť z reťazca v programe Excel:
„Tento kód VBA vytvorí funkciu na získanie textovej časti z reťazca. 1))) Potom Výsledok = Výsledok a stred (CellRef, i, 1) Ďalej i GetText = Funkcia ukončenia výsledku
Toto sú teda niektoré z 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.
