Korištenje Excel makronaredbi može ubrzati rad i uštedjeti puno vremena.
Jedan od načina dobivanja VBA koda je snimanje makronaredbe i uzimanje koda koji generira. Međutim, taj kod makro snimača često je pun koda koji zapravo nije potreban. Makro rekorder također ima neka ograničenja.
Stoga se isplati imati zbirku korisnih VBA makro kodova koje možete imati u stražnjem džepu i koristiti ih po potrebi.
Dok ispisujete Excel VBA makro kôd može u početku proći neko vrijeme, a nakon što ga završite, možete ga držati dostupnim kao referencu i koristiti ga kad god vam zatreba.
U ovom ogromnom članku navest ću neke korisne primjere makronaredbi programa Excel koje mi često trebaju i koje ću držati skrivene u svom privatnom trezoru.
Nastavit ću ažurirati ovaj vodič s više primjera makroa. Ako mislite da bi se nešto trebalo naći na popisu, ostavite komentar.
Ovu stranicu možete označiti za buduće potrebe.
Prije nego što uđem u Makro primjer i dam vam VBA kôd, prvo ću vam pokazati kako se koriste ti primjeri kodova.
Korištenje koda iz primjera makronaredbi programa Excel
Evo koraka koje morate slijediti da biste koristili kôd iz bilo kojeg od primjera:
- Otvorite radnu knjigu u kojoj želite koristiti makronaredbu.
- Držite tipku ALT i pritisnite F11. Ovo otvara VB Editor.
- Desnom tipkom miša kliknite bilo koji objekt u istraživaču projekata.
- Idite na Umetni -> Modul.
- Kopirajte i zalijepite kôd u prozor koda modula.
U slučaju da primjer kaže da morate zalijepiti kôd u prozor koda radnog lista, dvaput kliknite objekt radnog lista i kopirajte zalijepite kôd u prozor koda.
Nakon što umetnete kôd u radnu knjigu, morate ga spremiti s nastavkom .XLSM ili .XLS.
Kako pokrenuti makro
Nakon što ste kopirali kôd u VB Editor, evo koraka za pokretanje makronaredbe:
- Idite na karticu Developer.
- Kliknite na Makroi.
- U dijaloškom okviru Makronaredbe odaberite makronaredbu koju želite pokrenuti.
- Pritisnite gumb Pokreni.
U slučaju da ne možete pronaći karticu razvojnog programera na vrpci, pročitajte ovaj vodič kako biste saznali kako do njega doći.
Povezani vodič: Različiti načini pokretanja makronaredbi u Excelu.
U slučaju da je kôd zalijepljen u prozor koda radnog lista, ne morate brinuti o pokretanju koda. Automatski će se pokrenuti kada se dogodi navedena radnja.
Pređimo sada na korisne primjere makroa koji vam mogu pomoći da automatizirate posao i uštedite vrijeme.
Napomena: Naći ćete mnoge slučajeve apostrofa (‘) nakon kojih slijedi redak ili dva. To su komentari koji se zanemaruju tijekom izvođenja koda i postavljaju se kao bilješke za sebe/čitatelja.
U slučaju da pronađete bilo kakvu pogrešku u članku ili kodu, budite izvrsni i obavijestite me.
Primjeri makronaredbi programa Excel
U nastavku su navedeni primjeri makroa:
Otkrijte sve radne listove jednim pokretom
Ako radite u radnoj knjizi koja ima više skrivenih listova, morate ih otkriti jedan po jedan. To bi moglo potrajati ako postoji mnogo skrivenih listova.
Ovdje je kôd koji će otkriti sve radne listove u radnoj knjizi.
'Ovaj kôd će otkriti sve listove u radnoj knjizi Sub UnhideAllWoksheets () Zatamniti w kao radni list za svaki ws u ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Sljedeći ws End Sub
Gornji kôd koristi VBA petlju (za svaku) za pregled svih radnih listova u radnoj knjizi. Zatim mijenja vidljivo svojstvo radnog lista u vidljivo.
Evo detaljnog vodiča o tome kako koristiti različite metode za otkrivanje listova u Excelu.
Sakrij sve radne listove osim aktivnog lista
Ako radite na izvješću ili nadzornoj ploči i želite sakriti sav radni list osim onog koji ima izvješće/nadzornu ploču, možete upotrijebiti ovaj kôd makronaredbe.
'Ovaj će makro sakriti sav radni list osim aktivnog lista Sub HideAllExceptActiveSheet () Zatamniti w kao radni list za svaki ws u ThisWorkbook.Worksheets Ako ws.Name ActiveSheet.Name Zatim ws.Visible = xlSheetHidden Sljedeći ws Završni pod
Sortirajte radne listove po abecedi koristeći VBA
Ako imate radnu knjigu s mnogo radnih listova i želite ih poredati po abecedi, ovaj kôd makroa može vam jako dobro doći. To bi mogao biti slučaj ako imate nazive listova kao godine ili nazive zaposlenika ili nazive proizvoda.
'Ovaj kôd će sortirati radne listove po abecednom redu 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 Ako Sheets (j) .Naziv <Sheets (i) .Naziv Zatim Sheets (j) .Prijemjesti prije: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Zaštitite sve radne listove odjednom
Ako imate mnogo radnih listova u radnoj knjizi i želite zaštititi sve listove, možete upotrijebiti ovaj kod makroa.
Omogućuje vam da odredite lozinku unutar koda. Ova će vam lozinka trebati za uklanjanje zaštite radnog lista.
'Ovaj kôd će zaštititi sve listove odjednom Sub ProtectAllSheets () Dim ws As Worksheet Dim password as String password = "Test123"' zamijenite Test123 lozinkom koju želite Za svaki ws U radnim listovima ws.Zaštita lozinke: = lozinka Sljedeća ws Kraj podm
Uklonite zaštitu sa svih radnih listova odjednom
Ako imate neke ili sve radne listove zaštićene, možete upotrijebiti malu izmjenu koda koji se koristi za zaštitu listova kako biste ga uklonili.
'Ovaj kôd će zaštititi sve listove odjednom Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' zamijenite Test123 lozinkom koju želite Za svaki ws U radnim listovima ws.Unprotect lozinka: = lozinka Sljedeća ws Kraj podm
Imajte na umu da lozinka mora biti ista ona koja je korištena za zaključavanje radnih listova. Ako nije, vidjet ćete pogrešku.
Otkrijte sve redove i stupce
Ovaj kôd makronaredbe otkrit će sve skrivene retke i stupce.
Ovo bi moglo biti od velike pomoći ako dobijete datoteku od nekoga drugog i želite biti sigurni da nema skrivenih redaka/stupaca.
'Ovaj kôd će otkriti sve retke i stupce u Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Uklonite sve spojene ćelije
Uobičajena je praksa spajanje stanica kako bi bile jedna. Dok radi, kad se ćelije spoje, nećete moći sortirati podatke.
U slučaju da radite s radnim listom s spojenim ćelijama, upotrijebite donji kôd da biste odjednom spojili sve spojene ćelije.
'Ovaj kôd će ukloniti sve spojene ćelije Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Imajte na umu da umjesto Spajanje i Središnje preporučujem upotrebu opcije Središnji odabir.
Spremite radnu knjigu s vremenskom oznakom u njeno ime
Puno ćete vremena možda trebati stvoriti verzije svog rada. To je vrlo korisno u dugim projektima u kojima s vremenom radite s datotekom.
Dobra je praksa spremanje datoteke s vremenskim oznakama.
Korištenje vremenskih oznaka omogućit će vam da se vratite na određenu datoteku kako biste vidjeli koje su promjene napravljene ili koji su podaci korišteni.
Ovdje je kôd koji će automatski spremiti radnu knjigu u navedenu mapu i dodati vremensku oznaku kad god se spremi.
'Ovaj će kôd spremiti datoteku s vremenskom oznakom u svom imenu Sub SaveWorkbookWithTimeStamp () Zatamniti vremensku oznaku kao String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Morate navesti lokaciju mape i naziv datoteke.
U gornjem kodu „C: UsersUsernameDesktop je mjesto mape koju sam koristio. Morate navesti mjesto mape u koju želite spremiti datoteku. Također, koristio sam generički naziv “WorkbookName” kao prefiks naziva datoteke. Možete navesti nešto vezano za vaš projekt ili tvrtku.
Spremite svaki radni list kao zaseban PDF
Ako radite s podacima za različite godine ili odjele ili proizvode, možda ćete morati spremiti različite radne listove kao PDF datoteke.
Iako bi to mogao biti dugotrajan proces ako se radi ručno, VBA ga zaista može ubrzati.
Ovdje je VBA kôd koji će svaki radni list spremiti kao zaseban PDF.
'Ovaj će kôd spremiti svaki vjernik kao zaseban PDF Sub SaveWorkshetAsPDF () Zatamni kao radni list Za svaki ws U radnim listovima ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Sljedeći ws End Sub
U gornjem kodu naveo sam adresu lokacije mape u koju želim spremiti PDF -ove. Također, svaki PDF dobit će isto ime kao i naziv radnog lista. Morat ćete promijeniti ovo mjesto mape (osim ako je vaše ime također Sumit i spremate ga u testnu mapu na radnoj površini).
Imajte na umu da ovaj kôd radi samo za radne listove (a ne za listove grafikona).
Spremite svaki radni list kao zaseban PDF
Ovdje je kôd koji će cijelu vašu radnu knjigu spremiti kao PDF u navedenu mapu.
'Ovaj će kôd spremiti cijelu radnu knjigu kao PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Morat ćete promijeniti mjesto mape da biste koristili ovaj kôd.
Pretvorite sve formule u vrijednosti
Koristite ovaj kôd ako imate radni list koji sadrži mnogo formula i želite pretvoriti te formule u vrijednosti.
'Ovaj kôd će pretvoriti sve formule u vrijednosti Sub ConvertToValues () S ActiveSheet.UsedRange .Value = .Vrijednost završi s End Sub
Ovaj kôd automatski identificira korištene ćelije i pretvara ga u vrijednosti.
Zaštitite/zaključajte ćelije s formulama
Možda želite zaključati ćelije s formulama ako imate puno izračuna, a ne želite ga slučajno izbrisati ili promijeniti.
Ovdje je kôd koji će zaključati sve ćelije koje imaju formule, dok sve ostale ćelije nisu zaključane.
'Ovaj kôd makroa zaključat će sve ćelije s formulama Sub LockCellsWithFormulas () s ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True. Zaštiti AllowDeletingRows: = True End With End Sub
Povezani vodič: Kako zaključati ćelije u Excelu.
Zaštitite sve radne listove u radnoj bilježnici
Upotrijebite donji kod kako biste odjednom zaštitili sve radne listove u radnoj knjizi.
'Ovaj kôd štitit će sve listove u radnoj knjizi Pod ProtectAllSheets () Zatamniti kao radni list Za svaki ws U radnim listovima ws.Zaštititi Sljedeći ws Završni pod
Ovaj kôd će proći kroz sve radne listove jedan po jedan i zaštititi ga.
U slučaju da želite ukloniti zaštitu sa svih radnih listova, upotrijebite ws.Unprotect umjesto ws.Protect u kodu.
Umetnite redak nakon svakog drugog retka u odabir
Koristite ovaj kôd ako želite umetnuti prazan redak nakon svakog retka u odabranom rasponu.
'Ovaj kôd umetnut će redak nakon svakog retka u odabiru Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 Za brojanje redova ActiveCell.EntireRow. Umetnite ActiveCell.Offset (2, 0). Odaberite Next i End Sub
Slično, ovaj kôd možete izmijeniti tako da nakon svakog stupca u odabranom rasponu umetnete prazan stupac.
Automatski umetnite datum i vremensku oznaku u susjednu ćeliju
Vremenska oznaka je nešto što koristite kada želite pratiti aktivnosti.
Na primjer, možda biste htjeli pratiti aktivnosti kao što su kada je nastao određeni trošak, u koje vrijeme je izrađena faktura za prodaju, kada je unos podataka izvršen u ćeliji, kada je izvješće zadnji put ažurirano itd.
Pomoću ovog koda umetnite oznaku datuma i vremena u susjednu ćeliju prilikom unosa ili uređivanja postojećeg sadržaja.
'Ovaj kôd umetnut će vremensku oznaku u susjednu ćeliju 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-ggggg hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Imajte na umu da ovaj kôd morate umetnuti u prozor koda radnog lista (a ne u prozor kôda modula kao što smo to učinili u drugim primjerima makronaredbi Excel do sada). Da biste to učinili, u VB Editoru dvaput kliknite na naziv lista na kojem želite ovu funkcionalnost. Zatim kopirajte i zalijepite ovaj kôd u prozor koda tog lista.
Također, ovaj kôd radi kako bi se unos podataka izvršio u stupcu A (imajte na umu da kôd ima redak Target.Column = 1). To možete promijeniti u skladu s tim.
Istaknite zamjenske redove u odabiru
Isticanje zamjenskih redaka može iznimno povećati čitljivost vaših podataka. To može biti korisno kada trebate ispisati i pregledati podatke.
Ovdje je kôd koji će trenutno označiti zamjenske redove u odabiru.
'Ovaj kôd označio bi zamjenske retke u odabiru Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Odabir za svaki Myrow u Myrange.Rows Ako Myrow.Row Mod 2 = 1 Zatim Myrow.Interior.Color = vbCyan Kraj If Next Myrow End Sub
Imajte na umu da sam u kodu naveo boju kao vbCyan. Možete navesti i druge boje (poput vbRed, vbGreen, vbBlue).
Označite ćelije pogrešno napisanim riječima
Excel nema provjeru pravopisa kao u Wordu ili PowerPointu. Dok provjeru pravopisa možete pokrenuti pritiskom na tipku F7, nema vizualnog znaka kada postoji pravopisna pogreška.
Pomoću ovog koda odmah označite sve ćelije u kojima je pravopisna pogreška.
'Ovaj će kôd istaknuti ćelije koje imaju pogrešno napisane riječi Sub HighlightMisspelledCells () Dim cl As Range Za svaki cl U ActiveSheet.UsedRange Ako nije Application.CheckSpelling (word: = cl.Text) Zatim cl.Interior.Color = vbRed End If Next cl Kraj Sub
Imajte na umu da su označene ćelije one koje imaju tekst koji Excel smatra pravopisnom pogreškom. U mnogim slučajevima istaknuo bi i nazive ili izraze robnih marki koje ne razumije.
Osvježite sve zaokretne tablice u radnoj knjizi
Ako imate više od jedne zaokretne tablice u radnoj knjizi, ovaj kôd možete upotrijebiti za osvježavanje svih ovih zaokretnih tablica odjednom.
'Ovaj kôd osvježit će svu zaokretnu tablicu u pod -radnoj knjizi Ref RefreshAllPivotTables () Zatamniti PT kao zaokretnu tablicu za svaki PT u ActiveSheet -u.
Ovdje možete pročitati više o osvježavanju zaokretnih tablica.
Promijenite veličinu slova odabranih ćelija u velika slova
Iako Excel ima formule za promjenu velikih i malih slova u tekstu, to vas tjera da to učinite u drugom skupu ćelija.
Pomoću ovog koda odmah promijenite velika i mala slova u tekstu u odabranom tekstu.
'Ovaj kôd će promijeniti odabir u velika slova Pod ChangeCase () Dim Rng kao raspon za svaki Rng u odabiru. Ćelije Ako je Rng.HasFormula = False Tada Rng.Value = UCase (Rng.Value) Kraj Ako je sljedeći Rng Kraj Sub
Imajte na umu da sam u ovom slučaju upotrijebio UCase za povećanje gornjih slova u tekstu. Možete koristiti LCase za mala slova.
Označite sve ćelije komentarima
Upotrijebite donji kod za označavanje svih ćelija s komentarima.
"Ovaj kôd će istaknuti ćelije koje imaju komentare" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
U ovom slučaju koristio sam vbBlue za davanje ćelija plavoj boji. Ako želite, ovo možete promijeniti u druge boje.
Označite prazne ćelije pomoću VBA
Iako možete označiti praznu ćeliju uvjetnim oblikovanjem ili pomoću dijaloškog okvira Idi na Posebno, ako to morate činiti prilično često, bolje je upotrijebiti makronaredbu.
Nakon izrade, ovu makronaredbu možete imati na alatnoj traci za brzi pristup ili je spremiti u svoju osobnu radnu knjigu makronaredbi.
Evo VBA makro koda:
'Ovaj kôd će istaknuti sve prazne ćelije u skupu podataka Pod HighlightBlankCells () Zatamniti skup podataka kao skup podataka raspona = skup podataka za odabir.SpecialCells (xlCellTypeBlanks). Unutrašnjost.Boja = vbRed End Sub
U ovom sam kodu naveo prazne ćelije koje treba označiti crvenom bojom. Možete odabrati druge boje, poput plave, žute, cijan itd.
Kako sortirati podatke po jednom stupcu
Pomoću donjeg koda možete sortirati podatke prema navedenom stupcu.
Pod SortDataHeader () Raspon ("DataRange"). Sort Key1: = Raspon ("A1"), Order1: = xlAscending, Header: = xlDa Kraj Sub
Imajte na umu da sam stvorio imenovani raspon s imenom 'DataRange' i koristio sam ga umjesto referenci ćelije.
Ovdje se koriste i tri ključna parametra:
- Ključ1 - Ovo je mjesto na kojem želite sortirati skup podataka. U gornjem primjeru koda, podaci će se sortirati na temelju vrijednosti u stupcu A.
- Redoslijed- Ovdje morate navesti želite li sortirati podatke uzlaznim ili silaznim redoslijedom.
- Zaglavlje - Ovdje morate navesti imaju li vaši podaci zaglavlja ili ne.
Pročitajte više o tome kako sortirati podatke u Excelu pomoću VBA.
Kako sortirati podatke po više stupaca
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:
Ispod je kôd koji će sortirati podatke na temelju više stupaca:
PodrazvrstajVišestrukih stupaca () s ActiveSheet.Sort .SortFields.Add Key: = Raspon ("A1"), Redoslijed: = xlAscending .SortFields.Add Ključ: = Raspon ("B1"), Redoslijed: = xlAscending .SetRange Raspon ("A1 : C13 "). Glava = xlDa. Nanesite End With End Sub
Imajte na umu da sam ovdje naveo prvo sortiranje na temelju stupca A, a zatim na temelju stupca B.
Izlaz bi bio nešto kao što je prikazano u nastavku:
Kako dobiti samo numerički dio iz niza u Excelu
Ako želite izdvojiti samo numerički dio ili samo tekstualni dio iz niza, možete stvoriti prilagođenu funkciju u VBA.
Zatim možete koristiti ovu VBA funkciju na radnom listu (baš kao i obične Excel funkcije) i ona će izdvojiti samo numerički ili tekstualni dio iz niza.
Nešto kao što je prikazano ispod:
Ispod je VBA kôd koji će stvoriti funkciju za izdvajanje numeričkog dijela iz niza:
'Ovaj VBA kôd će stvoriti funkciju za dobivanje numeričkog dijela 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) ) Zatim Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Funkcija kraja rezultata
Potrebno vam je mjesto u kodu u modulu, a zatim možete koristiti funkciju = GetNumeric na radnom listu.
Ova funkcija će uzeti samo jedan argument, a to je referenca ćelije ćelije iz koje želite dobiti numerički dio.
Slično, dolje je funkcija koja će vam dobiti samo tekstualni dio iz niza u Excelu:
'Ovaj VBA kôd će stvoriti funkciju za dobivanje tekstualnog dijela iz niza Funkcija GetText (CellRef kao niz) Dim StringLength As Integer StringLength = Len (CellRef) Za i = 1 Za StringLength Ako nije (IsNumeric (Mid (CellRef, i, 1))) Zatim Result = Result & Mid (CellRef, i, 1) Next i GetText = Funkcija kraja rezultata
Ovo su neki od korisnih Excel makro kodova koje možete koristiti u svakodnevnom radu za automatizaciju zadataka i biti mnogo produktivniji.
