Verweisen auf benannte Bereiche

Artikel
04/07/2023

Bereiche lassen sich leichter durch Namen als durch die A1-Schreibweise identifizieren. Um einen markierten Bereich mit einem Namen zu versehen, klicken Sie auf das Namenfeld am linken Ende der Bearbeitungsleiste, geben einen Namen ein und drücken die EINGABETASTE.

Hinweis Es gibt zwei Arten von benannten Bereichen: benannter Arbeitsmappenbereich und WorkSHEET-spezifischer benannter Bereich.

Benannter Bereich Arbeitsmappe

Der benannte Bereich Arbeitsmappe bezieht sich auf einen bestimmten Bereich innerhalb der Arbeitsmappe (wird global angewandt).

Vorgehensweise: Erstellen eines benannten Bereichs Arbeitsmappe:

Wie oben erläutert, wird dieser benannte Bereich normalerweise durch die Eingabe des Namens in das Namensfeld links unten auf der Bearbeitungsleiste erstellt. Beachten Sie, dass der Name keine Leerzeichen enthalten darf.

ArbeitsBLATT-spezifischer benannter Bereich

Der ArbeitsBLATT-spezifische benannte Bereich bezieht sich auf einen Bereich in einem bestimmten Arbeitsblatt und gilt nicht global für alle Arbeitsblätter in einer Arbeitsmappe. Verweisen Sie auf diesen benannten Bereich nur anhand des Namens im selben Arbeitsblatt, aber aus einem anderen Arbeitsblatt müssen Sie den Arbeitsblattnamen einschließlich "!" den Namen des Bereichs verwenden (Beispiel: der Bereich "Name" "=Sheet1! Name").

Der Vorteil ist, dass Sie VBA-Code verwenden können, um neue Arbeitsblätter mit denselben Namen für dieselben Bereiche innerhalb dieser Blätter zu generieren, ohne eine Fehlermeldung zu erhalten, die besagt, dass der Name bereits vergeben ist.

Vorgehensweise: ArbeitsBLATT-spezifischen benannten Bereich erstellen:

Wählen Sie den Bereich aus, den Sie benennen möchten.
Klicken Sie auf die Registerkarte "Formeln" im Excel-Menüband am oberen Rand des Fensters.
Klicken Sie auf der Registerkarte "Namen definieren" der Registerkarte "Formeln“.
Wählen Sie im Dialogfeld "Neuer Name" unter dem Feld "Bereich" das spezifische Arbeitsblatt aus, in dem sich der Bereich, den Sie definieren möchten, befindet (d. h. "Blatt1"). Dadurch wird der Name für dieses Arbeitsblatt spezifisch. Wenn Sie "Arbeitsmappe" auswählen, erhalten Sie einen ArbeitsMAPPE-Namen.

Beispiel für einen workSHEET-spezifischen benannten Bereich: Ausgewählter Bereich für den Namen ist A1:A10

Der ausgewählte Name des Bereichs ist "name" innerhalb desselben Arbeitsblattes. Verweisen Sie auf den genannten Namen, indem Sie Folgendes in eine Zelle eingeben "=name". Aus einem anderen Arbeitsblatt verweisen Sie auf den Arbeitsblatt-spezifischen Bereich, indem Sie den Namen des Arbeitsblatts in eine Zelle integrieren "=Sheet1!name".

Verweisen auf einen benannten Bereich

Im folgenden Beispiel wird auf den Bereich mit Namen "MyRange" in der Arbeitsmappe "MyBook.xls" verwiesen.

Sub FormatRange() 
    Range("MyBook.xls!MyRange").Font.Italic = True 
End Sub

Im folgenden Beispiel wird auf den tabellenblattspezifischen Bereich mit Namen "Sheet1!Sales" in der Arbeitsmappe "Report.xls" verwiesen.

Sub FormatSales() 
    Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin 
End Sub

Wenn Sie einen benannten Bereich markieren möchten, verwenden Sie die GoTo-Methode. Sie aktiviert die Arbeitsmappe sowie das Arbeitsblatt und markiert anschließend den Bereich.

Sub ClearRange() 
    Application.Goto Reference:="MyBook.xls!MyRange" 
    Selection.ClearContents 
End Sub

Das folgende Beispiel zeigt, wie die gleiche Prozedur für die aktive Arbeitsmappe geschrieben würde.

Sub ClearRange() 
    Application.Goto Reference:="MyRange" 
    Selection.ClearContents 
End Sub

Beispielcode bereitgestellt von:Dennis Wallentin, VSTO & .NET & Excel

In diesem Beispiel wird ein benannter Bereich als Formel für die Datenüberprüfung verwendet. Das Beispiel erfordert die Überprüfungsdaten in Tabelle2 im Bereich A2:A100. Diese Überprüfungsdaten dienen zum Überprüfen von Daten, die in Tabelle1 im Bereich D2:D10 eingegeben wurden.

Sub Add_Data_Validation_From_Other_Worksheet()
'The current Excel workbook and worksheet, a range to define the data to be validated, and the target range
'to place the data in.
Dim wbBook As Workbook
Dim wsTarget As Worksheet
Dim wsSource As Worksheet
Dim rnTarget As Range
Dim rnSource As Range

'Initialize the Excel objects and delete any artifacts from the last time the macro was run.
Set wbBook = ThisWorkbook
With wbBook
    Set wsSource = .Worksheets("Sheet2")
    Set wsTarget = .Worksheets("Sheet1")
    On Error Resume Next
    .Names("Source").Delete
    On Error GoTo 0
End With

'On the source worksheet, create a range in column A of up to 98 cells long, and name it "Source".
With wsSource
    .Range(.Range("A2"), .Range("A100").End(xlUp)).Name = "Source"
End With

'On the target worksheet, create a range 8 cells long in column D.
Set rnTarget = wsTarget.Range("D2:D10")

'Clear out any artifacts from previous macro runs, then set up the target range with the validation data.
With rnTarget
    .ClearContents
    With .Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:="=Source"
        
'Set up the Error dialog with the appropriate title and message
        .ErrorTitle = "Value Error"
        .ErrorMessage = "You can only choose from the list."
    End With
End With

End Sub

Durchlaufen von Zellen in einem benannten Bereich in einer Schleife

Das folgende Beispiel durchläuft jede Zelle in einem benannten Bereich mithilfe einer For Each...Next-Schleife. Wenn der Wert einer beliebigen Zelle im Bereich den Wert von Limit übersteigt, wird die Zelle gelb.

Sub ApplyColor() 
    Const Limit As Integer = 25 
    For Each c In Range("MyRange") 
        If c.Value > Limit Then 
            c.Interior.ColorIndex = 27 
        End If 
    Next c 
End Sub

Über den Autor

Dennis Wallentin ist Autor des Blogs VSTO & .NET & Excel, dessen Schwerpunkt auf .NET Framework-Lösungen für Excel und Excel Services liegt. Dennis entwickelt Excel-Lösungen seit über 20 Jahren und ist Co-Autor von "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition)."

Support und Feedback

Haben Sie Fragen oder Feedback zu Office VBA oder zu dieser Dokumentation? Unter Office VBA-Support und Feedback finden Sie Hilfestellung zu den Möglichkeiten, wie Sie Support erhalten und Feedback abgeben können.

Freigeben über