Globale benannte Bereiche in lokale umwandeln

Sprache / Programm: Excel ab Version 2000

Beschreibung

In Excel können benannte Bereiche angelegt werden (Einfügen -> Name -> Definieren ...), mit denen man Zellbezüge durch aussagekräftige Namen ersetzen kann.

Über die Benutzeroberfläche ist es aber nur möglich, Namen zu vergeben, die in der ganzen Arbeitsmappe verfügbar sind. Dies ist hilfreich, wenn man beispielsweise auf einer Seite Umrechnungskurse ablegt, die von den übrigen Blättern der Arbeitsmappe referenziert werden können.

Daneben erstellt Excel für den Druckbereich benannte Bereiche, die nur innerhalb der jeweiligen Tabelle verfügbar sind.

Solche lokalen benannten Bereiche haben den Vorteil, dass man auf sie innerhalb eines Blattes zugreifen kann. Erstellt man Kopien eines Blattes mit lokalen benannten Bereichen, um z.B. eine Berechnung mehrfach mit unterschiedlichen Eingabewerten anzulegen und zu vergleichen, bleiben die Namen erhalten und funktionieren für sich in jeder Tabelle.

Das Programm untersucht den markierten Bereich und wandelt die gefundenen benannten Bereiche in lokale Bereiche um. Wenn dabei benannte Bereiche gefunden werden, die nicht mehr auf eine Zelle verweisen (#BEZUG!), bietet das Programm an, diese zu löschen.

VBA-Quelltext

Public Sub NamenUmwandeln()
Dim BereichsName As Name, tmpName As String, tmpBereich As Excel.Range

On Error Resume Next
If LCase(TypeName(ActiveSheet)) <> "worksheet" Then Exit Sub
For Each BereichsName In ActiveWorkbook.Names
tmpName = BereichsName.Name
Set tmpBereich = Nothing
Set tmpBereich = Range(BereichsName.RefersTo) 'BereichsName.RefersToRange
If tmpBereich Is Nothing Then
If MsgBox("Reference of '" & tmpName & "' to" & vbNewLine & BereichsName.RefersTo & vbNewLine & "seems To be invalid. Delete?", vbOKCancel) = vbOK Then
ActiveWorkbook.Names(tmpName).Delete
End If
ElseIf tmpBereich.Parent.Name = ActiveSheet.Name Then
If Not (Intersect(tmpBereich, Selection) Is Nothing) Then
ActiveWorkbook.Names(tmpName).Delete
ActiveSheet.Names.Add tmpName, tmpBereich
End If
End If

Next BereichsName
End Sub

Argumente der Funktion/Prozedur

: Keine. Das Programm wertet die markierten Zellbereiche aus.

Rückgabewert

Keine. Die Änderungen sind im Menü Einfügen -> Namen -> Definieren ... Daran zu erkennen, dass jetzt ein Tabellenname neben dem benannten Bereich erscheint.

Es werden nur benannte Bereiche angezeigt, die entwender in der ganzen Arbeitsmappe oder im gerade aktiven Tabellenblatt definiert sind.