24 nyttige Excel-makroeksempler for VBA-nybegynnere (klar til bruk)

Bruk av Excel-makroer kan gjøre arbeidet ditt raskere og spare mye tid.

En måte å få VBA-koden på er å ta opp en makro og hente koden den genererer.Imidlertid er makroopptakerens kode ofte full av kode som egentlig ikke er nødvendig.Makroopptakeren har også noen begrensninger.

Så det er verdt å ha et nyttig sett med VBA-makroer som du kan ha i baklommen og bruke når du trenger det.

Selv om det kan ta litt tid å kode en Excel VBA-makro innledningsvis, kan du bruke den som referanse når du er ferdig, og bruke den neste gang du trenger den.

I denne massive artikkelen vil jeg liste noen nyttige Excel-makroeksempler som jeg ofte trenger og oppbevarer i mitt private hvelv.

Jeg vil fortsette å oppdatere denne opplæringen med flere makroeksempler.Legg igjen en kommentar hvis du synes noe burde stå på listen.

Du kan bokmerke denne siden for fremtidig referanse.

Nå, før jeg går inn i makroeksemplene og gir deg VBA-koden, la meg først vise deg hvordan du bruker eksempelkoden.

Bruke koden fra Excel-makroeksemplet

Her er trinnene du må følge for å bruke koden fra noen av eksemplene:

• Åpne arbeidsboken der du vil bruke makroen.
• Hold nede ALT-tasten og trykk F11.Denne åpnesVB redaktør.
• Høyreklikk på et objekt i Project Explorer.
• Gå til Sett inn -> Moduler.
• Kopier og lim inn koden i modulkodevinduet.

Hvis eksemplet sier at du må lime inn koden i regnearkkodevinduet, dobbeltklikker du regnearkobjektet og kopierer og limer inn koden i kodevinduet.

Etter at du har satt inn koden i arbeidsboken, må du lagre den med utvidelsen .XLSM eller .XLS.

Hvordan kjøre en makro

Etter å ha kopiert koden i VB-editoren, er trinnene for å kjøre makroen som følger:

• Gå til utviklerfanen.
• Klikk på Makro.

• I dialogboksen Makroer velger du makroen som skal kjøres.
• Klikk på Kjør-knappen.

Hvis du ikke finner utviklerfanen på båndet, les denne veiledningen for å lære hvordan du får tak i den.

Hvis koden er limt inn i regnearkkodevinduet, trenger du ikke bekymre deg for å kjøre koden.Den kjører automatisk når den angitte handlingen skjer.

La oss nå gå inn på nyttige makroeksempler som kan hjelpe deg med å automatisere arbeidet ditt og spare tid.

Merk: Du vil finne mange apostrof (') etterfulgt av en linje eller to.Dette er kommentarer som ignoreres når koden kjøres og plasseres som egen-/leserkommentarer.

Hvis du finner noen feil i artikkelen eller koden, vennligst gi meg beskjed.

Excel-makroeksempel

Denne artikkelen beskriver følgende makroeksempler:

Vis alle regneark samtidig

Hvis du jobber med en arbeidsbok med flere skjulte ark, må du vise disse arkene ett etter ett.Dette kan ta litt tid hvis det er mange skjulte ark.

Her er koden for å vise alle arkene i arbeidsboken.

'Denne koden vil vise alle ark 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-løkke (for hver) for å iterere gjennom hvert regneark i arbeidsboken.Deretter endres den synlige egenskapen til arket til synlig.

Her er en detaljert veiledning om hvordan du viser et regneark i Excel ved hjelp av ulike metoder.

Skjul alle regneark unntatt det aktive regnearket

Denne makrokoden kan brukes hvis du jobber med en rapport eller dashboard og ønsker å skjule alle ark unntatt arket som har rapporten/dashboardet.

'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 regnearket alfabetisk ved hjelp av VBA

Denne makrokoden kan være nyttig hvis du har en arbeidsbok med mange ark og du vil sortere disse arkene alfabetisk.Dette kan skje hvis du har arknavnet 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 ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Beskytt alle regneark samtidig

Hvis du har mange ark i arbeidsboken og du vil beskytte alle arkene, kan du bruke denne makrokoden.

Den lar deg spesifisere et passord i kode.Du trenger dette passordet for å oppheve beskyttelsen av arket.

'Denne koden vil beskytte alle arkene på én gang Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'erstatt Test123 med passordet du ønsker For Every ws In Worksheets ws.Protect passord:=passord Neste ws End Sub

Fjern beskyttelsen av alle regnearkene samtidig

Hvis du beskyttet noen eller alle regnearkene dine, kan du oppheve beskyttelsen av dem med bare en liten endring av koden som ble brukt for å beskytte regnearkene.

'Denne koden vil beskytte alle arkene på én gang Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'erstatt Test123 med passordet du ønsker For Every ws In Worksheets ws.Unprotect password:=password Next ws End Sub

Merk at passordet må være det samme passordet som brukes til å låse arket.Hvis ikke, vil du se en feil.

Vis alle rader og kolonner

Denne makrokoden vil vise alle skjulte rader og kolonner.

Dette kan være nyttig hvis du tar filen fra noen andre og vil forsikre deg om 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

Fjern sammenslåingen av alle sammenslåtte celler

Å slå sammen celler til én er en vanlig praksis.Mens det fungerer, vil du ikke kunne sortere dataene når cellene er slått sammen.

Hvis du bruker et regneark med sammenslåtte celler, bruk koden nedenfor for å oppheve sammenslåingen av alle de sammenslåtte cellene samtidig.

'Denne koden vil oppheve sammenslåingen av alle de sammenslåtte cellene Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Merk at jeg anbefaler å bruke alternativet "Center Across Selection" i stedet for alternativet "Merge and Center".

Lagre arbeidsbok med tidsstempel i navnet

Det er mange ganger du kanskje må lage en fungerende versjon.Disse er flotte for langsiktige prosjekter som omhandler filer over tid.

En god praksis er å lagre filen med et tidsstempel.

Ved å bruke et tidsstempel kan du gå tilbake til en 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 ved lagring.

'Denne koden vil lagre 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" & tidsstempel End Sub

Du må spesifisere mappeplassering og filnavn.

I koden ovenfor, "C:UsersUsernameDesktop er mappeplasseringen jeg bruker.Du må spesifisere mappeplasseringen der du vil lagre filen.Jeg brukte også det generiske navnet "WorkbookName" som filnavnprefiks.Du kan spesifisere innhold relatert til prosjektet eller bedriften din.

Lagre hvert regneark som en separat PDF

Hvis du bruker data fra forskjellige år eller avdelinger eller produkter, kan det hende du må lagre forskjellige regneark som PDF-filer.

Selv om dette kan være en tidkrevende prosess hvis det gjøres manuelt, setter VBA fart på sakene.

Her er en VBA-kode som lagrer hvert regneark som en separat PDF.

'Denne koden vil lagre hvert worsheet 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

I koden ovenfor har jeg spesifisert adressen til mappeplasseringen der jeg vil lagre PDF-en.Dessuten vil hver PDF få samme navn som arket.Du må endre denne mappeplasseringen (med mindre navnet ditt også er Sumit og du har lagret det i en testmappe på skrivebordet ditt).

Merk at denne koden bare fungerer for regneark (ikke for diagramark).

Lagre hvert regneark som en separat PDF

Her er koden for å lagre hele arbeidsboken som PDF i den angitte mappen.

'Denne koden vil lagre hele arbeidsboken som PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Du må endre mappeplasseringen for å bruke denne koden.

Konverter alle formler til verdier

Bruk denne koden når du har et regneark med mange formler og du vil konvertere disse formlene til verdier.

'Denne koden vil konvertere alle formler til verdier Sub ConvertToValues() Med ActiveSheet.UsedRange .Value = .Value End With End Sub

Denne koden gjenkjenner automatisk cellen som brukes og konverterer den til en verdi.

Beskytt/lås celler med formler

Når du har mange beregninger og ikke vil slette eller endre det ved et uhell, kan det være lurt å låse celler med formler.

Her er koden som låser alle celler med formler og lar alle andre celler være ulåste.

'Denne makrokoden vil låse alle cellene med formler Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True

Relatert opplæring: Hvordan låse celler i Excel.

Beskytt alle ark i en arbeidsbok

Bruk følgende kode for å beskytte alle ark i en arbeidsbok samtidig.

'Denne koden vil beskytte alle ark i arbeidsboken Sub ProtectAllSheets() Dim ws As Worksheet For Every ws In Worksheets ws. Protect Next ws End Sub

Denne koden vil gå gjennom alle arkene ett etter ett og beskytte dem.

Hvis du vil oppheve beskyttelsen av alle ark, bruk ws.Unprotect i stedet for ws.Protect i koden.

Sett inn en rad etter hver 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

Igjen 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 tilstøtende celler

Tidsstempler er det du bruker når du vil spore aktivitet.

For eksempel vil du kanskje spore aktiviteter som når en bestemt utgift ble påløpt, når en salgsfaktura ble opprettet, når data ble lagt inn i en celle, når en rapport sist ble oppdatert, og så videre.

Bruk denne koden til å sette inn et dato- og tidsstempel i tilstøtende celler når du skriver eller redigerer eksisterende innhold.

'Denne koden vil sette inn et tidsstempel i den tilstøtende cellen Private Sub Worksheet_Change(ByVal Target As Range) on Error GoTo Handler Hvis Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Nå(), "dd-mm-åååå tt:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Merk at du må sette inn denne koden i regnearkkodevinduet (ikke i modulkodevinduet slik vi har gjort i andre Excel-makroeksempler så langt).For å gjøre dette, i VB-editoren, dobbeltklikk på arknavnet du vil ha denne funksjonaliteten for.Deretter kopierer og limer du inn denne koden i kodevinduet på det arket.

Denne koden fungerer også når datainntasting gjøres i kolonne A (merk at koden har en Target.Column = 1 linje).Du kan endre det tilsvarende.

Marker alternative rader i utvalget

Å fremheve vekslende linjer kan i stor grad forbedre lesbarheten til dataene dine.Dette kan være nyttig når du trenger å skrive det ut og bla 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

Merk at jeg spesifiserte fargen som vbCyan i koden.Du kan også spesifisere andre farger (f.eks. vbRed, vbGreen, vbBlue).

Uthev feilstavede celler

Excel har ikke stavekontrollen i Word eller PowerPoint.Mens du kan kjøre stavekontroll ved å trykke F7, er det ingen visuell pekepinn for stavefeil.

Bruk denne koden for å umiddelbart fremheve alle feilstavede celler.

'Denne koden vil fremheve cellene som har feilstavede ord Sub HighlightMisspelledCells() Dim cl As Range For Every cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

Merk at de uthevede cellene er cellene med tekst som Excel tror er feilstavet.I mange tilfeller fremhever den også navn eller merkeord den ikke forstår.

Oppdater alle pivottabeller i en arbeidsbok

Hvis du har flere pivottabeller i arbeidsboken, kan du bruke denne koden til å oppdatere dem alle samtidig.

'Denne koden vil oppdatere all pivottabellen i arbeidsboken Sub RefreshAllPivotTables() Dim PT Som pivottabell for hver PT i ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Du kan lese mer om oppfriskning av pivottabeller her.

Endre store bokstaver i valgte celler til store bokstaver

Mens Excel har formler for å endre store og små bokstaver i tekstbokstaver, lar den deg gjøre dette i et annet sett med celler.

Bruk denne koden for å umiddelbart endre store og små bokstaver i teksten i den valgte teksten.

'Denne koden vil endre utvalget til Store bokstaver Sub ChangeCase() Dim Rng As Range For Every Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Merk at i dette tilfellet bruker jeg UCase for å sette store bokstaver i teksten.Du kan bruke LCase for små bokstaver.

Merk alle celler med kommentarer

Bruk følgende kode for å markere alle celler med kommentarer i dem.

'Denne koden vil fremheve celler som har kommentarer' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

I dette tilfellet bruker jeg vbBlue for å gi cellen en blå farge.Du kan endre den til en annen farge om nødvendig.

Uthev tomme celler med VBA

Selv om du kan bruke betinget formatering eller bruke dialogboksen Gå til spesial for å markere tomme celler, er det best å bruke en makro hvis du må gjøre dette ofte.

Når den er opprettet, kan du ha denne makroen i hurtigtilgangsverktøylinjen eller lagre den i din personlige makroarbeidsbok.

Her er VBA-makrokoden:

'Denne koden vil fremheve alle de tomme cellene i datasettet Sub HighlightBlankCells() Dim Dataset som Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

I denne koden spesifiserte jeg tomme celler uthevet i rødt.Du kan velge andre farger som blå, gul, cyan, etc.

Hvordan sortere data etter en enkelt kolonne

Du kan bruke følgende kode for å sortere data etter en spesifisert kolonne.

Sub SortDataHeader() 
Range("DataRange").Sorteringsnøkkel1:=Rekkevidde("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

Merk at jeg opprettet et navngitt område kalt "DataRange" og brukte det i stedet for cellereferansen.

Det er også tre nøkkelparametere som brukes her:

• Nøkkel1 – Dette er nøkkelen som du vil sortere datasettet med.I eksempelkoden ovenfor vil dataene bli sortert basert på verdiene i kolonne A.
• Rekkefølge1 – Her må du spesifisere om du vil sortere dataene i stigende eller synkende rekkefølge.
• Header – Her må du spesifisere om dataene dine har en header eller ikke.

Les mer om hvordan du sorterer data i Excel ved hjelp av VBA.

Hvordan sortere data etter flere kolonner

Anta at du har et datasett som ser slik ut:

Her er koden for å sortere data basert på flere kolonner:

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

Merk at jeg her har spesifisert å sortere på kolonne A først og deretter på kolonne B.

Utgangen vil se slik ut:

Hvordan få bare den numeriske delen fra en streng i Excel

Hvis du bare vil trekke ut den numeriske delen eller tekstdelen fra strengen, kan du lage en egendefinert funksjon i VBA.

Du kan deretter bruke denne VBA-funksjonen i regnearket ditt (akkurat som en vanlig Excel-funksjon), og den vil kun trekke ut nummeret eller tekstdelen fra strengen.

Som vist under:

Her er VBA-koden som vil lage en funksjon som trekker ut den numeriske delen fra en streng:

'Denne VBA-koden vil lage en funksjon for å hente 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) ) Så Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

Du må legge inn koden i en modul og deretter kan du bruke funksjonen =GetNumeric i regnearket.

Denne funksjonen tar bare én parameter, cellereferansen til cellen som den numeriske delen skal hentes fra.

På samme måte vil funksjonen nedenfor kun hente tekstdelen fra en streng i Excel:

'Denne VBA-koden vil lage en funksjon for å hente 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 & Midt(CellRef, i, 1) Neste i GetText = Resultat sluttfunksjon

Så dette er noen nyttige Excel-makrokoder som du kan bruke i ditt daglige arbeid for å automatisere oppgaver og øke produktiviteten.