Inhaltsverzeichnis

Excel Objektmodell

Allgemein

Bevor wir uns mit den Excel-Objekten befassen, müssen wir zuerst abklären, was unter einem Objekt bzw. einer Klasse verstanden wird.

Stellen wir uns ein beliebiges Auto vor: vielleicht ist dieses Auto in roter Farbe und hat ein Eigengewicht von 1.200 kg. Vermutlich kann dieses Auto auch beschleunigen und bremsen. Stellen wir uns nun ein weiteres Auto vor, das eine blaue Farbe und ein Eigengewicht von 1.500 kg hat. Auch dieses Auto kann beschleunigen und bremsen. Dieses Gedankenspiel könnten wir nun mit einem dritten und vierten Auto fortsetzen.

Da alle realen Autos durch das gleiche Set von Eigenschaften (Farbe, Gewicht) und Verhalten (Beschleunigen, Bremsen) beschrieben werden, drängt sich förmlich die Idee auf, alle diese realen Autos zu einem allgemeinen Typ Auto zusammenzufassen.

Der Typ Auto definiert also das Vorhandensein von Eigenschaften (Farbe, Gewicht) und Verhalten (Beschleunigen, Bremsen). Dies entspricht dem Begriff der Klasse.

Das reale Auto als Instanz des allgemeinen Typs Auto besitzt alle definierten Eigenschaften (Farbe, Gewicht) und Verhalten (Beschleunigen, Bremsen) und hat als Wert der Eigenschaft Farbe die Farbe rot und als Wert der Eigenschaft Eigengewicht die Zahl 1.200. Das reale Auto entspricht dem Begriff des Objekts.

Egal ob Sie nun ein reales Auto oder 100 reale Autos erzeugen, alle diese Autos werden durch das gleiche Set von Eigenschaften (Farbe, Gewicht) und Verhalten (Beschleunigen, Bremsen) beschrieben, wobei jedes dieser realen Autos eine spezifische Farbe bzw. ein spezifisches Eigengewicht besitzt.

Nehmen wir nun ein Beispiel aus der Excel-Welt. Eine Arbeitsmappe enthält ein oder mehrere Tabellenblätter. Wenn wir überlegen, welchen Eigenschaften eines dieser Tabellenblätter besitzt, fallen uns möglicherweise die folgenden ein: Name, Sichtbarkeit, Spalten, Zeilen, Zellen, usw.. Welches Verhalten besitzt dieses Tabellenblatt? Es kann aktiviert, verschoben, kopiert, gelöscht, neuberechnet oder gedruckt werden.

Alle Tabellenblätter einer Arbeitsmappe werden durch das gleiche Set von Eigenschaften und Verhalten beschrieben. Analog dem allgemeinen Typ Auto gibt es in Excel den Typ (die Klasse) Worksheet, der als Bauplan für jedes reale Tabellenblatt dient.

Im Folgenden werden wir die wichtigsten Excel-Objekte durchbesprechen, wobei wir vom obersten zum untersten Objekt gehen. Wir starten mit dem Applikationsobjekt (Application), das die Excel-Anwendung referenziert. Dann machen wir einen kurzen Exkurs zum Thema Auflistungen. Weiter geht es mit dem Arbeitsmappenobjekt (Workbook), das eine geöffnete Excel-Datei referenziert. Eine Ebene tiefer finden wir das Tabellenblattobjekt (Worksheet), das ein einzelnes Tabellenblatt in einer Arbeitsmappe referenziert. Abschließend sehen wir uns das Bereichsobjekt (Range) an, das eine oder mehrere Zellen in einem bestimmten Tabellenblatt referenziert.

Die folgenden Codebeispiele verwenden die Anweisung Debug.Print um in das Ausgabefenster zu schreiben. Blenden Sie das Ausgabefenster im VBA-Editor über den Menübefehl Ansicht - Direktfenster ein.

Applikationsobjekt (Application)

Allgemein

Das Applikationsobjekt entspricht der Host-Anwendung, innerhalb der unser VBA-Code läuft. Da VBA-Code in allen Office-Anwendungen lauffähig ist, ist das Applikationsobjekt die entsprechende Office-Anwendung (z. B. Excel, Word, Powerpoint oder Outlook).

Im folgenden Codebeispiel geben wir den Namen des Applikationsobjektes im Direktfenster aus. Um auf eine Eigenschaft eines Objektes zuzugreifen, verwenden Sie den Punkt-Operator.

'Ausgabe des Applikationsnamens im Ausgabefenster
Debug.Print Application.Name

Läuft unser Code in Excel, so wird im Ausgabefenster der Text Microsoft Excel ausgegeben.

Im folgenden Codebeispiel ist die Ausgabe die gleiche, allerdings wollen wir das Applikationsobjekt in einer Variable mit der Bezeichnung app referenzieren. Beachten Sie, dass der Datentyp der Variable Application ist und Sie beim Zuweisen des Applikationsobjektes zur Variablen app das Schlüsselwort Set verwenden, da es sich um eine Objektvariable handelt.

'Ausgabe des Applikationsnamens im Ausgabefenster
Dim app As Application
Set app = Application
Debug.Print app.Name

Läuft unser Code in Excel, so wird im Ausgabefenster der Text Microsoft Excel ausgegeben.

Nehmen wir an, unser Code ist in einer Excel-Datei gespeichert. Dieser Code öffnet ein Word-Dokument und schreibt Inhalte aus unserer Excel-Datei in das Word-Dokument. Wenn Sie jetzt auf die Excel-Applikation zugreifen wollen, können Sie das selbstverständlich über die Anweisung Application erledigen, da unser Code ja in Excel läuft und daher das Excel-Applikationsobjekt das naheliegendste ist.

Jedes VBA-Objekt ist eindeutig einem Namensraum (Namespace) zugeordnet. Alle Excel-Objekte liegen im Namensraum Excel. Sie sollten sich angewöhnen, diesen Namensraum auch zu verwenden, da dies vor allem dann wichtig wird, wenn Sie Objekte aus anderen Namensräumen verwenden, wie z. B. das Applikationsobjekt von Word.

'Ausgabe des Applikationsnamens im Ausgabefenster
Dim app As Excel.Application
Set app = Excel.Application
Debug.Print app.Name

Im Ausgabefenster wird der Text Microsoft Excel ausgegeben.

In Objektvariablen wird nicht das Objekt selbst, sondern eine Referenz auf das Objekt eingetragen (Referenztypen). Nehmen wir an, Sie haben zwei Objektvariablen, die eine Referenz auf die Excel-Anwendung eingetragen haben.

'Objektreferenzen
Dim app1 As Excel.Application
Dim app2 As Excel.Application
Set app1 = Excel.Application
Set app2 = Excel.Application
Debug.Print app1.Name
Debug.Print app2.Name

Beide Variablen app1 und app2 referenzieren die bestehende Excel-Anwendung. Es gibt nicht zwei Excel-Anwendungen, sondern nur ein Application-Objekt, das über zwei Variablen referenziert wird.

Um zu prüfen, ob zwei Objektvariablen das gleiche Objekt referenzieren, verwenden Sie den Is-Operator.

'Vergleich Objektreferenzen
Dim app1 As Excel.Application
Dim app2 As Excel.Application
Set app1 = Excel.Application
Set app2 = Excel.Application
If app1 Is app2 Then
  Debug.Print "Referenz auf das gleiche Objekt"
Else
  Debug.Print "Referenz auf unterschiedliche Objekte"
End If

Im Ausgabefenster sollte der Text Referenz auf das gleiche Objekt ausgegeben werden.

Eine Objektvariable, die noch keine Referenz auf ein Objekt enthält, hat den Wert Nothing. Um eine Objektvariable wieder freizugeben, also die eingetragene Referenz zu entfernen, setzen Sie die Variable auf den Wert Nothing.

'Objektreferenzen
Dim app As Excel.Application
Set app = Excel.Application
Debug.Print app.Name
Set app = Nothing

Um zu püfen, ob eine Objektvariable eine Referenz auf ein Objekt enthält, verwenden Sie den Operator Is.

'Objektreferenzen
Dim app As Excel.Application
If app Is Nothing Then
  Debug.Print "Keine Objektreferenz"
Else
  Debug.Print "Enthält Objektreferenz"
End If

Um die Logik der Prüfung umzukehren, verwenden Sie den logischen Operator Not.

'Objektreferenzen
Dim app As Excel.Application
If Not app Is Nothing Then
  Debug.Print "Enthält Objektreferenz"
Else
  Debug.Print "Keine Objektreferenz"
End If

Eigenschaften des Applikationsobjekts

Um auf die Eigenschaften des Applikationsobjektes zuzugreifen, verwenden Sie den Punktoperator. Beachten Sie, dass jede Eigenschaft selbst wiederum einen bestimmten Datentyp besitzt. So ist die Eigenschaft Name vom Datentyp String.

Nachfolgend eine Übersicht über wichtige Eigenschaften des Applikationsobjektes:

Methoden des Applikationsobjekts

Methoden legen ein bestimmtes Verhalten fest. Um z. B. die Applikation zu beenden, rufen Sie die Methode Quit auf. Um auf die Methoden des Applikationsobjektes zuzugreifen, verwenden Sie den Punktoperator.

Nachfolgend eine Übersicht über wichtige Methoden des Applikationsobjektes:

Beachten Sie, dass bestimmte Methoden nur ausgeführt werden, aber keinen Funktionswert (Rückgabewert) zurückliefern. Diese Methoden werden ohne Angabe von runden Klammern aufgerufen.

'Beenden der Applikation
'Kein Funktionswert, daher Aufruf ohne runde Klammern
Application.Quit

Methoden, die einen Funktionswert (Rückgabewert) zurückliefern, werden mit runden Klammern aufgerufen, sofern der Methodenaufruf in einer Variable oder einem Ausdruck verwendet wird.

'Umrechnung Zentimeter in Punkte
'Rückgabe eines Funktionswertes, daher Aufruf mit runden Klammern
Debug.Print Application.CentimetersToPoints(2.5)

Auflistungen

Auflistungen (Collections) sind Sammlungen von Objekten, wobei meistens Objekte des gleichen Datentyps in einer Sammlung liegen.

In Excel gibt es einige Objekte, die Zugriff auf weitere Objekte in Form von Auflistungen (Collections) zur Verfügung stellen. Die Bezeichnung dieser Auflistung ist immer in der Mehrzahl und meist vom Typ der Objekte, die in der jeweiligen Auflistung enthalten sind.

Arbeitsmappen sind vom Datentyp Workbook. Die Anweisung Application.Workbooks liefert die Sammlung aller offenen Arbeitsmappen.

Tabellenblätter sind vom Datentyp Worksheet. Die Anweisung Application.Worksheets liefert die Sammlung aller Tabellenblätter in der aktiven Arbeitsmappe.

Zeilen und Spalten sind vom Datentyp Row bzw. Column. Dementsprechend liefen die Anweisungen Application.Rows und Application.Columns die Sammlungen aller Zeilen bzw. Spalten des aktiven Tabellenblatts in der aktiven Arbeitsmappe.

Zellen bzw. Zellbereiche sind vom Datentyp Range. Die Anweisung Application.Cells bzw. Application.Range liefert die Sammlung aller Zellen des aktiven Tabellenblatts in der aktiven Arbeitsmappe.

Schon jetzt sei angemerkt, dass auch der Datentyp Worksheet die Eigenschaften Rows, Columns, Cells und Range kennt. Somit kann auch auf die Zeilen, Spalten, Zellen und Zellbereiche eines bestimmten Tabellenblatts zugegriffen werden.

Eine wichtige Methode jeder Auflistung ist Count, die die Anzahl der Objekte einer Auflistung zurückgibt.

'Verwendung von Count
Dim app As Excel.Application
Set app = Excel.Application
Debug.Print app.Workbooks.Count
Debug.Print app.Worksheets.Count
Debug.Print app.Rows.Count
Debug.Print app.Columns.Count

Arbeitsmappenobjekt (Workbook)

Allgemein

Eine Excel-Arbeitsmappe ist vom Datentyp Workbook. Um mit den Eigenschaften und Methoden einer Arbeitsmappe zu arbeiten, sollten Sie die gewünschte Arbeitsmappe über eine Variable vom Datentyp Workbook referenzieren.

Die Anweisung Application.Workbooks liefert eine Sammlung aller offenen Arbeitsmappen. Der Zugriff auf eine bestimmte Arbeitsmappe erfolgt über einen 1-basierten Index oder über die Dateibezeichnung.

Der Zugriff über den Index wird meist verwendet, um in einer Schleife über alle offenen Arbeitsmappen zu gehen. Im folgenden Beispiel werden die Dateibezeichnung, der Verzeichnispfad und der volle Name (Verzeichnispfad und Dateibezeichnung) aller offenen Arbeitsmappen im Direktfenster ausgegeben.

'Schleife über alle offenen Arbeitsmappen
Dim i As Integer
Dim wb As Excel.Workbook
For i = 1 To Application.Workbooks.Count
  Set wb = Application.Workbooks(i)
  Debug.Print wb.Name
  Debug.Print wb.Path
  Debug.Print wb.FullName
Next

Im folgenden Beispiel werden die Dateibezeichnung, der Verzeichnispfad und der volle Name (Verzeichnispfad und Dateibezeichnung) einer bestimmten geöffneten Arbeitsmappe im Direktfenster ausgegeben, wobei hier der Zugriff über die Dateibezeichnung erfolgt.

'Zugriff auf offene Arbeitsmappe über die Dateibezeichnung
Dim wb As Excel.Workbook
Set wb = Application.Workbooks("MeineDatei.xlsx")
Debug.Print wb.Name
Debug.Print wb.Path
Debug.Print wb.FullName

Der Zugriff auf eine bestimmte Arbeitsmappe über den Index bzw. die Dateibezeichnung ist nicht gut, da der Index von der Reihenfolge des Öffnens abhängt und die Dateibezeichnung sich ändern kann.

Besser ist der Zugriff über die beiden Eigenschaften des Applikationsobjekts, mit denen die aktive Arbeitsmappe (ActiveWorkbook) bzw. die Arbeitsmappe, in der der ausgeführte Code implementiert ist (ThisWorkbook) referenziert werden kann.

'Zugriff auf eine bestimmte Arbeitsmappe
Dim wb As Excel.Workbook
'Zugriff auf die aktive Arbeitsmappe
Set wb = Application.ActiveWorkbook
Debug.Print wb.Name
Debug.Print wb.Path
Debug.Print wb.FullName
'Zugriff auf die Arbeitsmappe, in der der Code implementiert ist.
'Diese muss nicht die aktive Arbeitmappe sein.
Set wb = Application.ThisWorkbook
Debug.Print wb.Name
Debug.Print wb.Path
Debug.Print wb.FullName

Um eine neue Arbeitsmappe zu erzeugen, verwenden Sie die Add-Methode der Workbooks-Auflistung.

'Erstellen einer neuen Arbeitsmappe
Application.Workbooks.Add
Debug.Print Application.ActiveWorkbook.Name

In obigem Beispiel ist die neue Arbeitsmappe die aktive Arbeitsmappe und kann über ActiveWorkbook referenziert werden. Da die Add-Methode eine Referenz auf die neue Arbeitsmappe zurückliefert, ist es besser, die neue Arbeitsmappe gleich über eine Variable zu referenzieren. Beachten Sie den Methodenaufruf mit runden Klammern, da wir den Funktionswert (Rückgabewert) von Add verwenden.

'Besseres Erstellen einer neuen Arbeitsmappe
Dim wb As Excel.Workbook
Set wb = Application.Workbooks.Add()
Debug.Print wb.Name

Die Add-Methode nimmt optional auch den Dateipfad zu einer Excel-Vorlage (.xltx oder .xltm) entgegen, die als Basis für die neue Arbeitsmappe dient.

'Neue Arbeitsmappe basierend auf einer Vorlage
Dim wb As Excel.Workbook
Set wb = Application.Workbooks.Add("C:\MeineVorlage.xltx")
Debug.Print wb.Name

Um eine bestehende Arbeitsmappe zu öffnen, verwenden Sie die Open-Methode der Workbooks-Auflistung.

'Öffnen einer Arbeitsmappe
Application.Workbooks.Open("C:\MeineArbeitsmappe.xlsx")
Debug.Print Application.ActiveWorkbook.Name

In obigem Beispiel ist die geöffnete Arbeitsmappe die aktive Arbeitsmappe und kann über ActiveWorkbook referenziert werden. Da die Open-Methode eine Referenz auf die geöffnete Arbeitsmappe zurückliefert, ist es besser, die geöffnete Arbeitsmappe gleich über eine Variable zu referenzieren. Beachten Sie den Methodenaufruf mit runden Klammern, da wir den Funktionswert (Rückgabewert) von Open verwenden.

'Besseres Öffnen einer Arbeitsmappe
Dim wb As Excel.Workbook
Set wb = Application.Workbooks.Open("C:\MeineArbeitsmappe.xlsx")
Debug.Print wb.Name

Eigenschaften einer Arbeitsmappe

Um auf die Eigenschaften einer Arbeitsmappe zuzugreifen, verwenden Sie den Punktoperator. Beachten Sie, dass jede Eigenschaft selbst wiederum einen bestimmten Datentyp besitzt. So ist die Eigenschaft Name vom Datentyp String.

Nachfolgend eine Übersicht über wichtige Eigenschaften einer Arbeitsmappe:

Methoden einer Arbeitsmappe

Methoden legen ein bestimmtes Verhalten fest. Um z. B. die Arbeitsmappe zu schließen, rufen Sie die Methode Close auf. Um auf die Methoden einer Arbeitsmappe zuzugreifen, verwenden Sie den Punktoperator.

Nachfolgend eine Übersicht über wichtige Methoden einer Arbeitsmappe:

Beachten Sie, dass bestimmte Methoden nur ausgeführt werden, aber keinen Funktionswert (Rückgabewert) zurückliefern. Diese Methoden werden ohne Angabe von runden Klammern aufgerufen.

'Schließen der aktiven Arbeitsmappe
'Änderungen werden gespeichert
Dim wb As Excel.Workbook
Set wb = Application.ActiveSheet
wb.Close True

Tabellenblattobjekt (Worksheet)

Allgemein

Ein Excel-Tabellenblatt ist vom Datentyp Worksheet. Um mit den Eigenschaften und Methoden eines Tabellenblatts zu arbeiten, sollten Sie das gewünschte Tabellenblatt über eine Variable vom Datentyp Worksheet referenzieren.

Die Anweisung Application.Worksheets liefert eine Sammlung aller Tabellenblätter der aktiven Arbeitsmappe. Auch das Arbeitsmappenobjekt kennt die Anweisung Worksheets. Somit können Sie auf die Tabellenblätter einer bestimmten Arbeitsmappe zugreifen. Der Zugriff auf ein bestimmtes Tabellenblatt erfolgt über einen 1-basierten Index oder über die Tabellenblattbezeichnung.

Der Zugriff über den Index wird meist verwendet, um in einer Schleife über alle Tabellenblätter zu gehen. Im folgenden Beispiel werden die Tabellenblattbezeichnung und die Sichtbar-Eigenschaft aller Tabellenblätter der aktiven Arbeitsmappe im Direktfenster ausgegeben.

'Schleife über alle Tabellenblätter der aktiven Arbeitsmappe
Dim i As Integer
Dim ws As Excel.Worksheet
For i = 1 To Application.Worksheets.Count
  Set ws = Application.Worksheets(i)
  Debug.Print ws.Name
  Debug.Print ws.Visible
Next

Im folgenden Beispiel wird die Dateibezeichnung der aktiven Arbeitsmappe und die Tabellenblattbezeichnung und die Sichtbar-Eigenschaft aller Tabellenblätter der aktiven Arbeitsmappe im Direktfenster ausgegeben.

'Schleife über alle Tabellenblätter der aktiven Arbeitsmappe
Dim i As Integer
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Debug.Print wb.Name
For i = 1 To wb.Worksheets.Count
  Set ws = wb.Worksheets(i)
  Debug.Print ws.Name
  Debug.Print ws.Visible
Next

Im folgenden Beispiel wird die Sichtbar-Eigenschaft eines bestimmten Tabellenblatts im Direktfenster ausgegeben, wobei der Zugriff über die Tabellenblattbezeichnung erfolgt.

'Zugriff auf ein Tabellenblatt über die Bezeichnung
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets("Tabelle1")
Debug.Print ws.Visible

Um ein neues Tabellenblatt zu erzeugen, verwenden Sie die Add-Methode der Worksheets-Auflistung. Durch zusätzliche Argumente können Sie steuern, an welcher Position das neue Tabellenblatt eingefügt wird.

Im folgenden Beispiel wird ein neues Tabellenblatt am Beginn eingefügt und der Name des neuen Tabellenblatts im Direktfenster ausgegeben.

'Erstellen eines neuen Tabellenblatt am Beginn
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets.Add()
Debug.Print ws.Name

über die Argumente Before bzw. After geben Sie jenes Tabellenblatt an, vor bzw. nach welchem das neue Tabellenblatt eingefügt. wird. Um auf das letzte Tabellenblatt zuzugreifen verwenden Sie den indexbasierten Zugriff der Worksheets-Anweisung und geben als Index die Anweisung Worksheets.Count an.

Im folgenden Beispiel wird ein neues Tabellenblatt nach dem letzten Tabellenblatt eingefügt und der Name des neuen Tabellenblatts im Direktfenster ausgegeben.

'Erstellen eines neuen Tabellenblatt am Ende
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets.Add(, wb.Worksheets(wb.Worksheets.Count))
Debug.Print ws.Name

Um ein Tabellenblatt zu löschen, rufen Sie die Delete-Methode des Tabellenblattobjekts auf. Beim Ausführen der Delete-Methode wird der User um Bestätigung des Löschvorgangs gebeten.

'Löschen des ersten Tabellenblatts
Dim wb As Excel.Workbook
Set wb = Application.ActiveWorkbook
wb.Worksheets(1).Delete

Im folgenden Beispiel werden alle Tabellenblätter bis auf das erste gelöscht. Dazu verwenden Sie eine Schleife, die läuft, bis die Anzahl der Tabellenblätter gleich 1 ist.

'Löschen aller Tabellenblätter bis auf das erste
Dim wb As Excel.Workbook
Set wb = Application.ActiveWorkbook
Do Until wb.Worksheets.Count = 1
  wb.Worksheets(2).Delete
Loop

Um die mehrmalige Löschbestätigungsmeldung zu unterdrücken, können Sie vor der Schleife die DisplayAlerts-Eigenschaft des Applikationsobjekts auf False setzen. Vergessen Sie nicht, nach der Schleife die DisplayAlerts-Eigenschaft auf True zu setzen. Generell sollten Sie derartige Applikationseinstellungen erst vornehmen, wenn Sie über das Behandeln von Laufzeitfehlern Bescheid wissen, um beim Auftreten eines Fehlers die geänderten Applikationseinstellungen wieder zurückzusetzen.

'Löschen aller Tabellenblätter bis auf das erste
Dim wb As Excel.Workbook
Set wb = Application.ActiveWorkbook
Application.DisplayAlerts = False
Do Until wb.Worksheets.Count = 1
  wb.Worksheets(2).Delete
Loop
Application.DisplayAlerts = True

Eigenschaften eines Tabellenblatts

Um auf die Eigenschaften eines Tabellenblatts zuzugreifen, verwenden Sie den Punktoperator. Beachten Sie, dass jede Eigenschaft selbst wiederum einen bestimmten Datentyp besitzt. So ist die Eigenschaft Name vom Datentyp String.

Nachfolgend eine Übersicht über wichtige Eigenschaften eines Tabellenblatts:

Methoden eines Tabellenblatts

Methoden legen ein bestimmtes Verhalten fest. Um z. B. ein Tabellenblatt zu löschen, rufen Sie die Methode Delete auf. Um auf die Methoden eines Tabellenblatts zuzugreifen, verwenden Sie den Punktoperator.

Nachfolgend eine Übersicht über wichtige Methoden eines Tabellenblatts:

Beachten Sie, dass bestimmte Methoden nur ausgeführt werden, aber keinen Funktionswert (Rückgabewert) zurückliefern. Diese Methoden werden ohne Angabe von runden Klammern aufgerufen.

Im folgenden Beispiel wird das erste Tabellenblatt an die letzte Position verschoben.

'Verschieben des ersten Tabellenblattes an die letzte Position
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets(1)
ws.Move , wb.Worksheets(wb.Worksheets.Count)

Im folgenden Beispiel wird das letzte Tabellenblatt an die erste Position verschoben.

'Verschieben des letzten Tabellenblattes an die erste Position
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets(wb.Worksheets.Count)
ws.Move wb.Worksheets(1)

Bereich (Range)

Allgemein

Ein Bereich ist vom Datentyp Range. Ein Bereich kann eine oder mehrere Zellen umfassen, kann aber auch eine oder mehrere Zeilen bzw. Spalten umfassen. Deshalb gibt es eine Unzahl von Anweisungen, die ein Rangeobjekt zurückliefern.

Die folgenden Anweisungen liefern jeweils einen Bereich zurück und können zum Teil auf das Applikationsobjekt oder ein bestimmtes Tabellenblattobjekt aufgerufen werden. Wenn Sie die Anweisungen alleinstehend aufrufen, so beziehen diese sich immer auf das aktive Tabellenblatt der aktiven Arbeitsmappe.

Beachten Sie, dass es sich beim Datentyp Range um einen Objektdatentyp handelt. Wird der gewünschte Bereich einer Variablen zugewiesen, so müssen Sie das Schüsselwort Set verwenden.

'Referenzieren eines Bereichs über eine Variable
Dim r As Excel.Range
Set r = Range("A1")

Um den Inhalt eines Bereichs zu schreiben, verwenden Sie die Value-Eigenschaft. Die Value-Eigenschaft ist die Standardeigenschaft. Ist aus dem Anweisungskontext klar, dass Sie den Inhalt lesen oder schreiben wollen, so können Sie den Aufruf von Value weglassen.

'Lesen und Schreiben des Inhalts eines Bereichs
Dim r As Excel.Range
Set r = Range("A1")
'Ausgabe des Inhalts der Zelle A1 im Direktfenster mit Value
Debug.Print r.Value
'Ausgabe des Inhalts der Zelle A1 im Direktfenster ohne Value
Debug.Print r
'Schreiben der Zahl 10 in die Zelle A1 mit Value
r.Value = 10
'Schreiben der Zahl 10 in die Zelle A1 ohne Value
r = 10

Umfasst Ihr Bereich mehrere Zellen, so können Sie mit einer Anweisung den gleichen Inhalt in die Zellen des Bereichs schreiben. Beim Auslesen erhalten Sie eine Fehlermeldung, da ein mehrzelliger Bereich keinen auslesbaren Inhalt besitzt. Um auf die erste Zelle dieses Bereichs zuzugreifen, müssen Sie auf den Bereich wiederrum die Cells-Anweisung aufrufen.

'Lesen und Schreiben des Inhalts eines mehrzelligen Bereichs
Dim r As Excel.Range
Set r = Range("A1:C3")
'Fehler, beim Ausführen der nächsten beiden Anweisungen
Debug.Print r.Value
Debug.Print r
'Ausgabe des Inhalts der ersten Zelle im Bereich im Direktfenster mit Value
Debug.Print r.Cells(1,1).Value
'Ausgabe des Inhalts der ersten Zelle im Bereich im Direktfenster ohne Value
Debug.Print r.Cells(1,1)
'Schreiben der Zahl 10 in alle Zellen des Bereichs mit Value
r.Value = 10
'Schreiben der Zahl 10 in alle Zellen des Bereichs ohne Value
r = 10

Eigenschaften eines Bereichs

Um auf die Eigenschaften eines Bereichs zuzugreifen, verwenden Sie den Punktoperator. Beachten Sie, dass jede Eigenschaft selbst wiederum einen bestimmten Datentyp besitzt. So ist die Eigenschaft Address vom Datentyp String.

Nachfolgend eine Übersicht über wichtige Eigenschaften eines Bereichs:

Mit der Anweisung Row ermitteln Sie die Zeilennummer der ersten Zeile eines Bereichs. Mit der Anweisung Column ermitteln Sie die Spaltennummer der ersten Spalte eines Bereichs, wobei 1 der Spalte A, 2 der Spalte B, usw. entspricht.

'Ausgabe der Zeilen und Spaltennummer der ersten Zeile bzw. Spalte eines Bereichs im Ausgabefenster.
Dim r As Excel.Range
Set r = Range("C2")
Debug.Print r.Row
Debug.Print r.Column

Im Ausgabefenster sollten die Zahlen 2 und 3 ausgegeben werden.

Mit der Anweisungen Cells.Count lässst sich sehr leicht feststellen, ob ein Bereich ein- oder mehrzellig ist.

'Prüfen, ob der User einen ein- oder mehrzelligen Bereich ausgewählt hat.
Dim r As Excel.Range
Set r = Selection
If r.Cells.Count = 1 Then
  Debug.Print "Eine Zelle ausgewählt."
Else
  Debug.Print "Mehrere Zellen ausgewählt."
End If

Mit der Eigenschaft Offset können Sie ausgehend von einem Bereich weitere Bereiche mit einem Zeilen- bzw. Spaltenversatz ansprechen.

Im folgenden Beispiel geben wir ausgehend von der Zelle A1 die Inhalte der Zellen A2, B1 und B2 aus.

'Verwenden von Offset.
Dim r As Excel.Range
Set r = Range("A1")
Debug.Print r.Offset(1, 0) 'Ausgabe von A2
Debug.Print r.Offset(0, 1) 'Ausgabe von B1
Debug.Print r.Offset(1, 1) 'Ausgabe von B2

Methoden eines Bereichs

Methoden legen ein bestimmtes Verhalten fest. Um z. B. einen Bereichsinhalt zu löschen, rufen Sie die Methode ClearContents auf. Um auf die Methoden eines Bereichs zuzugreifen, verwenden Sie den Punktoperator.

Nachfolgend eine Übersicht über wichtige Methoden eines Bereichs:

Beachten Sie, dass bestimmte Methoden nur ausgeführt werden, aber keinen Funktionswert (Rückgabewert) zurückliefern. Diese Methoden werden ohne Angabe von runden Klammern aufgerufen.

Im folgenden Beispiel werden die Zeilen und Spalten des Bereichs, den der User ausgewählt hat auf die optimale Höhe bzw. Breite gesetzt.

'Optimale Zeilenhöhe und Spaltenbreite
Dim r As Excel.Range
Set r = Selection
r.Rows.AutoFit
r.Columns.AutoFit

Anweisung With...End With

Beim Zugriff auf die Eigenschaften und Methoden eines Objekts wird der Punktoperator verwendet. Wollen Sie mehrere Eigenschaften setzen oder Methoden aufrufen, so ist die wiederholte Angabe der Objektvariable nicht sehr komfortabel.

'Wiederholte Angabe der Objektvariable
Dim ws As Excel.Worksheet
Set ws = Application.ActiveSheet
ws.Name = "MeineTabelle"
ws.Visible = True
ws.Activate
ws.Cells(1, 1).Select

Erleichterung bietet die With...End Width-Anweisung. Alle Anweisungen, die mit dem Punktoperator beginnen, beziehen sich auf die in der With-Anweisung angegebenen Objektvariable.

'Erleichterung mit With...End With
Dim ws As Excel.Worksheet
Set ws = Application.ActiveSheet
With ws
  .Name = "MeineTabelle"
  .Visible = True
  .Activate
  .Cells(1, 1).Select
End With

Die With...End With-Anweisung kann auch verschachtelt werden. Im folgenden Beispiel bezieht sich die erste With-Anweisung auf die aktive Zelle, die innere With-Anweisung bezieht sich auf die Font-Eigenschaft der Zelle, mit der Sie weitere Einstellungen zur Schriftformatierung treffen können.

'Verschachtelung von With...End With
Dim r As Excel.Range
Set r = Application.ActiveCell
With r
  .Select
  .Value = 10
  With .Font
    .Bold = True
    .Color = vbRed
    .Size = 20
  End With
End With

Download Codebeispiele

Die gezeigten Codebeispiele können Sie als xlsm-Datei herunterladen.

Seitenanfang   Inhaltsverzeichnis