24 uporabnih primerov Excelovih makrov za začetnike VBA (pripravljen za uporabo)

Uporaba Excelovih makrov lahko pospeši vaše delo in prihranite veliko časa.

Eden od načinov za pridobitev kode VBA je snemanje makra in pridobivanje kode, ki jo ustvari.Vendar je koda snemalnika makrov pogosto polna kode, ki v resnici ni potrebna.Snemalnik makrov ima tudi nekaj omejitev.

Zato je vredno imeti uporaben nabor makrov VBA, ki jih lahko hranite v zadnjem žepu in jih uporabite, ko jih potrebujete.

Čeprav lahko začetno kodiranje makra Excel VBA traja nekaj časa, ga lahko uporabite kot referenco in ga uporabite naslednjič, ko ga boste potrebovali.

V tem velikem članku bom navedel nekaj uporabnih primerov Excelovih makrov, ki jih pogosto potrebujem in hranim v svojem zasebnem trezorju.

To vadnico bom še naprej posodabljal z več primeri makrov.Pustite komentar, če menite, da bi moralo biti nekaj na seznamu.

To stran lahko dodate med zaznamke za nadaljnjo uporabo.

Zdaj, preden se lotim primerov makrov in vam dam kodo VBA, naj vam najprej pokažem, kako uporabiti vzorčno kodo.

Uporaba kode iz primera makra Excel

Sledite korakom za uporabo kode iz katerega koli od primerov:

• Odprite delovni zvezek, v katerem želite uporabiti makro.
• Držite tipko ALT in pritisnite F11.To se bo odprloVB urednik.
• Z desno tipko miške kliknite kateri koli predmet v Project Explorerju.
• Pojdite na Vstavi -> Moduli.
• Kopirajte in prilepite kodo v okno kode modula.

Če primer pravi, da morate kodo prilepiti v okno kode delovnega lista, dvokliknite predmet delovnega lista in kodo kopirajte in prilepite v okno kode.

Ko vstavite kodo v delovni zvezek, jo morate shraniti s pripono .XLSM ali .XLS.

Kako zagnati makro

Po kopiranju kode v urejevalniku VB so koraki za zagon makra naslednji:

• Pojdite na zavihek za razvijalce.
• Kliknite Makro.

• V pogovornem oknu Makri izberite makro, ki ga želite zagnati.
• Kliknite gumb Zaženi.

Če na traku ne najdete zavihka za razvijalce, preberite to vadnico, če želite izvedeti, kako ga dobite.

Če je koda prilepljena v okno kode delovnega lista, vam ni treba skrbeti za izvajanje kode.Zažene se samodejno, ko se izvede določeno dejanje.

Zdaj pa poglejmo uporabne primere makrov, ki vam lahko pomagajo avtomatizirati svoje delo in prihraniti čas.

Opomba: našli boste veliko apostrofov ('), ki jim sledita vrstica ali dve.To so komentarji, ki so pri izvajanju kode prezrti in postavljeni kot komentarji lastnega/bralnega bralca.

Če najdete kakršne koli napake v članku ali kodi, me obvestite.

Primer makra v Excelu

Ta članek opisuje naslednje primere makrov:

Razkrij vse delovne liste hkrati

Če delate na delovnem zvezku z več skritimi listi, jih morate razkriti enega za drugim.To lahko traja nekaj časa, če je veliko skritih listov.

Tukaj je koda za razkrivanje vseh listov v delovnem zvezku.

'Ta koda bo razkrila vse liste v delovnem zvezku Sub UnhideAllWoksheets() Dim ws As Worksheet For Every ws In ActiveWorkbook. Delovni listi ws. Visible = xlSheetVisible Next ws End Sub

Zgornja koda uporablja zanko VBA (za vsako) za ponavljanje po vsakem delovnem listu v delovnem zvezku.Nato spremeni vidno lastnost lista v vidno.

Tukaj je podrobna vadnica o tem, kako razkriti delovni list v Excelu z različnimi metodami.

Skrij vse delovne liste razen aktivnega delovnega lista

To kodo makra lahko uporabite, če delate na poročilu ali nadzorni plošči in želite skriti vse liste, razen lista, ki ima poročilo/nadzorno ploščo.

'Ta makro bo skril ves delovni list, razen aktivnega lista Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Every ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

Razvrstite delovni list po abecedi z uporabo VBA

Ta koda makra je lahko uporabna, če imate delovni zvezek z veliko listi in želite te liste razvrstiti po abecedi.To se lahko zgodi, če imate ime lista kot leto ali ime zaposlenega ali ime izdelka.

'Ta koda bo razvrstila delovne liste po abecedi Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count Za i = 1 Za ShCount - 1 Za j = i + 1 za ShCount Če Sheets(j).Name < Sheets(i).Name Then Sheets(j).Premik pred:=Liste(i) Konec Če Naprej j Naprej i Application.ScreenUpdating = True End Sub

Zaščitite vse delovne liste hkrati

Če imate v delovnem zvezku veliko listov in želite zaščititi vse liste, lahko uporabite to kodo makra.

Omogoča vam, da določite geslo v kodi.To geslo boste potrebovali, da odstranite zaščito lista.

'Ta koda bo zaščitila vse liste naenkrat Sub ProtectAllSheets() Dim ws As Worksheet Dim geslo As String password = "Test123" 'zamenjajte Test123 z geslom, ki ga želite Za Vsak ws Na delovnih listih ws.Zaščitno geslo:=geslo Naslednji ws Končni sub

Odstranite zaščito vseh delovnih listov hkrati

Če ste zaščitili nekatere ali vse svoje delovne liste, jih lahko odstranite z le rahlo spremembo kode, ki se uporablja za zaščito delovnih listov.

'Ta koda bo zaščitila vse liste naenkrat Sub ProtectAllSheets() Dim ws As Worksheet Dim geslo As String password = "Test123" 'zamenjajte Test123 z geslom, ki ga želite Za vsako ws v delovnih listih ws.Unprotect password:=password Next ws Končni sub

Upoštevajte, da mora biti geslo isto geslo, ki se uporablja za zaklepanje lista.Če ne, boste videli napako.

Razkrij vse vrstice in stolpce

Ta koda makra bo razkrila vse skrite vrstice in stolpce.

To je lahko koristno, če jemljete datoteko od nekoga drugega in se želite prepričati, da ni skritih vrstic/stolpcev.

'Ta koda bo razkrila vse vrstice in stolpce v delovnem listu Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Razdruži vse združene celice

Združevanje celic v eno je običajna praksa.Medtem ko deluje, ne boste mogli razvrstiti podatkov, ko bodo celice združene.

Če uporabljate delovni list z združenimi celicami, uporabite spodnjo kodo, da razdružite vse združene celice hkrati.

'Ta koda bo razdružila vse združene celice Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Upoštevajte, da priporočam uporabo možnosti »Center Across Selection« namesto možnosti »Združi in sredi«.

Shranite delovni zvezek s časovnim žigom v imenu

Velikokrat boste morda morali ustvariti delujočo različico.Te so odlične za dolgoročne projekte, ki se sčasoma ukvarjajo z datotekami.

Dobra praksa je, da datoteko shranite s časovnim žigom.

Uporaba časovnega žiga vam bo omogočila, da se vrnete v datoteko in si ogledate, katere spremembe so bile narejene ali kateri podatki so bili uporabljeni.

Tukaj je koda, ki samodejno shrani delovni zvezek v določeno mapo in pri shranjevanju doda časovni žig.

'Ta koda bo shranila datoteko s časovnim žigom v imenu Sub SaveWorkbookWithTimeStamp() Dim časovni žig kot niz časovni žig = Format(Datum, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & časovni žig End Sub

Določiti morate lokacijo mape in ime datoteke.

V zgornji kodi je "C:UsersUsernameDesktop lokacija mape, ki jo uporabljam.Določiti morate lokacijo mape, kamor želite shraniti datoteko.Prav tako sem kot predpono imena datoteke uporabil splošno ime "WorkbookName".Določite lahko vsebino, povezano z vašim projektom ali podjetjem.

Vsak delovni list shranite kot ločen PDF

Če uporabljate podatke iz različnih let ali oddelkov ali izdelkov, boste morda morali shraniti različne delovne liste kot datoteke PDF.

Čeprav je to lahko dolgotrajen postopek, če ga izvajate ročno, VBA stvari pospeši.

Tukaj je koda VBA, ki shrani vsak delovni list kot ločen PDF.

'Ta koda bo shranila vsako delovno listo kot ločeno datoteko PDF ShraniWorkshetAsPDF() Dim ws kot delovni list za vsak ws v delovnih listih ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Naslednji ws End Sub

V zgornji kodi sem navedel naslov lokacije mape, kamor želim shraniti PDF.Prav tako bo vsak PDF dobil isto ime kot list.To lokacijo mape boste morali spremeniti (razen če je vaše ime tudi Sumit in ste jo shranili v testno mapo na namizju).

Upoštevajte, da ta koda deluje samo za delovne liste (ne za liste grafikonov).

Vsak delovni list shranite kot ločen PDF

Tukaj je koda za shranjevanje celotnega delovnega zvezka kot PDF v določeno mapo.

'Ta koda bo shranila celoten delovni zvezek kot PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Če želite uporabiti to kodo, morate spremeniti lokacijo mape.

Pretvorite vse formule v vrednosti

Uporabite to kodo, ko imate delovni list z veliko formulami in želite te formule pretvoriti v vrednosti.

'Ta koda bo pretvorila vse formule v vrednosti Sub ConvertToValues() z ActiveSheet.UsedRange .Value = .Value End With End Sub

Ta koda samodejno prepozna uporabljeno celico in jo pretvori v vrednost.

Zaščitite/zaklenite celice s formulami

Ko imate veliko izračunov in jih ne želite po nesreči izbrisati ali spremeniti, boste morda želeli zakleniti celice s formulami.

Tukaj je koda, ki bo zaklenila vse celice s formulami in pustila vse druge celice odklenjene.

Ta makro koda bo zaklenila vse celice s formulami Sub LockCellsWithFormulas() z ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect With End Allow=True).

Sorodna vadnica: Kako zakleniti celice v Excelu.

Zaščitite vse liste v delovnem zvezku

Uporabite naslednjo kodo za zaščito vseh listov v delovnem zvezku hkrati.

'Ta koda bo zaščitila vse liste v delovnem zvezku Sub ProtectAllSheets() Dim ws kot delovni list za vsako ws V delovnih listih ws. Zaščiti naslednje ws End Sub

Ta koda bo šla skozi vse liste enega za drugim in jih zaščitila.

Če želite odstraniti zaščito vseh listov, v kodi uporabite ws.Unprotect namesto ws.Protect.

Za vsako vrstico izbire vstavite vrstico

Uporabite to kodo, če želite vstaviti prazno vrstico za vsako vrstico v izbranem obsegu.

'Ta koda bo vstavila vrstico za vsako vrstico v izboru Sub InsertAlternateRow() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count Za i = 1 Za CountRow ActiveCell.EntireRow. Vstavite ActiveCell.Offset(2, 0).Izberite Next i End Sub

Ponovno lahko spremenite to kodo, da vstavite prazen stolpec za vsak stolpec v izbranem obsegu.

Samodejno vstavite datum in časovni žig v sosednje celice

Časovni žigi so tisto, kar uporabite, ko želite slediti dejavnosti.

Morda boste želeli na primer spremljati dejavnosti, kot so kdaj je nastal določen strošek, kdaj je bil ustvarjen prodajni račun, kdaj so bili podatki vneseni v celico, kdaj je bilo poročilo nazadnje posodobljeno itd.

S to kodo vstavite žig datuma in časa v sosednje celice, ko vnašate ali urejate obstoječo vsebino.

'Ta koda bo vstavila časovni žig v sosednjo celico Private Sub Worksheet_Change(ByVal Target As Range) Ob Error GoTo Handler Če Target.Column = 1 In Target.Value <> "" Potem Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Upoštevajte, da morate to kodo vstaviti v okno kode delovnega lista (ne v okno kode modula, kot smo to počeli v drugih primerih Excelovih makrov do sedaj).Če želite to narediti, v urejevalniku VB dvokliknite ime lista, za katerega želite to funkcijo.Nato kopirajte in prilepite to kodo v kodno okno tega lista.

Ta koda deluje tudi, ko se vnesejo podatki v stolpec A (upoštevajte, da ima koda vrstico Target.Column = 1).Ustrezno ga lahko spremenite.

Označite nadomestne vrstice v izboru

Označevanje izmeničnih vrstic lahko močno izboljša berljivost vaših podatkov.To je lahko uporabno, ko ga morate natisniti in brskati po podatkih.

Tukaj je koda, ki bo takoj označila nadomestne vrstice v izboru.

'Ta koda bi poudarila nadomestne vrstice v izboru Sub HighlightAlternateRows() Zatemni Myrange kot obseg Zatemni Myrow kot obseg Nastavi Myrange = izbor za vsako Myrow v Myrange.Rows Če Myrow.Row Mod 2 = 1 Potem Myrow.Interior.Color = vbCyan End Če Next Myrow End Sub

Upoštevajte, da sem v kodi navedel barvo kot vbCyan.Določite lahko tudi druge barve (npr. vbRed, vbGreen, vbBlue).

Označite napačno črkovane celice

Excel nima preverjanja črkovanja v Wordu ali PowerPointu.Čeprav lahko zaženete preverjanje črkovanja s pritiskom na F7, ni vizualnega znaka za črkovalne napake.

S to kodo takoj označite vse napačno črkovane celice.

'Ta koda bo poudarila celice, ki imajo napačno črkovane besede Sub HighlightMisspeledCells() Dim cl As Range For Every cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Nato cl.Interior.Color = vbRed End, če je naslednji cl Končni sub

Upoštevajte, da so označene celice celice z besedilom, za katerega Excel meni, da je napačno črkovano.V mnogih primerih izpostavi tudi imena ali izraze blagovnih znamk, ki jih ne razume.

Osvežite vse vrtilne tabele v delovnem zvezku

Če imate v delovnem zvezku več vrtilnih tabel, lahko s to kodo osvežite vse naenkrat.

'Ta koda bo osvežila vso vrtilno tabelo v delovnem zvezku Sub RefreshAllPivotTables() Dim PT kot vrtilno tabelo za vsak PT v ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Več o osveževanju vrtilnih tabel si lahko preberete tukaj.

Spremenite velike črke izbranih celic v velike črke

Čeprav ima Excel formule za spreminjanje velikih črk besedila, vam to omogoča v drugem nizu celic.

S to kodo takoj spremenite velike črke besedila v izbranem besedilu.

'Ta koda bo spremenila izbor v velike črke Sub ChangeCase() Dim Rng As Range za vsak Rng v Selection.Cells Če Rng.HasFormula = False Potem Rng.Value = UCase(Rng.Value) End, če je naslednji Rng End Sub

Upoštevajte, da v tem primeru uporabljam UCase za nastavitev velikih črk besedila.Za male črke lahko uporabite LCase.

Označite vse celice s komentarji

Z naslednjo kodo označite vse celice s komentarji v njih.

"Ta koda bo označila celice, ki imajo komentarje" Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

V tem primeru uporabim vbBlue, da celici dam modro barvo.Po potrebi jo lahko spremenite v drugo barvo.

Označite prazne celice z VBA

Čeprav lahko uporabite pogojno oblikovanje ali uporabite pogovorno okno Pojdi na posebno, da označite prazne celice, je najbolje, da uporabite makro, če morate to početi pogosto.

Ko ga ustvarite, lahko ta makro imate v orodni vrstici za hitri dostop ali ga shranite v osebni delovni zvezek z makrami.

Tukaj je koda makra VBA:

'Ta koda bo označila vse prazne celice v naboru podatkov Sub HighlightBlankCells() Zatemni nabor podatkov kot nabor podatkov za obseg = nabor podatkov za izbor.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

V tej kodi sem določil prazne celice, označene z rdečo.Izberete lahko druge barve, kot so modra, rumena, cian itd.

Kako razvrstiti podatke po enem stolpcu

Za razvrščanje podatkov po določenem stolpcu lahko uporabite naslednjo kodo.

SubSortDataHeader() 
Obseg("DataRange").Ključ za razvrščanje1:=Obseg("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

Upoštevajte, da sem ustvaril imenovani obseg z imenom "DataRange" in ga uporabil namesto sklicevanja na celico.

Tu so uporabljeni tudi trije ključni parametri:

• Ključ 1 – To je ključ, po katerem želite razvrstiti nabor podatkov.V zgornji vzorčni kodi bodo podatki razvrščeni na podlagi vrednosti v stolpcu A.
• Vrstni red1 – tukaj morate določiti, ali želite podatke razvrstiti v naraščajočem ali padajočem vrstnem redu.
• Glava – tukaj morate določiti, ali imajo vaši podatki glavo ali ne.

Preberite več o razvrščanju podatkov v Excelu z uporabo VBA.

Kako razvrstiti podatke po več stolpcih

Recimo, da imate nabor podatkov, ki izgleda takole:

Tukaj je koda za razvrščanje podatkov na podlagi več stolpcev:

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

Upoštevajte, da sem tukaj določil razvrščanje najprej po stolpcu A in nato po stolpcu B.

Izhod bo videti takole:

Kako dobiti samo številčni del iz niza v Excelu

Če želite samo izvleči številčni del ali del besedila iz niza, lahko ustvarite funkcijo po meri v VBA.

Nato lahko uporabite to funkcijo VBA na svojem delovnem listu (tako kot običajno funkcijo Excel) in iz niza bo izvlekla samo številko ali del besedila.

Kot je prikazano spodaj:

Tukaj je koda VBA, ki bo ustvarila funkcijo, ki izvleče številčni del iz niza:

Ta koda VBA bo ustvarila funkcijo za pridobivanje številskega dela iz niza Funkcija GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) Za i = 1 Do StringLength If IsNumeric(Mid(CellRef, i, 1) ) Nato Result = Result & Mid(CellRef, i, 1) Naprej i GetNumeric = Končna funkcija rezultata

Kodo morate vnesti v modul, nato pa lahko uporabite funkcijo = GetNumeric na delovnem listu.

Ta funkcija bo vzela samo en parameter, referenco celice, iz katere bo dobila številčni del.

Podobno bo spodnja funkcija dobila samo del besedila iz niza v Excelu:

'Ta koda VBA bo ustvarila funkcijo za pridobivanje dela besedila iz niza Funkcija GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) Za i = 1 do StringLength Če ne (IsNumeric(Mid(CellRef, i, 1))) Nato Rezultat = Rezultat & Mid(CellRef, i, 1) Naprej i GetText = Končna funkcija rezultata

Torej, to je nekaj uporabnih Excel makro kod, ki jih lahko uporabite pri vsakodnevnem delu za avtomatizacijo opravil in povečanje produktivnosti.