24 nützliche Excel-Makro-Beispiele für VBA-Anfänger (gebrauchsfertig)

Die Verwendung von Excel-Makros kann Ihre Arbeit beschleunigen und viel Zeit sparen.

Eine Möglichkeit, den VBA-Code abzurufen, besteht darin, ein Makro aufzuzeichnen und den generierten Code abzurufen.Der Code des Makrorekorders ist jedoch oft voller Code, der nicht wirklich benötigt wird.Der Makrorekorder hat auch einige Einschränkungen.

Es lohnt sich also, einen nützlichen Satz VBA-Makros zu haben, die Sie in Ihrer Gesäßtasche aufbewahren und bei Bedarf verwenden können.

Das Programmieren eines Excel-VBA-Makros kann zunächst einige Zeit in Anspruch nehmen, aber wenn Sie fertig sind, können Sie es als Referenz verwenden und es das nächste Mal verwenden, wenn Sie es benötigen.

In diesem umfangreichen Artikel werde ich einige nützliche Beispiele für Excel-Makros auflisten, die ich häufig benötige und in meinem privaten Tresor aufbewahre.

Ich werde dieses Tutorial weiterhin mit weiteren Makrobeispielen aktualisieren.Bitte hinterlassen Sie einen Kommentar, wenn Sie der Meinung sind, dass etwas auf der Liste stehen sollte.

Sie können diese Seite zum späteren Nachschlagen mit einem Lesezeichen versehen.

Bevor ich nun auf die Makrobeispiele eingehe und Ihnen den VBA-Code gebe, möchte ich Ihnen zunächst zeigen, wie Sie den Beispielcode verwenden.

Verwenden des Codes aus dem Excel-Makrobeispiel

Hier sind die Schritte, die Sie befolgen 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.Das wird geöffnetVB-Editor.
• Klicken Sie mit der rechten Maustaste auf ein beliebiges Objekt im Projekt-Explorer.
• Gehen Sie zu Einfügen -> Module.
• Kopieren Sie den Code und fügen Sie ihn in das Modulcodefenster ein.

Wenn das Beispiel besagt, dass Sie den Code in das Codefenster des Arbeitsblatts einfügen müssen, doppelklicken Sie auf das Arbeitsblattobjekt und kopieren Sie den Code und fügen Sie ihn in das Codefenster ein.

Nachdem Sie den Code in die Arbeitsmappe eingefügt haben, müssen Sie ihn mit der Erweiterung .XLSM oder .XLS speichern.

Wie man ein Makro ausführt

Nach dem Kopieren des Codes in den VB-Editor sind die Schritte zum Ausführen des Makros wie folgt:

• Gehen Sie zum Entwickler-Tab.
• Klicken Sie auf Makro.

• Wählen Sie im Dialogfeld „Makros“ das auszuführende Makro aus.
• Klicken Sie auf die Schaltfläche Ausführen.

Wenn Sie die Registerkarte "Entwickler" im Menüband nicht finden können, lesen Sie dieses Tutorial, um zu erfahren, wie Sie sie erhalten.

Wenn der Code in das Codefenster des Arbeitsblatts eingefügt wird, müssen Sie sich keine Gedanken über die Ausführung des Codes machen.Es wird automatisch ausgeführt, wenn die angegebene Aktion eintritt.

Kommen wir nun zu nützlichen Makrobeispielen, die Ihnen helfen können, Ihre Arbeit zu automatisieren und Zeit zu sparen.

Hinweis: Sie werden viele Apostrophe (') finden, gefolgt von einer oder zwei Zeilen.Dies sind Kommentare, die beim Ausführen des Codes ignoriert und als Selbst-/Leserkommentare platziert werden.

Wenn Sie Fehler im Artikel oder Code finden, lassen Sie es mich bitte wissen.

Excel-Makro-Beispiel

Dieser Artikel beschreibt die folgenden Makrobeispiele:

Blenden Sie alle Arbeitsblätter auf einmal ein

Wenn Sie an einer Arbeitsmappe mit mehreren ausgeblendeten Blättern arbeiten, müssen Sie diese Blätter einzeln wieder einblenden.Dies kann einige Zeit dauern, wenn viele ausgeblendete Blätter vorhanden sind.

Hier ist der Code zum Einblenden aller Blätter in der Arbeitsmappe.

"Dieser Code wird alle Blätter in der Arbeitsmappe Sub UnhideAllWoksheets() Dim ws als Arbeitsblatt für jedes ws in ActiveWorkbook einblenden. Arbeitsblätter ws. Visible = xlSheetVisible Next ws End Sub

Der obige Code verwendet eine VBA-Schleife (For Each), um jedes Arbeitsblatt in der Arbeitsmappe zu durchlaufen.Dann ändert es die sichtbare Eigenschaft des Blattes in sichtbar.

Hier ist ein ausführliches Tutorial zum Einblenden eines Arbeitsblatts in Excel mit verschiedenen Methoden.

Alle Arbeitsblätter außer dem aktiven Arbeitsblatt ausblenden

Dieser Makrocode kann verwendet werden, wenn Sie an einem Bericht oder Dashboard arbeiten und alle Blätter außer dem Blatt mit dem Bericht/Dashboard ausblenden möchten.

'Dieses Makro verbirgt alle Arbeitsblätter außer dem aktiven Arbeitsblatt 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

Sortieren Sie das Arbeitsblatt alphabetisch mit VBA

Dieser Makrocode kann nützlich sein, wenn Sie eine Arbeitsmappe mit vielen Blättern haben und diese Blätter alphabetisch sortieren möchten.Dies kann passieren, wenn Sie den Blattnamen als Jahr 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 Blätter in der Arbeitsmappe haben und alle Blätter schützen möchten, können Sie diesen Makrocode verwenden.

Es ermöglicht Ihnen, ein Passwort im Code anzugeben.Sie benötigen dieses Kennwort, um den Schutz des Blatts aufzuheben.

'Dieser Code schützt alle Blätter auf einmal End Sub

Entschützen Sie alle Arbeitsblätter auf einmal

Wenn Sie einige oder alle Ihre Arbeitsblätter geschützt haben, können Sie den Schutz mit nur einer geringfügigen Änderung des Codes aufheben, der zum Schützen der Arbeitsblätter verwendet wird.

'Dieser Code schützt alle Blätter auf einmal End Sub

Beachten Sie, dass das Kennwort dasselbe Kennwort sein muss, das zum Sperren des Blatts verwendet wird.Wenn nicht, wird ein Fehler angezeigt.

Alle Zeilen und Spalten einblenden

Dieser Makrocode blendet alle ausgeblendeten Zeilen und Spalten ein.

Dies kann nützlich sein, wenn Sie die Datei von jemand anderem übernehmen und sicherstellen möchten, dass keine versteckten Zeilen/Spalten vorhanden sind.

'Dieser Code blendet alle Zeilen und Spalten im Arbeitsblatt Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub ein

Trennen Sie alle verbundenen Zellen

Das Zusammenführen von Zellen zu einer ist eine gängige Praxis.Während es funktioniert, können Sie die Daten nicht sortieren, wenn die Zellen zusammengeführt werden.

Wenn Sie ein Arbeitsblatt mit verbundenen Zellen verwenden, verwenden Sie den folgenden Code, um die Verbindung aller verbundenen Zellen auf einmal aufzuheben.

'Dieser Code hebt die Zusammenführung aller verbundenen Zellen auf Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Beachten Sie, dass ich die Verwendung der Option "Über Auswahl zentrieren" anstelle der Option "Zusammenführen und zentrieren" empfehle.

Arbeitsmappe mit Zeitstempel im Namen speichern

Es kann vorkommen, dass Sie eine funktionierende Version erstellen müssen.Diese eignen sich hervorragend für langfristige Projekte, die sich im Laufe der Zeit mit Dateien befassen.

Es empfiehlt sich, die Datei mit einem Zeitstempel zu speichern.

Durch die Verwendung eines Zeitstempels können Sie zu einer 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:BenutzerBenutzernameDesktopWorkbookName“ & 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 Ordner angeben, in dem Sie die Datei speichern möchten.Außerdem habe ich den generischen Namen „WorkbookName“ als Dateinamenpräfix verwendet.Sie können Inhalte angeben, die sich auf Ihr Projekt oder Unternehmen beziehen.

Speichern Sie jedes Arbeitsblatt als separates PDF

Wenn Sie Daten aus verschiedenen Jahren oder Abteilungen oder Produkten verwenden, müssen Sie möglicherweise verschiedene Arbeitsblätter als PDF-Dateien speichern.

Während dies manuell ein zeitaufwändiger Prozess sein kann, beschleunigt VBA die Dinge.

Hier ist ein VBA-Code, der jedes Arbeitsblatt als separates PDF speichert.

„Dieser Code speichert jedes Arbeitsblatt als separates PDF-Sub

Im obigen Code habe ich die Adresse des Ordners angegeben, in dem ich das PDF speichern möchte.Außerdem erhält jedes PDF den gleichen Namen wie das Blatt.Sie müssen den Speicherort dieses Ordners ändern (es sei denn, Ihr Name ist auch Sumit und Sie haben ihn in einem Testordner auf Ihrem Desktop gespeichert).

Beachten Sie, dass dieser Code nur für Arbeitsblätter funktioniert (nicht für Diagrammblätter).

Speichern Sie jedes Arbeitsblatt als separates PDF

Hier ist der Code zum Speichern der gesamten Arbeitsmappe als PDF im angegebenen Ordner.

'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.

Wandeln Sie alle Formeln in Werte um

Verwenden Sie diesen Code, wenn Sie ein Arbeitsblatt mit vielen Formeln haben und diese Formeln in Werte umwandeln möchten.

'Dieser Code konvertiert alle Formeln in Werte Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Dieser Code erkennt automatisch die verwendete Zelle und wandelt sie in einen Wert um.

Zellen mit Formeln schützen/sperren

Wenn Sie viele Berechnungen haben und diese nicht versehentlich löschen oder ändern möchten, möchten Sie möglicherweise Zellen mit Formeln sperren.

Hier ist der Code, der alle Zellen mit Formeln sperrt und alle anderen Zellen entsperrt lässt.

'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

Zugehöriges Tutorial: So sperren Sie Zellen in Excel.

Schützen Sie alle Blätter in einer Arbeitsmappe

Verwenden Sie den folgenden Code, um alle Blätter in einer Arbeitsmappe gleichzeitig 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 geht alle Blätter einzeln durch und schützt sie.

Wenn Sie den Schutz aller Blätter aufheben möchten, verwenden Sie in Ihrem Code ws.Unprotect anstelle von ws.Protect.

Fügen Sie nach jeder Zeile der Auswahl eine Zeile ein

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. Insert ActiveCell.Offset(2, 0).Select Next i End Sub

Auch hier können Sie diesen Code ändern, um nach jeder Spalte im ausgewählten Bereich eine leere Spalte einzufügen.

Datum und Zeitstempel automatisch in angrenzende Zellen einfügen

Zeitstempel sind das, was Sie verwenden, wenn Sie Aktivitäten verfolgen möchten.

Beispielsweise möchten Sie möglicherweise Aktivitäten nachverfolgen, z. B. wann eine bestimmte Ausgabe angefallen ist, wann eine Verkaufsrechnung erstellt wurde, wann Daten in eine Zelle eingegeben wurden, wann ein Bericht zuletzt aktualisiert wurde und so weiter.

Verwenden Sie diesen Code, um beim Eingeben oder Bearbeiten vorhandener Inhalte einen Datums- und Zeitstempel in benachbarte Zellen einzufügen.

'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 (nicht in das Modulcodefenster, wie wir es bisher in anderen Excel-Makrobeispielen getan haben).Doppelklicken Sie dazu im VB-Editor auf den Blattnamen, für den Sie diese Funktionalität wünschen.Kopieren Sie dann diesen Code und fügen Sie ihn in das Codefenster dieses Blattes ein.

Dieser Code funktioniert auch, wenn die Dateneingabe in Spalte A erfolgt (beachten Sie, dass der Code eine Target.Column = 1-Zeile hat).Sie können es entsprechend ändern.

Markieren Sie alternative Zeilen in der Auswahl

Das Hervorheben abwechselnder Linien kann die Lesbarkeit Ihrer Daten erheblich verbessern.Dies kann nützlich sein, wenn Sie es ausdrucken und die Daten durchsuchen müssen.

Hier ist ein Code, der sofort alternative Zeilen in der Auswahl hervorhebt.

'Dieser Code würde alternative Zeilen in der Auswahl hervorheben Wenn Next Myrow End Sub

Beachten Sie, dass ich die Farbe im Code als vbCyan angegeben habe.Sie können auch andere Farben angeben (z. B. vbRed, vbGreen, vbBlue).

Markieren Sie falsch geschriebene Zellen

Excel hat keine Rechtschreibprüfung in Word oder PowerPoint.Während Sie die Rechtschreibprüfung durch Drücken von F7 ausführen können, gibt es keinen visuellen Hinweis auf Rechtschreibfehler.

Verwenden Sie diesen Code, um alle falsch geschriebenen Zellen sofort hervorzuheben.

'Dieser Code hebt die Zellen hervor, die falsch geschriebene Wörter haben cl Ende Sub

Beachten Sie, dass die hervorgehobenen Zellen die Zellen mit Text sind, von dem Excel annimmt, dass er falsch geschrieben ist.In vielen Fällen hebt es auch Namen oder Markenbegriffe hervor, die es nicht versteht.

Aktualisieren Sie alle Pivot-Tabellen in einer Arbeitsmappe

Wenn die Arbeitsmappe mehrere Pivot-Tabellen enthält, können Sie mit diesem Code alle gleichzeitig aktualisieren.

„Dieser Code aktualisiert die gesamte Pivot-Tabelle im Workbook Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Weitere Informationen zum Aktualisieren von Pivot-Tabellen finden Sie hier.

Ändern Sie die Großschreibung ausgewählter Zellen in Großbuchstaben

Während Excel über Formeln verfügt, um die Groß- und Kleinschreibung von Textbuchstaben zu ändern, können Sie dies in einem anderen Satz von Zellen tun.

Verwenden Sie diesen Code, um die Groß- und Kleinschreibung von Text im ausgewählten Text sofort zu ändern.

'Dieser Code ändert die Auswahl in Upper Case Sub ChangeCase() Dim Rng As Range For Each 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 verwende, um die Groß- und Kleinschreibung des Textes festzulegen.Sie können LCase für Kleinbuchstaben verwenden.

Markieren Sie alle Zellen mit Kommentaren

Verwenden Sie den folgenden Code, um alle Zellen mit Kommentaren hervorzuheben.

'Dieser Code hebt Zellen mit Kommentaren hervor' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

In diesem Fall verwende ich vbBlue, um der Zelle eine blaue Farbe zu geben.Sie können es bei Bedarf in eine andere Farbe ändern.

Markieren Sie leere Zellen mit VBA

Sie können zwar bedingte Formatierung verwenden oder das Dialogfeld „Gehe zu Spezial“ verwenden, um leere Zellen hervorzuheben, aber wenn Sie dies häufig tun müssen, ist es am besten, ein Makro zu verwenden.

Einmal erstellt, können Sie dieses Makro in der Symbolleiste für den Schnellzugriff haben oder es in Ihrer persönlichen Makro-Arbeitsmappe speichern.

Hier ist der VBA-Makrocode:

'Dieser Code markiert alle leeren Zellen im Datensatz Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

In diesem Code habe ich leere Zellen angegeben, die rot hervorgehoben sind.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 einer bestimmten Spalte zu sortieren.

Sub SortDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

Beachten Sie, dass ich einen benannten Bereich namens "DataRange" erstellt und diesen anstelle des Zellbezugs verwendet habe.

Auch hier werden drei Schlüsselparameter verwendet:

• Schlüssel1 – Dies ist der Schlüssel, nach dem Sie den Datensatz sortieren möchten.Im obigen Beispielcode werden die Daten basierend auf den Werten in Spalte A sortiert.
• Order1 – Hier müssen Sie angeben, ob Sie die Daten in aufsteigender oder absteigender Reihenfolge sortieren möchten.
• Kopfzeile – Hier müssen Sie angeben, ob Ihre Daten eine Kopfzeile 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, der so aussieht:

Hier ist der Code zum Sortieren von Daten basierend auf mehreren Spalten:

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 wird wie folgt aussehen:

So erhalten Sie nur den numerischen Teil aus einer Zeichenfolge in Excel

Wenn Sie nur den numerischen Teil oder den Textteil aus der Zeichenfolge extrahieren möchten, können Sie eine benutzerdefinierte Funktion in VBA erstellen.

Sie können diese VBA-Funktion dann in Ihrem Arbeitsblatt verwenden (genau wie eine normale Excel-Funktion) und sie extrahiert nur den Zahlen- oder Textteil aus der Zeichenfolge.

Wie nachfolgend dargestellt:

Hier ist der VBA-Code, der eine Funktion erstellt, die den numerischen Teil aus einer Zeichenfolge extrahiert:

'Dieser VBA-Code erstellt eine Funktion, um den numerischen Teil aus einer Zeichenfolge abzurufen Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Dann Ergebnis = Ergebnis & Mid(CellRef, i, 1) Weiter i GetNumeric = Ergebnis Endfunktion

Sie müssen den Code in ein Modul einfügen und können dann die Funktion =GetNumeric im Arbeitsblatt verwenden.

Diese Funktion benötigt nur einen Parameter, die Zellreferenz der Zelle, aus der der numerische Teil abgerufen werden soll.

Ebenso erhält die folgende Funktion nur den Textteil aus einer Zeichenfolge in Excel:

'Dieser VBA-Code erstellt eine Funktion, um den Textteil aus einer Zeichenfolge abzurufen. Funktion 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 = Ergebnis Endfunktion

Dies sind also einige nützliche Excel-Makrocodes, die Sie bei Ihrer täglichen Arbeit verwenden können, um Aufgaben zu automatisieren und Ihre Produktivität zu steigern.