Ich habe mein Problem auf den folgenden Testfall reduziert:
Erstellen Sie eine neue Arbeitsmappe;
Geben Sie einen konstanten Wert ein, z. 123 in Sheet1!A1 ;
definiert einen Namen, z.B. foo , das bezieht sich auf die Formel =CHOOSE(!$A, Sheet1!$A) ;
Geben Sie den konstanten Wert 1 in Sheet2!A1 ;
Geben Sie die Formel =foo in eine andere Zelle in Sheet2 ein, z. Sheet2!B1 : Beachten Sie, dass das Ergebnis erwartungsgemäß der Wert ist, der in Schritt 2 oben in Sheet1!A1 eingegeben wurde;
create Führen Sie dann eine VBA-Prozedur aus, die den folgenden Code enthält:
%Vor% Sie werden bemerken, dass die Zelle von Schritt 5 jetzt einen #VALUE! Fehler enthält.
Darüber hinaus löst eine einfache Blattneuberechnung (ob mit dem Schlüssel F9 oder der Application.Calculate -Methode) das Problem nicht: Stattdessen muss entweder eine vollständige Neuberechnung durchgeführt werden von VBA (mit der Application.CalculateFull -Methode) oder sonst einer vollständigen Neuerstellung von der interaktiven UI (mit STRG + ALT + UMSCHALT + F9 Tastenkombination).
Durch Trial-and-Error habe ich festgestellt, dass für diese Bedingung entstehen:
Das Argument CHOOSE() index muss eine relative Zellreferenz enthalten (keine Konstanten oder absolute Blattverweise);
Das Argument CHOOSE() value, das indiziert wird, muss einen Verweis auf ein anderes Blatt enthalten;
Die Änderung der angezeigten Gliederungsebene muss innerhalb einer VBA Prozedur erfolgen (nicht von den Gliederung-Steuerelementen in der interaktiven Benutzeroberfläche oder dem VBA-Direktfenster); und
Der Methodenaufruf ShowLevels (dessen Argumente irrelevant sind) muss auf ein Blatt angewendet werden, auf das unter einem der Argumente Wert (nicht Index) auf CHOOSE() verwiesen wird.
Was ist los?
Ich möchte sehr gerne ein Arbeitsblatt, das ich unter den Wertargumenten auf CHOOSE() verweise, auf seine höchste Gliederungsebene reduzieren, ohne diesen Fehler auszulösen, wie eine vollständige Neuberechnung meiner eigentlichen Arbeitsmappe (noch immer nur ein paar Sekunden) ist aus UX-Sicht unerwünscht.
Vorschläge für eine Problemumgehung (während Sie immer noch einen definierten Namen verwenden, der die CHOOSE() -Funktion zusammen mit einem relativen Blatt-Index-Argument enthält) wären sehr willkommen!
Meine Plattform: Excel 2010 (14.0.6123.5001, 32-Bit) unter Windows 7 Home Premium (SP1, 64-Bit).
Das Problem ist mit Ihrer benannten Formel: =CHOOSE(!$A, Sheet1!$A) , speziell die !A1
Das führende ! ist ungültig (ohne einen vorausgehenden Blattnamen, zB Sheet1!$A ist gültig). Gib einfach ein Blatt an und dein Problem verschwindet.
Ich vermute, dass Sie das nicht befriedigen kann, abhängig von warum Sie !A1 an erster Stelle verwendet haben. Wenn Sie möchten, dass =foo einen Indexwert von A1 auf dem Blatt verwendet, auf dem die Formel =foo auf platziert wird, verwenden Sie INDIRECT("A1") anstelle von !A1
BTW: Ich denke, Sie haben vielleicht einen Fehler gefunden oder zumindest ein undefiniertes Verhalten, weil die Formel =CHOOSE(!$A, Sheet1!$A) ungültig ist und immer einen #Value Fehler zurückgeben soll.
Wenn Sie diese UDF einschließen, gibt es das richtige Verhalten?
%Vor%Es kann Auswirkungen auf die Leistung haben, wenn Sie eine UDF anstelle eines definierten Namens / einer definierten Formel verwenden. Es kann auch problematisch sein, dass es einen in-Parameter braucht, was Ihr ursprünglicher Ansatz nicht tut. Dies kann umgangen werden, indem ein Makro ausgeführt wird, um eine Arbeitsblatt-definierte Formel pro Blatt wie folgt einzuführen: (der Teil, der über alle notwendigen Blätter läuft, ist nicht enthalten)
%Vor%Tags und Links vba excel excel-vba excel-2010