Aufbau einer Excel Arbeitsmappe
Zum besseren Verständnis der Excel Objekte ist in der folgenden Abbildung der Aufbau einer Excel Arbeitsmappe (engl. Workbook) gezeigt. Die Abbildung zeigt eine Arbeitsmappe, die drei Tabellenblätter (engl. Worksheets) enthält. Ein Tabellenblatt enthält Zellen (engl. cells), die in Spalten (engl. column) und Zeilen (engl. row) organisiert sind:
- die Spalten werden durch einen Buchstaben bzw. eine Buchstabengruppe gekennzeichnet.
- die Zeilen werden durchnummeriert.
Zellen können in Bereiche (engl. range) zusammengefasst werden, um Gruppen von Zellen zu adressieren.
Excel Objekt Hierarchie
Die Excel Ojekte sind hierarchisch organisiert. Wichtige Beispiele sind das „Cells“ und „Range“ Objekt. Die Hierarchie für diese Objekte ist in der Abbildung mit blauen Ellipsen markiert.
Workbook und Worksheet Objekte
Workbook Objekt
Mit dem Workbook-Objekt kann man auf eine Excel Arbeitsmappe (engl. Workbook) zugreifen. Sind mehrere Excel Arbeitsmappen geöffnet, werden die Workbook-Objekte in einer Workbook Collection erfasst. Auf ein Workbook kann man wie folgt zugreifen
- durch Angabe eines Index als fortlaufende Nummer für alle geöffneten Workbooks
- durch Angabe des Workbook-Namens
Beispiel:
Ist der Dateiname der Excel Arbeitsmappe „Test.xlsm“, wird die Arbeitsmappe über den Dateinamen ohne den Pfad angesprochen:
Workbooks("Test.xlsm")Worksheet Objekt
Eine Excel Arbeitsmappe kann ein oder mehrere Tabellenblätter enthalten. Mit dem Worksheet-Objekt kann man auf die Tabellenblätter zugreifen. Enthät eine Excel Arbeitsmappe mehrere Tabellenblätter werden die Worksheet-Objekte in einer Worksheets Collection erfasst. Auf ein Worksheet kann man wie folgt zugreifen
- durch Angabe eines Index als fortlaufende Nummer aller vorhandenen Worksheets
- durch Angabe des Worksheet-Namens
Beispiel:
Enthät die Excel Arbeitsmappe ein Tabellenblatt mit dem Namen „Tabelle1“, wird das Tabellenblatt wie folgt über diesen Namen angesprochen:
Worksheets("Tabelle1")Spezielle Workbook- oder Worksheet-Objekte
Manchmal möchte man die aktive Arbeitsmappe oder das aktive Tabellenblatt referenzieren. Dazu können folgende Objekte verwendet werden
- ActiveWorkbook für die aktive Arbeitsmappe.
- ActiveSheet für das aktive Tabellenblatt.
Falls die Arbeitsmappe referenziert werden soll, die den VBA Code enthält kann man folgendes Objekt verwenden
- ThisWorkbook für die Arbeitsmappe, die den VBA Code enthält.
Werte in eine Zelle schreiben oder aus einer Zelle auslesen
Jedes Tabellenblatt besteht aus ungezählten kleinen Rechtecken, die als Zellen bezeichnet werden. Wie in der Abbildung gezeigt befindet sich eine Zelle im Schnittpunkt einer Zeile (engl. Row) und einer Spalte (engl. Column). Zeilen- und Spaltenindex bilden die Adresse einer Zelle.
- Die Spalten werden durch Buchstaben (A, B, C) gekennzeichnet.
- Die Zeilen werden durch Zahlen (1, 2, 3) gekennzeichnet.
Klickt man mit der linken Maustaste auf eine Zelle, wird die Adresse im blau umrandeten Namenfeld angezeigt. Im gezeigten Beispiel ist die Adresse der markierten Zelle „D4“, für die Spalte D und die Zeile 4.
Die Interaktion zwischen VBA und den Zellen in einem Excel Tabellenblatt ist sehr wichtig. In VBA wird für die Adressierung von Zellen oder Zellbereichen entweder das Range- oder Cells–Objekt verwendet.
Cells und Range Objekt
Mit dem Cells und Range Objekt lassen sich einzelne Zellen oder auch Zellbereiche in einem Excel Tabellenblatt ansprechen:
- Range Objekt
Argument:
– Durch Hochkommata eingeschlossene Zelladresse z. B. „A1“, „B2“, „C4“
– Ein durch Hochkommas eingeschlossener Zellbereich z. B. „B3:D8“
– Eine durch Kommata getrennte Liste mehrerer Zellenbereiche z. B. „A1:C5, D5:F8“ - Cells Objekt
Argument: Zeilen- und Spaltenindex
Beispiel:
' Den Wert 20 in die Zelle in der Zeile 1 und Spalte 2 schreiben
Application.Workbooks("Test.xlsm").Worksheets("Tabelle1").Cells(1, 2).Value = 20
Application.Workbooks("Test.xlsm").Worksheets("Tabelle1").Range("B1").Value = 20Hierbei ist „Value“ (deutsch Wert) eine Eigenschaft des „Cells“ oder „Range“ Objektes. Mit den beiden Zeilen wird der Wert 20 in die Zelle „B1“ geschrieben.
Die Objekte der Objekt Hierarchie und die Eigenschaft werden durch Punkte getrennt aufgeführt. Wie im Beispiel gezeigt, kann das ziemlich lang werden. Ist nur ein Workbook geöffnet, kann man eine verkürzte Notation verwenden. VBA ergänzt in diesem Fall automatisch die fehlenden Angaben.
Beispiel
' Den Wert 20 in die Zelle in der Zeile 1 und Spalte 2 schreiben
Worksheets("Tabelle1").Cells(1, 2).Value = 20
Worksheets("Tabelle1").Range("B1").Value = 20Beispiel Sub-Prozedur
Die Sub Prozedur schreibt den Wert 20 in die Excel Zelle „B1“ (in der Zeile 1 und Spalte 2). Außerdem liest sie den Wert in der Zelle B1 (Zeile 1 und Spalte 2) aus und weist ihn der Variablen iZahl zu. Der Inhalt der Variablen iZahl wird im Direktfenster ausgegeben.
Sub Test2()
' Variable ws deklarieren
Dim iZahl As Integer
Dim ws As Worksheet
' Objekt Variable ws setzen
Set ws = Worksheets("Tabelle3")
'
' Den Wert 20 in die Zelle in den Zeilen 2 und 3 in Spalte 1 schreiben
ws.Cells(2, 1).Value = 20
ws.Range("A3").Value = 20
' Den Wert 100.5 in die Zelle in den Zeilen 4 und 5 in Spalte 1 schreiben
ws.Cells(4, 1).Value = 100.5
ws.Range("A5").Value = 100.5
' Die Hex-Zahl 3E8 als Text in die Zelle in Zeile 6 und Spalte 1 schreiben
ws.Cells(6, 1).Value = "3E8"
' Die Hex-Zahl "3E8" als Text mit führendem Apostroph in die Zelle in Zeile 7
' und Spalte 1 schreiben. Der führende Apostroph zwingt Excel den Zellinhalt als Text
' zu behandeln.
ws.Cells(7, 1).Value = "'" & "3E8"
' Den Text "5-1" in die Zelle in Zeile 8 und Spalte 1 schreiben
ws.Cells(8, 1).Value = "5-1"
' Den Text "5-1" mit führendem Apostroph in die Zelle in Zeile 9 und Spalte 1 schreiben.
' Der führende Apostroph zwingt Excel den Zellinhalt als Text zu behandeln.
ws.Cells(9, 1).Value = "'" & "5-1"
'
' Wert aus der Zelle in der Zeile 1 und Spalte 2 auslesen
iZahl = ws.Cells(2, 1).Value
Debug.Print iZahl
' Objekt Variable ws loeschen
Set ws = Nothing
End SubDas Ergebnis sieht wie folgt aus:
Anmerkungen zum Makro „Test2“:
Kommentare in einem VBA Makro:
Im Beispiel wurden zwei Kommentare im Beispiel Makro eingefügt. Die Kommentare beginnen mit einem einfachen Anführungszeichen. Ein Beispiel für einen Kommentar ist in Zeile 2 gezeigt.
Deklaration der Variablen mit „Dim“:
Im Beispiel wurde eine Variable mit dem einleitenden Schlüsselwort „Dim“ deklariert. Es ist empfehlenswert, alle in einem Makro verwendeten Variablen zu deklarieren.
Setzen von Objektvariablen mit „Set“:
Objektvariablen müssen mit einem einleitenden „Set“ gesetzt werden. Mit
Set ws = Nothing
in der letzten Zeile des Beispiels wird die Objektvariable „ws“ gelöscht.
„Cells“ versus „Range“:
„Cells“ und „Range“ Objekt unterscheiden sich in der Adressierung der Zellen in einem Tabellenblatt:
– Range, Zelladresse: Ziffern 1, 2, 3, … für die Zeile, Buchstaben A, B, C, … für die Spalte
– Cells, Zelladresse: Ziffern 1, 2, 3, … für Zeile und Spalte, Cells(ZeilenIndex, SpaltenIndex)
In der Praxis ist die Variante mit dem „Cells“ Objekt wesentlich einfacher zu handhaben.
Gleitkommazahlen (Dezimalzahlen):
In der deutschen Excel Variante werden Gleitkommazahlen in einem Tabellenblatt mit einem Komma als Dezinaltrennzeichen dargestellt. In VBA muss dagegen ein Punkt als Dezimaltrennzeichen verwendet werden. Schreibt am eine Gleitkommazahl mit VBA in eine Excel Zelle wird die Zahl automatisch in das „deutsche Format“ mit einem Komma als Dezimaltrennzeichen umgewandelt. Liest man diese Zelle mit VBA aus, wird die Gleitkommazahl (Dezimalzahl) in VBA in eine Gleitkommazahl mit einem Punkt als Dezimaltrennzeichen umgewandelt.
Text in eine Zelle schreiben
In besonderen Fällen wird ein Text, der mit VBA in eine Zelle geschrieben wird automatisch in ein anderes Format umgewandelt. Möchte man das verhindern kann man dem Text ein führendes einfaches Anführungszeichen hinzufügen. Das zwingt Excel dazu, den Zellinhalt als Text zu behandeln und nicht umzuwandeln.
Excel Tabellenblätter auf die Z1S1 Bezugsart umschalten
Verwendet man „Cells“ empfiehlt es sich, die Standardansicht der Excel Tabellenblätter wie folgt auf die Z1S1 Bezugsart umzuschalten:
- auf der Excel Registerkarte „Datei“ auf „Optionen“ klicken, um das Dialogfeld „Optionen“ zu öffnen.
- auf der linken Seite des Dialogfelds „Formeln“ auswählen.
- unter der Rubrik „Arbeiten mit Formeln“ das Häkchen bei „Z1S1-Bezugsart“ setzen.
- Abschließend auf „OK“ klicken.
Wird das Häkchen entfernt erhält man wieder die ursprüngliche Bezugsart, also das usprüngliche Aussehen der Tabellenblätter. Zur Standard Ansicht gelangt man, in dem man das Häkchen wieder entfernt.
Unterschiede in den Bezugsarten in einem Excel Tabellenblatt
Standard Bezugsart:
- in der ersten Zeile der Excel Tabelle werden die Spalten mit Buchstaben A, B, C, .. nummeriert
- die Zeilen werden mit Ziffern 1, 2, 3, … nummeriert
Beispiel: B1- Spalte 2, Zeile 1
Z1S1 Bezugsart:
- in der ersten Zeile der Excel Tabelle werden die Spalten mit Ziffern 1, 2, 3, … nummeriert.
- die Zeilen werden mit Ziffern 1, 2, 3, … nummeriert.
Beispiel: Spalte 2, Zeile 1
Eine Zelle formatieren
Dies wird im Beitrag „Zellen und Tabellenblätter formatieren“ ausführlich behandelt. Dort wird beispielsweise behandelt, wie man den Hintergrund einer Zelle oder Text in einer Zelle einfärben kann.
Einen Zellbereich (engl. Range) ansprechen
Mit Bereichsvariablen arbeiten
In folgendem Beispiel Code wird gezeigt, wie man eine Bereichsvariable deklariert und ihr einen Bereich zuweisen kann.
Sub Bereichsvariable_deklarieren_und_zuweisen()
' Bereichsvariable deklarieren
Dim rngBereich As Range
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
'
' Der Bereichsvariabln einen Bereich zuweisen
Set rngBereich = ws.Range("B1:B10")
'
' Text in den Bereich schreiben
rngBereich.Value = "Test"
'
Set ws = Nothing
End SubIn folgendem Beispiel Code wird ein Rahmen für einen Bereich eingefügt:
Sub Rahmen_hinzufuegen()
'
Dim i As Integer, iNoOfRows As Integer, iNoOfColumns As Integer
Dim iStartRow As Integer
Dim aArrayB As Variant
Dim ws As Worksheet
'
' die vier folgende Zeilen muessen angepasst werden:
Set ws = Worksheets("Tabelle1") ' Tabellenblatt
iNoOfRows = 10 ' Anzahl der Zeilen
iNoOfColumns = 10 ' Anzahl der Spalten
iStartRow = 1 ' Startzeile
'
aArrayB = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
'
' Bereich auswählen
ws.Range(Cells(iStartRow, 1), Cells(iNoOfRows, iNoOfColumns)).Select
'
' Gitternetzlinien einfügen
For i = LBound(aArrayB) To UBound(aArrayB)
With Selection.Borders(aArrayB(i))
.LineStyle = xlContinuous ' Durchgezogene Linie
.Color = RGB(0, 0, 0) ' Schwarze Farbe
.TintAndShade = 0
.Weight = xlThin ' Strichstaerke fein
End With
Next i
Set ws = Nothing
End SubIn folgendem Beispiel Code wird der Inhalt eines Bereichs einer Datenfeld-Variablen zugeweisen:
Sub Bereich_einem_Datenfeld_zuweisen()
Dim ws As Worksheet, varArray() As Variant, i As Integer
Set ws = Worksheets("Tabelle1")
varArray = ws.Range(Cells(2, 1), Cells(21, 1))
'
For i = LBound(varArray) To UBound(varArray)
Debug.Print i, varArray(i, 1)
Next i
'
Set ws = Nothing
End SubEine Spalte ansprechen
in folgendem Beispiel Code wird die Breite einer Spalte ausgelesen und im Direktfenster ausgegeben. Dabei Excel misst Spaltenbreite basierend auf der Größe (Breite) der Schriftart, die im Stil „Normal“ verwendet wird (z. B. Calibri 11).
Sub Breite_einer_Spalte_auslesen()
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
'
' Breite der Spalte 1 auslesen und im Direktfenster ausgeben
Debug.Print ws.Columns(1).ColumnWidth
'
' Breite der Spalte 2 (= Spalte B) auslesen und im Direktfenster ausgeben
Debug.Print ws.Columns("B").ColumnWidth
'
' Breite der Spalte B automatisch anpassen
ws.Columns("B").AutoFit
'
Set ws = Nothing
End SubEine Möglichkeit besteht darin, die Spaltenbreite aus einem anderen Tabellenblatt zu „kopieren“. Wie das geht wird in folgendem Beispiel Code gezeigt:
Sub Breite_von_Spalte_aus_anderem_Tabellenblatt_kopieren()
Dim i As Integer
Dim wsInput As Worksheet, wsOutput As Worksheet
Set wsOutput = Worksheets("Tabelle1")
Set wsInput = Worksheets("Tabelle2")
'
For i = 1 To 10
' Breite der Spalte i kopieren
wsOutput.Columns(i).ColumnWidth = wsInput.Columns(i).ColumnWidth
Next i
'
Set wsOutput = Nothing
Set wsInput = Nothing
End SubEine Zeile (engl. Row) ansprechen
mit folgendem Beispiel Code wird eine Zeile selektiert
Sub Eine_Zeile_selektieren()
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
'
' Zeile 5 selektieren
ws.Rows(5).Select
'
Set ws = Nothing
End SubMit folgendem Beispiel Code werden mehrere Zeilen selektiert
Sub Mehrere_Zeile_selektieren()
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
'
' Zeilen 5 bis 8 selektieren
ws.Rows("5:8").Select
'
Set ws = Nothing
End Sub
Intellisense
Mit Intellisense werden im Visual Basic Editor nach der Eingabe eines Punktes nach einem Objekt die verfügbaren Objekte, Methoden und Eigenschaften in einer DropDown Liste angezeigt. Wenn man den ersten Buchstaben des Objektes, der Methode oder der Eigenschaft eingibt, springt Intellisense an die gewünschte Stelle. Ein Beispiel ist in der folgenden Abbildung zu sehen.
Objektkatalog
Der Visual Basic Editor stellt auch einen Objektkatalog zur Verfügung. Man kann ich auf unterschiedliche Weise anzeigen
- F2 Taste drücken.
- Menübefehl Ansicht -> Objektkatalog aufrufen.
- in der Symbolleiste Voreinstellung auf das Symbol Objektkatalog klicken.
Tipp:
Wenn man im Projekt Explorer des Visual Basic Editors auf das zu bearbeitende Modul klickt, wird das Code Fenster für das Modul statt dem Objekt Explorer angezeigt.
In der Abbildung ist der Objektkatalog dargestellt. In der linken Spalte habe ich auf „Worksheet“ geklickt. In der rechten Spalte werden die Elemente von „Worksheet“ angezeigt.
