Der Makrorekorder von Excel hat viel Leistung, aber er hat 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. Es kann auch schwierig sein, es für längere Makros zu verwenden - Sie können Ihre Aktionen vorher mit einem Storyboard versehen, nur um kostspielige Fehler zu vermeiden.
Dieser Artikel soll Ihnen helfen, Makros in VBA von Grund auf neu zu programmieren. Sie lernen, wo Makros gespeichert werden, schreiben ein grundlegendes Makro und lernen die Grundlagen der Programmierung in VBA mit Variablen, Logik und Schleifen.
Einstieg
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, egal ob sie handcodiert oder mit dem Makrorekorder erstellt wurden.
Sie können mit dem Visual Basic-Editor auf den gesamten VBA-Code in einer Arbeitsmappe zugreifen. Dies ist ein spezieller Texteditor und Debugger, der in alle Office-Apps, einschließlich Excel, integriert ist. Normalerweise öffnen Sie diesen Editor mit der ALT+F11 Tastenkombination in Excel, aber Sie können auch über Excel darauf zugreifen Entwickler Registerkarte, wenn Sie es aktiviert haben.
Der Projekt-Explorer
Die Projekt-Explorer ist ein Fenster im VB-Editor, das Ihnen alle Elemente anzeigt, die VBA-Code enthalten können. Wenn dieses Fenster nicht angezeigt wird, drücken Sie F5 um es erscheinen zu lassen oder auszuwählen Projekt-Explorer von dem Sicht Speisekarte.
Wenn Sie im Projekt-Explorer auf ein Element doppelklicken, wird der Code für dieses Element angezeigt. Es gibt verschiedene Arten von Elementen, die im Projekt-Explorer angezeigt werden können:
- Arbeitsbücher
- Arbeitsblätter
- Benutzerformulare
- Klassenmodule
- Module (Makros werden in diesen Elementen gespeichert)
Obwohl alle diese Elementtypen VBA-Code enthalten können, empfiehlt es sich, Makros in Modulen zu codieren.
Erstellen Sie Ihr erstes Makro
Verwenden der Makroliste
Die Makroliste zeigt Ihnen alle Makros in Ihrer Arbeitsmappe. In dieser Liste können Sie ein vorhandenes Makro bearbeiten oder ein neues erstellen.
So erstellen Sie ein neues Makro mithilfe der Liste Makros:
- Wählen Sie die Registerkarte Entwickler und klicken Sie auf Makros (oder drücke ALT+F8)
- Geben Sie einen neuen Namen für Ihr Makro ein und klicken Sie dann auf "Erstellen".
Nachdem Sie auf „Erstellen“ geklickt haben, erscheint der VB-Editor und zeigt das neu erstellte Makro. Excel erstellt bei Bedarf ein neues Modul für das Makro.
Manuell im VB-Editor
Sie können ein neues Makro manuell ohne die Liste Makros hinzufügen. Dies ist die bessere Option, wenn Sie das Modul angeben möchten, in dem das Makro gespeichert wird.
So fügen Sie ein Makro manuell hinzu:
- Öffnen Sie den VB-Editor (ALT+F11)
- Entweder:
- Fügen Sie ein neues Modul hinzu, indem Sie auf klicken Einfügen > Modul im Menü (das Modul öffnet sich automatisch)
-
- ODER doppelklicken Sie im Projekt-Explorer auf ein vorhandenes Modul, um es zu öffnen
- Geben Sie im Modul den Code für Ihr neues Makro ein
Sub MyMacro() Ende Sub
Diese beiden Zeilen geben den Anfang und das Ende eines Makros mit dem Namen „MyMacro“ an (beachten Sie die erforderlichen Klammern). Diese wird in Excel im Dialog „Makros anzeigen“ angezeigt und kann einer Schaltfläche zugewiesen werden (auch wenn sie noch nichts tut).
Fügen Sie dem Makro etwas Code hinzu
Fügen wir nun etwas Code zwischen den Zeilen "Sub" und "End Sub" hinzu, damit dieses Makro tatsächlich etwas tut:
Sub MyMacro() Range("A1").Value = "Hallo Welt!" End Sub
Grundlegende Codestrukturen
Das Range-Objekt
Excel VBA verwendet das Range-Objekt, um Zellen in einem Arbeitsblatt darzustellen. Im obigen Beispiel wird ein Range-Objekt mit dem Code erstellt Reichweite("A1") um auf den Wert der Zelle A1 zuzugreifen.
Bereichsobjekte werden hauptsächlich verwendet, um Zellenwerte festzulegen:
Bereich("A1").Wert = 1
Range("A1").Value = "Erste Zelle"
Beachten Sie, dass Sie beim Definieren von Zellenwerten als Zahlen nur die Zahl eingeben, aber bei der Texteingabe müssen Sie den Text in Anführungszeichen setzen.
Bereiche können auch verwendet werden, um auf viele Eigenschaften von Zellen wie Schriftart, Rahmen, Formeln und mehr zuzugreifen.
Sie können beispielsweise die Schriftart einer Zelle wie folgt auf Fett setzen:
Bereich("A1").Font.Bold = True
Sie können auch die Formel einer Zelle festlegen:
Bereich("A1").Formel = "=Summe(A2:A10)"
In Excel können Sie mit dem Cursor einen Zellenblock auswählen (z. B. von A1 bis D10) und alle auf Fettdruck setzen. Range-Objekte können wie folgt auf Zellblöcke zugreifen:
Bereich("A1:D10").Font.Bold = True
Sie können auch auf mehrere Zellen/Blöcke gleichzeitig verweisen:
Bereich("A1:D10,A12:D12,G1").Font.Bold = True
Das Format hierfür entspricht dem Format, das Sie beim Auswählen von Zellen für die SUM()-Formel in Excel verwenden würden. Jeder Block wird durch ein Komma getrennt, und Blöcke werden durch die oberen linken und unteren rechten Zellen gekennzeichnet, die durch einen Doppelpunkt getrennt sind.
Schließlich verfügen Range-Objekte über integrierte Methoden zum Ausführen allgemeiner Operationen in einem Arbeitsblatt. Sie möchten beispielsweise einige Daten von einem Ort an einen 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() aus, das in Zelle C1 beginnt - genau wie Sie es manuell in Excel tun würden. Beachten Sie, dass dieses Beispiel zeigt, wie Sie PasteSpecial() verwenden, um nur Werte und Formate einzufügen – es gibt Parameter für alle Optionen, die Sie im Dialogfeld Inhalte einfügen sehen.
Hier ist ein Beispiel zum Einfügen von „Alle“ in ein anderes Arbeitsblatt:
Range("A1:D10").Copy Sheets("Sheet2").Range("A1").PasteSpecial xlPasteAll
Wenn-Anweisungen
Mit einem Wenn Anweisung, können Sie einen Codeabschnitt nur ausführen lassen, „wenn“ eine bestimmte Aussage wahr ist.
Sie möchten beispielsweise eine Zelle fett formatieren und rot fä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 wie folgt aus (eckige Klammern kennzeichnen optionale Komponenten):
Wenn, dann
[SonstWenn Dann]
[Anders]
Ende Wenn
Sie können so viele hinzufügen SonstWenn Blöcke, wie Sie mehrere Bedingungen testen möchten. Sie können auch eine hinzufügen Anders Block, der nur ausgeführt wird, wenn keine der anderen Bedingungen in der If-Anweisung erfüllt ist.
Hier ist ein weiteres Beispiel, das auf dem vorherigen basiert, bei dem die Zelle je nach Wert auf verschiedene Arten 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 = vbYellow 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 dargestellt. Sie können Nest If-Anweisungen, um das Duplizieren von Code zu vermeiden, wie folgt:
If Range("A4").Value < 100 Then Range("A4").Font.Bold = True Range("A4").Interior.Color = vbRed Else Range("A4").Font.Bold = False ' Schrift nur einmal aufheben If Range("A4").Value < 200 Then Range("A4").Interior.Color = vbYellow Else Range("A4").Interior.Color = vbGreen End If End If
Variablen
EIN Variable ist ein Speicherteil, der verwendet wird, um temporäre Informationen zu speichern, 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 mehrmals in einem Makro verwenden möchten.
Hier ist ein Beispiel für eine Variable und wie Sie sie verwenden könnten:
Sub ExtractSerialNumber() Dim strSerial As String ' dies ist die Variablendeklaration ' 'As String' bedeutet, dass diese Variable Text aufnehmen soll ' Vorgeben einer Seriennummer: Range("A4").Value = "serial# 804567-88" ” 'parse die Seriennummer aus Zelle A4 und weise sie der Variablen strSerial = Mid(Range(“A4”).Value, 9) 'benutze die Variable jetzt zweimal, anstatt die Seriennummer zweimal parsen zu müssen Range(“ B4").Value = strSerial MsgBox strSerial End Sub
In diesem einfachen Beispiel wird die Variable „strSerial“ verwendet, um die Seriennummer mit der Funktion Mid() aus Zelle A4 zu extrahieren, und wird dann an zwei anderen Stellen verwendet.
Der Standard-Weg zu erklären eine Variable lautet wie folgt:
Dim egalname [Wie Typ]
- egalname ist der Name, den Sie Ihrer Variablen geben möchten
- Typ ist der Datentyp der Variablen
Die „[As Typ]” kann weggelassen werden - wenn ja, wird die Variable als Variant-Typ deklariert, der jede Art von Daten aufnehmen kann. Obwohl absolut gültig, sollten Variantentypen vermieden werden, da sie zu unerwarteten Ergebnissen führen können, wenn Sie nicht aufpassen.
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 „! @ & $ #”.
Hier sind einige Beispiele für Variablendeklarationen:
Dim strFilename As String ' guter Namensstil - beschreibend und verwendet Präfix Dim i As Long ' schlechter Namesstil - nur für einige Iteratoren akzeptabel Dim SalePrice As Double ' okayer Namensstil - beschreibend, verwendet aber kein Präfix Dim iCounter ' okayer Name - nicht zu beschreibend, verwendet Präfix, kein Datentyp
Alle diese Beispiele verwenden leicht unterschiedliche Namensschemata, 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 besser lesbar macht.
VBA enthält viele grundlegende Datentypen. Zu den beliebtesten gehören:
- Zeichenfolge (wird verwendet, um Textdaten zu speichern)
- Lang (wird verwendet, um ganze Zahlen zu halten, d. h. keine Nachkommastellen)
- Doppelt (wird verwendet, um Gleitkommazahlen, d. h. Dezimalstellen, zu speichern)
Eine vollständige Liste der intrinsischen VBA-Datentypen finden Sie hier: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Bereichsobjektvariablen
Es ist auch möglich, Variablen zu erstellen, die auf Range-Objekte verweisen. Dies ist nützlich, wenn Sie in Ihrem Code an mehreren Stellen auf einen bestimmten Bereich verweisen möchten. Wenn Sie also den Bereich ändern müssen, müssen Sie ihn nur an einer Stelle ändern.
Wenn Sie eine Range-Objektvariable erstellen, müssen Sie sie auf eine Instanz eines Ranges „setzen“. Zum Beispiel:
Dim rMyRange As Range Set rMyRange = Range(“A1:A10;D1:J10”)
Das Weglassen der Anweisung „Set“ bei der Zuweisung einer Range-Variablen führt zu einem Fehler.
Schleifen
Schleifen sind Blöcke, die den Code in ihnen eine bestimmte Anzahl von Malen wiederholen. Sie sind nützlich, um die Menge an Code zu reduzieren, die Sie schreiben müssen, und ermöglichen es Ihnen, einen Codeabschnitt zu schreiben, der die gleichen Aktionen für viele verschiedene verwandte Elemente ausführt.
Fürs nächste
EIN Fürs nächste block ist eine Schleife, die sich eine bestimmte Anzahl von Malen wiederholt. Es verwendet eine Variable als ein Iterator um zu zählen, wie oft es ausgeführt wurde, und diese Iteratorvariable kann innerhalb der Schleife verwendet werden. Dies macht For-Next-Schleifen sehr nützlich, um Zellen oder Arrays zu durchlaufen.
Hier ist ein Beispiel, das Zellen in den Zeilen 1 bis 100, Spalte 1 durchläuft und ihre Werte auf den Wert der Iteratorvariablen setzt:
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 beliebige Start- und Endzahlen einstellen; Sie können auch Variablen für diese Zahlen verwenden.
For-Next-Schleifen zählen standardmäßig mit 1. Wenn Sie mit einer anderen Zahl zählen möchten, können Sie die Schleife mit einem expliziten . schreiben Schritt Klausel:
Für i = 5 bis 100 Schritt 5
Diese Schleife beginnt bei 5 und fügt dann bei jeder Wiederholung 5 zu „i“ hinzu (also ist „i“ bei der zweiten Wiederholung 10, bei der dritten 15 usw.).
Verwenden von Schritt, können Sie eine Schleife auch rückwärts zählen lassen:
Für i = 100 zu 1 Schritt -1
Du kannst auch Nest For-Next-Schleifen. Jeder Block benötigt eine eigene Variable zum Zählen, aber Sie können diese Variablen überall verwenden, wo Sie möchten. Hier ist ein Beispiel dafür, wie dies in Excel VBA nützlich ist:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells(i, j).Value = i * j Next j Next i
Auf diese Weise können Sie sowohl Zeilen als auch Spalten durchlaufen.
WARNUNG: Obwohl es erlaubt ist, sollten Sie die Iterator-Variable in einem For-Next-Block NIEMALS ÄNDERN, da sie diesen Iterator verwendet, um die Schleife zu verfolgen. Das Ändern des Iterators kann eine Endlosschleife verursachen und Ihr Makro hängen lassen. Zum Beispiel:
Für i = 1 bis 100 i = 1 Weiter i
In dieser Schleife wird 'I' nie über 2 hinausgehen, bevor es auf 1 zurückgesetzt wird, und die Schleife wird für immer wiederholt.
Für jeden
Für jeden Blöcke sind For-Next-Blöcken sehr ähnlich, außer dass sie keinen Zähler verwenden, um anzugeben, wie oft sie durchlaufen. Stattdessen nimmt ein For-Each-Block eine „Sammlung“ von Objekten (wie ein Zellbereich) und wird so oft ausgeführt, wie Objekte in dieser Sammlung vorhanden sind.
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 Iteratorvariable, 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 besteht darin, dass Sie alle Zellen in einem Bereich durchlaufen können, ohne Schleifen zu verschachteln. Dies kann praktisch sein, wenn Sie alle Zellen in einem komplexen Bereich wie durchlaufen müssen Bereich ("A1: D12, J13, M1: Y12").
Ein Nachteil von For-Each-Schleifen besteht darin, dass Sie keine Kontrolle über die Reihenfolge haben, in der Zellen verarbeitet werden. Obwohl in der Praxis Excel durchläuft die Zellen der Reihe nach, in der Theorie es könnte die Zellen in einer völlig zufälligen Reihenfolge verarbeiten. Wenn Sie Zellen in einer bestimmten Reihenfolge verarbeiten müssen, sollten Sie stattdessen For-Next-Schleifen verwenden.
Do-Loop
Während For-Next-Blöcke Zähler verwenden, um zu wissen, wann zu stoppen ist, Do-Loop Blöcke laufen, bis eine Bedingung erfüllt ist. Dazu verwenden Sie ein Bis um -Klausel entweder am Anfang oder am Ende des Blocks, die die Bedingung testet und bewirkt, dass die Schleife anhält, wenn diese Bedingung erfüllt ist.
Beispiel:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & " " & "Buffalo" Loop Range("A1").Value = str
In dieser Schleife wird „Buffalo“ jedes Mal durch die Schleife mit „str“ verkettet, 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 nicht daran liegt, dass wir ihn nicht so gestartet haben, sondern wenn) würde die Schleife nicht einmal laufen .
Sie können die Schleife mindestens einmal ausführen, indem Sie die Until-Klausel wie folgt an das Ende verschieben:
Do str = str & " " & "Buffalo" Loop Bis str = “Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo”
Sie können die Version verwenden, die in Ihrem Makro sinnvoll ist.
WARNUNG: Sie können eine Endlosschleife mit einem Do-Loop-Block verursachen, wenn die Bis-Bedingung nie erfüllt wird. Schreiben Sie Ihren Code immer so, dass die Until-Bedingung definitiv erfüllt wird, wenn Sie diese Art von Schleife verwenden.
Was kommt als nächstes?
Wenn Sie die Grundlagen verstanden haben, warum versuchen Sie nicht, einige fortgeschrittene Techniken zu erlernen? Unser Tutorial unter https://easyexcel.net/excel/learn-vba-tutorial/ baut auf allem auf, was Sie hier gelernt haben und erweitern Ihre Fähigkeiten mit Events, UserForms, Code-Optimierung und vielem mehr!
