Wenn wir unter Excel mit VBA programmieren, dann geht es in vielen Fällen um die Interaktion mit Mappen und Zellen. Darauf können wir auf unterschiedliche Art und Weise zugreifen, die darüber entscheiden, wie das Programm mit Änderungen der Tabellenstruktur umgehen kann. Und genau diese Möglichkeiten und die damit verbundenen Vor- und Nachteile möchte ich in diesem Beitrag beschreiben.
Problemstellung: Zugriff auf eine Excel-Mappe
Sehen wir uns kurz eine einfache Codezeile an.
umsatz = Worksheets("Monatszahlen Dez2019").Range("F20").Value
Diese Zeile wählt aus der Auflistung aller Mappen diejenige Mappe aus, welche die Überschrift „Monatszahlen Dez2019“ hat. Anschließend wird der Inhalt der Zelle F20 ausgelesen und der Variablen umsatz zugewiesen. An sich ist an dieser Zeile nichts auszusetzen, die funktioniert tadellos und liefert das richtige Ergebnis zurück. Stellen wir uns jedoch vor, uns ist eine treffendere Bezeichnung für die Mappe eingefallen, beispielsweise „Monatsproduktion Dez2019“, die wir dann auch eintragen. Was passiert dann, wenn die Codezeile ausgeführt wird? Deren Ausführung wird natürlich fehlschlagen, weil in der Auflistung aller Mappen keine Mappe mit der alten Bezeichnung mehr existiert. Wie können wir dieser Problematik am besten begegnen? Wie können wir es schaffen, dass die Anpassung der Bezeichnung nicht mehr zu solchen Seiteneffekten führt? Eine Passage wie „Der Name der Mappe darf nicht geändert werden“, mag vielleicht einige Nutzer von Umbenennung abhalten, es löst aber das eigentliche Problem nicht.
Verbesserungsvorschlag: Zugriff auf eine Excel-Mappe
Die Lösung ist von der Idee her sehr einfach. Schauen wir uns die Eigenschaften unserer Mappe an. Excel ist hier an der Stelle leider nicht ganz konsequent – erkennbar daran, dass es hier scheinbar zwei Eigenschaften mit der Bezeichnung „Name“ gibt. Die untere Eigenschaft „Name“ (hier wäre die Bezeichnung Caption“ treffender) repräsentiert die Bezeichnung der Mappe, also das was wir in unserem Beispiel angepasst haben. Die obere Eigenschaft „Name“ repräsentiert den technischen Namen der Mappe, der nach außen hin nicht in Erscheinung tritt und vom Nutzer nicht angepasst werden kann. Da können wir einen sinnvollen Namen vergeben, beispielsweise „mappeZahlen122019„. Anschließend können wir unsere Anweisung folgendermaßen umformen.
umsatz = mappeZahlen122019.Range("F20").Value
Dadurch haben wir nun erreicht, dass die Änderung der Bezeichnung keinen Einfluss mehr auf die oben genannte Codezeile hat. Der Zugriff darauf wird bei jeder Änderung der Bezeichnung weiterhin zuverlässig funktionieren, solange die Mappe nicht gelöscht wird.
Problemstellung: Zellenzugriff
Bei der Interaktion mit Zellen stehen wir im Prinzip vor ähnlichen Herausforderungen, allerdings mit dem Unterschied, dass beim Zellenzugriff die Probleme nicht so offensichtlich in Erscheinung treten.
Sehen wir uns die vorher angepasste Zeile mit dem direkten Zugriff auf die Zelle F20 an. Den Zugriff auf die Mappen haben wir ja an der Stelle schon verbessert. Was passiert jedoch, wenn sich in dieser Mappe die Struktur ändert und eine neue Spalte vor die Spalte F gesetzt wird?
Nun, offenbar passiert nichts sichtbares. Die Codezeile wird ganz normal, ohne Fehler, ausgeführt. Allerdings stehen nach diesen Verschiebungen ganz andere fachliche Inhalte in der Zelle F20 drin, sodass beispielsweise Kostensätze darin stehen könnten. Und das ist eigentlich auch das schlimme daran, dass der Fehler höchstwahrscheinlich unbemerkt bliebt. Auch wenn wir unseren Code gut strukturiert haben und die Inhalte, die von „außen“ kommen, auf deren Plausibilität checken, bleiben solche Fehler höchstwahrscheinlich unbemerkt, weil die Formate und die Wertebereiche der Daten an sich die selben sind, obwohl diese fachlich was ganz anders aussagen.
Verbesserungsvorschlag: Zellenzugriff
Anstatt auf einen ganz konkreten Zellennamen zuzugreifen, besteht die Möglichkeit, dies über einen Alias zu tun. Dafür klicken wir auf die Zeile F20 und vergeben unter „Formeln > Namensmanager > Neu“ für diese Zelle einen Alias, beispielsweise „Umsatz„. Dabei kann festgelegt werden, ob dieser Name global in der gesamten Exceltabelle oder nur in der unserer Beispielmappe gelten soll.
Nun können wir die Zelle direkt mit diesen Namen ansprechen, sodass unsere Anweisung folgendermaßen ausschaut.
umsatz = mappeZahlen122019.Range("Umsatz").Value
Nun ist es unrelevant, ob sich die Zelle durch ein Einfügen von Spalten verschiebt. Der Name und somit der Zugriff bleibt bestehen und es wird dabei auch die ursprünglich festgelegte Zelle angesprochen.
Fazit
Die hier vorgestellte Verbesserung des Mappenzugriffs kann ich uneingeschränkt empfehlen, ich nutze diese auch sehr gerne bei der Entwicklung von Excel-VBA-Programmen. Die vorgestellte Verbesserung des Zellenzugriff hingegen lässt sich so nicht in jedem Fall einsetzen. Diese ist perfekt, wenn es um wenige Zellen geht, auf die zugegriffen werden soll.
Handelt es sich jedoch um ganz viele Zellen, auf die wir zugreifen wollen, können wir ja schlecht für jede Zelle separate Namen vergeben. In solchen Konstellationen nutze ich diese Namen nicht, um die einzelnen Zellen zu benennen. Stattdessen vergebe ich die Namen für die einzelnen Spalten, um auf die richtige Spalte trotz einer möglichen Verschiebung zugreifen zu können.