Brug af Excel -makroer kan fremskynde arbejdet og spare dig for en masse tid.
En måde at få VBA -koden på er at optage makroen og tage den kode, den genererer. Denne kode ved makrooptager er dog ofte fuld af kode, der ikke rigtig er nødvendig. Også makrooptager har nogle begrænsninger.
Så det kan betale sig at have en samling nyttige VBA -makrokoder, som du kan have i baglommen og bruge den, når det er nødvendigt.
Mens det kan tage noget tid i starten at skrive en Excel VBA -makrokode, når det er gjort, kan du holde den tilgængelig som reference og bruge den, når du har brug for den næste gang.
I denne massive artikel vil jeg liste nogle nyttige Excel -makroeksempler, som jeg ofte har brug for og gemme væk i mit private hvælving.
Jeg vil blive ved med at opdatere denne vejledning med flere makroeksempler. Hvis du synes, at noget skal være på listen, skal du bare efterlade en kommentar.
Du kan bogmærke denne side til fremtidig reference.
Nu, før jeg går ind i makroeksemplet og giver dig VBA -koden, lad mig først vise dig, hvordan du bruger disse eksempelkoder.
Brug af koden fra Excel -makroeksempler
Her er de trin, du skal følge for at bruge koden fra et af eksemplerne:
- Åbn den projektmappe, hvor du vil bruge makroen.
- Hold ALT -tasten nede, og tryk på F11. Dette åbner VB Editor.
- Højreklik på et af objekterne i projektudforskeren.
- Gå til Indsæt -> Modul.
- Kopier og indsæt koden i vinduet Modulkode.
Hvis eksemplet siger, at du skal indsætte koden i regnearkets kodevindue, skal du dobbeltklikke på regnearksobjektet og kopier indsætte koden i kodevinduet.
Når du har indsat koden i en projektmappe, skal du gemme den med en .XLSM- eller .XLS -udvidelse.
Sådan kører du makroen
Når du har kopieret koden i VB Editor, er her trinene til at køre makroen:
- Gå til fanen Udvikler.
- Klik på Makroer.
- Vælg den makro, du vil køre, i dialogboksen Makro.
- Klik på knappen Kør.
Hvis du ikke kan finde fanen Udvikler på båndet, kan du læse denne vejledning for at lære, hvordan du får den.
Relateret selvstudium: Forskellige måder at køre en makro i Excel på.
Hvis koden indsættes i regnearkets kodevindue, behøver du ikke bekymre dig om at køre koden. Den kører automatisk, når den angivne handling finder sted.
Lad os nu komme ind på de nyttige makroeksempler, der kan hjælpe dig med at automatisere arbejde og spare tid.
Bemærk: Du finder mange tilfælde af en apostrof (‘) efterfulgt af en linje eller to. Dette er kommentarer, der ignoreres, mens koden køres og placeres som noter til mig selv/læser.
Hvis du finder en fejl i artiklen eller koden, skal du være fantastisk, og lad mig vide det.
Excel -makroeksempler
Nedenfor er makroeksempler dækket i denne artikel:
Vis alle regneark på én gang
Hvis du arbejder i en projektmappe, der har flere skjulte ark, skal du fjerne disse ark én efter én. Dette kan tage noget tid, hvis der er mange skjulte ark.
Her er koden, der vil vise alle regnearkene i projektmappen.
'Denne kode vil vise alle ark i projektmappen Sub UnhideAllWoksheets () Dim ws As Worksheet For Every ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Ovenstående kode bruger en VBA -loop (for hver) til at gå gennem hvert regneark i projektmappen. Det ændrer derefter regnearkets synlige egenskab til synlig.
Her er en detaljeret vejledning i, hvordan du bruger forskellige metoder til at fjerne ark i Excel.
Skjul alle regneark undtagen det aktive ark
Hvis du arbejder på en rapport eller dashboard, og du vil skjule alt regnearket undtagen det, der har rapporten/dashboardet, kan du bruge denne makrokode.
'Denne makro skjuler hele regnearket undtagen det aktive ark Sub HideAllExceptActiveSheet () Dim ws Som regneark for hver ws i ThisWorkbook.Worksheets Hvis ws.Name ActiveSheet.Name Herefter ws.Visible = xlSheetHidden Næste ws End Sub
Sorter regneark alfabetisk ved hjælp af VBA
Hvis du har en projektmappe med mange regneark, og du vil sortere disse alfabetisk, kan denne makrokode komme rigtig godt til gode. Dette kan være tilfældet, hvis du har arknavne som år eller medarbejdernavne eller produktnavne.
'Denne kode sorterer regnearkene alfabetisk 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) .Flyt før: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Beskyt alle regneark på én gang
Hvis du har mange regneark i en projektmappe, og du vil beskytte alle arkene, kan du bruge denne makrokode.
Det giver dig mulighed for at angive adgangskoden i koden. Du skal bruge denne adgangskode for at afbeskytte regnearket.
'Denne kode beskytter alle arkene på én gang Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' erstat Test123 med den adgangskode, du ønsker For hver ws I regneark ws.Protect password: = password Næste ws Afslut Sub
Afbeskyt alle regneark på én gang
Hvis du har nogle eller alle regnearkene beskyttet, kan du bare bruge en lille ændring af koden, der bruges til at beskytte ark, for at afbeskytte den.
'Denne kode beskytter alle arkene på én gang Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' erstat Test123 med den adgangskode, du ønsker For hver ws I regneark ws.Unprotect password: = password Næste ws Afslut Sub
Bemærk, at adgangskoden skal være den samme, som er blevet brugt til at låse regnearkene. Hvis det ikke er det, vil du se en fejl.
Vis alle rækker og kolonner
Denne makrokode vil vise alle de skjulte rækker og kolonner.
Dette kan være virkelig nyttigt, hvis du får en fil fra en anden og vil være sikker på, at der ikke er skjulte rækker/kolonner.
'Denne kode vil vise alle rækker og kolonner i regnearksunderen UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Slå alle flettede celler sammen
Det er en almindelig praksis at flette celler for at gøre det til en. Selvom det gør arbejdet, kan du ikke sortere dataene, når celler flettes.
Hvis du arbejder med et regneark med flettede celler, skal du bruge koden herunder til at flette alle de flettede celler på én gang.
'Denne kode vil opløse alle de flettede celler Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Bemærk, at i stedet for Fletning og Center anbefaler jeg at bruge indstillingen Center på tværs af markering.
Gem projektmappe med tidsstempel i sit navn
Meget tid skal du muligvis oprette versioner af dit arbejde. Disse er ganske nyttige i lange projekter, hvor du arbejder med en fil over tid.
En god praksis er at gemme filen med tidsstempler.
Brug af tidsstempler giver dig mulighed for at gå tilbage til en bestemt fil for at se, hvilke ændringer der blev foretaget, eller hvilke data der blev brugt.
Her er koden, der automatisk gemmer projektmappen i den angivne mappe og tilføjer et tidsstempel, når den er gemt.
'Denne kode gemmer filen med et tidsstempel i sit navn Sub SaveWorkbookWithTimeStamp () Dim tidsstempel som streng tidsstempel = Format (dato, "dd-mm-åååå") & "_" & format (tid, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & tidsstempel Slut Sub
Du skal angive mappens placering og filnavnet.
I ovenstående kode er “C: UsersUsernameDesktop den mappeplacering, jeg har brugt. Du skal angive den mappeplacering, hvor du vil gemme filen. Jeg har også brugt et generisk navn "WorkbookName" som filnavnets præfiks. Du kan angive noget, der er relateret til dit projekt eller din virksomhed.
Gem hvert regneark som en separat PDF
Hvis du arbejder med data for forskellige år eller divisioner eller produkter, har du muligvis brug for at gemme forskellige regneark som PDF -filer.
Selvom det kan være en tidskrævende proces, hvis det udføres manuelt, kan VBA virkelig fremskynde det.
Her er en VBA -kode, der gemmer hvert regneark som en separat PDF.
'Denne kode gemmer hvert arbejdsark som en separat PDF Sub SaveWorkshetAsPDF () Dim ws As Worksheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub Sub
I ovenstående kode har jeg angivet adressen på den mappeplacering, hvor jeg vil gemme PDF -filer. Hver PDF får også det samme navn som regnearket. Du bliver nødt til at ændre denne mappeplacering (medmindre dit navn også er Sumit, og du gemmer det i en testmappe på skrivebordet).
Bemærk, at denne kode kun fungerer til regneark (og ikke diagramark).
Gem hvert regneark som en separat PDF
Her er koden, der gemmer hele din projektmappe som en PDF i den angivne mappe.
'Denne kode gemmer hele projektmappen som PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Du bliver nødt til at ændre mappens placering for at bruge denne kode.
Konverter alle formler til værdier
Brug denne kode, når du har et regneark, der indeholder mange formler, og du vil konvertere disse formler til værdier.
'Denne kode konverterer alle formler til værdier Sub ConvertToValues () With ActiveSheet.UsedRange .Value = .Value End With End Sub
Denne kode identificerer automatisk, at celler bruges, og konverterer det til værdier.
Beskyt/lås celler med formler
Du vil måske låse celler med formler, når du har mange beregninger, og du ikke ved et uheld vil slette eller ændre det.
Her er koden, der vil låse alle de celler, der har formler, mens alle de andre celler ikke er låst.
'Denne makrokode låser alle cellerne med formler Sub LockCellsWithFormulas () Med ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub Sub
Relateret selvstudium: Sådan låses celler i Excel.
Beskyt alle regneark i projektmappen
Brug nedenstående kode til at beskytte alle regnearkene i en projektmappe på én gang.
'Denne kode beskytter alle ark i projektmappen Sub ProtectAllSheets () Dim ws As Worksheet For Every ws In Worksheets ws.Protect Next ws End Sub
Denne kode vil gå igennem alle regnearkene en efter en og beskytte den.
Hvis du vil afbeskytte alle regneark, skal du bruge ws.Unprotect i stedet for ws.Protect i koden.
Indsæt en række efter hver anden række i markeringen
Brug denne kode, når du vil indsætte en tom række efter hver række i det valgte område.
'Denne kode vil indsætte en række efter hver række i valget 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. Indsæt ActiveCell.Offset (2, 0) .Vælg Next i End Sub
På samme måde kan du ændre denne kode for at indsætte en tom kolonne efter hver kolonne i det valgte område.
Indsæt automatisk dato og tidsstempel i den tilstødende celle
Et tidsstempel er noget, du bruger, når du vil spore aktiviteter.
For eksempel vil du måske spore aktiviteter, f.eks. Hvornår blev en særlig udgift afholdt, hvilket tidspunkt blev salgsfakturaen oprettet, hvornår blev dataindtastningen foretaget i en celle, hvornår blev rapporten sidst opdateret osv.
Brug denne kode til at indsætte et dato- og tidsstempel i den tilstødende celle, når en indtastning foretages, eller det eksisterende indhold redigeres.
'Denne kode indsætter et tidsstempel i den tilstødende celle 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 (Nu (), "dd-mm-åååå hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Bemærk, at du skal indsætte denne kode i regnearkets kodevindue (og ikke i modulets kodevindue, som vi har gjort i andre Excel -makroeksempler hidtil). For at gøre dette skal du i VB Editor dobbeltklikke på det arknavn, som du vil have denne funktionalitet på. Kopier og indsæt derefter denne kode i arkets kodevindue.
Denne kode får også til at fungere, når dataindtastningen foretages i kolonne A (bemærk, at koden har linjen Target.Column = 1). Du kan ændre dette i overensstemmelse hermed.
Fremhæv alternative rækker i markeringen
Fremhævelse af alternative rækker kan øge læsbarheden af dine data enormt. Dette kan være nyttigt, når du skal udskrive og gennemgå dataene.
Her er en kode, der øjeblikkeligt vil fremhæve alternative rækker i markeringen.
'Denne kode vil fremhæve alternative rækker i valget Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Every Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Hvis Next Myrow End Sub
Bemærk, at jeg har angivet farven som vbCyan i koden. Du kan også angive andre farver (f.eks. VbRed, vbGreen, vbBlue).
Fremhæv celler med stavefejl
Excel har ikke en stavekontrol, som den har i Word eller PowerPoint. Selvom du kan køre stavekontrollen ved at trykke på F7 -tasten, er der ingen visuel signal, når der er en stavefejl.
Brug denne kode til øjeblikkeligt at markere alle de celler, der har en stavefejl i den.
'Denne kode vil markere de celler, der har stavet ord forkert Sub HighlightMisspelledCells () Dim cl som område for hver cl i ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Herefter cl.Interior.Color = vbRed End If Next cl Afslut Sub
Bemærk, at de markerede celler er dem, der har tekst, som Excel betragter som en stavefejl. I mange tilfælde vil det også fremhæve navne eller mærkebetegnelser, som det ikke forstår.
Opdater alle pivottabeller i projektmappen
Hvis du har mere end én pivottabel i projektmappen, kan du bruge denne kode til at opdatere alle disse pivottabeller på én gang.
'Denne kode vil opdatere alle pivottabellerne i Workbook Sub RefreshAllPivotTables () Dim PT som pivottabel for hver PT i ActiveSheet.PivotTables PT.RefreshTable Næste PT End Sub
Du kan læse mere om opdatering af pivottabeller her.
Skift bogstavet for udvalgte celler til store bogstaver
Mens Excel har formlerne til at ændre tekstens store bogstaver, får du dig til at gøre det i et andet sæt celler.
Brug denne kode til øjeblikkeligt at ændre bogstavet i teksten i den markerede tekst.
'Denne kode ændrer markeringen til store bogstaver Sub ChangeCase () Dim Rng som område for hver Rng i markering. Celler Hvis Rng.HasFormula = False Så Rng.Value = UCase (Rng.Value) Slut hvis næste Rng End Sub
Bemærk, at i dette tilfælde har jeg brugt UCase til at lave teksten til Upper. Du kan bruge LCase til små bogstaver.
Fremhæv alle celler med kommentarer
Brug nedenstående kode til at markere alle de celler, der har kommentarer i den.
'Denne kode vil markere celler, der har kommentarer' Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
I dette tilfælde har jeg brugt vbBlue til at give cellerne en blå farve. Du kan ændre dette til andre farver, hvis du vil.
Fremhæv tomme celler med VBA
Selvom du kan markere en tom celle med betinget formatering eller bruge dialogboksen Gå til speciel, er det bedre at bruge en makro, hvis du skal gøre det ret ofte.
Når den er oprettet, kan du have denne makro på værktøjslinjen Hurtig adgang eller gemme den i din personlige makro -projektmappe.
Her er VBA -makrokoden:
'Denne kode vil fremhæve alle de tomme celler i datasættet Sub HighlightBlankCells () Dim Dataset som Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
I denne kode har jeg angivet de tomme celler, der skal fremhæves i den røde farve. Du kan vælge andre farver som blå, gul, cyan osv.
Sådan sorteres data efter enkelt kolonne
Du kan bruge nedenstående kode til at sortere data efter den angivne kolonne.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlJa End Sub
Bemærk, at jeg har oprettet et navngivet område med navnet 'DataRange' og har brugt det i stedet for cellereferencer.
Der er også tre nøgleparametre, der bruges her:
- Nøgle1 - Dette er det, du vil sortere datasættet på. I ovenstående eksempelkode sorteres dataene baseret på værdierne i kolonne A.
- Ordre- Her skal du angive, om du vil sortere dataene i stigende eller faldende rækkefølge.
- Header - Her skal du angive, om dine data har overskrifter eller ej.
Læs mere om, hvordan du sorterer data i Excel ved hjælp af VBA.
Sådan sorteres data efter flere kolonner
Antag, at du har et datasæt som vist herunder:
Nedenfor er koden, der vil sortere data baseret på flere kolonner:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlJa. Anvend slut med slut sub
Bemærk, at jeg her har angivet at først sortere baseret på kolonne A og derefter baseret på kolonne B.
Outputtet ville være noget som vist nedenfor:
Sådan får du kun den numeriske del fra en streng i Excel
Hvis du kun vil udtrække den numeriske del eller kun tekstdelen fra en streng, kan du oprette en brugerdefineret funktion i VBA.
Du kan derefter bruge denne VBA -funktion i regnearket (ligesom almindelige Excel -funktioner), og den udtrækker kun den numeriske eller tekstdel fra strengen.
Noget som vist herunder:
Nedenfor er VBA -koden, der vil oprette en funktion til at udtrække numerisk del fra en streng:
'Denne VBA -kode vil oprette en funktion for at få den numeriske del fra en streng Funktion GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Så Resultat = Resultat & Midt (CellRef, i, 1) Næste i GetNumeric = Resultatafslutningsfunktion
Du skal have plads i kode i et modul, og derefter kan du bruge funktionen = GetNumeric i regnearket.
Denne funktion tager kun et argument, som er cellereferencen til cellen, hvorfra du vil hente den numeriske del.
På samme måde er nedenstående funktionen, der kun får tekstdelen fra en streng i Excel:
'Denne VBA -kode vil oprette en funktion for at få tekstdelen fra en streng Funktion GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Så Resultat = Resultat & Midt (CellRef, i, 1) Næste i GetText = Resultatafslutningsfunktion
Så det er nogle af de nyttige Excel-makrokoder, som du kan bruge i dit daglige arbejde til at automatisere opgaver og være meget mere produktive.
