Office: Unter Excel2007 funktionierende Formel wird von Excel2010 nicht akzeptiert

Hallo,

Ich versuche ein Dokument, welches schon seit langem mit Excel2007 verwendet wird nun nach Office2010 zu portieren.
Dabei bin ich auf folgendes Problem gestoßen:

In Makro fügt in bestimmte Zellen folgende Formel ein:

Code:

=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()-1)));"";WENN(UND(NICHT(ISTLEER(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))));NICHT(INDIREKT(ADRESSE(ZEILE();SPALTE()-1))="n/a");NICHT(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))="n/a"));INDIREKT(ADRESSE(ZEILE();SPALTE()-1))-INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1));"n/a"))

Hübsches komplexes Formelmonster, ich weiß ;)

Also unter Excel 2007 funktioniert das alles wunderbar.
Unter Excel 2010 bringt das selbe Makro Excel zum Abstürzen.

Um einen Fehler mit dem Makro auszuschließen habe ich dann versucht, diese Formel von Hand in eine Zelle einzutragen.

Excel 2010 weigert sich, diese Formel anzunehmen.
Es wirft die Fehlermeldung "Die eingegebene Formel enthält einen Fehler."



Wie kann das sein?
Hat Microsoft beim Wechsel von 2007 auf 2010 irgendetwas an den Syntax der Formelelementen geändert?

Kann mir jemand sagen, was ich umstellen muss, damit diese Formel sowohl unter Excel 2007 als auch unter Excel 2010 funktioniert?



Anhang:

Dies ist der Makro-Code, der die Formel in eine Zelle einfügt.
Hier drin ist die Formel etwas besser lesbar, da ich sie zur besseren Lesbarkeit in Blöcke zerlegt habe, die vom Code erst zusammengefügt werden.

Ich habe einige Stellen des Codes durch [...] ersetzt, um ihn besser lesbar zu machen. Dort werden nur Funktionen aufgerufen, die mit dem Problem nichts zu tun haben und auch alle einzeln auf Fehlerfreiheit und Kompatibilität zu Excel2010 geprüft wurden.

Code:

    Dim minuend, subtrahend, wenn, dann, sonst, innereFormel, differenzFormel As String
    Dim variable1, variable2 as Integer
    ' variable1 & variable2 wurden zur leichteren lesbarkeit umbenannt

[...]

        'differenzFormel
        'diese Formel wird zwecks lebarkeit und wartbarkeit aus mehreren Strings zusammen gesetzt.
        
        'Feld links von der Formel
        minuend = "INDIREKT(ADRESSE(" & _
                      "ZEILE();SPALTE()-1" & _
                  "))"
        
        'Feld links unterhalb der Formel
        subtrahend = "INDIREKT(ADRESSE(" & _
                         "ZEILE()+1;SPALTE()-1" & _
                     "))"
        
        wenn = "UND(" & _
                   "NICHT(ISTLEER(" & subtrahend & "));" & _
                   "NICHT(" & minuend & "=""n/a"");" & _
                   "NICHT(" & subtrahend & "=""n/a"")" & _
               ")"
    
        dann = minuend & "-" & subtrahend
        sonst = """n/a"""
        
        innereFormel = "WENN(" & wenn & ";" & dann & ";" & sonst & ")"
        
        differenzFormel = "=WENN(ISTLEER(" & minuend & ");"""";" & innereFormel & ")"

        'Zusammengesetzt ergibt dies
        'differenzFormel = "=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()-1)));"""";WENN(UND(NICHT(ISTLEER(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))));NICHT(INDIREKT(ADRESSE(ZEILE();SPALTE()-1))=""n/a"");NICHT(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))=""n/a""));INDIREKT(ADRESSE(ZEILE();SPALTE()-1))-INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1));""n/a""))"

        'in der Zelle steht hinterher:
        '=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()-1)));"";WENN(UND(NICHT(ISTLEER(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))));NICHT(INDIREKT(ADRESSE(ZEILE();SPALTE()-1))="n/a");NICHT(INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))="n/a"));INDIREKT(ADRESSE(ZEILE();SPALTE()-1))-INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1));"n/a"))

        

[...]

            ' diese zeile führt unter excel2010 zum Absturz
            ' jedoch nicht, wenn man differenzFormel durch eine einfachere Formel wie z.B. "=SUMME(K6:K8)" ersetzt
            ' unter excel2007 gibt es hiermit keinerlei Probleme

            worksheet.Cells( variable1, variable2 + 1).Formula = differenzFormel

            ' variable1 & variable2 wurden zur leichteren lesbarkeit umbenannt
            ' variable1 = 11
            ' variable2 = 12

[...]

 

Grüezi Oliver

Deine Formel ist in Deutsch geschrieben - verwende im Code daher .FormulaLocal um die Formel in die Zelle zu schreiben.
Arbeitest Du mit .Formula musst Du die Formel mit englischen Namen schreiben.

 

Hallo!

Darf ich auch noch meine Anmerkung zu den beiden Deklarationszeilen machen:
Mit den 2 Deklarationszeilen möchtest Du wahrscheinlich zum Ausdruck bringen, dass die 7 Variablen der 1. Zeile alle vom Typ "String" sind, während die beiden Variablen der 2. Zeile vom Typ "Integer" (=16-Bit-Ganzzahl) sein sollten.
Tatsächlich ist aber nur "differenzFormel" vom Typ "String" und "variable2" vom Typ "Integer" - die restlichen Variablen sind jedoch vom Typ "Variant", denen dann später im Code ein dynamischer Subtyp zugewiesen wird.
In VBA (und auch in Visual Basic) muss man jede Variable einzeln mit "... AS Typbezeichner" deklarieren oder aber man verwendet die Typkennzeichen:

Entweder:
Dim minuend as string, subtrahend as string, wenn as string, sonst as string, innereFormel as String, differenzFormel as String
Dim variable1 as Integer, variable2 as Integer
oder:
Dim minuend$, subtrahend$, wenn$, sonst$, innereFormel$, differenzFormel$
Dim variable1%, variable2%

Man darf beide Methoden auch mischen, wie zB. in der 2.Zeile:
Dim variable1%, variable2 as Integer

Das ist leider eine gewisse Falle in BASIC, wenn man bisher in anderen Programmiersprachen (C, Pascal, ...) programmiert hat.

 

Hallo!

Darf ich noch eine weitere Anmerkung machen - diesmal zur eingesetzten Formel selbst und ihrer Konstruktion im Makro:
Du verwendest mehrmals folgende Formelsequenzen:
INDIREKT(ADRESSE(ZEILE();SPALTE()-1))
INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))
Dabei bezieht sich der 1. INDIREKT-Ausdruck auf die Zelle links neben der Formelzelle und der 2. INDIREKT-Ausdruck bezieht sich auf die Zelle links unterhalb in der nächsten Zeile. Daher könnte man das Formelkonstruktionsmakro erheblich vereinfachen (und schließlich auch die resultierende Formel), wenn man die relative Adressierung einsetzt.
In deutscher Formelsprache sieht das folgendermaßen aus:
aus "...INDIREKT(ADRESSE(ZEILE();SPALTE()-1))...." wird dann "...INDIREKT(ZS(-1))...."
aus "...INDIREKT(ADRESSE(ZEILE()+1;SPALTE()-1))..." wird dann "...INDIREKT(Z(1)S(-1)..."
wobei dann schließlich das Ganze mit worksheet.Cells(variable1, variable2 + 1).FormulaR1C1Local = differenzFormel in die betreffende Zelle deponiert wird.
oder dasselbe in englischer Originalformelsprache:
...INDIRECT(RC[-1])...
...INDIRECT(R[1]C[-1])...
und schließlich worksheet.Cells(variable1, variable2 + 1).FormulaR1C1 = differenzFormel

Da ich nicht weiß, wozu die INDIREKT-Funktion verwendet wird, habe ich sie vorhin unverändert übernommen. Falls sie aber nur dazu dienen sollte, die Zelladresse (im Stringformat) aufzulösen, um auf den Zellwert zuzugreifen, dann ist sie bei der vorgenannten Methode überflüssig und man kann die Formel nochmals vereinfachen, indem man die INDIREKT(...)-Funktion weglässt.
Die so resultierende Formel lautet dann:
=WENN(ISTLEER(ZS(-1));"";WENN(UND(NICHT(ISTLEER(Z(1)S(-1)));(ZS(-1)<>"n/a");(Z(1)S(-1)<>"n/a"));ZS(-1)-Z(1)S(-1);"n/a"))
die der Zelle.FormulaR1C1Local-Eigenschaft zuzuweisen ist (unter vorheriger Verdopplung der enthaltenen Anführungszeichen).
oder auf Englisch:
=IF(ISBLANK(RC[-1]),"",IF(AND(NOT(ISBLANK(R[1]C[-1])),(RC[-1]<>"n/a"),(R[1]C[-1]<>"n/a")),RC[-1]-R[1]C[-1],"n/a"))
die der Zelle.FormulaR1C1-Eigenschaft zuzuweisen ist (unter vorheriger Verdopplung der enthaltenen Anführungszeichen).

Falls man in der vorgenannten Formel auf die log. Bedingung der 2. WENN-Funktion das de-Morgan'sche Gesetz
UND(NICHT(a);NICHT(b);NICHT(c)) = NICHT(ODER(a;b;c))
anwendet, kann man sie noch etwas vereinfachen zu:
=WENN(ISTLEER(ZS(-1));"";WENN(ODER(ISTLEER(Z(1)S(-1));(ZS(-1)="n/a");(Z(1)S(-1)="n/a"));"n/a";ZS(-1)-Z(1)S(-1)))
bzw.
=IF(ISBLANK(RC[-1]),"",IF(OR(ISBLANK(R[1]C[-1]),(RC[-1]="n/a"),(R[1]C[-1]="n/a")),"n/a",RC[-1]-R[1]C[-1]))

 

Vielen Dank für diese Informationen :)

Ja, es stimmt, ich arbeite nicht oft mit BASIC und komme eher aus dem Bereich Java, C, Python...
Mit VBA befasse ich mich nur, weil ich ein bestehendes Tool anpassen muss.

Die Adressierung mittels INDIREKT wurde vorgenommen, weil die Formel dynamisch an unterschiedlichsten Stellen eingefügt wird und die Koordinaten nicht immer genau vorhersagbar sind.

Ich werde das jetzt erstmal ausprobieren und mich später dann hier melden.

 

Vielen Dank, Anton!

Du hast gleich mehrere Probleme auf einmal gelöst :)

Die Formel ist nun wesentlich übersichtlicher
Sie Funktioniert tadellos unter Excel2007 und Excel2010
Und ist auch noch unabhängig von der Lokalisierung

(Getestet mit Office2010en, Office2010de, Office2007en, Office2007de)

Jetzt kann ich mir auch den fehleranfälligen Teil des Formel-Zusammenbaus sparen, weil man sie auch so lesen kann.
Ich verwende nurnoch folgenden Code:

Code:

differenzFormel = "=IF(ISBLANK(RC[-1]),"""",IF(OR(ISBLANK(R[1]C[-1]),(RC[-1]=""n/a""),(R[1]C[-1]=""n/a"")),""n/a"",RC[-1]-R[1]C[-1]))"
worksheet.Cells(var1, var2 + 1).FormulaR1C1 = differenzFormel

Vielen Dank!