[xllimit7]  Maximale Anzahl Namen pro Arbeitsmappe

Laut Spezifikation von Microsoft können so viele Namen pro Arbeitsmappe definiert werden wie Speicher verfügbar ist. Ich bin der Meinung, dass diese Information falsch ist. Anhand mehrerer Tests habe ich herausgefunden, dass die maximale Anzahl Namen in einer Arbeitsmappe bei exakt 32'704 liegt. Ich konnte bei jedem Test genau maximal 32'704 Namen verwenden. Mehr als 32'704 Namen führten in Microsoft Excel immer auf irgend eine Art und Weise zu einem Problem. Je nach Test äusserte sich das entstandene Problem unterschiedlich. Hier ein Beispiel einer typischen Auswirkung bei Überschreitung der Limitation.

Die Limitation und ihre Auswirkungen
Ich konnte in einer Arbeitsmappe anhand einer kleinen VBA-Prozedur mehr als 32'704 Namen definieren. Beim Anlegen des 32'705-ten Namens sowie allen darauffolgenden erschien der Laufzeitfehler 1004. Da die Prozedur keine Fehlerbehandlungsroutine enthielt (sprich kein On Error Goto <ErrorHandler>), wurde wegen dem Laufzeitfehler die Programmausführung unterbrochen. Erstaunlich war allerdings, dass nach Schliessen des Fehlermeldungsdialoges über die "Testen"-Schaltfläche die Prozedur weiter ausgeführt werden konnte (also ohne Behebung der Fehlerquelle). Noch erstaunlicher war, dass der Name trotz Fehlermeldung erstellt wurde. Die Arbeitsmappe mit 32'705 enthaltenen Namen konnte problemlos gespeichert werden, was allerdings eine ganze Weile gedauert hat. Schliessen war auch kein Problem. Beim Öffnen der Mappe kam dann die Überraschung: Als die Öffnen-Fortschrittsanzeige bei ca. 15 % angelangt war, wurde der Vorgang einfach abgebrochen. Ohne Fehlermeldung und ohne einen Hinweis zum aktuellen Problem. Eine Analyse der xls-Datei bestätigte, dass in der Datei tatsächlich 32'705 Namen gespeichert waren.

VBA-Beispiel 1
Mit dieser Prozedur werden die Namen anhand der Name-Eigenschaft von Cells (Objekttyp Range) generiert:

Sub CreateRangeNames()
  Dim intCounter As Integer
  For intCounter = 1 To 32767
    ActiveSheet.Cells(intCounter, 1).Name = "Zellname" & CStr(intCounter)
  Next intCounter
End Sub

VBA-Beispiel 2
Der folgende VBA-Code generiert die Namen anhand der Add-Methode der Names-Auflistung:

Sub CreateBookNames()
  Dim intCounter As Integer
  For intCounter = 1 To 32767
    ActiveWorkbook.Names.Add Name:="Zellname" & CStr(intCounter), _
      RefersToR1C1:="=Tabelle1!R" & CStr(intCounter) & "C1"
  Next intCounter
End Sub

Bei beiden Prozeduren tritt beim Erstellen des 32'705-ten Namens der Laufzeitfehler 1004 auf.

Anmerkungen zu den VBA-Beispielen
- Die Ausführung des VBA-Codes dauert ungewöhnlich lange. Je nach Rechner-Geschwindigkeit können es ohne weiteres mehrere Minuten sein.
- Die Prozedur von VBA-Beispiel 1 ist geringfügig schneller als diejenige von VBA-Beispiel 2.
- Bevor Sie die Prozedur starten, sollten Sie alle Arbeitsmappen mit vielen Namen schliessen. Dies beschleunigt die Ausführungsgeschwindigkeit erheblich. Warum das so ist, lesen Sie unter 'Dauer zum Erstellen von weiteren Namen'.
- Im manuellen Berechnungsmodus werden Namen generell schneller angelegt. Um die Ausführungsdauer der Prozedur zu verkürzen, können Sie daher

  Application.Calculation = xlCalculationManual

am Anfang der Prozedur und

  Application.Calculation = xlCalculationAutomatic

am Schluss der Prozedur einfügen. Das bringt ca. 20 % schnelleren Code.

Probleme beim Benutzen sehr vieler Namen
Auch wenn man an sich grosszügig mit Namen umgehen kann, sollte man folgende Punkte unbedingt berücksichtigen:

1. Verhalten/Reaktionszeit des Namenfeldes
Wenn Sie auf die Schaltfläche für das Aufklappen der Auswahlliste im Namenfeld klicken, werden die Namen in die Liste gefüllt und schliesslich angezeigt.

Abbildung: Das Namenfeld

Normalerweise erscheint die Auswahlliste unmittelbar nach dem Klicken der Schaltfläche. Bei sehr vielen Namen jedoch verzögert sich das Einblenden der Liste, da die Namen zuerst in die Liste eingefüllt werden müssen. Zudem nimmt die benötigte Dauer zum Aufbereiten der Auswahlliste überproportional zu. Auf meinem Testrechner (300 MHz CPU) vergehen bei 5'000 Namen zwischen Klicken der Schaltfläche und Erscheinen der Auswahlliste ungefähr 5 Sekunden. Bei 10'000 Namen, also der doppelten Anzahl, vergehen bereits 22 Sekunden (und nicht etwa 10 Sekunden). Bei 30'000 Namen dauert das Öffnen der Auswahlliste selbst auf einem Computer mit einem 1 GHz Prozessor etwa 1 1/2 Minuten.

Unbedingt zu beachten ist, dass die gleiche Verzögerung auch dann auftritt, wenn Sie lediglich in das Eingabefeld des Namensfeldes klicken. Microsoft Excel benötigt die oben erwähnte Zeit, bis das Namenfeld bereit ist. Während dieser Zeit beträgt die Prozessorauslastung 100 %, und im Windows Task-Manager wird der Status der Anwendung mit "Keine Rückmeldung" angegeben.

Das Öffnen des Dialogfensters zum Definieren/Festlegen von Namen (in Microsoft Excel 97 ist es mit "Namen festlegen" angeschrieben) dauert dagegen auch bei sehr vielen Namen nur sehr kurz (ca. 1 Sekunde).

Abbildung: Dialogfenster "Namen festlegen"

2. Schnell zunehmende Dateigrösse
Durch die Art und Weise, wie Microsoft Excel Namen in einer Arbeitsmappendatei abgelegt, wird die xls-Datei unerwartet schnell grösser. Eine Mappe, die viele Namen enthält aber ansonsten leer ist, kann ohne weiteres eine Dateigrösse von 1 MB besitzen. Bei sehr vielen Namen ist eine Dateigrösse von 3 MB und mehr schnell erreicht.

3. Lange Dauer zum Öffnen und Speichern einer Arbeitsmappe
Das Öffnen und Speichern einer Mappe, die sehr viele Namen enthält, dauert ein Vielfaches länger als bei einer Mappe ohne oder mit nur wenigen Namen. Zudem liegt die CPU-Auslastung bei 100 %. In der Statusleiste erscheint während dem Öffnen-/Speichernvorgang die Fortschrittsanzeige.

Abbildung: Fortschrittsanzeige in der Statusleiste

4. Mögliche Dateibeschädigung
Wenn man mit einem VBA-Makro so viele Namen definiert, bis der Laufzeitfehler 1004 erscheint und dann die Mappe speichert und schliesst, so kann die Mappe nicht mehr geöffnet werden. Beim Versuch, die Arbeitsmappe mit Excel zu öffnen, wird der Ladevorgang abgebrochen und die Mappe erscheint nicht auf dem Bildschirm.

5. Dauer zum Erstellen von weiteren Namen
Je mehr Namen bereits existieren, desto länger dauert das Erstellen eines neuen Namens. Interessant ist dabei, dass dieses Phänomen sogar arbeitsmappenübergreifend auftritt. Das bedeutet folgendes: Wenn in Microsoft Excel eine Arbeitsmappe geöffnet ist, die beispielsweise 5'000 Namen enthält, so dauert das Erstellen von weiteren 5'000 Namen ca. 40 % länger als wie wenn noch keine 5'000 Namen existierten. Es spielt keine Rolle, ob die weiteren 5'000 Namen in der gleichen oder in einer anderen bzw. neuen Arbeitsmappe angelegt werden. Massgebend ist, dass im Arbeitsspeicher der Excel-Instanz bereits 5'000 Namen liegen. Ausserdem ist es egal, ob die weiteren 5'000 Namen gleich wie die bisherigen 5'000 Namen heissen, also die bestehenden Namen ersetzen.

In Bezug auf die Speicherbelastung lässt sich sagen, dass 5'000 Namen etwa 2 MB Arbeitsspeicher belegen. Hochgerechnet auf 32'704 Namen wären das ca. 13 MB, was nicht sehr viel ist.

Tipp: Namenfeld-Auswahlliste schneller öffnen
Wie oben erwähnt, dauert das Öffnen der Namenfeld-Auswahlliste bzw. das Aktivieren des Namenfeld-Eingabefeldes bei sehr vielen Namen ungewohnt lange. Beim Benutzer entsteht dadurch unvermeidbar der Eindruck, dass sich Microsoft Excel aufgehängt hat, weil unter anderem die Prozessorauslastung 100 % beträgt und im Windows Task-Manager der Anwendungsstatus mit "Keine Rückmeldung" angezeigt wird. Sie können dieses Problem umgehen, indem Sie Namen einfach ausblenden. Leider ist das über die Excel-Benutzeroberfläche nicht möglich. Das Ausblenden von Namen ist nur mittels VBA-Code machbar, und zwar durch Setzen des Visible-Argumentes der Add-Methode auf False.

Hier ein Beispiel, wie ein neuer Name ("EinName") der Zelle A1 des Tabellenblattes "Tabelle1" hinzugefügt und unsichtbar gemacht wird:

  ActiveWorkbook.Names.Add Name:="EinName", Visible:=False, RefersToR1C1:="=Tabelle1!R1C1"

Da ein ausgeblendeter Name für den Benutzer nicht sichtbar ist, wird er in der Auswahlliste des Namensfeldes nicht aufgeführt. Dadurch wird die Liste schneller geöffnet.