VBA BasicsVBA und Makros

Die wichtigsten VBA Codes

Makros von A bis Z:

Das wichtigste zuerst. Hier lernst du die am häufigsten eingesetzten VBA Codes kennen. 

Kopiere dir einfach den jeweilen Code deinen VBA-Editor in Excel oder lade dir unsere Beispieldateien herunter. Wenn du Fragen oder Vorschläge für Erweiterungen hast scrolle einfach nach ganz unten und schreibe einen Kommentar. 

Viel Spaß bei der VBA Programmierung
Daniel von ExcelMaster

Communitybewertung:

4.6/5

Menü - Schnellzugriff:

Tipp:

An vielen Stellen im VBA-Editor können Programmcodes durch das Drücken von STRG + Leertaste automatisch ausgeschrieben werden. Der VBA-Editor gibt einem dann alle Optionen, die zur aktuellen Eingabe passen. 

Selbes funktioniert mit Formeln, die in Excel-Zellen eingetragen werden mit der TAB-Taste. Somit muss man z.B. bei einem =SVERWEIS nicht immer „=SVERWEIS“ eintippen, sondern es reicht wenn die ersten Zeichen „=SVERW“ eingegeben wird und mit der TAB-Taste wird dann automatisch „=SVERWEIS(“ eingetragen.

Grundlegende Tastaturbefehle / Shortcuts für den VBA-Editor:

Windows

F5

F8

Mac

n.a.

n.a.

Beschreibung

Komplettes Makro abspielen

Makro Schritt für Schritt abspielen

1.

Grundlegender Aufbau eines Makros in Excel

1.1

Kommentare

'Hinweis: Alle grünen Zeilen, die mit einem Hochkomma beginnen sind Kommentare
'Hinweis: Alle grünen Zeilen, die mit einem Hochkomma beginnen sind Kommentare

Hinweis: Meistens stehen Kommentare vor einer Programmierzeile um zu erläutern was die folgende Zeile tut. Somit ist es später leichter nachzuvollziehen was programmiert wurde.

1.2

Start und Ende eines Makros

'Start und Name des Makros
Sub VBA_Basics()

'Hier steht der Inhalt deines Makros

'Beendet das Makro
End Sub

Hinweis: Tausche einfach den Text „VBA_Basics“ mit z.B. „Mein_Makro“ und schon hast du deinem Makro einen neuen Namen gegeben. Wichtig ist immer die öffnende und schließende Klammer hinter dem Makronamen. 
Makronamen können nicht mit Zahlen beginnen und dürfen auch keine Leerzeichen besitzen. Anstatt einem Leerzeichen kann z.B. ein Unterstrich eingesetzt werden.

1.3

Absätze in VBA-Programmzeilen

'Hinweis: Mit einem Unterstrich am Ende einer Programmzeile kann in der nächsten Zeile _
    weitergeschrieben werden

Hinweis: Dies funktioniert bei Kommentaren, aber auch bei normalen Programmzeilen. Wichtig hier ist, dass vor dem Unterstrich ein Leerzeichen eingefügt wird. Bei Kommentaren erkennt man, dass der Kommentar in der nächsten Zeile weiter geht, wenn auch die folgenden Zeilen grün markiert sind obwohl am Zeilenanfang kein Hochkomma ‚ gesetzt wurde.

2.

Workbooks / Arbeitsmappen

Für Arbeitsmappen in Excel gibt es etliche verschiedene Bezeichnungen, daher nicht verwirren lassen wenn manchmal von Workbooks, manchmal von Arbeitsmappen oder einfach nur Excel-Dateien gesprochen wird.

2.1

Workbook hinzufügen

'Eine neue Arbeitsmappe wird erstellt
    Workbooks.Add

Hinweis: Nicht vergessen das neu erstellte Workbook später auch zu speichern.

2.2

Workbook einer Variablen zuordnen

'Eine neue Variable wird als Workbook-Variable erstellt
Dim wb As Workbook

'Der neu hinzugefügten Arbeitsmappe wird die Variable wb zugeordnet, sodass später wieder auf diese _
Arbeitsmappe zugegriffen werden kann mit z.B. wb.activate
    Set wb = ActiveWorkbook

Hinweis: Der Name der Variablen ist egal, am besten ist dieser kurz gehalten und man kann sich später noch schnell daran erinnern.

2.3

Zurück zum Ausgangs-Workbook

'Die Arbeitsmappe wird ausgewählt von der aus der Programmcode gestartet wurde
ThisWorkbook.Activate

Hinweis: Wählt wieder die Arbeitsmappe aus in der das Makro verankert ist.

2.4

Workbook mit Variable auswählen

'Die Arbeitsmappe die den Variablennamen wb trägt wird ausgewählt
wb.Activate

Hinweis: Wählt die Arbeitsmappe aus, die mit der Variablen wb versehen wurde wie im Beispiel 2.2

2.5

Arbeitsmappen-Schutz

'Aktive Arbeitsmappe mit einem Arbeitsmappen-Schutz inklusive Passwort "Dein_Passwort" versehen
ActiveWorkbook.Protect "Dein_Passwort"
'Löst den Arbeitsmappen-Schutz mit dem Passwort "Dein_Passwort"
ActiveWorkbook.Unprotect "Dein_Passwort"

Hinweis: Der Arbeitsmappen-Schutz macht Sinn wenn man die Struktur der Arbeitsmappe schützen möchte. Beispielsweise kann somit kein Tabellenblatt mehr gelöscht werden.

2.6

Arbeitsmappe speichern

Hinweis: Anstatt „ActiveWorkbook“ kann natürlich auch eine Variable stehen. Es wird nicht empfohlen den exakten Dateinamen zum Speichern zu verwenden wie z.B. Workbooks(„Dein_Arbeitsmappenname.xlsx“).save , da sich dieser Name immer wieder ändern kann uns somit bist du sicherer wenn du einfach nur das aktive Workbook speicherst, damit bleibst du namensunabhängig.

'Speichert das aktive Workbook ab
ActiveWorkbook.Save

Hinweis: Wenn im Folgenden der Dateipfad auf dem PC verändert wird könnte das Makro bei diesem Code abbrechen. Daher empfehlen wir in manchen Fällen ersten den Dateipfad der aktuellen Datei auszulesen und dann später entweder in dem selben Pfad oder einem Unterordner Dateien abzuspeichern.

'Speichert die aktive Exceldatei unter einem bestimmten Namen
ActiveWorkbook.SaveAs Filename:="E:\User\JohnDoe\Dateiname.xlsx"

Hinweis: Der folgende Code zeigt, wie z.B. ein Dateipfad dynamisch aufgebaut werden kann. Dieser dynamische Dateipfad mit den Variablen „varPfad“ und „varDateiname“ kann den Inhalt seiner Variablen durch InputBoxen erhalten oder auch durch Zellen in denen Dateinamen oder Pfade hinterlegt sind. 

'Die Variable varPfad wird als Text-Variable/String erstellt
Dim varPfad As String
'Der Variablen varPfad wird der Wert "E:\User\JohnDoe\" zugeordnet
varPfad = "E:\User\JohnDoe\"
'Die Variable varDateiname wird als Text-Variable/String erstellt
Dim varDateiname As String
'Der Variablen varPfad wird der Wert "MeinDateiname" zugeordnet
varDateiname = "MeinDateiname"

'Speichert eine Datei unter einem bestimmten Namen und verwendet hierbei Werte aus Variablen

ActiveWorkbook.SaveAs Filename:=varPfad & varDateiname & ".xlsx"
2.7

Arbeitsmappe schließen

'Das aktive Workbook/die aktive Arbeitsmappe wird geschlossen
ActiveWorkbook.Close
'Schließt die aktive Exceldatei ohne sie zu speichern
ActiveWorkbook.Close savechanges:=False

Hinweis: Um zu vermeiden, dass die Abfrage kommt ob ihr eurer Workbook speichern wollt könnt ihr am Anfang des Makros Application.DisplayAlerts = False eintragen. Somit werden alle Popup-Fenster von Excel blockiert. Dies ist hilfreich, wenn man z.B. mehrere Dateien importiert und die importierten Dateien wieder schließen möchte.

2.8

Arbeitsmappe öffnen

'Öffnet eine weitere Datei
Workbooks.Open "D:\Dateipfad\Dateiname.xlsx"

Hinweis: Mit VBA können Daten aus mehreren Dateien in eine Excel-Datei importiert werden. Für diesen Datenimport kann man entweder einzeln alle Dateien einzeln öffnen lassen, die jeweiligen Daten kopieren und in einer Sammeldatei zusammenfügen. Mit einer Schleife können aber z.B. auch alle Dateien aus einem Ordner importiert werden. Mehr dazu bei den Schleifen weiter unten.

3.

Sheets / Tabellenblätter / Tabs / Register

3.1

Tabellenblatt hinzufügen / löschen

Es gibt viele verschiedene Wege ein Tabellenblatt hinzufügen. Welchen Weg wir bevorzugen bzw. welcher Weg für welche Lösung am meisten Sinn macht werden dir die folgenden Zeilen erläutern.

Die folgende Lösung empfehlen wir wenn das neue Tabellenblatt direkt hinter dem ausgewählten Tabellenblatt erstellt werden soll:

'Ein neues Tabellenblatt wird erstellt hinter dem aktuell ausgewählten Tabellenblatt
Sheets.Add After:=ActiveSheet

Die folgende Lösung empfehlen wir wenn das neue Tabellenblatt immer als allererstes Tabellenblatt erstellt werden soll:

'Ein neues Tabellenblatt wird erstellt vor dem aktuell ausgewählten Tabellenblatt
Sheets.Add Before:=Sheets(1)

Die folgende Lösung empfehlen wir wenn das neue Tabellenblatt als allerletztes Tabellenblatt eingefügt werden soll:

'Ein neues Tabellenblatt wird an letzter Stelle erstellt
Sheets.Add After:=Sheets(Sheets.Count)

Hinweis: Versucht dir immer vorher Gedanken zu machen wo das neue Tabellenblatt eingefügt werden soll, denn damit sparst du dir später sehr viel Arbeit.

3.2

Tabellenblätter auswählen

Es gibt mehrere verschiedene Wege ein Tabellenblatt auszuwählen. Welchen Weg wir bevorzugen bzw. welcher Weg für welche Lösung am meisten Sinn macht werden dir die folgenden Zeilen erläutern.

Dies ist der Standardcode, den auch der Makrorekorder aufzeichnen würde. Aber Vorsicht. Wird der Name des Tabellenblattes verändert bricht das Makro bei diesem Code ab:

'Wählt das Tabellenblatt mit dem Namen "Tabelle1" aus
Sheets("Tabelle1").Select

Die folgende Lösung empfehlen wir wenn das bestimmte Tabellenblatt immer an einer bestimmten Stelle steht. Wie im Beispiel an Stelle 1, 2, 3, …

'Das erste Tabellenblatt wird ausgewählt
Sheets(1).Select

'Das zweite Tabellenblatt wird ausgewählt
    Sheets(2).Select

'Das dritte Tabellenblatt wird ausgewählt
    Sheets(3).Select

Für die folgende Lösung muss zuerst im VBA-Editor auf der linken Seite unter „Projekt – VBAProject“ das jeweilige Tabellenblatt händisch ausgewählt werden. Dadurch werden im linken unteren Bereich des VBA-Editors die Eigenschaften dieser Tabelle angezeigt. Hier kann der (Name) nun geändert werden. Genau mit dem ausgewählten Namen kann später das Tabellenblatt ausgewählt und bearbeitet werden egal ob in der normalen Excel-Ansicht der Name des Tabellenblattes nachträglich geändert wird.

Dies ist mit die beste Variante um Tabellenblätter auszuwählen, da somit sichergestellt wird, dass das Tabellenblatt auch ausgewählt werden kann wenn der Name das Tabellenblattes geändert wurde.

'Wählt das Tabellenblatt aus, welches im VBA-Editor den Namen "Tabelle1" bekommen hat
Tabelle1.Select
'Wählt das Tabellenblatt aus, welches im VBA-Editor den Namen "MeinTabellenblatt" bekommen hat
MeinTabellenblatt.Select

Hinweis: Versucht dir immer vor der Programmierung Gedanken zu machen welche dieser Varianten die für dich am besten geeignete ist um auf sicherstem Weg dein Tabellenblatt auszuwählen. Wenn während eines Makrodurchlaufs ein Tabellenblatt umbenannt wird ist es besser vorab einen Namen im VBA-Editor für dieses Tabellenblatt zu definieren, sodass man es später wieder findet.

3.3

Meherere Tabellenblätter auswählen

Das folgende Makro funktioniert natürlich nur, wenn die drei Tabellen „Tabelle2“, „Tabelle3“ und „Tabelle4“ auch in deiner Excel-Datei existieren.

'Mehrere Tabellenblätter können direkt mit Namen über ein Array ausgewählt werden
Sheets(Array("Tabelle2", "Tabelle3", "Tabelle4")).Select

Hinweis:

3.4

Tabellenblätter verschieben

Hinweis: Es können einzelne Tabellenblätter verschoben werden oder auch mehrere.

'Verschiebt das Tabellenblatt "Tabelle2" hinter das Tabellenblatt 5
Sheets("Tabelle2").Move After:=Sheets(5)
'Verschiebt mehrere Tabellenblätter hinter das Tabellenblatt 5
Sheets(Array("Tabelle3", "Tabelle4")).Move Before:=Sheets(1)

Hinweis: Dies funktioniert nur wenn auch alle genannten Tabellenblätter mit exakt dem selben Namen existieren.

3.5

Tabellenblatt einfärben

Hinweis: Um Tabellenblätter hervorzuheben können diese eingefärbt werden.

'Färbt das aktive Tabellenblatt ein - Orange
    ActiveSheet.Tab.Color = 49407
'Färbt das aktive Tabellenblatt ein - Rot
Sheets("Tabelle2").Tab.Color = 255
'Färbt das aktive Tabellenblatt ein - Grün
    Sheets("Tabelle3").Color = 5296274

Hinweis: Dies funktioniert nur wenn auch alle genannten Tabellenblätter mit exakt dem selben Namen existieren.

3.6

Tabellenblatt Namen / umbenennen

Hinweis: Um Tabellenblätter hervorzuheben können diese eingefärbt werden. Ein Tabellenblattname kann aus bis zu 31 Zeichen bestehen. einige Zeichen wie z.B. ein Doppelpunkt „:“ oder ein Stern „*“  sind nicht erlaubt.

'Benennt das aktive Tabellenblatt in "Mein Tabellenblatt" um
ActiveSheet.Name = "Mein Tabellenblatt"
'Zeigt den Namen das aktuellen Tabellenblattes in einer MessagesBox an.
    MsgBox ActiveSheet.Name
'Benennt das Tabellenblatt "Tabelle1" in "Meine Tabelle" um
Sheets("Tabelle1").Name = "Meine Tabelle"

Hinweis: Dies funktioniert nur wenn auch alle genannten Tabellenblätter mit exakt dem selben Namen existieren.

3.7

Tabellenblatt ein-/ausblenden

Hinweis: Tabellenblätter können mit VBA aus- und wieder eingeblendet werden. Es gibt zusätzlich die Möglichkeit ein Tabellenblatt so zu verstecken, dass es von Hand ohne VBA nicht wieder eingeblendet werden kann. Dies ist besonders dann hilfreich, wenn der User die Inhalte des versteckten Tabellenblattes gar nicht sehen soll. Dies wird über VeryHidden gesteuert.

Tabellenblatt ausblenden
'Blendet das Tabellenblatt "Tabelle1" aus
Sheets("Tabelle1").Visible = False
'Blendet das aktive Tabellenblatt aus
ActiveSheet.Visible = xlSheetHidden
'Blendet das aktive Tabellenblatt aus und es kann vom User von Hand nicht wieder eingeblendet werden
ActiveSheet.Visible = xlSheetVeryHidden
Tabellenblatt einblenden
'Blendet das Tabellenblatt "Tabelle1" ein
    Sheets("Tabelle1").Visible = True

Hinweis: Dies funktioniert nur wenn auch alle genannten Tabellenblätter mit exakt dem selben Namen existieren. 

3.8

Tabellenblatt kopieren

Hinweis: Im folgenden werden verschiedene Möglichkeiten gezeigt ein Tabellenblatt zu kopieren

'Kopiert das Tabellenblatt "Tabelle1" und fügt es vor das erste Tabellenblatt ein
    Sheets("Tabelle1").Copy Before:=Sheets(1)
'Kopiert das Tabellenblatt "Tabelle1" und fügt es nach dem aktiven Tabellenblatt ein 
Sheets("Tabelle1").Copy After:=ActiveSheet
'Kopiert das Tabellenblatt "Tabelle1" und fügt es an letzter Stelle ein 
Sheets("Tabelle1").Copy After:=Sheets(Sheets.Count)

Hinweis: Dies funktioniert nur wenn auch alle genannten Tabellenblätter mit exakt dem selben Namen existieren. 

3.9

Tabellenblatt - Blatt schützen/Schutz aufheben

Hinweis: Mit einem Blattschutz kann aktiv verhindert werden, dass unbefugte Benutzer Zellen, formatieren, Formel oder Spalten löschen. Hier können noch einige weitere Einstellungen getroffen werden um nur bestimmte Teile des Tabellenblattes zu schützen. Nutzen Sie hierfür einfach den Makrorekorder und zeichnen Sie die von Ihnen gewünschten Optionen auf indem Sie von Hand mit einem Rechtsklick auf das Tabellenblatt dieses schützen.

'Das aktive Tabellenblatt mit einem Tabellenblattschutz inkl. Passwort versehen
ActiveSheet.Protect Password:="Dein_Passwort"
'Den Tabellenblattschutz mit Passwort vom aktiven Tabellenblatt entfernen
ActiveSheet.Unprotect Password:="Dein_Passwort"

Hinweis: Einzelne Zellen sind in Excel standardmäßig geschützt. Wählen Sie eine bestimmten Bereich oder auch nur eine Zelle in Excel aus und wählen Sie über einen Rechtsklick „Zellen formatieren“ (Shortcut: STRG + 1). Im aufgeploppten Fenster ganz rechts findet sich der Reiter „Schutz“. Hier können Sie wählen zwischen „gesperrt“ und „ausgeblendet“. Nehmen Sie den Haken bei „gesperrt“ raus um diese Zelle/n für die Bearbeitung trotz Tabellenblattschutz verfügbar zu machen. 

„Ausgeblendet“ dient dazu, dass z.B. komplexe Formeln oder Werte gar nicht oben in der Formelzeile angezeigt werden. Somit kann man gerade bei Formeln unter anderem Know-How oder verwirrende Formeln verstecken.

3.10

Tabellenblatt löschen

Hinweis: Sei vorsichtig mit dem Löschen von Tabellenblättern, da diese nicht wiederhergestellt werden können.

'Löscht das aktuell aktive Tabellenblatt
ActiveSheet.Delete

Hinweis: Sofern aktuell nur ein einziges Tabellenblatt in deiner Datei besteht wird dieser Code nicht funktionieren, da immer mindestens ein Tabellenblatt existieren muss.

Beim Löschen von Tabellenblättern erscheint in Excel ein Pop-Up Fenster, welches das Makro unterbrechen würde. Um dieses zu deaktivieren setze ganz am Anfang deines Makros den folgenden Code ein:

'Stellt alle "DisplyAlerts"/Pop-Up Nachrichten von Excel aus
Application.DisplayAlerts = False

Wichtig: Schalte am Ende deines Makros mit dem folgenden Code die DisplayAlerts wieder an, da sonst z.B. wichtige Pop-Up Fenster wie „Möchtest du diese Datei speichern“ beim Schließen der Datei erst wieder nach einem Neustart angezeigt werden.

'Schaltet alle "DisplyAlerts"/Pop-Up Nachrichten von Excel wieder ein
Application.DisplayAlerts = True

4.

Makro Geschwindigkeit optimieren​

4.1

Makro ScreenUpdating / Bildschirmaktualisierung

Hinweis: Wenn du noch am VBA lernen bist macht es oft Sinn das ScreenUpdating aktiv zu lassen, da du dadurch immer genau siehst was dein Makro macht.

Aber: Wenn dein Makro fertig programmiert ist macht es in den meisten Fällen Sinn das ScreenUpdating auszuschalten, da dieses 1. Dein Makro verlangsamt und 2. Dein Excel-Fenster zu flackern anfangen könnte und von manchen Usern als Absturz bezeichnet werden könnte. Mit dem deaktivierten ScreenUpdating kannst du natürlich auch verhindern, dass ein externer User deines Makros nachverfolgen kann welche Schritte das Makro im Detail ausgeführt hat.

ScreenUpdating ausschalten
'Schaltet die Bildschimaktualisierung AUS, somit werden die _
    einzelnen Schritte des Makros nicht mehr sichtbar dargestellt
    Application.ScreenUpdating = False

Wichtig: Denke daran, dass du das ScreenUpdating am Ende deines Makros wieder einschaltest, da du sonst Probleme bei der Ansicht in Excel bekommen könntest. Z.B. wird nicht mehr angezeigt wenn du Zellen auswählst. In so einem Fall hilft ein Neustart von Excel oder du führst einfach den folgenden Code einzeln aus:

ScreenUpdating einschlaten
'Schaltet die Bildschimaktualisierung EIN, somit werden die _
einzelnen Schritte des Makros dargestellt

Application.ScreenUpdating = True

Wichtig: Auch wenn es teilweise nur Sekunden sind, die man sich bei einem Makrodurchlauf durch das Ausschalten des ScreenUpdating spart wird diese Funktion dennoch sehr häufig eingesetzt.

4.2

Pop-Up-Fenster deaktivieren / DisplayAlerts

Hinweis: DisplayAlerts sind wichtig, dass du z.B. nicht mit einem Klick aus Versehen ein Tabellenblatt löschst oder deine Datei ohne zu speichern beendest. Bei Makrodurchläufen sind DisplayAlerts aber oft sehr unbeliebt, da diese das Durchlaufen deines Makros blockieren. Dem kannst du mit dem folgenden Code entgegenwirken

'Stellt alle "DisplyAlerts"/Pop-Up Nachrichten von Excel AUS
Application.DisplayAlerts = False

Hinweis: Sofern aktuell nur ein einziges Tabellenblatt in deiner Datei besteht wird dieser Code nicht funktionieren, da immer mindestens ein Tabellenblatt existieren muss.

Beim Löschen von Tabellenblättern erscheint in Excel ein Pop-Up Fenster, welches das Makro unterbrechen würde. Um dieses zu deaktivieren setze ganz am Anfang deines Makros den folgenden Code ein:

'Schaltet alle "DisplyAlerts"/Pop-Up Nachrichten von Excel wieder EIN
Application.DisplayAlerts = True

Wichtig: Schalte am Ende deines Makros mit dem folgenden Code die DisplayAlerts wieder an, da sonst z.B. wichtige Pop-Up Fenster wie „Möchtest du diese Datei speichern“ beim Schließen der Datei erst wieder nach einem Neustart angezeigt werden.

Es werden lediglich Message Boxen oder andere Pop-Up Fenster angezeigt, die du in deinem Makro explizit programmiert hast.

4.3

Berechnung Manuell / Automatisch

Hinweis: Mit die meiste Zeit sparst du bei einem Makro, wenn du die automatische Berechnung auf Manuell umstellst. Ansonsten berechnet Excel bei fast jeder Codezeile deine Excel-Tabelle neu. Hier kann man bei komplexen Exceldateien durchaus einige Stunden an Makrolaufzeit einsparen. ABER wichtig!: Falls während des Makrodurchlaufs natürlich Berechnungen stattfinden sollen müssen diese per Programmcode „Calculate“ wieder aktiviert werden.

'Schaltet die Berechnung aller Formeln und Verknüpfungen aus / Umstellung auf Manuell
Application.Calculation = xlCalculationManual

Hinweis: Mit dem folgenden Code „Calculate“ kannst du jeder Zeit in deinem Makro bestimmen wann einmal alle Formeln neu berechnet werden sollen.

'Berechnet einmalig alle Formeln und Verknüfpungen
Calculate

Hinweis: Vergiss nicht am Ende deines Makros die Kalkulationen wieder auf automatisch einzustellen, da du dich sonst wundern wirst, warum Formeln in deiner Excel-Datei nicht mehr die aktuelle Werte anzeigen. Teilweise wird dies auch übersehen und es fällt erst viel später auf nachdem man schon seit Stunden nach Fehlern in den Formeln gesucht hat. Also dran denken!

'Schaltet die Berechnung aller Formeln und Verknüpfungen wieder auf automatisch
Application.Calculation = xlCalculationAutomatic

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.