im Namensmanager per VBA Einträger erzeugen - werden nicht 'übernommen'

[Christian_2]

Hallo zusammen,

ich möchte im Namens-Manager per VBA zwei Einträge erzeugen. Diese Einträge erzeuge ich aus fixen Textteilen und einer Variablen (aktuelle Zeilennummer).

Das Ergebnis soll so aussehen
Name 1:         Diagramm_001_1a
Name 2:         Diagramm_001_1b
Wert 1:         {...}   (erhlich gesagt kann ich damit nichts anfangen. Wenn ich den Eintrag, den ich mittels VBA erzeugen will, von Hand eintippe, dann schreibt Excel das so hin.)
Wert 2:         {...}   
Bezieht sich auf 1:     =INDIREKT(VERKETTEN("'Werte-Ref'!$E$"; Grafiken!$B$12; ":$AF$"; Grafiken!$B$12))
Bezieht sich auf 2:     =INDIREKT(VERKETTEN("'Werte'!$E$"; Grafiken!$B$12; ":$AF$"; Grafiken!$B$12))



Mein VBA-Script sieht so aus:

Sub test()
'
' test Makro
'
' Tastenkombination: Strg+r
'
   
    Name = ActiveCell.Value                                                                 ' aktuellen Inhalt der Zelle im String 'Name' speichern
    NamensManager_Name1 = Name + "a"                                                        ' ein 'a' an den Namen anhängen (1. Eintrag)
    NamensManager_Name2 = Name + "b"                                                        ' ein 'b' an den Namen anhängen (2. Eintrag)
   
   
    aktuelle_Zeile = CStr(ActiveCell.Row)                                                   ' aktuelle Zeile in der Variablen aktuelle_Zeile speichern
    Variablenwert_a = "=INDIREKT(VERKETTEN(""'Werte-Ref'!$E$"", Grafiken!R" + aktuelle_Zeile + "C2, "":$AF$"", Grafiken!R" + aktuelle_Zeile + "C2))"
    Variablenwert_b = "=INDIREKT(VERKETTEN(""'Werte'!$E$"", Grafiken!R" + aktuelle_Zeile + "C2, "":$AF$"", Grafiken!R" + aktuelle_Zeile + "C2))"
   
   
    ActiveWorkbook.Names.Add Name:=NamensManager_Name1, RefersToR1C1:=Variablenwert_a
    ActiveWorkbook.Names.Add Name:=NamensManager_Name2, RefersToR1C1:=Variablenwert_b
     
End Sub


Ich möchte gleich dazu schreiben, dass dies mein allererstes selbst geschriebenes VBA-Script ist!

Was passiert, wenn ich mein Script ausführe?
Der Wert der aktuellen Zelle (C12) ist 'Diagramm_001_1'.
Daraus werden die Namen 'Diagramm_001_1a' und 'Diagramm_001_1b' erzeugt und in den Variablen 'NamensManager_Name1' und 'NamensManager_Name2' gespeichert.
Dann wird die aktuelle Zeilennummer gelesen und der Wert in 'aktuelle_Zeile' gespeichert.
Schließlich werden die Einträge für den Namens-Manager erzeugt und dort eingetragen:
=INDIREKT(VERKETTEN("'Werte-Ref'!$E$"; Grafiken!$B$12; ":$AF$"; Grafiken!$B$12))
=INDIREKT(VERKETTEN("'Werte'!$E$"; Grafiken!$B$12; ":$AF$"; Grafiken!$B$12))


So, prinzipiell habe ich damit meine Aufgabe eigentlich gelöst, weil der Text genau so aussehen soll.
Funktionieren tut die Geschichte aber trotzdem nicht. Ich glaube, dass die Einträge zwar im Namens-Manager eingetragen werden, aber wahrscheinlich nicht 'ausgeführt' oder 'aktiviert'.
Ich verwende diese Namen nämlich für Diagramme. Wenn ich das Script laufen lasse, erhalte ich die Fehlermeldung:
Eine Formel in diesem Arbeitsblatt enthält einen oder mehrere ungültige Bezüge.
Überprüfen Sie, ob der Pfad, der Name der Arbeitsmappe und der Name des Bereiches oder des Zellenbezuges korrekt ist.

Wenn ich nun aber in den Namens-Manager gehe, die Variable 'Diagramm_001_1a' anwähle und dann im Eingabefeld 'Bezieht sich auf:' an das Ende ein Leerzeichen anhänge, dann dieses Leerzeichen wieder lösche und dann auf den 'Übernehmen'-Haken gehe und das gleiche für den Eintrag 'Diagramm_001_1b' wiederhole, funktioniert alles einwandfrei.
Das bedeutet, den String, den ich in meinem VBA-Script erzeuge, ist schon richtig. Irgendwie scheint Excel diesen aber nicht zu übernehmen.

Dann vielleicht noch ein wichtiger Hinweis:
Wenn man sich genau ansieht, was ich in die Variable 'Variablenwert_a' eigentlich schreibe, dann sieht man, dass das nicht das gleiche ist! Der erste Unterschied ist, dass das Zellenformat unterschiedlich ist (einmal $B$12 und einmal R12C2) und der zweite Unterschied ist, dass ich einen String erzeuge, der vor 'Grafiken' ein Komma hat, Excel daraus aber ein Semikolon macht (und es auch braucht). Wenn ich gleich ein Semikolon einfüge, dann funktioniert das ganze NICHT. Finde ich sehr seltsam, aber gut.

Also, hat jemand eine Idee, was ich tun muss, dass die Einträge im Namens-Manager korrekt übernommen werden? Für mich wäre der Mehraufwand enorm, wenn ich jedes Mal den Eintrag händisch aktivieren müsste.


Eine Beispieldatei habe ich angehängt. Um die Sache nachzuvollziehen, gehe man auf das Tabellenblatt 'Grafiken', hier auf die Zelle C12 und drücke Strg-r (führt das Makro aus). Im Namens-Manager werden dann die entsprechenden Variable/Namen erzeugt. Das Diagramm zeigt aber immer noch nichts Vernünftiges an. Erst wenn man noch einmal in den namens-Manager geht und die Aktion, wie oben beschrieben durchführt, dann werden die Daten dargestellt.


Vielen Dank schon mal im Voraus für jede Antwort!

Christian