VBA-Makros von Grund auf neu schreiben

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Zakarya El Oirzadi

Last updated on Oktober 30, 2023

Der Excel-Makrorekorder ist sehr leistungsfähig, aber er hat auch seine Grenzen. Wie in einem anderen Artikel beschrieben, zeichnet der Makrorekorder oft unnötigen Code auf und kann Dinge wie Logik oder Interaktionen mit anderen Programmen nicht aufzeichnen. Bei längeren Makros kann es auch schwierig sein, ihn zu verwenden. Sie müssen Ihre Aktionen vorher in einem Storyboard aufzeichnen, um kostspielige Fehler zu vermeiden.

Der Zweck dieses Artikels besteht darin, Ihnen dabei zu helfen, Makros von Grund auf in VBA zu programmieren. Sie erfahren, wo Makros gespeichert werden, schreiben ein grundlegendes Makro und lernen die Grundlagen der Programmierung mit Variablen, Logik und Schleifen in VBA kennen.

Erste Schritte

VBA und der Visual-Basic-Editor

VBA oder Visual Basic for Applications, ist die Sprache, in der Makros geschrieben werden. Alle Makros werden als VBA-Code gespeichert, unabhängig davon, ob sie von Hand geschrieben oder mit dem Makrorekorder erstellt wurden.

Sie können auf den gesamten VBA-Code in einer Arbeitsmappe mit dem Visual Basic Editor zugreifen. Dies ist ein spezieller Texteditor und Debugger, der in allen Office-Anwendungen, einschließlich Excel, integriert ist. Normalerweise öffnen Sie diesen Editor mit der Tastenkombination ALT+F11 in Excel, aber Sie können auch über die Registerkarte „Entwicklertools“ von Excel darauf zugreifen, wenn Sie diese aktiviert haben.

visual basic editor

Der Projekt-Explorer

Der Projekt-Explorer ist ein Fenster innerhalb des VB-Editors, das Ihnen alle Elemente anzeigt, die VBA-Code enthalten können. Wenn Sie dieses Fenster nicht sehen, drücken Sie F5, damit es erscheint oder wählen Sie Projekt-Explorer im Menü Ansicht.

entwurf makros projektfenster

Wenn Sie auf ein Element im Projekt-Explorer doppelklicken, wird der Code für dieses Element angezeigt. Es gibt mehrere Arten von Elementen, die im Projekt-Explorer erscheinen können:

  • Arbeitsmappen
  • Arbeitsblätter
  • Benutzerformulare
  • Klassenmodule
  • Module (Makros werden in diesen Elementen gespeichert)

Obwohl alle diese Elementtypen VBA-Code enthalten können, ist es am besten, Makros in Modulen zu codieren.

Ihr erstes Makro erstellen

Verwenden der Makroliste

Die Makroliste zeigt Ihnen alle Makros in Ihrer Arbeitsmappe an. In dieser Liste können Sie ein vorhandenes Makro bearbeiten oder ein neues erstellen.

So erstellen Sie ein neues Makro über die Makroliste:

  • Wählen Sie die Hauptregisterkarte Entwicklertools und klicken Sie auf Makros (oder drücken Sie ALT+F8)

entwicklungstool hauptregisterharte makroliste

  • Geben Sie einen neuen Namen für Ihr Makro ein und klicken Sie dann auf „Erstellen“

makroliste makro neu erstellen

Nachdem Sie auf „Erstellen“ geklickt haben, erscheint der VB-Editor, der das neu erstellte Makro anzeigt. Falls erforderlich, erstellt Excel ein neues Modul für das Makro.

Manuell im VB-Editor arbeiten

Sie können ein neues Makro auch manuell hinzufügen, ohne dass die Makroliste angezeigt wird. Dies ist die bessere Option, wenn Sie das Modul angeben möchten, in dem das Makro gespeichert werden soll.

Um ein Makro manuell hinzuzufügen:

  • Öffnen Sie den VB-Editor(ALT+F11)
  • Entweder:
    • Fügen Sie ein neues Modul hinzu, indem Sie im Menü auf Einfügen > Modul klicken (das Modul wird automatisch geöffnet)

vba editor menubar modul einfuegen

    • ODER doppelklicken Sie auf ein bestehendes Modul im Projekt-Explorer, um es zu öffnen

vba editor module oeffnen

  • Geben Sie den Code für Ihr neues Makro ins Modul ein
Sub MeinMakro()

End Sub

Diese beiden Zeilen bilden den Anfang und das Ende eines Makros mit dem Namen „MeinMakro“ (beachten Sie dabei, dass die Klammern erforderlich sind). Dieses Makro wird im Dialogfeld „Makros anzeigen“ in Excel angezeigt und kann einer Schaltfläche zugewiesen werden (auch wenn es noch keine Funktion hat).

Dem Makro etwas Code hinzufügen

Fügen wir nun etwas Code zwischen den Zeilen „Sub“ und „End Sub“ ein, damit das Makro tatsächlich etwas tut:

Sub MeinMakro()
    Range("A1").Value = "Hallo Welt!"
End Sub

Grundlegende Codestrukturen

Das Range-Objekt

Excel-VBA verwendet das Range-Objekt, um Zellen auf einem Arbeitsblatt darzustellen. Im obigen Beispiel wird ein Range-Objekt mit dem Code Range(„A1“) erstellt, um auf den Wert der Zelle A1 zuzugreifen.

Range-Objekte werden hauptsächlich zum Festlegen von Zellenwerten verwendet:

Range("A1").Value = 1

Range("A1").Value = "Erste Zelle"

Beachten Sie, dass Sie bei der Definition von Zellenwerten als Zahlen nur die Zahl eingeben, während Sie bei der Eingabe von Text den Text mit Anführungszeichen umgeben müssen.

Bereiche können auch verwendet werden, um auf viele Eigenschaften von Zellen zuzugreifen, wie z. B. ihre Schriftart, Ränder, Formeln und vieles mehr.

Sie können zum Beispiel die Schriftart einer Zelle folgendermaßen auf Fett setzen:

Range("A1").Font.Bold = True

Sie können auch die Formel einer Zelle festlegen:

Range("A1").Formula = "=Sum(A2:A10)"

In Excel könnten Sie mit dem Mauszeiger einen Block von Zellen auswählen (z. B. von A1 bis D10) und sie alle fett markieren. Range-Objekte können folgendermaßen auf Zellenblöcke zugreifen:

Range("A1:D10").Font.Bold = True

Sie können auch auf mehrere Zellen/Blöcke auf einmal zugreifen:

Range("A1:D10,A12:D12,G1").Font.Bold = True

Das Format hierfür entspricht dem Format, das Sie bei der Auswahl von Zellen für die Formel SUM() in Excel verwenden würden. Jeder Block wird durch ein Komma getrennt, und die Blöcke werden durch die Zellen oben links und unten rechts gekennzeichnet, die durch einen Doppelpunkt getrennt sind.

Schließlich verfügen Range-Objekte über integrierte Methoden zur Durchführung gängiger Operationen auf einem Arbeitsblatt. So können Sie beispielsweise einige Daten von einer Stelle zu einer anderen kopieren. Hier ist ein Beispiel:

Range("A1:D10").Copy
Range("F1").PasteSpecial xlPasteValues
Range("F1").PasteSpecial xlPasteFormats

Dies kopiert die Zellen A1:D10 in die Zwischenablage und führt dann ein PasteSpecial() ab Zelle C1 aus, genau wie Sie es manuell in Excel tun würden. Beachten Sie, dass dieses Beispiel zeigt, wie man PasteSpecial() verwendet, um nur Werte und Formate einzufügen – es gibt Parameter für alle Optionen, die Sie im Dialogfeld „Einfügeoptionen“ sehen würden.

Hier ist ein Beispiel für das Einfügen von „Allem“ in ein anderes Arbeitsblatt:

Range("A1:D10").Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteAll

If-Anweisungen

Mit einer If-Anweisung können Sie einen Abschnitt des Codes nur dann ausführen lassen, wenn ein bestimmter Ausdruck „wahr“ ist.

Sie können zum Beispiel eine Zelle fett und rot einfärben, aber nur, wenn der Wert in der Zelle kleiner als 100 ist.

If Range("A4").Value < 100 Then
    Range("A4").Font.Bold = True
    Range("A4").Interior.Color = vbRed
End If

Die richtige Struktur einer If-Anweisung sieht folgendermaßen aus (eckige Klammern kennzeichnen optionale Komponenten):

If <Bedingung> Then

[ElseIf <andere Bedingung> Then]

[Else]

End If

Sie können so viele ElseIf-Blöcke einfügen, wie Sie möchten, um mehrere Bedingungen zu testen. Sie können auch einen Else-Block hinzufügen, der nur ausgeführt wird, wenn keine der anderen Bedingungen in der If-Anweisung erfüllt ist. Hier ist ein weiteres auf dem vorherigen basierendes Beispiel, bei dem die Zelle je nach Wert auf verschiedene Weise formatiert wird:

If Range("A4").Value < 100 Then
    Range("A4").Font.Bold = True
    Range("A4").Interior.Color = vbRed
ElseIf Range("A4").Value < 200 Then
    Range("A4").Font.Bold = False
    Range("A4").Interior.Color = vbGelb
Else
    Range("A4").Font.Bold = False
    Range("A4").Interior.Color = vbGreen
End If

Im obigen Beispiel ist die Zelle in den ElseIf-Blöcken, in denen der Wert nicht unter 100 liegt, nicht fett gedruckt. Sie können If-Anweisungen verschachteln, um doppelten Code zu vermeiden. Etwa so:

If Range("A4").Value < 100 Then
    Range("A4").Font.Bold = True
    Range("A4").Interior.Color = vbRed
Else
    Range("A4").Font.Bold = False ' Der Schriftschnitt wird nur einmal auf normal zurückgesetzt.
    If Range("A4").Value < 200 Then
        Range("A4").Interior.Color = vbYellow
    Else
        Range("A4").Interior.Color = vbGreen
    End If
End If

Variablen

Eine Variable ist ein Teil des Speichers, in dem temporäre Informationen gespeichert werden, während ein Makro ausgeführt wird. Sie werden oft in Schleifen als Iteratoren verwendet oder um das Ergebnis einer Operation zu speichern, die Sie im Verlauf eines Makros mehrmals verwenden möchten.

Hier ist ein Beispiel für eine Variable und ihre Verwendung:

Sub SerienNrExtrahieren()
    Dim strSerienNr As String ' dies ist die Variablendeklaration
                              ' As String' bedeutet, dass diese Variable für Text gedacht ist
    
    ' Einrichten einer fiktiven Seriennummer:
    Range("A4").Value = "serial# 804567-88"
    
    ' Die Seriennummer aus Zelle A4 auslesen und der Variable zuweisen
    strSerienNr = Mid(Range("A4").Value, 9)
    
    ' Die Variable nun zweimal verwenden, anstatt die Seriennummer zweimal zu analysieren
    Range("B4").Value = strSerienNr
    MsgBox strSerienNr
End Sub

In diesem einfachen Beispiel wird die Variable „strSerienNr“ verwendet, um die Seriennummer aus Zelle A4 mit der Funktion Mid() zu extrahieren, und wird dann an zwei anderen Stellen verwendet.

Standardmäßig wird eine Variable wie folgt deklariert: Dim irgendeinname[As type]

  • irgendeinname ist der Name, den Sie Ihrer Variable geben möchten
  • type ist der Datentyp der Variable

Der „[As type]“-Teil kann weggelassen werden, in diesem Fall wird die Variable als Variant-Typ deklariert, der jede Art von Daten enthalten kann. Obwohl sie durchaus zulässig sind, sollten Variant-Typen vermieden werden, da sie zu unerwarteten Ergebnissen führen können, wenn man mit ihnen nicht vorsichtig umgeht.

Es gibt Regeln für Variablennamen. Sie müssen entweder mit einem Buchstaben oder einem Unterstrich beginnen, dürfen keine Leerzeichen, Punkte, Kommas, Anführungszeichen oder die Zeichen „! @ & $ #” enthalten.

Hier sind einige Beispiele für Variablendeklarationen:

Dim strDateiname As String ' guter Namensstil - beschreibend und mit Präfix

Dim i As Long ' schlechter Namensstil - nur für einige Iteratoren akzeptabel

Dim VerkaufsPreis As Double ' befriedigender Namensstil - beschreibend, aber ohne Präfix

Dim iZaehler ' befriedigender Name - nicht zu beschreibend, verwendet Präfix, kein Datentyp

Alle diese Beispiele verwenden leicht unterschiedliche Benennungsschemata, aber alle sind gültig. Es ist keine schlechte Idee, einem Variablennamen eine Kurzform seines Datentyps voranzustellen (wie in einigen dieser Beispiele), da dies Ihren Code auf einen Blick lesbarer macht.

VBA enthält eine Vielzahl von grundlegenden Datentypen. Zu den bekanntesten gehören:

  • String (zur Speicherung von Textdaten)
  • Long (für ganze Zahlen, d. h. ohne Nachkommastellen)
  • Double (für Fließkommazahlen, d. h. mit Dezimalstellen)

Eine vollständige Liste der VBA-eigenen Datentypen finden Sie hier: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary

Range-Objekt-Variablen

Es ist auch möglich, Variablen zu erstellen, die auf Bereichsobjekte verweisen. Dies ist nützlich, wenn Sie in Ihrem Code an mehreren Stellen auf einen bestimmten Bereich verweisen möchten. Auf diese Weise brauchen Sie den Bereich nur an einer Stelle zu ändern.

Wenn Sie eine Range-Objektvariable erstellen, müssen Sie sie auf eine Instanz eines Bereichs „setzen“. Zum Beispiel:

Dim rMeinBereich As Range
Set rMeinBereich = Range("A1:A10;D1:J10")

Wenn Sie bei der Zuweisung einer Range-Variable die „Set“-Anweisung weglassen, führt dies zu einem Fehler.

Schleifen

Schleifen sind Blöcke, die den in ihnen enthaltenen Code eine bestimmte Anzahl von Malen wiederholen. Sie sind nützlich, um die Menge des zu schreibenden Codes zu reduzieren, und ermöglichen es Ihnen, ein Stück Code zu schreiben, das dieselben Aktionen für viele verschiedene verwandte Elemente durchführt.

For-Next

Ein For-Next-Block ist eine Schleife, die eine bestimmte Anzahl von Malen wiederholt wird. Er verwendet eine Variable als Iterator, um zu zählen, wie oft er durchlaufen wurde, und diese Iterator-Variable kann innerhalb der Schleife verwendet werden. Dadurch sind For-Next-Schleifen sehr nützlich für die Iteration durch Zellen oder Arrays.

Im folgenden Beispiel werden die Zellen in den Zeilen 1 bis 100, Spalte 1, in einer Schleife durchlaufen und ihre Werte auf den Wert der Iterator-Variable gesetzt:

Dim i As Long
For i = 1 To 100
    Cells(i, 1).Value = i
Next i

Die Zeile „For i = 1 To 100“ bedeutet, dass die Schleife bei 1 beginnt und nach 100 endet. Sie können jede beliebige Start- und Endnummer festlegen; Sie können auch Variablen für diese Nummern verwenden.

Standardmäßig zählen For-Next-Schleifen um 1. Wenn Sie um eine andere Zahl zählen möchten, können Sie die Schleife mit einer expliziten Step-Klausel schreiben:

For i = 5 To 100 Step 5

Diese Schleife beginnt bei 5 und addiert bei jeder Wiederholung 5 zu ‚i‘ (bei der zweiten Wiederholung ist ‚i‘ also 10, bei der dritten 15 usw.).

Mit Step können Sie eine Schleife auch rückwärts zählen lassen:

For i = 100 To 1 Step -1

Sie können auch For-Next-Schleifen verschachteln. Jeder Block benötigt seine eigene Variable zum Zählen, aber Sie können diese Variablen überall verwenden. Hier ein Beispiel, das in Excel VBA nützlich ist:

Dim i As Long, j As Long
For i = 1 bis 100
    For j = 1 bis 100
        Cells(i, j).Value = i * j
    Next j
Next i

So können Sie sowohl Zeilen als auch Spalten in einer Schleife durchlaufen.

ACHTUNG: Obwohl es erlaubt ist, sollten Sie die Iterator-Variable innerhalb eines For-Next-Blocks NIEMALS VERÄNDERN, da dieser Iterator verwendet wird, um die Schleife zu verfolgen. Eine Änderung des Iterators kann zu einer Endlosschleife führen und Ihr Makro blockieren. Zum Beispiel:

For i = 1 bis 100
    i = 1
Next i

In dieser Schleife kommt ‚i‘ nie über 2 hinaus, bevor es auf 1 zurückgesetzt wird und die Schleife wird sich ewig wiederholen.

For-Each

For-Each-Blöcke sind den For-Next-Blöcken sehr ähnlich, außer dass sie keinen Zähler verwenden, um anzugeben, wie oft sie eine Schleife durchlaufen. Stattdessen nimmt ein For-Each-Block eine „Sammlung“ von Objekten (z. B. einen Bereich von Zellen) und wird so oft wie die Anzahl der in der Sammlung vorhandenen Objekte ausgeführt.

Hier ist ein Beispiel:

Dim r As Range
For Each r In Range("A15:J54")
    If r.Value > 0 Then
        r.Font.Bold = True   
    End If
Next r

Beachten Sie die Verwendung der Range-Objektvariable „r“. Dies ist die Iterator-Variable, die in der For-Each-Schleife verwendet wird. Jedes Mal, wenn die Schleife durchlaufen wird, erhält ‚r‘ einen Verweis auf die nächste Zelle im Bereich.

Ein Vorteil der Verwendung von For-Each-Schleifen in Excel VBA ist die Tatsache, dass Sie alle Zellen in einem Bereich in einer Schleife durchlaufen können, ohne Schleifen zu verschachteln. Dies kann praktisch sein, wenn Sie eine Schleife durch alle Zellen in einem komplexen Bereich wie Range(„A1:D12,J13, M1:Y12“) durchführen müssen.

Ein Nachteil von For-Each-Schleifen ist die Tatsache, dass Sie keine Kontrolle über die Reihenfolge haben, in der die Zellen verarbeitet werden. Obwohl Excel in der Praxis die Zellen in einer Schleife der Reihe nach durchläuft, könnte es theoretisch die Zellen in einer völlig beliebigen Reihenfolge verarbeiten. Wenn Sie Zellen in einer bestimmten Reihenfolge verarbeiten müssen, sollten Sie stattdessen For-Next-Schleifen verwenden.

Do-Schleife

Während For-Next-Blöcke Zähler verwenden, um zu wissen, wann sie anhalten sollen, laufen Do-Loop-Blöcke, bis eine Bedingung erfüllt ist. Dazu verwenden Sie entweder am Anfang oder am Ende des Blocks eine Until-Klausel , die die Bedingung testet und die Schleife anhält, wenn die Bedingung erfüllt ist.

Beispiel:

Dim str As String
str = "Büffel"
Do Until str = "Büffel Büffel Büffel Büffel Büffel Büffel Büffel"
    str = str & " " & "Büffel"
Loop
Range("A1").Value = str

In dieser Schleife wird „Büffel“ jedes Mal an „str“ angehängt, bis es mit dem erwarteten Satz übereinstimmt. In diesem Fall wird der Test am Anfang der Schleife durchgeführt. wenn ’str‘ bereits der erwartete Satz wäre, was sie nicht ist, weil wir sie nicht so begonnen haben, aber wenn es der Fall wäre, würde die Schleife erst gar nicht laufen.

Sie können die Schleife mindestens einmal laufen lassen, indem Sie die Until-Klausel an das Ende verschieben. Etwa so:

Do
    str = str & " " & "Büffel"
Loop Until str = "Büffel Büffel Büffel Büffel Büffel Büffel Büffel"

Sie können die Version verwenden, die in Ihrem Makro sinnvoll ist.

ACHTUNG: Sie können mit einem Do-Loop-Block eine Endlosschleife verursachen, wenn die Until-Bedingung nie erfüllt wird. Schreiben Sie Ihren Code immer so, dass die Until-Bedingung auf jeden Fall erfüllt wird, wenn Sie diese Art von Schleife verwenden.

Wie geht’s weiter?

Wenn Sie die Grundlagen verstanden haben, warum versuchen Sie nicht, einige fortgeschrittenere Techniken zu erlernen? Unser Tutorial auf https://www.automateexcel.com/excel/learn-vba-tutorial/ baut auf allem auf, was Sie hier gelernt haben, und erweitert Ihre Fähigkeiten mit Ereignissen, Benutzerformularen, Codeoptimierung und vielem mehr!

VBA Code Generator