Å bruke Excel -makroer kan øke hastigheten på arbeidet og spare deg for mye tid.
En måte å få VBA -koden på er å registrere makroen og ta koden den genererer. Imidlertid er denne koden med makroopptaker ofte full av kode som egentlig ikke er nødvendig. Makroopptaker har også noen begrensninger.
Så det lønner seg å ha en samling nyttige VBA -makrokoder som du kan ha i baklommen og bruke den når det trengs.
Selv om det kan ta litt tid i begynnelsen å skrive en Excel VBA -makrokode, kan den først holde den tilgjengelig som referanse og bruke den når du trenger den.
I denne massive artikkelen skal jeg liste opp noen nyttige Excel -makroeksempler som jeg ofte trenger og holde lagret i mitt private hvelv.
Jeg vil fortsette å oppdatere denne opplæringen med flere makroeksempler. Hvis du synes noe burde stå på listen, er det bare å legge igjen en kommentar.
Du kan bokmerke denne siden for fremtidig referanse.
Nå før jeg går inn i makroeksemplet og gir deg VBA -koden, la meg først vise deg hvordan du bruker disse eksempelkodene.
Bruke koden fra Excel -makroeksempler
Her er trinnene du må følge for å bruke koden fra noen av eksemplene:
- Åpne arbeidsboken der du vil bruke makroen.
- Hold inne ALT -tasten og trykk på F11. Dette åpner VB Editor.
- Høyreklikk på et av objektene i prosjektutforskeren.
- Gå til Sett inn -> Modul.
- Kopier og lim inn koden i modulkodevinduet.
Hvis eksemplet sier at du må lime inn koden i regnearkets kodevindu, dobbeltklikker du på regnearkobjektet og kopierer inn koden i kodevinduet.
Når du har satt inn koden i en arbeidsbok, må du lagre den med en .XLSM eller .XLS forlengelse.
Slik kjører du makroen
Når du har kopiert koden i VB Editor, er her trinnene for å kjøre makroen:
- Gå til kategorien Utvikler.
- Klikk på Makroer.
- Velg makroen du vil kjøre i dialogboksen Makro.
- Klikk på Kjør -knappen.
Hvis du ikke finner utviklerfanen på båndet, kan du lese denne opplæringen for å lære hvordan du får den.
Relatert opplæring: Ulike måter å kjøre en makro i Excel.
Hvis koden limes inn i regnearkets kodevindu, trenger du ikke bekymre deg for å kjøre koden. Den kjøres automatisk når den angitte handlingen skjer.
La oss gå inn på de nyttige makroeksemplene som kan hjelpe deg med å automatisere arbeidet og spare tid.
Merk: Du finner mange forekomster av en apostrof (‘) etterfulgt av en linje eller to. Dette er kommentarer som ignoreres mens du kjører koden og plasseres som notater for deg selv/leser.
Hvis du finner feil i artikkelen eller koden, vær så snill og gi meg beskjed.
Excel -makroeksempler
Nedenfor er makroeksempler dekket i denne artikkelen:
Vis alle regneark på en gang
Hvis du jobber i en arbeidsbok som har flere skjulte ark, må du fjerne disse arkene én etter én. Dette kan ta litt tid hvis det er mange skjulte ark.
Her er koden som vil vise alle regnearkene i arbeidsboken.
'Denne koden vil vise alle arkene i arbeidsboken Sub UnhideAllWoksheets () Dim ws As Worksheet For Every ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Koden ovenfor bruker en VBA -sløyfe (for hver) for å gå gjennom hvert regneark i arbeidsboken. Den endrer deretter den synlige egenskapen til regnearket til synlig.
Her er en detaljert opplæring om hvordan du bruker forskjellige metoder for å vise ark i Excel.
Skjul alle regneark unntatt det aktive arket
Hvis du jobber med en rapport eller et dashbord og du vil skjule alt regnearket bortsett fra det som har rapporten/dashbordet, kan du bruke denne makrokoden.
'Denne makroen vil skjule alt regnearket bortsett fra det aktive arket 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
Sorter regneark alfabetisk ved hjelp av VBA
Hvis du har en arbeidsbok med mange regneark og du vil sortere disse alfabetisk, kan denne makrokoden være veldig nyttig. Dette kan være tilfelle hvis du har arknavn som år eller ansattnavn eller produktnavn.
'Denne koden vil sortere 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 Shount If Sheets (j) .Name <Sheets (i) .Name Then Sheets (j) .Flytt før: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Beskytt alle regneark på en gang
Hvis du har mange regneark i en arbeidsbok og du vil beskytte alle arkene, kan du bruke denne makrokoden.
Den lar deg spesifisere passordet i koden. Du trenger dette passordet for å fjerne beskyttelsen av regnearket.
'Denne koden beskytter alle arkene på en gang Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' erstatt Test123 med passordet du vil For hver ws I regneark ws.Protect passord: = passord Neste ws Slutt Sub
Fjern beskyttelsen av alle regneark på en gang
Hvis du har noen eller alle regnearkene beskyttet, kan du bare bruke en liten modifikasjon av koden som brukes til å beskytte arkene for å fjerne beskyttelsen.
'Denne koden vil beskytte alle arkene på en gang Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' erstatt Test123 med passordet du vil For hver ws I regneark ws.Unbeskytt passord: = passord Neste ws Slutt Sub
Vær oppmerksom på at passordet må være det samme som har blitt brukt til å låse regnearkene. Hvis det ikke er det, vil du se en feil.
Vis alle rader og kolonner
Denne makrokoden vil vise alle de skjulte radene og kolonnene.
Dette kan være veldig nyttig hvis du får en fil fra noen andre og vil være sikker på at det ikke er noen skjulte rader/kolonner.
'Denne koden vil vise alle radene og kolonnene i regnearket Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Slå sammen alle sammenslåtte celler
Det er en vanlig praksis å slå sammen celler for å gjøre dem til en. Selv om det gjør arbeidet, vil du ikke kunne sortere dataene når celler slås sammen.
Hvis du arbeider med et regneark med sammenslåtte celler, bruker du koden nedenfor for å slå sammen alle de sammenslåtte cellene samtidig.
'Denne koden vil fusjonere alle de sammenslåtte cellene Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Vær oppmerksom på at i stedet for Merge and Center, anbefaler jeg å bruke alternativet Center Across Selection.
Lagre arbeidsbok med tidsstempel i navnet
Mye tid må du kanskje lage versjoner av arbeidet ditt. Disse er ganske nyttige i lange prosjekter der du jobber med en fil over tid.
En god praksis er å lagre filen med tidsstempler.
Ved å bruke tidsstempler kan du gå tilbake til en bestemt fil for å se hvilke endringer som ble gjort eller hvilke data som ble brukt.
Her er koden som automatisk lagrer arbeidsboken i den angitte mappen og legger til et tidsstempel når den lagres.
'Denne koden lagrer filen med et tidsstempel i navnet Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" og tidsstempel End Sub
Du må angi mapplasseringen og filnavnet.
I koden ovenfor er “C: UsersUsernameDesktop mapplasseringen jeg har brukt. Du må angi mapplasseringen der du vil lagre filen. Jeg har også brukt et generisk navn "WorkbookName" som filnavnprefiks. Du kan spesifisere noe relatert til prosjektet eller firmaet ditt.
Lagre hvert regneark som en separat PDF -fil
Hvis du jobber med data for forskjellige år eller divisjoner eller produkter, kan det hende du må lagre forskjellige regneark som PDF -filer.
Selv om det kan være en tidkrevende prosess hvis det gjøres manuelt, kan VBA virkelig øke hastigheten.
Her er en VBA -kode som lagrer hvert regneark som en egen PDF.
'Denne koden lagrer hvert arbeidsark som en separat PDF -del SaveWorkshetAsPDF () Dim ws As Worksheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub Sub
I koden ovenfor har jeg spesifisert adressen til mappeplasseringen der jeg vil lagre PDF -filene. Hver PDF får også samme navn som i regnearket. Du må endre denne mappens plassering (med mindre navnet ditt også er Sumit og du lagrer det i en testmappe på skrivebordet).
Vær oppmerksom på at denne koden bare fungerer for regneark (og ikke diagramark).
Lagre hvert regneark som en separat PDF -fil
Her er koden som vil lagre hele arbeidsboken din som en PDF i den angitte mappen.
'Denne koden lagrer hele arbeidsboken som PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Du må endre mappens plassering for å bruke denne koden.
Konverter alle formler til verdier
Bruk denne koden når du har et regneark som inneholder mange formler, og du vil konvertere disse formlene til verdier.
'Denne koden vil konvertere alle formler til verdier Sub ConvertToValues () With ActiveSheet.UsedRange .Value = .Value End With End Sub
Denne koden identifiserer automatisk cellene som brukes og konverterer den til verdier.
Beskytt/lås celler med formler
Det kan være lurt å låse celler med formler når du har mange beregninger, og du ikke vil slette dem ved et uhell eller endre det.
Her er koden som vil låse alle cellene som har formler, mens alle de andre cellene ikke er låst.
'Denne makrokoden låser alle cellene med formlene Sub LockCellsWithFormulas () With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub Sub
Relatert opplæring: Slik låser du celler i Excel.
Beskytt alle regneark i arbeidsboken
Bruk koden nedenfor for å beskytte alle regnearkene i en arbeidsbok på en gang.
'Denne koden vil beskytte alle arkene i arbeidsboken Sub ProtectAllSheets () Dim ws As Worksheet For Every ws In Worksheets ws.Protect Next ws End Sub
Denne koden går gjennom alle regnearkene en etter en og beskytter den.
Hvis du vil fjerne beskyttelsen til alle regnearkene, bruker du ws.Unprotect i stedet for ws.Protect i koden.
Sett inn en rad etter hver annen rad i utvalget
Bruk denne koden når du vil sette inn en tom rad etter hver rad i det valgte området.
'Denne koden vil sette inn en rad etter hver rad i utvalget 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. Sett inn ActiveCell.Offset (2, 0) .Velg Next i End Sub
På samme måte kan du endre denne koden for å sette inn en tom kolonne etter hver kolonne i det valgte området.
Sett automatisk inn dato og tidsstempel i den tilstøtende cellen
Et tidsstempel er noe du bruker når du vil spore aktiviteter.
Det kan for eksempel være lurt å spore aktiviteter, for eksempel når en spesiell utgift ble påløpt, hvilken tid salgsfakturaen ble opprettet, når ble datainføringen gjort i en celle, når ble rapporten sist oppdatert osv.
Bruk denne koden til å sette inn et dato- og klokkeslettstempel i den tilstøtende cellen når en oppføring gjøres eller det eksisterende innholdet redigeres.
'Denne koden vil sette inn et tidsstempel i den tilstøtende cellen 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 (Nå (), "dd-mm-åååå hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Vær oppmerksom på at du må sette inn denne koden i regnearkets kodevindu (og ikke i modulkodevinduet slik vi har gjort i andre Excel -makroeksempler så langt). For å gjøre dette, dobbeltklikker du på arknavnet du vil ha denne funksjonaliteten i, i VB Editor. Kopier og lim inn denne koden i arkvinduets kodevindu.
Denne koden får også til å fungere når datainføringen er utført i kolonne A (merk at koden har linjen Target.Column = 1). Du kan endre dette tilsvarende.
Marker alternative rader i utvalget
Å markere alternative rader kan øke lesbarheten til dataene dine enormt. Dette kan være nyttig når du trenger å skrive ut og gå gjennom dataene.
Her er en kode som umiddelbart vil markere alternative rader i utvalget.
'Denne koden vil markere alternative rader i utvalget 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 If Next Myrow End Sub
Vær oppmerksom på at jeg har angitt fargen som vbCyan i koden. Du kan også angi andre farger (for eksempel vbRed, vbGreen, vbBlue).
Fremhev celler med feilstavede ord
Excel har ikke en stavekontroll som den har i Word eller PowerPoint. Mens du kan kjøre stavekontrollen ved å trykke på F7 -tasten, er det ingen visuell indikasjon når det er en stavefeil.
Bruk denne koden til å umiddelbart markere alle cellene som har en stavefeil i den.
'Denne koden vil markere cellene som har feilstavede ord Sub HighlightMisspelledCells () Dim cl som område for hver cl i ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Deretter cl.Interior.Color = vbRed End If Next cl Slutt Sub
Vær oppmerksom på at cellene som er markert er de som har tekst som Excel anser som en stavefeil. I mange tilfeller vil det også markere navn eller merkeord som det ikke forstår.
Oppdater alle pivottabeller i arbeidsboken
Hvis du har mer enn ett pivottabell i arbeidsboken, kan du bruke denne koden til å oppdatere alle disse pivottabellene samtidig.
'Denne koden vil oppdatere alle pivottabellene i arbeidsboken Sub RefreshAllPivotTables () Dim PT As PivotTable for Every PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Du kan lese mer om forfriskende pivottabeller her.
Endre bokstaver for utvalgte celler til store bokstaver
Selv om Excel har formlene for å endre bokstaver i teksten, får du det til i et annet sett med celler.
Bruk denne koden til å umiddelbart endre store bokstaver i teksten i den valgte teksten.
'Denne koden endrer utvalget til store bokstaver Sub ChangeCase () Dim Rng som område for hver Rng i utvalg. Celler Hvis Rng.HasFormula = False deretter Rng.Value = UCase (Rng.Value) Slutt hvis neste Rng End Sub
Vær oppmerksom på at i dette tilfellet har jeg brukt UCase til å lage tekstetasjen Øvre. Du kan bruke LCase for små bokstaver.
Fremhev alle celler med kommentarer
Bruk koden nedenfor for å markere alle cellene som har kommentarer i den.
'Denne koden vil markere celler som har kommentarer' Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
I dette tilfellet har jeg brukt vbBlue for å gi cellene en blå farge. Du kan endre dette til andre farger hvis du vil.
Fremhev tomme celler med VBA
Selv om du kan markere en tom celle med betinget formatering eller bruke dialogboksen Gå til spesiell, men hvis du må gjøre det ganske ofte, er det bedre å bruke en makro.
Når den er opprettet, kan du ha denne makroen i verktøylinjen for hurtig tilgang eller lagre den i din personlige makro -arbeidsbok.
Her er VBA -makrokoden:
'Denne koden vil markere alle de tomme cellene i datasettet Sub HighlightBlankCells () Dim Datasett som Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
I denne koden har jeg spesifisert de tomme cellene som skal fremheves i den røde fargen. Du kan velge andre farger som blå, gul, cyan, etc.
Slik sorterer du data etter enkelt kolonne
Du kan bruke koden nedenfor til å sortere data etter den angitte kolonnen.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlJa End Sub
Vær oppmerksom på at jeg har opprettet et navngitt område med navnet 'DataRange' og har brukt det i stedet for cellereferansene.
Det er også tre viktige parametere som brukes her:
- Nøkkel 1 - Dette er det du vil sortere datasettet på. I eksempelkoden ovenfor vil dataene bli sortert basert på verdiene i kolonne A.
- Ordre- Her må du angi om du vil sortere dataene i stigende eller synkende rekkefølge.
- Overskrift - Her må du angi om dataene dine har overskrifter eller ikke.
Les mer om hvordan du sorterer data i Excel ved hjelp av VBA.
Slik sorterer du data etter flere kolonner
Anta at du har et datasett som vist nedenfor:
Nedenfor er koden som vil sortere dataene basert 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. Søk på slutten med slutten
Vær oppmerksom på at her har jeg spesifisert å først sortere basert på kolonne A og deretter basert på kolonne B.
Utgangen vil være noe som vist nedenfor:
Hvordan få bare den numeriske delen fra en streng i Excel
Hvis du bare vil trekke ut den numeriske delen eller bare tekstdelen fra en streng, kan du opprette en egendefinert funksjon i VBA.
Du kan deretter bruke denne VBA -funksjonen i regnearket (akkurat som vanlige Excel -funksjoner), og den trekker bare ut den numeriske eller tekstdelen fra strengen.
Noe som vist nedenfor:
Nedenfor er VBA -koden som vil opprette en funksjon for å trekke ut numerisk del fra en streng:
'Denne VBA -koden vil opprette en funksjon for å få den numeriske delen fra en streng Funksjon GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Deretter Resultat = Resultat & Midt (CellRef, i, 1) Neste i GetNumeric = Resultatsluttfunksjon
Du trenger plass i kode i en modul, og deretter kan du bruke funksjonen = GetNumeric i regnearket.
Denne funksjonen tar bare ett argument, som er cellereferansen til cellen du vil hente den numeriske delen fra.
På samme måte er funksjonen som bare gir deg tekstdelen fra en streng i Excel:
'Denne VBA -koden vil opprette en funksjon for å få tekstdelen fra en streng Funksjon GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Deretter Resultat = Resultat og midten (CellRef, i, 1) Neste i GetText = Resultatsluttfunksjon
Så dette er noen av de nyttige Excel-makrokodene du kan bruke i det daglige arbeidet for å automatisere oppgaver og være mye mer produktive.
