Z uporabo makrov Excel lahko pospešite delo in prihranite veliko časa.
Eden od načinov pridobivanja kode VBA je snemanje makra in prevzem kode, ki jo ustvari. Vendar je ta koda snemalnika makrov pogosto polna kode, ki v resnici ni potrebna. Tudi makro snemalnik ima nekatere omejitve.
Zato se splača imeti zbirko uporabnih makro kod VBA, ki jo lahko imate v zadnjem žepu in jo uporabite po potrebi.
Medtem ko lahko pisanje makro kode Excel VBA na začetku traja nekaj časa, potem ko jo končate, jo lahko hranite kot referenco in jo uporabite, ko jo naslednjič potrebujete.
V tem obsežnem članku bom navedel nekaj uporabnih primerov Excelovih makrov, ki jih pogosto potrebujem, in jih shranite v zasebnem trezorju.
To vadnico bom še naprej posodabljal z več primeri makrov. Če menite, da bi moralo biti nekaj na seznamu, pustite komentar.
To stran lahko dodate med zaznamke za prihodnjo uporabo.
Zdaj, preden pridem v makro primer in vam dam kodo VBA, vam najprej pokažem, kako uporabljati te vzorčne kode.
Uporaba kode iz Excelovih primerov makrov
Če želite uporabiti kodo iz katerega koli primera, morate slediti naslednjim korakom:
- Odprite delovni zvezek, v katerem želite uporabiti makro.
- Držite tipko ALT in pritisnite F11. S tem se odpre urejevalnik VB.
- Z desno miškino tipko kliknite kateri koli predmet v raziskovalcu projektov.
- Pojdite na Vstavi -> Modul.
- Kopirajte in prilepite kodo v okno kode modula.
Če primer kaže, da morate kodo prilepiti v okno kode delovnega lista, dvokliknite predmet delovnega lista in kopirajte prilepite kodo v okno za kodo.
Ko kodo vstavite v delovni zvezek, jo morate shraniti z razširitvijo .XLSM ali .XLS.
Kako zagnati makro
Ko ste kodo kopirali v urejevalniku VB, sledite tem korakom za zagon makra:
- Pojdite na zavihek Razvijalec.
- Kliknite na makre.
- V pogovornem oknu Makro 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 jo dobiti.
Sorodne vaje: Različni načini izvajanja makra v Excelu.
Če je koda prilepljena v okno kode delovnega lista, vam ni treba skrbeti za zagon kode. Samodejno se bo zagnal, ko se izvede določeno dejanje.
Zdaj pa pojdimo na uporabne primere makrov, ki vam lahko pomagajo avtomatizirati delo in prihraniti čas.
Opomba: Našli boste veliko primerov apostrofa ('), ki mu sledi vrstica ali dve. To so komentarji, ki se med izvajanjem kode prezrejo in so nameščeni kot opombe za samega sebe/bralca.
Če najdete kakšno napako v članku ali kodi, bodite super in mi sporočite.
Primeri makrov Excel
Spodaj so v tem članku obravnavani primeri makrov:
Odkrijte vse delovne liste naenkrat
Če delate v delovnem zvezku, ki ima več skritih listov, jih morate enega za drugim razkriti. To lahko traja nekaj časa, če obstaja veliko skritih listov.
Tu je koda, ki bo razkrila vse delovne liste v delovnem zvezku.
"Ta koda bo razkrila vse liste v delovnem zvezku Sub UnhideAllWoksheets () Zatemni ws kot delovni list za vsak ws v ActiveWorkbook. Delovni listi ws.Visible = xlSheetVisible Naslednji ws End Sub
Zgornja koda uporablja zanko VBA (za vsakega) za pregled vseh delovnih listov v delovnem zvezku. Nato spremeni vidno lastnost delovnega lista v vidno.
Tukaj je podrobna vadnica o tem, kako z različnimi metodami razkriti liste v Excelu.
Skrij vse delovne liste razen aktivnega lista
Če delate na poročilu ali nadzorni plošči in želite skriti ves delovni list, razen tistega, na katerem je poročilo/nadzorna plošča, lahko uporabite to kodo makra.
'Ta makro bo skril ves delovni list, razen aktivnega lista Sub HideAllExceptActiveSheet () Zatemni ws kot delovni list za vsak ws v ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Potem ws.Visible = xlSheetHidden Next ws End Sub
Razvrstite delovne liste po abecedi z uporabo VBA
Če imate delovni zvezek z veliko delovnimi listi in jih želite razvrstiti po abecedi, je ta koda makra lahko zelo priročna. To lahko velja, če imate imena listov kot leta ali imena zaposlenih ali imena izdelkov.
"Ta koda bo razvrstila delovne liste po abecednem redu Pod 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 Če so listi (j).
Zaščitite vse delovne liste naenkrat
Če imate v delovnem zvezku veliko delovnih listov in želite zaščititi vse liste, lahko uporabite to kodo makra.
Omogoča vam, da v kodi določite geslo. To geslo boste potrebovali za odstranitev zaščite delovnega lista.
'Ta koda bo zaščitila vse liste naenkrat Sub ProtectAllSheets () Zatemni ws kot delovni list Zatemni geslo kot geslo niza = "Test123"' zamenjajte Test123 z geslom, ki ga želite Za vsak ws Na delovnih listih ws.Zaščitite geslo: = geslo Naslednji ws End Sub
Odstranite zaščito vseh delovnih listov naenkrat
Če imate nekaj ali vse delovne liste zaščitene, lahko z rahlo spremembo kode, ki se uporablja za zaščito listov, odstranite zaščito.
'Ta koda bo zaščitila vse liste naenkrat Sub ProtectAllSheets () Zatemni ws kot delovni list Zatemni geslo kot geslo niza = "Test123"' zamenjajte Test123 z geslom, ki ga želite Za vsak ws Na delovnih listih ws.Odstrani geslo Naslednji ws End Sub
Upoštevajte, da mora biti geslo enako, kot je bilo uporabljeno za zaklepanje delovnih listov. Če ni, se prikaže napaka.
Razkrij vse vrstice in stolpce
Ta koda makra bo razkrila vse skrite vrstice in stolpce.
To bi lahko bilo zelo koristno, če datoteko dobite od nekoga drugega in želite biti prepričani, da ni skritih vrstic/stolpcev.
'Ta koda bo razkrila vse vrstice in stolpce v stolpcih Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Odstrani vse združene celice
Običajna praksa je, da se celice združijo v eno. Medtem ko deluje, med združevanjem celic ne boste mogli razvrstiti podatkov.
Če delate z delovnim listom s spojenimi celicami, uporabite spodnjo kodo, da naenkrat odstranite vse združene celice.
'Ta koda bo združila vse združene celice Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Upoštevajte, da namesto Merge and Center priporočam uporabo možnosti Center Across Selection.
Shranite delovni zvezek s časovnim žigom v svojem imenu
Veliko časa boste morda morali ustvariti različice svojega dela. To je zelo koristno pri dolgih projektih, kjer sčasoma delate z datoteko.
Dobra praksa je, da datoteko shranite s časovnimi žigi.
Uporaba časovnih žigov vam omogoča, da se vrnete v določeno datoteko, da vidite, katere spremembe so bile narejene ali kateri podatki so bili uporabljeni.
Tu je koda, ki bo samodejno shranila delovni zvezek v določeno mapo in dodala časovni žig, kadar koli je shranjen.
"Ta koda bo shranila datoteko s časovnim žigom v svojem imenu Sub SaveWorkbookWithTimeStamp () Zatemni časovni žig kot časovni žig niza = Oblika (datum," dd-mm-yyyy ") &" _ "& Oblika (čas," hh-ss ") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & časovni žig End Sub
Določiti morate mapo in ime datoteke.
V zgornji kodi je »C: UsersUsernameDesktop lokacija mape, ki sem jo uporabil. Določiti morate lokacijo mape, kamor želite shraniti datoteko. Kot predpono za ime datoteke sem uporabil tudi generično ime "WorkbookName". Določite lahko nekaj, kar je povezano z vašim projektom ali podjetjem.
Vsak delovni list shranite kot ločen PDF
Če delate s podatki za različna leta ali oddelke ali izdelke, boste morda morali shraniti različne delovne liste kot datoteke PDF.
Čeprav bi bil postopek ročno dolgotrajen, ga lahko VBA resnično pospeši.
Tukaj je koda VBA, ki bo vsak delovni list shranila kot ločen PDF.
"Ta koda bo shranila vsak bogoslužni list kot ločen PDF Sub SaveWorkshetAsPDF () Zatemni ws kot delovni list za vsak ws v delovnih listih ws.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ws.Name &" .pdf "Naprej ws End Sub
V zgornji kodi sem navedel naslov lokacije mape, v katero želim shraniti datoteke PDF. Prav tako bo vsak PDF dobil isto ime kot delovni list. To mapo boste morali spremeniti (razen če je tudi vaše ime Sumit in jo shranite v preskusno mapo na namizju).
Upoštevajte, da ta koda deluje samo za delovne liste (in ne za liste grafikonov).
Vsak delovni list shranite kot ločen PDF
Tu je koda, ki bo v določeni mapi shranila celoten delovni zvezek kot PDF.
'Ta koda bo shranila celoten delovni zvezek kot PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Za uporabo te kode boste morali spremeniti lokacijo mape.
Pretvorite vse formule v vrednosti
To kodo uporabite, če imate delovni list, ki vsebuje veliko formul in želite te formule pretvoriti v vrednosti.
'Ta koda pretvori vse formule v vrednosti Sub ConvertToValues () z ActiveSheet.UsedRange .Value = .Value End With End Sub
Ta koda samodejno identificira uporabljene celice in jo pretvori v vrednosti.
Zaščitite/zaklenite celice s formulami
Če imate veliko izračunov in jih ne želite pomotoma izbrisati ali spremeniti, boste celice želeli zakleniti s formulami.
Tukaj je koda, ki bo zaklenila vse celice s formulami, vse ostale celice pa niso zaklenjene.
"Ta koda makra bo zaklenila vse celice s formulami Sub LockCellsWithFormulas () z ActiveSheet. Odstrani.
Sorodne vaje: Kako zakleniti celice v Excelu.
Zaščitite vse delovne liste v delovnem zvezku
S spodnjo kodo zaščitite vse delovne liste v delovnem zvezku naenkrat.
'Ta koda bo zaščitila vse liste v delovnem zvezku Sub ProtectAllSheets () Zatemni ws kot delovni list za vsak ws v delovnih listih ws.Zaščita naslednjega ws End Sub
Ta koda bo pregledala vse delovne liste enega za drugim in jo zaščitila.
Če želite odstraniti zaščito vseh delovnih listov, uporabite ws.Unprotect namesto ws.Protect v kodi.
V izbiro vnesite vrstico za vsako drugo vrstico
To kodo uporabite, če želite za vsako vrstico v izbranem obsegu vstaviti prazno vrstico.
"Ta koda bo za vsako vrstico v izbiri vstavila vrstico 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. Vstavite ActiveCell.Offset (2, 0). Izberite Next i End Sub
Podobno lahko to kodo spremenite tako, da za vsakim stolpcem v izbranem obsegu vstavite prazen stolpec.
Samodejno vstavi datum in časovni žig v sosednjo celico
Časovni žig je nekaj, kar uporabite, če želite spremljati dejavnosti.
Morda boste na primer želeli slediti dejavnostim, na primer kdaj so nastali določeni stroški, kdaj je bil ustvarjen račun za prodajo, kdaj je bil vnos podatkov v celico, kdaj je bilo poročilo nazadnje posodobljeno itd.
S to kodo vstavite datum in časovni žig v sosednjo celico, ko vnesete vnos ali uredite obstoječo vsebino.
'Ta koda bo v sosednjo celico vstavila časovni žig Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If 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 Če upravljavec: End Sub
Upoštevajte, da morate to kodo vstaviti v okno kode delovnega lista (in ne v okno kode modula, kot smo to storili v drugih primerih makra Excel doslej). Če želite to narediti, v urejevalniku VB dvokliknite ime lista, na katerem želite to funkcijo. Nato kodo kopirajte in prilepite v okno s kodo tega lista.
Ta koda deluje tudi, ko se vnesejo podatki v stolpec A (upoštevajte, da ima koda vrstico Target.Column = 1). To lahko ustrezno spremenite.
Označite nadomestne vrstice v izboru
Označevanje nadomestnih vrstic lahko izjemno poveča berljivost vaših podatkov. To je lahko koristno, ko morate natisniti in pregledati podatke.
Tukaj je koda, ki bo v izbiri takoj označila nadomestne vrstice.
"Ta koda bi označila nadomestne vrstice v izbiri Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Izbor za vsako Myrow v Myrange.Rows If Myrow.Row Mod 2 = 1 Potem Myrow.Interior.Color = vbCyan Konec Če naslednji Myrow End Sub
Upoštevajte, da sem barvo v kodi navedel kot vbCyan. Določite lahko tudi druge barve (na primer vbRed, vbGreen, vbBlue).
Označite celice z napačno črkovanimi besedami
Excel nima preverjanja črkovanja, kot ga ima Word ali PowerPoint. Preverjanje črkovanja lahko zaženete tako, da pritisnete tipko F7, vendar pri črkovalni napaki ni vidnega znaka.
S to kodo takoj označite vse celice, ki imajo črkovalno napako.
"Ta koda bo označila celice, ki imajo napačno črkovane besede. cl End Sub
Upoštevajte, da so označene celice tiste, ki imajo besedilo, ki ga Excel obravnava kot pravopisno napako. V mnogih primerih bi izpostavil 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 te vrtilne tabele hkrati.
"Ta koda bo osvežila vse vrtilne tabele v pododdelku Delovni zvezek RefreshAllPivotTables () Zatemni PT kot vrtilno tabelo za vsak PT v aktivnem listu. Vrtilne tabele PT.
Več o osveževanju vrtilnih tabel lahko preberete tukaj.
Spremenite črko izbranih celic v velike črke
Čeprav ima Excel formule za spreminjanje velikih črk besedila, to naredite v drugem nizu celic.
S to kodo lahko takoj spremenite črke besedila v izbranem besedilu.
"Ta koda bo spremenila izbiro v veliko črko Sub ChangeCase () Dim Rng As Range Za vsak Rng v izboru. Celice Če je Rng.HasFormula = False Potem Rng.Value = UCase (Rng.Value) End Če Naslednji Rng End Sub
Upoštevajte, da sem v tem primeru uporabil UCase, da naredim črke velike. Za male črke lahko uporabite LCase.
Označi vse celice s komentarji
S spodnjo kodo označite vse celice, v katerih so komentarji.
"Ta koda bo označevala celice s komentarji" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments).
V tem primeru sem uporabil vbBlue, da sem celicam dal modro barvo. Če želite, lahko to spremenite v druge barve.
Prazne celice označite z VBA
Čeprav lahko s pogojnim oblikovanjem označite prazno celico ali uporabite pogovorno okno Pojdi na posebno, je bolje, če uporabite to makro.
Ta makro lahko ustvarite v orodni vrstici za hitri dostop ali ga shranite v osebni delovni zvezek makra.
Tu je koda makra VBA:
"Ta koda bo označila vse prazne celice v nizu podatkov Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks). Inner.Color = vbRed End Sub
V tej kodi sem določil prazne celice, ki jih je treba označiti z rdečo barvo. Izberete lahko druge barve, kot so modra, rumena, cijan itd.
Kako razvrstiti podatke po enem stolpcu
S spodnjo kodo lahko podatke razvrstite po podanem stolpcu.
Podrazred SortDataHeader () Obseg ("Podatkovno območje"). Ključ razvrščanja1: = Obseg ("A1"), Vrstica1: = xlAscending, Glava: = xlDa
Upoštevajte, da sem ustvaril imenovano območje z imenom 'DataRange' in ga uporabil namesto sklicev na celice.
Tu se uporabljajo tudi trije ključni parametri:
- Ključ1 - To je polje, na katerem želite razvrstiti niz podatkov. V zgornjem primeru kode bodo podatki razvrščeni glede na vrednosti v stolpcu A.
- Vrstni red- Tu morate določiti, ali želite razvrstiti podatke po naraščajočem ali padajočem vrstnem redu.
- Glava - tu morate določiti, ali imajo vaši podatki glave ali ne.
Preberite več o razvrščanju podatkov v Excelu z uporabo VBA.
Kako razvrstiti podatke po več stolpcih
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Spodaj je koda, ki bo razvrstila podatke na podlagi več stolpcev:
Podrazvrsti več stolpcev () z ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Glava = xlDa. Uporabi End with End Sub
Upoštevajte, da sem tukaj določil, da se najprej razvrsti na podlagi stolpca A in nato na podlagi stolpca B.
Izhod bi bil nekaj takega, kot je prikazano spodaj:
Kako v nizu Excel dobiti samo številčni del
Če želite iz niza izvleči samo številski ali samo besedilni del, lahko v VBA ustvarite funkcijo po meri.
Nato lahko uporabite to funkcijo VBA na delovnem listu (tako kot običajne funkcije programa Excel) in bo iz niza izvlekla samo številski ali besedilni del.
Nekaj, kot je prikazano spodaj:
Spodaj je koda VBA, ki bo ustvarila funkcijo za izvlečenje številskega dela 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) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Nato Result = Result & Mid (CellRef, i, 1) Naprej i GetNumeric = Končna funkcija rezultata
V kodo potrebujete mesto v modulu, nato pa lahko uporabite funkcijo = GetNumeric na delovnem listu.
Ta funkcija bo sprejela samo en argument, to je sklic na celico celice, iz katere želite dobiti številčni del.
Podobno je spodaj funkcija, ki vam bo dala le besedilni del iz niza v Excelu:
'Ta koda VBA bo ustvarila funkcijo za pridobivanje besedilnega dela iz niza Funkcija GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Nato Result = Result & Mid (CellRef, i, 1) Naprej i GetText = Funkcija konca rezultata
To je torej nekaj uporabnih kod Excela za makro, ki jih lahko uporabite pri vsakodnevnem delu za avtomatizacijo opravil in veliko večjo produktivnost.
