Die Verwendung von Excel-Makros kann die Arbeit beschleunigen und Ihnen viel Zeit sparen.
Eine Möglichkeit, den VBA-Code zu erhalten, besteht darin, das Makro aufzuzeichnen und den generierten Code zu verwenden. Dieser Code des Makrorekorders ist jedoch oft voller Code, der nicht wirklich benötigt wird. Auch der Makrorekorder hat einige Einschränkungen.
Es lohnt sich also, eine Sammlung nützlicher VBA-Makrocodes zu haben, die Sie in Ihrer Gesäßtasche haben und bei Bedarf verwenden können.
Während das Schreiben eines Excel-VBA-Makrocodes anfangs einige Zeit in Anspruch nehmen kann, können Sie ihn nach Abschluss als Referenz verfügbar halten und ihn jederzeit verwenden, wenn Sie ihn das nächste Mal benötigen.
In diesem umfangreichen Artikel werde ich einige nützliche Excel-Makrobeispiele auflisten, die ich oft benötige und in meinem privaten Tresor verwahre.
Ich werde dieses Tutorial mit weiteren Makrobeispielen aktualisieren. Wenn Sie der Meinung sind, dass etwas auf der Liste stehen sollte, hinterlassen Sie einfach einen Kommentar.
Sie können diese Seite mit einem Lesezeichen versehen, um später darauf zurückgreifen zu können.
Bevor ich nun zum Makrobeispiel komme und Ihnen den VBA-Code gebe, möchte ich Ihnen zunächst zeigen, wie Sie diese Beispielcodes verwenden.
Verwenden des Codes aus Excel-Makrobeispielen
Hier sind die Schritte, die Sie ausführen müssen, um den Code aus einem der Beispiele zu verwenden:
- Öffnen Sie die Arbeitsmappe, in der Sie das Makro verwenden möchten.
- Halten Sie die ALT-Taste gedrückt und drücken Sie F11. Dies öffnet den VB-Editor.
- Klicken Sie mit der rechten Maustaste auf eines der Objekte im Projektexplorer.
- Gehen Sie zu Einfügen -> Modul.
- Kopieren Sie den Code und fügen Sie ihn in das Modulcodefenster ein.
Falls das Beispiel besagt, dass Sie den Code in das Arbeitsblatt-Codefenster einfügen müssen, doppelklicken Sie auf das Arbeitsblatt-Objekt und kopieren Sie den Code in das Codefenster.
Nachdem Sie den Code in eine Arbeitsmappe eingefügt haben, müssen Sie ihn mit der Erweiterung .XLSM oder .XLS speichern.
So führen Sie das Makro aus
Nachdem Sie den Code in den VB-Editor kopiert haben, gehen Sie wie folgt vor, um das Makro auszuführen:
- Wechseln Sie zur Registerkarte Entwickler.
- Klicken Sie auf Makros.
- Wählen Sie im Dialogfeld Makro das Makro aus, das Sie ausführen möchten.
- Klicken Sie auf die Schaltfläche Ausführen.
Falls Sie die Registerkarte Entwickler im Menüband nicht finden können, lesen Sie dieses Tutorial, um zu erfahren, wie Sie sie erhalten.
Verwandtes Tutorial: Verschiedene Möglichkeiten zum Ausführen eines Makros in Excel.
Falls der Code in das Arbeitsblatt-Codefenster eingefügt wird, müssen Sie sich nicht um die Ausführung des Codes kümmern. Es wird automatisch ausgeführt, wenn die angegebene Aktion auftritt.
Kommen wir nun zu den nützlichen Makrobeispielen, die Ihnen helfen können, die Arbeit zu automatisieren und Zeit zu sparen.
Hinweis: Sie werden viele Instanzen eines Apostrophs (‘) finden, gefolgt von ein oder zwei Zeilen. Dies sind Kommentare, die beim Ausführen des Codes ignoriert und als Notizen für sich selbst / den Leser platziert werden.
Falls Sie einen Fehler im Artikel oder im Code finden, seien Sie bitte großartig und lassen Sie es mich wissen.
Excel-Makro-Beispiele
Die folgenden Makrobeispiele werden in diesem Artikel behandelt:
Alle Arbeitsblätter auf einmal einblenden
Wenn Sie in einer Arbeitsmappe mit mehreren ausgeblendeten Blättern arbeiten, müssen Sie diese Blätter nacheinander einblenden. Dies kann einige Zeit dauern, falls es viele versteckte Blätter gibt.
Hier ist der Code, der alle Arbeitsblätter in der Arbeitsmappe einblendet.
'Dieser Code blendet alle Blätter in der Arbeitsmappe ein Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Der obige Code verwendet eine VBA-Schleife (For Each), um alle Arbeitsblätter in der Arbeitsmappe zu durchlaufen. Es ändert dann die sichtbare Eigenschaft des Arbeitsblatts in sichtbar.
Hier finden Sie ein detailliertes Tutorial zur Verwendung verschiedener Methoden zum Einblenden von Blättern in Excel.
Alle Arbeitsblätter außer dem aktiven Blatt ausblenden
Wenn Sie an einem Bericht oder Dashboard arbeiten und das gesamte Arbeitsblatt außer dem mit dem Bericht/Dashboard ausblenden möchten, können Sie diesen Makrocode verwenden.
'Dieses Makro wird das gesamte Arbeitsblatt außer dem aktiven Blatt ausblenden 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
Arbeitsblätter mit VBA alphabetisch sortieren
Wenn Sie eine Arbeitsmappe mit vielen Arbeitsblättern haben und diese alphabetisch sortieren möchten, kann dieser Makrocode sehr praktisch sein. Dies kann der Fall sein, wenn Sie Blattnamen als Jahre oder Mitarbeiternamen oder Produktnamen haben.
'Dieser Code sortiert die Arbeitsblätter alphabetisch 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
Schützen Sie alle Arbeitsblätter auf einmal
Wenn Sie viele Arbeitsblätter in einer Arbeitsmappe haben und alle Blätter schützen möchten, können Sie diesen Makrocode verwenden.
Es ermöglicht Ihnen, das Passwort innerhalb des Codes anzugeben. Sie benötigen dieses Kennwort, um den Schutz des Arbeitsblatts aufzuheben.
'Dieser Code schützt alle Blätter auf einmal End Sub
Alle Arbeitsblätter auf einmal aufheben
Wenn Sie einige oder alle Arbeitsblätter geschützt haben, können Sie einfach eine geringfügige Änderung des Codes verwenden, der zum Schützen von Blättern verwendet wird, um den Schutz aufzuheben.
'Dieser Code schützt alle Blätter auf einmal End Sub
Beachten Sie, dass das Kennwort das gleiche sein muss, das zum Sperren der Arbeitsblätter verwendet wurde. Ist dies nicht der Fall, wird ein Fehler angezeigt.
Alle Zeilen und Spalten einblenden
Dieser Makrocode blendet alle ausgeblendeten Zeilen und Spalten ein.
Dies kann sehr hilfreich sein, wenn Sie eine Datei von einer anderen Person erhalten und sicherstellen möchten, dass keine versteckten Zeilen/Spalten vorhanden sind.
'Dieser Code blendet alle Zeilen und Spalten im Worksheet Sub ein. UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Alle zusammengeführten Zellen aufheben
Es ist eine gängige Praxis, Zellen zusammenzuführen, um sie zu einer Einheit zu machen. Während es die Arbeit erledigt, können Sie die Daten beim Zusammenführen von Zellen nicht sortieren.
Falls Sie mit einem Arbeitsblatt mit verbundenen Zellen arbeiten, verwenden Sie den folgenden Code, um alle verbundenen Zellen auf einmal aufzuheben.
'Dieser Code hebt alle zusammengeführten Zellen auf Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
Beachten Sie, dass ich anstelle von Zusammenführen und Zentrieren empfehle, die Option Über Auswahl zentrieren zu verwenden.
Arbeitsmappe mit TimeStamp im Namen speichern
Möglicherweise müssen Sie viel Zeit für die Erstellung von Versionen Ihrer Arbeit benötigen. Diese sind bei langen Projekten, in denen Sie im Laufe der Zeit mit einer Datei arbeiten, sehr hilfreich.
Es empfiehlt sich, die Datei mit Zeitstempeln zu speichern.
Durch die Verwendung von Zeitstempeln können Sie zu einer bestimmten Datei zurückkehren, um zu sehen, welche Änderungen vorgenommen oder welche Daten verwendet wurden.
Hier ist der Code, der die Arbeitsmappe automatisch im angegebenen Ordner speichert und beim Speichern einen Zeitstempel hinzufügt.
'Dieser Code speichert die Datei mit einem Zeitstempel im Namen Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & Zeitstempel End Sub
Sie müssen den Speicherort des Ordners und den Dateinamen angeben.
Im obigen Code ist „C:UsersUsernameDesktop der von mir verwendete Ordnerspeicherort. Sie müssen den Speicherort des Ordners angeben, in dem Sie die Datei speichern möchten. Außerdem habe ich einen generischen Namen „WorkbookName“ als Dateinamenspräfix verwendet. Sie können etwas mit Bezug zu Ihrem Projekt oder Unternehmen angeben.
Jedes Arbeitsblatt als separates PDF speichern
Wenn Sie mit Daten für verschiedene Jahre oder Sparten oder Produkte arbeiten, müssen Sie möglicherweise verschiedene Arbeitsblätter als PDF-Dateien speichern.
Obwohl es ein zeitaufwändiger Prozess sein kann, wenn er manuell durchgeführt wird, kann VBA ihn wirklich beschleunigen.
Hier ist ein VBA-Code, der jedes Arbeitsblatt als separates PDF speichert.
'Dieser Code speichert jedes Arbeitsblatt als separates PDF-Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
Im obigen Code habe ich die Adresse des Ordners angegeben, in dem ich die PDFs speichern möchte. Außerdem erhält jedes PDF den gleichen Namen wie das Arbeitsblatt. Sie müssen diesen Ordnerspeicherort ändern (es sei denn, Ihr Name ist ebenfalls Sumit und Sie speichern ihn in einem Testordner auf dem Desktop).
Beachten Sie, dass dieser Code nur für Arbeitsblätter (und nicht für Diagrammblätter) funktioniert.
Jedes Arbeitsblatt als separates PDF speichern
Hier ist der Code, der Ihre gesamte Arbeitsmappe als PDF im angegebenen Ordner speichert.
'Dieser Code speichert die gesamte Arbeitsmappe als PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Sie müssen den Speicherort des Ordners ändern, um diesen Code zu verwenden.
Alle Formeln in Werte umwandeln
Verwenden Sie diesen Code, wenn Sie ein Arbeitsblatt mit vielen Formeln haben und diese Formeln in Werte konvertieren möchten.
'Dieser Code konvertiert alle Formeln in Werte Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
Dieser Code identifiziert automatisch verwendete Zellen und wandelt ihn in Werte um.
Zellen mit Formeln schützen/sperren
Möglicherweise möchten Sie Zellen mit Formeln sperren, wenn Sie viele Berechnungen haben und sie nicht versehentlich löschen oder ändern möchten.
Hier ist der Code, der alle Zellen mit Formeln sperrt, während alle anderen Zellen nicht gesperrt sind.
'Dieser Makrocode sperrt alle Zellen mit Formeln Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
Verwandtes Tutorial: So sperren Sie Zellen in Excel.
Alle Arbeitsblätter in der Arbeitsmappe schützen
Verwenden Sie den folgenden Code, um alle Arbeitsblätter in einer Arbeitsmappe auf einmal zu schützen.
'Dieser Code schützt alle Blätter in der Arbeitsmappe Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub
Dieser Code durchläuft nacheinander alle Arbeitsblätter und schützt sie.
Falls Sie den Schutz aller Arbeitsblätter aufheben möchten, verwenden Sie im Code ws.Unprotect anstelle von ws.Protect.
Eine Zeile nach jeder anderen Zeile in die Auswahl einfügen
Verwenden Sie diesen Code, wenn Sie nach jeder Zeile im ausgewählten Bereich eine leere Zeile einfügen möchten.
'Dieser Code fügt nach jeder Zeile in der Auswahl eine Zeile ein 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. ActiveCell.Offset(2, 0) einfügen.Wählen Sie Next i End Sub
Ebenso können Sie diesen Code ändern, um nach jeder Spalte im ausgewählten Bereich eine leere Spalte einzufügen.
Datum und Zeitstempel automatisch in die angrenzende Zelle einfügen
Ein Zeitstempel ist etwas, das Sie verwenden, wenn Sie Aktivitäten verfolgen möchten.
Sie möchten beispielsweise Aktivitäten nachverfolgen, z. B. wann eine bestimmte Ausgabe angefallen ist, wann die Verkaufsrechnung erstellt wurde, wann die Dateneingabe in eine Zelle erfolgt, wann der Bericht zuletzt aktualisiert wurde usw.
Verwenden Sie diesen Code, um einen Datums- und Zeitstempel in die angrenzende Zelle einzufügen, wenn eine Eingabe gemacht oder der vorhandene Inhalt bearbeitet wird.
'Dieser Code fügt einen Zeitstempel in die angrenzende Zelle ein 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-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub
Beachten Sie, dass Sie diesen Code in das Arbeitsblattcodefenster einfügen müssen (und nicht in das Modulcodefenster, wie wir es in anderen Excel-Makrobeispielen bisher getan haben). Doppelklicken Sie dazu im VB-Editor auf den Blattnamen, auf dem Sie diese Funktionalität wünschen. Kopieren Sie dann diesen Code und fügen Sie ihn in das Codefenster dieses Blatts ein.
Außerdem funktioniert dieser Code, wenn die Dateneingabe in Spalte A erfolgt (beachten Sie, dass der Code die Zeile Target.Column = 1 enthält). Sie können dies entsprechend ändern.
Markieren Sie alternative Zeilen in der Auswahl
Das Hervorheben alternativer Zeilen kann die Lesbarkeit Ihrer Daten enorm erhöhen. Dies kann nützlich sein, wenn Sie einen Ausdruck erstellen und die Daten durchgehen müssen.
Hier ist ein Code, der sofort alternative Zeilen in der Auswahl hervorhebt.
'Dieser Code würde alternative Zeilen in der Auswahl hervorheben Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Auswahl für jede Myrow in Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Beachten Sie, dass ich im Code die Farbe als vbCyan angegeben habe. Sie können auch andere Farben angeben (z. B. vbRed, vbGreen, vbBlue).
Markieren Sie Zellen mit falsch geschriebenen Wörtern
Excel hat keine Rechtschreibprüfung wie in Word oder PowerPoint. Während Sie die Rechtschreibprüfung durch Drücken der Taste F7 ausführen können, gibt es keinen visuellen Hinweis auf einen Rechtschreibfehler.
Verwenden Sie diesen Code, um sofort alle Zellen hervorzuheben, die einen Rechtschreibfehler enthalten.
'Dieser Code markiert die Zellen mit falsch geschriebenen Wörtern Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Ende Sub
Beachten Sie, dass die hervorgehobenen Zellen diejenigen sind, die Text enthalten, den Excel als Rechtschreibfehler betrachtet. In vielen Fällen würde es auch Namen oder Markenbegriffe hervorheben, die es nicht versteht.
Alle Pivot-Tabellen in der Arbeitsmappe aktualisieren
Wenn die Arbeitsmappe mehr als eine Pivot-Tabelle enthält, können Sie mit diesem Code alle diese Pivot-Tabellen gleichzeitig aktualisieren.
'Dieser Code aktualisiert die gesamte Pivot-Tabelle in der Arbeitsmappenunter RefreshAllPivotTables() Dim PT As PivotTable für jedes PT in ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Weitere Informationen zum Aktualisieren von Pivot-Tabellen finden Sie hier.
Ändern Sie die Groß-/Kleinschreibung ausgewählter Zellen in Großbuchstaben
Während Excel über die Formeln zum Ändern der Groß-/Kleinschreibung des Textes verfügt, können Sie dies in einem anderen Satz von Zellen tun.
Verwenden Sie diesen Code, um die Groß-/Kleinschreibung des Textes im ausgewählten Text sofort zu ändern.
'Dieser Code ändert die Auswahl in Großbuchstaben 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
Beachten Sie, dass ich in diesem Fall UCase verwendet habe, um den Text Groß-/Kleinschreibung zu machen. Sie können LCase für Kleinbuchstaben verwenden.
Markieren Sie alle Zellen mit Kommentaren
Verwenden Sie den folgenden Code, um alle Zellen hervorzuheben, die Kommentare enthalten.
'Dieser Code markiert Zellen mit Kommentaren` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
In diesem Fall habe ich vbBlue verwendet, um den Zellen eine blaue Farbe zu verleihen. Sie können dies zu anderen Farben ändern, wenn Sie möchten.
Markieren Sie leere Zellen mit VBA
Während Sie leere Zellen mit bedingter Formatierung hervorheben oder das Dialogfeld Gehe zu Spezial verwenden können, ist es besser, ein Makro zu verwenden, wenn Sie dies häufig tun müssen.
Nach der Erstellung können Sie dieses Makro in der Symbolleiste für den Schnellzugriff haben oder in Ihrer persönlichen Makro-Arbeitsmappe speichern.
Hier ist der VBA-Makrocode:
'Dieser Code markiert alle leeren Zellen im Datensatz Sub HighlightBlankCells() Dim Dataset als Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
In diesem Code habe ich die leeren Zellen angegeben, die rot hervorgehoben werden sollen. Sie können andere Farben wie Blau, Gelb, Cyan usw. auswählen.
So sortieren Sie Daten nach einer einzelnen Spalte
Sie können den folgenden Code verwenden, um Daten nach der angegebenen Spalte zu sortieren.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
Beachten Sie, dass ich einen benannten Bereich mit dem Namen ‚DataRange‘ erstellt und anstelle der Zellbezüge verwendet habe.
Außerdem gibt es drei wichtige Parameter, die hier verwendet werden:
- Key1 - Dies ist der, nach dem Sie den Datensatz sortieren möchten. Im obigen Beispielcode werden die Daten basierend auf den Werten in Spalte A sortiert.
- Reihenfolge - Hier müssen Sie angeben, ob Sie die Daten aufsteigend oder absteigend sortieren möchten.
- Kopfzeile - Hier müssen Sie angeben, ob Ihre Daten Kopfzeilen haben oder nicht.
Lesen Sie mehr darüber, wie Sie Daten in Excel mit VBA sortieren.
So sortieren Sie Daten nach mehreren Spalten
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Unten ist der Code, der die Daten basierend auf mehreren Spalten sortiert:
Sub SortMultipleColumns() With 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
Beachten Sie, dass ich hier angegeben habe, zuerst nach Spalte A und dann nach Spalte B zu sortieren.
Die Ausgabe wäre etwas wie unten gezeigt:
So erhalten Sie nur den numerischen Teil aus einer Zeichenfolge in Excel
Wenn Sie nur den numerischen Teil oder nur den Textteil aus einer Zeichenfolge extrahieren möchten, können Sie in VBA eine benutzerdefinierte Funktion erstellen.
Sie können diese VBA-Funktion dann im Arbeitsblatt verwenden (genau wie normale Excel-Funktionen) und sie extrahiert nur den numerischen oder Textteil aus der Zeichenfolge.
Etwas wie unten gezeigt:
Unten ist der VBA-Code, der eine Funktion zum Extrahieren eines numerischen Teils aus einer Zeichenfolge erstellt:
'Dieser VBA-Code erstellt eine Funktion zum Abrufen des numerischen Teils aus einem String Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function
Sie müssen Code in einem Modul platzieren und können dann die Funktion =GetNumeric im Arbeitsblatt verwenden.
Diese Funktion benötigt nur ein Argument, nämlich die Zellreferenz der Zelle, aus der Sie den numerischen Teil erhalten möchten.
In ähnlicher Weise finden Sie unten die Funktion, mit der Sie nur den Textteil aus einer Zeichenfolge in Excel abrufen:
'Dieser VBA-Code erstellt eine Funktion zum Abrufen des Textteils aus einer Zeichenfolge Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function
Dies sind einige der nützlichen Excel-Makrocodes, die Sie in Ihrer täglichen Arbeit verwenden können, um Aufgaben zu automatisieren und viel produktiver zu sein.
