🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Named Range in Excel — Names.Add, RefersTo und warum Ihre Referenz driftet

|

VBA Named Range in Excel — Names.Add, RefersTo und warum Ihre Referenz driftet

TL;DR — Ein Named Range ist eine gespeicherte Formel, kein Etikett. Range("B2:B50").Name = "SalesData" ist der einzeilige Weg, einen zu erstellen; Names.Add Name:="TaxRate", RefersTo:="=Sheet1!$B$1" ist die Langform. Die zwei Dinge, die jeden erwischen: RefersTo braucht ein führendes = und absolute $-Zeichen (lassen Sie das $ weg, und der Name driftet bei jeder Verwendung stillschweigend zu einer anderen Zelle), und ein Name hat einen Scope — arbeitsmappenweit oder ein einzelnes Blatt. Lesen Sie einen Namen mit Range("TaxRate").Value oder der Abkürzung [TaxRate].

' Einzeilig: arbeitsmappenweit, absolut - der haeufige Fall
Range("B2:B50").Name = "SalesData"

' Langform, mit explizitem Scope und RefersTo-Formel
ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"

Debug.Print Range("TaxRate").Value          ' den Wert lesen, auf den der Name zeigt
Range("SalesData").Interior.Color = vbYellow ' den Namen wie jeden Range nutzen

Jeder greift zu Named Ranges, um nicht länger Range("$B$2") über ein ganzes Makro zu streuen, und stößt dann auf eine Reihe kleiner Rätsel: Der Name verweist auf die falsche Zelle oder auf wörtlichen Text, oder derselbe Name bedeutet auf zwei Blättern zwei verschiedene Dinge, oder ein Name überlebt eine gelöschte Spalte und vergiftet jede Formel, die ihn nutzte. Alle stammen aus einer Idee, die festzuhalten sich lohnt: Ein Name ist eine gespeicherte RefersTo-Formel, die Excel bei jeder Verwendung neu auswertet — also sind die Regeln, die Formeln steuern (das führende =, die $-Absoluten, der Blatt-Qualifizierer), genau die Regeln, die Namen steuern.

Was Sie lernen

  • Das mentale Modell — ein Name ist eine gespeicherte Formel, kein Spitzname
  • Einen Namen erstellen — das einzeilige Range.Name gegenüber Names.Add mit RefersTo
  • Warum $ zwischen einem stabilen Namen und einem entscheidet, der mit der aktiven Zelle driftet
  • Scope — arbeitsmappenweit gegenüber einem einzelnen Blatt, und die Kollision, die er verursacht
  • Einen Namen lesen und nutzen, und Konstanten-Namen, die keine Zellen haben
  • #REF!-Namen aufräumen und die versteckte Namen-Aufblähung, die sie hinterlassen

Das mentale Modell: ein Name ist eine gespeicherte Formel

Wenn Sie einen Named Range erstellen, markiert Excel nicht die Zelle. Es speichert eine RefersTo-Formel und löst sie jedes Mal auf, wenn der Name erscheint. SalesData ist nicht „Zelle B2:B50", eingefroren an ihrem Platz — es ist die Formel =Sheet1!$B$2:$B$50, bei Bedarf ausgewertet. Lesen Sie Range("SalesData"), und Excel führt diese Formel aus und reicht Ihnen, worauf auch immer sie jetzt auflöst.

Deshalb geht es bei allem, was folgt, in Wahrheit darum, die RefersTo-Formel korrekt zu schreiben:

ThisWorkbook.Names.Add Name:="SalesData", RefersTo:="=Sheet1!$B$2:$B$50"
Debug.Print ThisWorkbook.Names("SalesData").RefersTo   ' =Sheet1!$B$2:$B$50

RefersTo ist eine Zeichenkette, die mit = beginnen muss, genau wie beim Tippen in den Namensmanager. Lassen Sie das = weg — RefersTo:="Sheet1!$B$2" — und Sie bekommen keinen Fehler; Sie bekommen einen Namen, der auf den wörtlichen Text „Sheet1!$B$2" verweist, was fast nie das ist, was Sie meinten. Halten Sie „es ist eine Formel" fest, und das = hört auf, ein Rätsel zu sein.

Einen Namen erstellen: der Einzeiler und die Langform

Für den häufigen Fall — einen absoluten, arbeitsmappenweiten Namen — weisen Sie .Name einem Range zu, und Sie sind fertig:

Range("B2:B50").Name = "SalesData"      ' Arbeitsmappen-Scope, absolut, eine Zeile

Greifen Sie zu Names.Add, wenn Sie den Scope ausdrücklich setzen, einen Namen per Formel referenzieren oder eine Konstante statt Zellen speichern müssen:

ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"   ' Arbeitsmappen-Scope
Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9" ' Blatt-Scope
ThisWorkbook.Names.Add Name:="VAT", RefersTo:="=0.2"              ' eine Konstante, keine Zellen

Beide sind in Ordnung; der Unterschied ist Kontrolle. Range.Name = ist das schnellste Korrekte für einen schlichten Range; Names.Add nutzen Sie in dem Moment, in dem Scope oder ein Nicht-Range-Ziel zählt.

Warum $ zwischen einem stabilen und einem driftenden Namen entscheidet

Das ist der Named-Range-Bug, den Leute nicht erklären können: „Mein Name zeigt bei jedem Makrolauf auf eine andere Zelle." Die Ursache ist ein relativer Name — ein RefersTo ohne $-Zeichen.

' DRIFTET - relative Referenz, aufgeloest gegen die AKTIVE Zelle
ThisWorkbook.Names.Add Name:="Prev", RefersTo:="=Sheet1!A1"

' STABIL - absolute Referenz, immer dieselbe Zelle
ThisWorkbook.Names.Add Name:="Anchor", RefersTo:="=Sheet1!$A$1"

Ein relativer Name wird relativ zu der Stelle gespeichert, an der die aktive Zelle beim Erstellen stand, und Excel verankert ihn bei jeder Verwendung neu an der aktiven Zelle — also löst Prev vielleicht auf A1 auf, dann D5, dann Z99, je nach Auswahl. Relative Namen sind ein echtes, gelegentlich nützliches Feature (ein Name, der „die Zelle eins nach links" bedeutet), aber wenn Sie es nicht mit Absicht getan haben, liest es sich wie ein Spuk. Schreiben Sie $ sowohl auf die Spalte als auch auf die Zeile, es sei denn, Sie wollen ausdrücklich, dass der Name sich bewegt. Wenn Sie Range.Name = nutzen, bekommen Sie automatisch absolut — ein weiterer Grund, warum es der sicherere Standard ist.

Scope: arbeitsmappenweit gegenüber einem einzelnen Blatt

Jeder Name lebt in einem Scope. ThisWorkbook.Names.Add (und Range.Name =) erstellt einen arbeitsmappenweiten Namen, von überall sichtbar. Worksheets("Jan").Names.Add erstellt einen blattweiten Namen, nur auf jenem Blatt sichtbar — was Jan und Feb je einen eigenen Region-Namen geben lässt, der auf die eigenen Daten zeigt.

Die Falle ist, sie zu verwechseln:

Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9"
' Aus einem Standardmodul sieht dies den blattlokalen Namen NICHT zuverlaessig:
' Debug.Print Range("Region").Address   ' kann Fehler werfen oder eine andere Region treffen
Debug.Print Worksheets("Jan").Range("Region").Address   ' qualifiziere ihn -> funktioniert

Ein blattweiter Name muss über sein Blatt erreicht werden — Worksheets("Jan").Range("Region") — nicht mit einem nackten Range("Region") aus einem Modul. Entscheiden Sie bewusst: eine einzelne Konstante für die ganze Datei (TaxRate) ist Arbeitsmappen-Scope; eine Region, die sich pro Blatt wiederholt (Region auf jedem Monats-Tab) ist Blatt-Scope, und Sie qualifizieren sie jedes Mal. Siehe VBA Worksheet für das Adressieren von Blättern.

Lesen, nutzen und die Konstanten-Namen-Falle

Ein Range-Name verhält sich wie jeder Range. Lesen, schreiben, formatieren Sie ihn:

Range("TaxRate").Value = 0.19            ' in die benannte Zelle schreiben
Debug.Print Range("SalesData").Cells.Count
Set rng = ThisWorkbook.Names("SalesData").RefersToRange  ' das Range-Objekt

Range("TaxRate").Value liest den Wert; [TaxRate] ist eine Abkürzung für dasselbe (es ist Evaluate("TaxRate")). Aber achten Sie auf die letzte Zeile oben: .RefersToRange funktioniert nur, wenn der Name auf Zellen verweist. Haben Sie eine Konstante gespeichert — Names.Add Name:="VAT", RefersTo:="=0.2" —, steht kein Range dahinter, also werfen Range("VAT") und .RefersToRange einen Fehler, während [VAT] und Evaluate("VAT") korrekt 0.2 zurückgeben. Wissen Sie, welche Art Name Sie haben, bevor Sie ihn als Zellen behandeln. Dazu, wie ein Name als Formel auflöst, siehe VBA Formula.

#REF!-Namen aufräumen und versteckte Namen-Aufblähung

Löschen Sie die Zeilen oder Spalten, die ein Name abdeckt, und der Name stirbt nicht — sein RefersTo wird zu =#REF!, eine scharfe Landmine, die jede Formel bricht, die den Namen referenziert. Namen wandern außerdem mit, wenn Sie ein Blatt kopieren, und häufen sich still an, bis eine Arbeitsmappe Tausende versteckter, kaputter Namen trägt und im Schneckentempo kriecht. Beides ist der Grund, warum das Prüfen von Namen eine Wartungsaufgabe ist, keine einmalige Einrichtung:

Dim nm As Name
For Each nm In ThisWorkbook.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then
        Debug.Print "broken: " & nm.Name & " -> " & nm.RefersTo
        nm.Delete                      ' die Landmine entfernen
    End If
Next nm

Durchlaufen Sie ThisWorkbook.Names, markieren Sie jeden, dessen RefersTo #REF! enthält, und Deleten Sie ihn. Lassen Sie dieselbe Schleife mit nm.Visible = False in der Bedingung laufen, um die versteckten Namen zu finden, die ein eingefügtes Blatt hereinschleppte. Ein Name ist billig zu erstellen und leicht zu vergessen — behandeln Sie die Namensliste als etwas, das Sie säubern, nicht nur füllen.

Wie ExcelMaster hilft

Die Named-Range-Fehler, die echte Zeit kosten, sind keine Tippfehler — es sind der relative Name, der driftet, weil ein $ fehlte, der blattweite Name, den ein Modul nicht sieht, das .RefersToRange, das auf einem Konstanten-Namen hochgeht, und der #REF!-Name, den niemand bemerkte, bis ein Bericht falsch hinausging. Jeder läuft; er löst nur auf die falsche Stelle auf.

ExcelMaster schreibt Namen so, wie es ein sorgfältiger Entwickler täte. Bitten Sie es, „die Steuersatz-Zelle zu benennen und in der Berechnung zu nutzen", und es erstellt einen absoluten, arbeitsmappenweiten Namen, referenziert ihn überall, statt $B$1 hart zu verdrahten, und wählt Blatt-Scope nur, wenn die Region sich wirklich pro Tab wiederholt. Es liest benannte Werte mit dem richtigen Aufruf für Range-Namen gegenüber Konstanten-Namen und kann die kaputten #REF!-Namen prüfen und beseitigen, die eine Arbeitsmappe gesammelt hat. Sie benennen, was Sie meinen; es verdrahtet die Referenz so, dass eine eingefügte Zeile sie nie stillschweigend bricht.

Häufig gestellte Fragen

Wie erstelle ich einen Named Range in VBA?

Der kürzeste Weg ist, die Name-Eigenschaft eines Range zuzuweisen: Range("B2:B50").Name = "SalesData", was einen arbeitsmappenweiten, absoluten Namen erstellt. Für mehr Kontrolle nutzen Sie Names.Add: ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1". Die RefersTo-Zeichenkette muss mit = beginnen, und Sie wollen fast immer absolute $-Referenzen, damit der Name nicht driftet.

Was ist der Unterschied zwischen Arbeitsmappen- und Blatt-Scope für einen Namen?

Ein arbeitsmappenweiter Name (ThisWorkbook.Names.Add oder Range.Name =) ist von jedem Blatt und jedem Modul sichtbar. Ein blattweiter Name (Worksheets("Jan").Names.Add) ist nur auf jenem Blatt sichtbar, sodass verschiedene Blätter denselben Namen für ihre eigenen Daten wiederverwenden können. Erreichen Sie einen blattweiten Namen über sein Blatt: Worksheets("Jan").Range("Region"), nicht ein nacktes Range("Region").

Wie erhalte ich den Range, auf den ein Name in VBA verweist?

Nutzen Sie ThisWorkbook.Names("SalesData").RefersToRange, um das Range-Objekt zu erhalten, oder einfach Range("SalesData") für einen arbeitsmappenweiten Namen. Um seinen Wert zu lesen, Range("SalesData").Value oder die Abkürzung [SalesData]. .RefersToRange funktioniert nur für Namen, die auf Zellen zeigen — ein Konstanten-Name wie =0.2 hat keinen Range und muss mit Evaluate oder [Name] gelesen werden.

Warum zeigt mein Named Range in VBA auf #REF!?

Weil die Zellen, auf die er verwies, gelöscht wurden. Das Löschen der Zeilen oder Spalten, die ein Name abdeckt, löscht nicht den Namen; stattdessen wird sein RefersTo zu =#REF!, und jede Formel, die den Namen nutzt, bricht. Prüfen Sie sie, indem Sie ThisWorkbook.Names durchlaufen und prüfen, ob nm.RefersTo #REF! enthält, und dann nm.Delete auf den kaputten aufrufen.

Wie lösche ich einen Named Range in VBA?

Rufen Sie .Delete auf dem Namen auf: ThisWorkbook.Names("SalesData").Delete, oder durchlaufen Sie ThisWorkbook.Names und löschen nach einer Bedingung (zum Beispiel jeden, dessen RefersTo #REF! enthält). Das Löschen des Namens rührt die Zellen nicht an; es entfernt nur den definierten Namen, was der Weg ist, die kaputten und versteckten Namen auszuräumen, die sich beim Kopieren von Blättern ansammeln.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 01.09.2026.

Verwandte Anleitungen: VBA Range · VBA Cell Value · VBA Formula · VBA Worksheet · VBA Offset