Lawrence benötigt eine Möglichkeit, die Nummer eines Arbeitsblatts zu bestimmen, auch wenn das Arbeitsblatt umbenannt wurde. Wenn ein Arbeitsblatt beispielsweise Sheet11 heißt, ist es leicht zu erkennen, dass es sich um Blatt 11 handelt. Wenn er das Blatt in Januar umbenennt, muss Lawrence noch wissen, ob es sich um Blatt 11 handelt.

Die Lösung für dieses Problem erfolgt am besten mit einer benutzerdefinierten Funktion (einem Makro). In Wirklichkeit gibt es zwei Zahlen, die das Makro für jedes Arbeitsblatt zurückgeben könnte. Die erste ist die Indexnummer für das Arbeitsblatt. Diese Nummer repräsentiert den Index des Arbeitsblattobjekts des Arbeitsblatts in der Arbeitsblattsammlung. Dieser Wert kann von einem Makro zurückgegeben werden, das dem folgenden ähnelt:

Function SheetNumber1(shtname As String)

Dim sht As Worksheet

Application.Volatile     For Each sht In ThisWorkbook.Worksheets          If LCase(sht.Name) = LCase(shtname) Then               SheetNumber1 = sht.Index               Exit Function          End If     Next     SheetNumber1 = -1 End Function

Diese Funktion gibt bei Verwendung in einem Arbeitsblatt die Indexnummer eines Arbeitsblatts zurück, dessen Name an die Funktion übergeben wird. Wenn der Name, der an die Funktion übergeben wird, in der Arbeitsblattsammlung nicht vorhanden ist, gibt die Funktion den Wert -1 zurück. Beispielsweise würde Folgendes, das in einer Zelle verwendet wird, den Indexwert für das Arbeitsblatt mit dem Namen „Januar“ in der Sammlung zurückgeben:

=SheetNumber("January")

Das Problem bei diesem Ansatz besteht darin, dass sich die Reihenfolge der Arbeitsblattobjekte in der Arbeitsblattsammlung im Laufe der Zeit ändern kann. Daher können Sie nicht immer davon ausgehen, dass das elfte Blatt in der Sammlung das Blatt ist, das ursprünglich Blatt 11 war.

Eine konsistentere Methode zum Ermitteln des ursprünglichen Namens für ein Arbeitsblatt (unabhängig davon, wie es umbenannt wird) besteht darin, das zu verwenden, was Visual Basic als „Codename“ des Arbeitsblatts bezeichnet. Dies ist eine Eigenschaft des Arbeitsblatts und kann auf folgende Weise bestimmt werden:

Function SheetNumber2(shtname As String)

Dim sht As Worksheet     Dim sTemp As String

Application.Volatile     For Each sht In ThisWorkbook.Worksheets          If LCase(sht.Name) = LCase(shtname) Then               sTemp = sht.CodeName               SheetNumber2 = Val(Mid(sTemp, 6, 4))

Exit Function          End If     Next     SheetNumber2 = -1 End Function

Die CodeName-Eigenschaft ist in einem Makro schreibgeschützt. Es wird zum Zeitpunkt der Erstellung des Arbeitsblatts zugewiesen, kann jedoch im Visual Basic-Editor manuell geändert werden. Der Codename ist immer eine Zeichenfolge, die den allerersten Namen darstellt, der auf das Arbeitsblatt angewendet wurde. Es handelt sich also um „Sheet11“. Sobald der Codename festgelegt ist, bleibt das Arbeitsblatt auch dann stabil, wenn es umbenannt wird (z. B. in „Januar“) („Sheet11“).

Im Makrobeispiel (SheetNumber2) wird die CodeName-Eigenschaft der Variablen sTemp zugewiesen. Dies wird meistens so etwas wie „Sheet3“ oder „Sheet11“ sein. Das Makro erfasst dann den numerischen Wert von allem, was mit dem sechsten Zeichen beginnt (direkt nach „Blatt“). Dies ist der Wert, der von der Funktion zurückgegeben wird.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (11103) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tippes für die ältere Menüoberfläche von Excel finden Sie hier:

Bestimmen der Nummer eines Arbeitsblatts.