Verwendung von Named Ranges – Wieso!?

Hier ein kleiner Tipp für Excel-Begeisterte, die noch nicht so häufig unter VBA programmiert haben.

 

Vielen Excel-Anwendern sind die sogenannten Bereichsnamen (engl. „Named Ranges“) nicht bekannt. Oft kommt es vor, dass die Arbeitsmappe wunderbar strukturiert wurde. Die Designs wurden gesetzt. Chef und Mitarbeiter sind begeistert, wie toll das doch alles aussieht. Und dann noch die selbst erstellten VBA-Makros, die das Ganze zu einem perfekten Unterstützungstool machen.

Aber ein paar Kleinigkeiten wären noch nett. „Könnten wir hier nicht noch eine Spalte für die netto Kosten einfügen?“. „Dieser Eingabebereich sollte vielleicht erst im nächsten Block sitzen“ und und und…

Gesagt getan. Alles umgesetzt und die Kollegen können endlich mit dem Tool arbeiten. Doch schon nach kurzer Zeit kommt der Anruf eines Kollegen „Du hör mal, irgendwas stimmt da nicht!“. Eine andere Kollegin berichtet irgendwas von einem Laufzeitfehler. Was ist das los?

Häufig ist die Antwort in einem solchen Fall recht einfach: Die Zellbezüge sind falsch. Gerne liest man in VBA-Code etwas wie:

 

Cells(3, 4) = ThisWorkbook.Worksheets(„Eingabe“).Cells(5, 5)

 

Von dem fehlenden “.Value” und der Verwendung von Arbeitsblatt-Bezeichnungen (anstatt der Verwendung von Codenamen) einmal abgesehen liegt hier der Fehler auf der Hand: Direkte Zellbezüge!

Durch die Umstrukturierung der Arbeitsblätter stimmen diese Bezüge nicht mehr. Was in Formelbezügen vielleicht noch kompensiert wird, kann im VBA-Code jedoch schwerwiegende Folgen nach sich ziehen.

Abhilfe können hier sogenannte Bereichsnamen schaffen. Diese enthalten auch dann den richtigen Bezug, wenn die Zielzellen verschoben werden. Ein Beispiel: In einer Übersichtstabelle existieren zehn verschiedene Spalten, die Vertriebsdaten zu Produkten enthalten. Eine Möglichkeit Daten aus einer der Spalten zur weiteren Verarbeitung zu ziehen wäre folgender Code-Schnipsel, bei dem nur die Daten summiert werden, für die die Verkaufszahlen vorliegen:

 

Dim i As Integer

Dim Summe As Double

 

Summe = 0

For i = 4 To 5000

If ThisWorkbook.Worksheets(“Daten”).Cells(i, 4) <> “” Then

Summe = Summe + ThisWorkbook.Worksheets(“Daten”).Cells(i, 4)

End If

Next i

 

Schön und gut. Doch schauen wir uns den Code einmal genauer an:

 

Variablendeklaration:

Grundsätzlich ist für die Benennung von Variablen zu empfehlen, dass der Datentyp mit angegeben wird. Dies verhindert späteres Suchen und erleichtert ungemein die Wartung des Codes.

Besonders sollte sich hier gefragt werden, was passiert, wenn die Zahl der Datensätze zum Beispiel 100.000 beträgt!?

 

Schleifendurchlauf:

Wie zu sehen, beginnt der Schleifenindex bei 4. In Zeile 4 beginnen also die ersten Datensätze. Aber wieso läuft die Schleife bis 5.000? Vermutlich weil so sichergestellt werden kann, dass alle Daten berücksichtigt werden. Es wird davon ausgegangen, dass maximal 5000 Datensätze vorhanden sein werden. Dies ist nicht nur gefährlich, da die Zahl der existierenden Datensätze variieren kann und zum anderen führt es zu unnötigen Schleifendurchläufen leerer Zeilen.

 

Das Kriterium und das Aufsummieren:

Wie erwähnt ist das Kriterium zum Berücksichtigen des Datensatzes,  eine existierende Verkaufszahl. In Spalte 4 (Spalte D) müssen wir also die Zahl der verkauften Einheiten wiederfinden. Sicherlich wird diese Zahl nicht bloß an dieser Stelle im Code verwendet, sondern auch häufig woanders.

Nun wird verlangt, dass noch weitere Spalten hinzugefügt werden. Unter anderem soll eine Bemerkungsspalte direkt vor den Verkaufszahlen eingefügt werden. Somit finden wir nun in Spalte D keine Zahlen mehr sondern Textinformationen. Das Ergebnis: Ein Laufzeitfehler, sobald versucht wird den Zellinhalt auf eine Double-Variable zu addieren.

 

Wie man sieht enthält dieses kleine Stückchen Code bereits zahlreiche Fallstricke. Besonders kritisch ist hier jedoch der fehlerhafte Bezug zu einer fixen Spalte. Um dieses Problem zu lösen bieten sich Named Ranges an. Einzelne Zellen oder ganze Zellbereiche lassen sich sowohl lokal (d.h. auf dem Arbeitsblatt), als auch global (Arbeitsmappenweit) Namen zuordnen, die codeseitig verwendet werden können.

Um einer Zelle (oder einem Bereich) einen solchen Namen zuzuordnen gibt es folgende Möglichkeiten:

Nach der Markierung der Zelle, kann im Kontextmenü nach dem Rechtsklick der Eintrag „Namen definieren…“ ausgewählt werden. In dem dann geöffneten Fenster können der Name, der Gültigkeitsbereich (Arbeitsblatt oder Arbeitsmappe), sowie eine Bemerkung und der Zellbezug angeben werden. Zum selben Fenster kommt man auch über den Reiter „Formeln >> Namensmanager“, wo man alle angelegten Named Ranges sehen, neu anlegen, ändern und löschen kann.

Für globale Named Ranges lässt sich dieser auch direkt im Namensfeld angeben. Dieses befindet sich oben links neben der Bearbeitungsleiste.

 

Mit diesem Werkzeug können wir nun den Spaltenüberschriften Named Ranges zuordnen. Unseren Verkaufszahlen also z.B. „rngVerkaufszahlen“ (Zelle mit Zeilenindex 3 und Spaltenindex 4). Aber gehen wir noch einen kleinen Schritt weiter und fügen für die erste Zeile nach dem Datenbereich eine Named Range mit dem Namen „rngLetzteZeile“ hinzu. Damit könnte unser Code z.B. folgendermaßen aussehen:

 

 

Auf den ersten Blick sieht dieser Code natürlich etwas aufgebläht aus. Doch durch die Eigenschaften „.Row“ (Zeilennummer hier 3) und „.Column“ (Spaltennummer; hier 4) erhalten wir stets den korrekten Zellbezug, auch wenn wir Zeilen oder Spalten verschieben sollten.

 

Einige werden sich sicherlich fragen, was es mit dem „wksDaten“ auf sich hat. Bei dieser Bezeichnung handelt es sich um den Codenamen des Arbeitsblattes „Daten“.

 

Die Named Range „rngLetzteZeile“  hat neben der Beschränkung des Datenbereichs auch noch eine weitere Funktion. Diese werden aber in einem anderen Beitrag erläutert.