Namen und RefersTo in Excel VBA richtig verwenden
Schritt-für-Schritt-Anleitung
Um einen Namen in Excel VBA zu erstellen und ihm einen Zellbereich zuzuweisen, kannst du folgende Schritte befolgen:
-
Finde den Wert: Verwende die Find-Methode, um den gesuchten Wert in der Spalte A zu finden.
Set rngFind = Columns(1).Find(CDate(ComboBox1.Value))
-
Namen löschen: Bevor du einen Namen hinzufügst, musst du sicherstellen, dass er nicht bereits existiert. Lösche ihn gegebenenfalls:
For Each nam In ActiveWorkbook.Names
nam.Delete
Next
-
Namen hinzufügen: Verwende die Names.Add-Methode, um den Namen mit der RefersTo-Eigenschaft zu erstellen:
ActiveWorkbook.Names.Add Name:="DatenT", RefersTo:="=" & ActiveSheet.Name & "!" & Range(rngFind.Offset(0, 1), rngFind.Offset(95, 1)).AddressLocal
-
Zellenbezüge festlegen: Füge den zweiten Namen ähnlich hinzu:
ActiveWorkbook.Names.Add Name:="DatenW", RefersTo:="=" & ActiveSheet.Name & "!" & Range(rngFind.Offset(0, 2), rngFind.Offset(95, 2)).AddressLocal
Häufige Fehler und Lösungen
-
Fehler bei der Namenszuweisung: Wenn der Name bereits existiert und nicht gelöscht wird, kann Excel einen Fehler anzeigen. Stelle sicher, dass du den Namen vorher löschst.
-
Falsche Syntax bei RefersTo: Achte darauf, dass du die korrekte Syntax verwendest. Ein Beispiel für die richtige Zuweisung ist:
ActiveWorkbook.Names.Add Name:="DatenT", RefersTo:="=" & ActiveSheet.Name & "!" & Range(rngFind.Offset(0, 1), rngFind.Offset(95, 1)).AddressLocal
-
Referenz auf die falsche Arbeitsmappe: Stelle sicher, dass du die richtige Arbeitsmappe aktiv hast, wenn du ActiveWorkbook.Names.Add verwendest.
Alternative Methoden
Anstelle von VBA kannst du auch die Excel-Oberfläche nutzen, um Namen manuell zu erstellen:
- Markiere den Zellbereich, den du benennen möchtest.
- Gehe zur Registerkarte „Formeln“ und klicke auf „Namen definieren“.
- Gib den Namen und den Zellbereich ein.
Diese Methode ist besonders nützlich, wenn du kein VBA verwenden möchtest oder eine schnelle Lösung benötigst.
Praktische Beispiele
Hier ist ein komplettes Beispiel für die Verwendung von Excel VBA Refersto mit einer ComboBox:
Private Sub ComboBox1_Change()
Dim rngFind As Range
Set rngFind = Columns(1).Find(CDate(ComboBox1.Value))
' Vorhandene Namen löschen
For Each nam In ActiveWorkbook.Names
nam.Delete
Next
' Neue Namen hinzufügen
ActiveWorkbook.Names.Add Name:="DatenT", RefersTo:="=" & ActiveSheet.Name & "!" & Range(rngFind.Offset(0, 1), rngFind.Offset(95, 1)).AddressLocal
ActiveWorkbook.Names.Add Name:="DatenW", RefersTo:="=" & ActiveSheet.Name & "!" & Range(rngFind.Offset(0, 2), rngFind.Offset(95, 2)).AddressLocal
End Sub
Dieses Skript aktualisiert die Namen „DatenT“ und „DatenW“ basierend auf der Auswahl in der ComboBox.
Tipps für Profis
-
Verwende Option Explicit: Dies hilft dir, Variablen zu deklarieren und Fehler bei der Variablenverwendung zu vermeiden.
-
Schreibe Fehlerbehandlungsroutinen: Implementiere On Error Resume Next und On Error GoTo 0, um Fehler beim Ausführen deines Codes zu managen.
-
Dokumentiere deinen Code: Füge Kommentare hinzu, um den Zweck deiner Variablen und Funktionen zu erklären.
FAQ: Häufige Fragen
1. Was passiert, wenn ich versuche, einen Namen hinzuzufügen, der bereits existiert?
Excel wird einen Fehler anzeigen. Du musst den bestehenden Namen zuerst löschen.
2. Wie kann ich sicherstellen, dass mein Code immer auf die richtige Arbeitsmappe zugreift?
Verwende ThisWorkbook anstelle von ActiveWorkbook, um sicherzustellen, dass du die korrekte Arbeitsmappe verwendest.
3. Kann ich RefersTo auch für andere Zellbereiche verwenden?
Ja, RefersTo kann für beliebige Zellbereiche verwendet werden, solange du die korrekte Syntax beibehältst.