Att använda Excel -makron kan påskynda arbetet och spara mycket tid.
Ett sätt att få VBA -koden är att spela in makrot och ta koden som den genererar. Den koden med makroinspelare är dock ofta full av kod som egentligen inte behövs. Makroinspelaren har också vissa begränsningar.
Så det lönar sig att ha en samling användbara VBA -makrokoder som du kan ha i bakfickan och använda den vid behov.
Medan det kan ta tid innan du skriver en Excel VBA -makrokod, när det är klart kan du hålla den tillgänglig som referens och använda den när du behöver den nästa gång.
I den här massiva artikeln kommer jag att lista några användbara Excel -makroexempel som jag ofta behöver och hålla undan i mitt privata valv.
Jag kommer att uppdatera denna handledning med fler makroexempel. Om du tycker att något borde finnas på listan är det bara att lämna en kommentar.
Du kan bokmärka denna sida för framtida referens.
Nu innan jag går in i makroexemplet och ger dig VBA -koden, låt mig först visa dig hur du använder dessa exempelkoder.
Använda koden från Excel -makroexempel
Här är stegen du måste följa för att använda koden från något av exemplen:
- Öppna arbetsboken där du vill använda makrot.
- Håll ner ALT -tangenten och tryck på F11. Detta öppnar VB Editor.
- Högerklicka på något av objekten i projektutforskaren.
- Gå till Infoga -> Modul.
- Kopiera och klistra in koden i modulkodfönstret.
Om exemplet säger att du måste klistra in koden i kalkylbladets kodfönster, dubbelklicka på kalkylbladets objekt och kopiera klistra in koden i kodfönstret.
När du har infogat koden i en arbetsbok måste du spara den med ett .XLSM- eller .XLS -tillägg.
Hur man kör makrot
När du har kopierat koden i VB Editor, här är stegen för att köra makrot:
- Gå till fliken Utvecklare.
- Klicka på Makron.
- Markera det makro du vill köra i dialogrutan Makro.
- Klicka på knappen Kör.
Om du inte hittar utvecklarfliken i menyfliksområdet, läs den här självstudien för att lära dig hur du får det.
Relaterad handledning: Olika sätt att köra ett makro i Excel.
Om koden klistras in i kalkylbladets kodfönster behöver du inte oroa dig för att köra koden. Den körs automatiskt när den angivna åtgärden inträffar.
Låt oss nu gå in på de användbara makroexemplen som kan hjälpa dig att automatisera arbete och spara tid.
Obs! Du hittar många förekomster av en apostrof (‘) följt av en rad eller två. Det här är kommentarer som ignoreras när koden körs och placeras som anteckningar för själv/läsare.
Om du hittar något fel i artikeln eller koden, var snäll och meddela mig.
Exempel på makroer i Excel
Nedanstående makroexempel behandlas i denna artikel:
Ta bort alla kalkylblad på en gång
Om du arbetar i en arbetsbok som har flera dolda ark måste du ta bort dessa blad en efter en. Detta kan ta lite tid om det finns många dolda ark.
Här är koden som visar alla kalkylblad i arbetsboken.
'Denna kod kommer att visa alla blad i arbetsboken Sub UnhideAllWoksheets () Dim ws As Worksheet For each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Ovanstående kod använder en VBA -slinga (för varje) för att gå igenom varje kalkylblad i arbetsboken. Den ändrar sedan den synliga egenskapen för kalkylbladet till synlig.
Här är en detaljerad handledning om hur du använder olika metoder för att ta bort blad i Excel.
Dölj alla kalkylblad utom det aktiva bladet
Om du arbetar med en rapport eller instrumentpanel och du vill dölja hela kalkylbladet utom det som har rapporten/instrumentpanelen kan du använda den här makrokoden.
'Det här makrot döljer allt kalkylblad utom det aktiva bladet Sub HideAllExceptActiveSheet () Dim ws As Worksheet For each ws In ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub
Sortera kalkylblad alfabetiskt med VBA
Om du har en arbetsbok med många kalkylblad och du vill sortera dessa alfabetiskt, kan den här makrokoden vara väldigt användbar. Detta kan vara fallet om du har bladnamn som år eller anställdas namn eller produktnamn.
'Denna kod kommer att sortera kalkylblad alfabetiskt 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
Skydda alla kalkylblad på en gång
Om du har många kalkylblad i en arbetsbok och du vill skydda alla blad kan du använda den här makrokoden.
Det låter dig ange lösenordet i koden. Du behöver det här lösenordet för att avskydda kalkylbladet.
'Den här koden skyddar alla blad på en gång Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' ersätt Test123 med det lösenord du vill ha För varje ws I Worksheets ws.Protect password: = password Next ws Avsluta Sub
Skydda alla kalkylblad på en gång
Om du har några eller alla kalkylblad skyddade kan du bara använda en liten ändring av koden som används för att skydda blad för att avskydda den.
'Den här koden skyddar alla blad på en gång Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' ersätt Test123 med det lösenord du vill ha För varje ws I Worksheets ws.Unprotect password: = password Next ws Avsluta Sub
Observera att lösenordet måste vara detsamma som har använts för att låsa kalkylblad. Om det inte är det ser du ett fel.
Ta bort alla rader och kolumner
Denna makrokod visar alla dolda rader och kolumner.
Detta kan vara till stor hjälp om du får en fil från någon annan och vill vara säker på att det inte finns några dolda rader/kolumner.
'Den här koden visar alla rader och kolumner i kalkylbladets sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Ta bort alla sammanslagna celler
Det är en vanlig metod att slå ihop celler för att göra det till en. Medan det gör jobbet kommer du inte att kunna sortera data när celler slås samman.
Om du arbetar med ett kalkylblad med sammanslagna celler kan du använda koden nedan för att slå ihop alla sammanslagna celler på en gång.
'Denna kod kommer att sammanfoga alla de sammanslagna cellerna Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Observera att i stället för Sammanfoga och Centrera rekommenderar jag att du använder alternativet Mitt över markering.
Spara arbetsbok med tidsstämpel i sitt namn
Mycket tid kan du behöva skapa versioner av ditt arbete. Dessa är ganska användbara i långa projekt där du arbetar med en fil över tid.
En bra metod är att spara filen med tidsstämplar.
Genom att använda tidsstämplar kan du gå tillbaka till en viss fil för att se vilka ändringar som gjordes eller vilken data som användes.
Här är koden som automatiskt sparar arbetsboken i den angivna mappen och lägger till en tidsstämpel när den sparas.
'Den här koden sparar filen med en tidsstämpel i sitt namn Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & tidsstämpel End Sub
Du måste ange mappplatsen och filnamnet.
I koden ovan är "C: UsersUsernameDesktop den mappplats jag har använt. Du måste ange mapplatsen där du vill spara filen. Jag har också använt ett generiskt namn "WorkbookName" som filnamnsprefix. Du kan ange något som rör ditt projekt eller företag.
Spara varje kalkylblad som en separat PDF -fil
Om du arbetar med data för olika år eller avdelningar eller produkter kan du behöva spara olika kalkylblad som PDF -filer.
Även om det kan vara en tidskrävande process om det görs manuellt, kan VBA verkligen påskynda det.
Här är en VBA -kod som sparar varje kalkylblad som en separat PDF.
'Den här koden sparar varje arbetsblad 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 ovan har jag angett adressen till mappplatsen där jag vill spara PDF -filerna. Varje PDF -fil får också samma namn som för kalkylbladet. Du måste ändra den här mappplatsen (om inte ditt namn också är Sumit och du sparar det i en testmapp på skrivbordet).
Observera att den här koden endast fungerar för kalkylblad (och inte diagramark).
Spara varje kalkylblad som en separat PDF -fil
Här är koden som sparar hela din arbetsbok som en PDF -fil i den angivna mappen.
'Denna kod sparar hela arbetsboken som PDF -del SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Du måste ändra mappplatsen för att använda den här koden.
Konvertera alla formler till värden
Använd den här koden när du har ett kalkylblad som innehåller många formler och du vill konvertera dessa formler till värden.
'Denna kod kommer att konvertera alla formler till värden Sub ConvertToValues () With ActiveSheet.UsedRange .Value = .Value End With End Sub
Denna kod identifierar automatiskt celler som används och konverterar det till värden.
Skydda/lås celler med formler
Du kanske vill låsa celler med formler när du har många beräkningar och du inte vill radera det eller ändra det av misstag.
Här är koden som låser alla celler som har formler, medan alla andra celler inte är låsta.
'Den här makrokoden låser alla celler med formler Sub LockCellsWithFormulas () Med ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub Sub
Relaterad handledning: Hur man låser celler i Excel.
Skydda alla kalkylblad i arbetsboken
Använd koden nedan för att skydda alla kalkylblad i en arbetsbok på en gång.
'Den här koden skyddar alla blad i arbetsboken Sub ProtectAllSheets () Dim ws As Worksheet For each ws In Worksheets ws.Protect Next ws End Sub
Den här koden går igenom alla kalkylblad en efter en och skyddar den.
Om du vill avskydda alla kalkylblad använder du ws.Unprotect istället för ws.Protect i koden.
Infoga en rad efter varannan rad i urvalet
Använd den här koden när du vill infoga en tom rad efter varje rad i det valda intervallet.
'Denna kod kommer att infoga en rad efter varje rad i urvalet 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. Infoga ActiveCell.Offset (2, 0) .Välj Next i End Sub
På samma sätt kan du ändra denna kod för att infoga en tom kolumn efter varje kolumn i det valda intervallet.
Infoga datum och tidsstämpel automatiskt i den intilliggande cellen
En tidsstämpel är något du använder när du vill spåra aktiviteter.
Till exempel kanske du vill spåra aktiviteter som när en särskild kostnad uppstod, vilken tid skapades försäljningsfakturan, när gjordes datainmatningen i en cell, när uppdaterades rapporten senast osv.
Använd den här koden för att infoga en datum- och tidsstämpel i den intilliggande cellen när en post görs eller det befintliga innehållet redigeras.
'Denna kod kommer att infoga en tidsstämpel i den intilliggande 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 (Nu (), "dd-mm-åååå hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Observera att du måste infoga den här koden i kalkylfönstret för kalkylbladet (och inte fönstret i modulkod som vi har gjort i andra Excel -makroexempel hittills). För att göra detta dubbelklickar du på det bladnamn som du vill ha den här funktionen i VB Editor. Kopiera sedan och klistra in den här koden i det bladets kodfönster.
Denna kod får också att fungera när datainmatningen görs i kolumn A (observera att koden har raden Target.Column = 1). Du kan ändra detta i enlighet därmed.
Markera alternativa rader i markeringen
Att markera alternativa rader kan öka läsbarheten för dina data enormt. Detta kan vara användbart när du behöver ta ut en utskrift och gå igenom data.
Här är en kod som omedelbart kommer att markera alternativa rader i valet.
'Den här koden markerar alternativa rader i urvalet Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Urval för varje Myrow i Myrange.Rows If Myrow.Row Mod 2 = 1 Sedan Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Observera att jag har angett färgen som vbCyan i koden. Du kan också ange andra färger (t.ex. vbRed, vbGreen, vbBlue).
Markera celler med felstavade ord
Excel har inte en stavningskontroll som den har i Word eller PowerPoint. Medan du kan köra stavningskontrollen genom att trycka på F7 -tangenten, finns det ingen visuell signal när det finns ett stavfel.
Använd den här koden för att omedelbart markera alla celler som har ett stavfel.
'Den här koden markerar cellerna som har felstavade ord Sub HighlightMisspelledCells () Dim cl som intervall för varje cl i ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Sedan cl.Interior.Color = vbRed End If Next cl Avsluta sub
Observera att de markerade cellerna är de som har text som Excel betraktar som ett stavfel. I många fall skulle det också markera namn eller varumärkesterm som det inte förstår.
Uppdatera alla pivottabeller i arbetsboken
Om du har mer än en pivottabell i arbetsboken kan du använda den här koden för att uppdatera alla dessa pivottabeller samtidigt.
'Den här koden uppdaterar alla pivottabeller i arbetsboken Sub RefreshAllPivotTables () Dim PT As PivotTable for each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Du kan läsa mer om uppdatering av pivottabeller här.
Ändra bokstaven för utvalda celler till versaler
Medan Excel har formlerna för att ändra bokstäver i texten, får du dig att göra det i en annan uppsättning celler.
Använd den här koden för att omedelbart ändra bokstaven för texten i den markerade texten.
'Den här koden ändrar markeringen till stora bokstäver Sub ChangeCase () Dim Rng som intervall för varje Rng i Selection.Cells Om Rng.HasFormula = False Sedan Rng.Value = UCase (Rng.Value) Avsluta om nästa Rng End Sub
Observera att i det här fallet har jag använt UCase för att göra textfallet Övre. Du kan använda LCase för små bokstäver.
Markera alla celler med kommentarer
Använd koden nedan för att markera alla celler som har kommentarer i den.
"Denna kod markerar celler som har kommentarer" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
I det här fallet har jag använt vbBlue för att ge cellerna en blå färg. Du kan ändra detta till andra färger om du vill.
Markera tomma celler med VBA
Även om du kan markera en tom cell med villkorlig formatering eller använda dialogrutan Gå till special, men om du måste göra det ganska ofta är det bättre att använda ett makro.
När du har skapat det kan du ha det här makrot i verktygsfältet Snabbåtkomst eller spara det i din personliga makro -arbetsbok.
Här är VBA -makrokoden:
'Denna kod markerar alla tomma celler i datasetet Sub HighlightBlankCells () Dim Dataset som Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
I den här koden har jag specificerat de tomma celler som ska markeras i den röda färgen. Du kan välja andra färger som blått, gult, cyan, etc.
Hur man sorterar data efter en enda kolumn
Du kan använda koden nedan för att sortera data efter den angivna kolumnen.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub
Observera att jag har skapat ett namngivet område med namnet 'DataRange' och har använt det istället för cellreferenser.
Det finns också tre viktiga parametrar som används här:
- Nyckel1 - Det här är det som du vill sortera datamängden på. I ovanstående exempelkod kommer data att sorteras baserat på värdena i kolumn A.
- Order- Här måste du ange om du vill sortera data i stigande eller fallande ordning.
- Rubrik - Här måste du ange om dina data har rubriker eller inte.
Läs mer om hur du sorterar data i Excel med VBA.
Så här sorterar du data efter flera kolumner
Anta att du har en dataset som visas nedan:
Nedan är koden som kommer att sortera data baserat på flera kolumner:
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. Applicera Slut med slut Sub
Observera att här har jag angett att först sortera baserat på kolumn A och sedan baserat på kolumn B.
Utmatningen skulle vara något som visas nedan:
Så här får du bara den numeriska delen från en sträng i Excel
Om du bara vill extrahera den numeriska delen eller endast textdelen från en sträng kan du skapa en anpassad funktion i VBA.
Du kan sedan använda den här VBA -funktionen i kalkylbladet (precis som vanliga Excel -funktioner) och den extraherar endast den numeriska eller textdelen från strängen.
Något som visas nedan:
Nedan finns VBA -koden som skapar en funktion för att extrahera numerisk del från en sträng:
'Den här VBA -koden skapar en funktion för att få den numeriska delen från en sträng Funktion GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Sedan Resultat = Resultat & Mitt (CellRef, i, 1) Nästa i GetNumeric = Resultatavslutningsfunktion
Du behöver plats i kod i en modul, och sedan kan du använda funktionen = GetNumeric i kalkylbladet.
Denna funktion tar bara ett argument, vilket är cellreferensen för cellen från vilken du vill hämta den numeriska delen.
På samma sätt nedan är funktionen som får dig bara textdelen från en sträng i Excel:
'Den här VBA -koden skapar en funktion för att få textdelen från en sträng Funktion GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Sedan Resultat = Resultat & Mitt (CellRef, i, 1) Nästa i GetText = Resultatavslutningsfunktion
Så det här är några av de användbara Excel-makrokoderna som du kan använda i ditt dagliga arbete för att automatisera uppgifter och bli mycket mer produktiva.
