VBA-Blätter – Der ultimative Leitfaden

Dies ist der ultimative Leitfaden für die Arbeit mit Excel-Blättern bzw. -Arbeitsblättern in VBA.

Am Ende dieses Leitfadens haben wir einen Spickzettel mit gängigen Befehlen für die Arbeit mit Arbeitsblättern erstellt.

Sheets im Vergleich zu Worksheets

Es gibt zwei Möglichkeiten, auf Blättern mit VBA zu verweisen. Die erste Möglichkeit ist die Verwendung des Sheets-Objekts:

Sheets("Tabelle1").Activate

Die andere ist mit dem Worksheets-Objekt:

Worksheets("Tabelle1").Activate

in 99 % der Fälle sind diese beiden Objekte identisch. Wenn Sie im Internet nach VBA-Codebeispielen gesucht haben, haben Sie wahrscheinlich beide Objekte schon einmal gesehen.

Hier ist der Unterschied: Die Sheets-Sammlung enthält Worksheets UND Chart Sheets.

Verwenden Sie also Sheets, wenn Sie normale Arbeitsblätter UND Diagrammblätter einschließen wollen. Verwenden Sie Worksheets, wenn Sie Diagrammblätter ausschließen wollen. Im weiteren Verlauf dieses Leitfadens werden wir Sheets und Worksheets synonym verwenden.

Auf Blätter verweisen

Es gibt verschiedene Möglichkeiten, auf Arbeitsblätter zu verweisen:

• ActiveSheet
• Blattregisterkartenname
• Blattindexnummer
• Blatt-Codename

ActiveSheet

Das ActiveSheet ist das Blatt, das gerade aktiv ist. Mit anderen Worten, wenn Sie Ihren Code anhalten und auf Excel schauen, ist es das Blatt, das sichtbar ist. Im folgenden Codebeispiel wird eine Meldungsbox mit dem Namen vom ActiveSheet angezeigt.

MsgBox ActiveSheet.Name

Blattname

Wahrscheinlich sind Sie mit de Verweisen auf Blättern durch ihren Registerkartennamen am meisten vertraut:

Sheets("Registerkartenname").Activate

Dies ist der Name des Blattes, der für Excel-Benutzer sichtbar ist. Geben Sie ihn in das Sheets-Objekt als von Anführungszeichen umgebenen Text ein.

Blattindexnummer

Die Blattindexnummer gibt die Position des Blattes in der Arbeitsmappe an. 1 ist das erste Blatt. 2 ist das zweite Blatt usw.:

Sheets(1).Activate

Blattindexnummer – Letztes Blatt in der Arbeitsmappe

Um auf das letzte Blatt in der Arbeitsmappe zu verweisen, verwenden Sie Sheets.Count zum  Ermitteln der letzten Indexnummer und Aktivieren des entsprechenden Blattes:

Sheets(Sheets.Count).Activate

Blatt-Codename

Der Blatt-Codename ist sein Objektname in VBA:

CodeName.Activate

Auf Blätter in anderen Arbeitsmappen verweisen

Es ist auch einfach, auf Arbeitsblätter in anderen Arbeitsmappen zu verweisen. Dazu müssen Sie das Workbooks-Objekt verwenden:

Workbooks("VBA_Beispiele.xlsm").Worksheets("Tabelle1").Activate

Wichtig: Die Arbeitsmappe muss geöffnet sein, bevor Sie auf ihre Blätter verweisen können.

Blatt aktivieren oder auswählen (Activate vs. Select)

In einem anderen Artikel besprechen wir alles über das Aktivieren und Auswählen von Blättern. Die Kurzversion lautet wie folgt:

Wenn Sie ein Blatt aktivieren, wird es zum ActiveSheet. Dies ist das Blatt, das Sie sehen, wenn Sie Ihr Excel-Programm betrachten. Es kann jeweils nur ein Blatt aktiviert werden.

Ein Blatt aktivieren

Sheets("Tabelle1").Activate

Wenn Sie ein Blatt auswählen, wird es auch zum ActiveSheet. Sie können jedoch auch mehrere Blätter auf einmal auswählen. Wenn mehrere Blätter gleichzeitig ausgewählt sind, ist das „oberste“ Blatt das ActiveSheet. Sie können jedoch das ActiveSheet innerhalb der ausgewählten Blätter umschalten.

Ein Blatt auswählen

Worksheets("Tabelle1").Select

Mehrere Blätter auswählen

Verwenden Sie ein Array, um mehrere Blätter auf einmal auszuwählen:

Worksheets(Array("Tabelle2", "Tabelle3")).Select

Arbeitsblattvariable

Wenn Sie ein Arbeitsblatt einer Objektvariablen zuweisen, können Sie dieses über seinen Variablennamen referenzieren. Das spart eine Menge Tipparbeit und macht Ihren Code einfacher zu lesen. Es gibt noch viele andere Gründe, warum Sie Variablen verwenden sollten.

So deklarieren Sie eine Arbeitsblattvariable:

Dim ws as Worksheet

Weisen Sie ein Arbeitsblatt einer Variablen zu:

Set ws = Worksheets("Tabelle1")

Jetzt können Sie auf die Arbeitsblattvariable in Ihrem Code verweisen:

ws.Activate

Alle Arbeitsblätter in der Arbeitsmappe durchlaufen

Arbeitsblattvariablen sind nützlich, wenn Sie alle Arbeitsblätter in einer Arbeitsmappe in einer Schleife durchlaufen wollen. Am einfachsten geht das folgendermaßen:

Dim ws as Worksheet

For Each ws in Worksheets
  MsgBox ws.name
Next ws

Dieser Code durchläuft alle Arbeitsblätter in der Arbeitsmappe in einer Schleife und zeigt jeden Arbeitsblattnamen in einer Meldungsbox an. Das Durchlaufen aller Arbeitsblätter in einer Arbeitsmappe ist sehr nützlich, wenn mehrere Arbeitsblätter gleichzeitig gesperrt/entsperrt oder ein- und ausgeblendet werden sollen.

Arbeitsblattschutz

Schützen der Arbeitsmappe

Der Arbeitsmappenschutz sperrt die Arbeitsmappe vor strukturellen Änderungen wie dem Hinzufügen, Löschen, Verschieben oder Ausblenden von Arbeitsblättern.

Sie können den Arbeitsmappenschutz mit VBA aktivieren:

ActiveWorkbook.Protect Password:="Kennwort"

oder den Arbeitsmappenschutz deaktivieren:

ActiveWorkbook.UnProtect Password:="Kennwort"

Hinweis: Sie können den Schutz auch ohne Kennwort aktivieren/deaktivieren, indem Sie das Argument Kennwort weglassen:

ActiveWorkbook.Protect

Arbeitsblattschutz

Der Schutz auf Arbeitsblattebene verhindert Änderungen an einzelnen Arbeitsblättern.

Arbeitsblatt schützen

Worksheets("Tabelle1").Protect "Kennwort"

Arbeitsblattschutz aufheben

Worksheets("Tabelle1").Unprotect "Kennwort"

Es gibt eine Reihe von Optionen für den Schutz von Arbeitsblättern (Formatierungsänderungen zulassen, dem Benutzer das Einfügen von Zeilen erlauben usw.). Wir empfehlen die Verwendung des Makro-Aufzeichners, um die gewünschten Einstellungen aufzuzeichnen.

Wir besprechen den Schutz von Arbeitsblättern hier im Detail.

Die Arbeitsblatteigenschaft Visible

Sie wissen vielleicht schon, dass Arbeitsblätter ausgeblendet werden können:

Es gibt eigentlich drei Einstellungen für die Sichtbarkeit von Arbeitsblättern: Visible, Hidden und VeryHidden. Ausgeblendete Blätter können von jedem normalen Excel-Benutzer eingeblendet werden, indem er mit der rechten Maustaste in den Arbeitsblattregisterkartenbereich klickt (siehe oben). Sehr ausgeblendete Blätter (VeryHidden) können nur mit dem VBA-Code oder im VBA-Editor eingeblendet werden. Verwenden Sie die folgenden Codebeispiele, um Arbeitsblätter ein- und auszublenden:

Arbeitsblatt wieder einblenden

Worksheets("Tabelle1").Visible = xlSheetVisible

Arbeitsblatt ausblenden

Worksheets("Tabelle1").visible = xlSheetHidden

Arbeitsblatt sehr ausblenden

Worksheets("Tabelle1").Visible = xlSheetVeryHidden

Ereignisse auf Arbeitsblattebene

Ereignisse sind Auslöser, die die Ausführung von „Ereignisprozeduren“ bewirken können. Sie können beispielsweise dafür sorgen, dass ein Code jedes Mal ausgeführt wird, wenn eine Zelle auf einem Arbeitsblatt geändert wird oder ein Arbeitsblatt aktiviert wird.

Arbeitsblattereignisprozeduren müssen in einem Arbeitsblattmodul platziert werden:

Es gibt zahlreiche Arbeitsblattereignisse. Um eine vollständige Liste zu sehen, gehen Sie zu einem Arbeitsblattmodul und wählen Sie „Arbeitsblatt“ aus der ersten Dropdown-Liste. Wählen Sie dann eine Ereignisprozedur aus der zweiten Dropdown-Liste, um sie in das Modul einzufügen.

 

Arbeitsblattaktivierungsereignis

Arbeitsblattaktivierungsereignisse werden jedes Mal ausgeführt, wenn das Arbeitsblatt geöffnet wird.

Private Sub Worksheet_Activate()
  Range("A1").Select
End Sub

Dieser Code wählt bei jedem Öffnen des Arbeitsblatts die Zelle A1 aus (und setzt den Ansichtsbereich auf die obere linke Seite des Arbeitsblatts zurück).

Arbeitsblattänderungsereignis

Arbeitsblattänderungsereignisse werden immer dann ausgeführt, wenn ein Zellenwert auf dem Arbeitsblatt geändert wird. Lesen Sie unser Tutorial über Arbeitsblattänderungsereignisse für weitere Informationen.

Arbeitsblatt Spickzettel

Nachfolgend finden Sie ein Spickzettel mit allgemeinen Codebeispielen für die Arbeit mit Arbeitsblättern in VBA.

VBA Worksheets Cheatsheet

VBA worksheets Cheatsheet

Description	Code Example
Referencing and Activating Sheets
Tab Name	Sheets("Input").Activate
VBA Code Name	Sheet1.Activate
Index Position	Sheets(1).Activate
Select Sheet
Select Sheet	Sheets("Input").Select
Set to Variable	Dim ws as Worksheet Set ws = ActiveSheet
Name / Rename	ActiveSheet.Name = "NewName"
Next Sheet	ActiveSheet.Next.Activate
Loop Through all Sheets	Dim ws as Worksheet For each ws in Worksheets Msgbox ws.name Next ws
Loop Through Selected Sheets	Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets MsgBox ws.Name Next ws
Get ActiveSheet	MsgBox ActiveSheet.Name
Add Sheet	Sheets.Add
Add Sheet and Name	Sheets.Add.Name = "NewSheet"
Add Sheet With Name From Cell	Sheets.Add.Name = range("a3").value
Add Sheet After Another	Sheets.Add After:=Sheets("Input")
Add Sheet After and Name	Sheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Add Sheet Before and Name	Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
Add Sheet to End of Workbook	Sheets.Add After:=Sheets(Sheets.Count)
Add Sheet to Beginning of Workbook	Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
Add Sheet to Variable	Dim ws As Worksheet Set ws = Sheets.Add
Copy Worksheets
Move Sheet to End of Workbook	Sheets("Sheet1").Move After:=Sheets(Sheets.Count)
To New Workbook	Sheets("Sheet1").Copy
Selected Sheets To New Workbook	ActiveWindow.SelectedSheets.Copy
Before Another Sheet	Sheets("Sheet1").Copy Before:=Sheets("Sheet2")
Before First Sheet	Sheets("Sheet1").Copy Before:=Sheets(1)
After Last Sheet	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Copy and Name	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "LastSheet"
Copy and Name From Cell Value	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Range("A1").Value
To Another Workbook	Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
Hide / Unhide Sheets
Hide Sheet	Sheets("Sheet1").visible = False or Sheets("Sheet1").visible = xlSheetHidden
Unhide Sheet	Sheets("Sheet1").Visible = True or Sheets("Sheet1").Visible = xlSheetVisible
Very Hide Sheet	Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete or Clear Sheets
Delete Sheet	Sheets("Sheet1").Delete
Delete Sheet (Error Handling)	On Error Resume Next Sheets("Sheet1").Delete On Error GoTo 0
Delete Sheet (No Prompt)	Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True
Clear Sheet	Sheets("Sheet1").Cells.Clear
Clear Sheet Contents Only	Sheets("Sheet1").Cells.ClearContents
Clear Sheet UsedRange	Sheets("Sheet1").UsedRange.Clear
Protect or Unprotect Sheets
Unprotect (No Password)	Sheets("Sheet1").Unprotect
Unprotect (Password)	Sheets("Sheet1").Unprotect "Password"
Protect (No Password)	Sheets("Sheet1").Protect
Protect (Password)	Sheets("Sheet1").Protect "Password"
Protect but Allow VBA Access	Sheets("Sheet1").Protect UserInterfaceOnly:=True
Unprotect All Sheets	Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect "password" Next ws