Hello,
i habe a problem to set a FormulaArray in Excel.
I get following Error: "Unable to set the FormulaArray property of the Range class."
When i search for that error:
I get an MS Articel that the error occurs when the Formula is longer then 255 characters. But my Formula is shorter.
The Formla written looks like this: (German writing)
{=Summe(Wenn((G13:G349="Ot")*(H13:H349="Lag");E13:E349))}
When i Record the Formula i get this in the MacroEditor:
Selection.FormulaArray = _ "=SUM(IF((R[-339]C[2]:R[-3]C[2]=""Ot"")*(R[-339]C[3]:R[-3]C[3]=""Lag""),R[-339]C:R[-3]C))"
I build in my program a display that i see the generated Form and it is exact the same as in the Macro generated by Excel.
Cobol-Code i use:
compute tab-zeile(xls-idx) = tab-zeile(xls-idx) 1.
move tab-zeile(xls-idx) to zeile.
move "E" to spalte.
string '=SUM(IF((R[-' str-r1
']C[2]:R[-' str-r2
']C[2]=""' hlp-art
'"")*R[-' str-r1
']C[3]:R[-' str-r2
']C[3]=""' "Lag"
'""),R[-' str-r1
']C:R[-' str-r2
']C))' delimited by "¿" into xls-matrix.
perform write-mat.
***********************************************************
write-mat section.
inquire xls-sheet @CELLS::@ITEM(zeile, spalte) xls-range.
modify xls-range @FormulaArray xls-matrix.
destroy xls-range.
move spaces to xls-matrix.
write-mat-ende.
exit.
write-mat-e.
***********************************************************
In an Delphi-Forum i found some other infos, but i don't know if i can use it for Cobol:
Please try to test the following code:
| try |
| App.Selection.FormulaArray := '=' Formula; |
| except |
| App.Selection.FormulaArray := '=' Formula; |
| end; |
Frankly speaking I don't know exactly why a without-parameters function works at first attempt while a with-parameters function does not. But I know for sure that Excel does not load UDF add-ins immediately at start up, it loads them on request (e.g. when the user enters your formula). So, I can assume that something prevents Excel from running a with-parameters function right away and we need to kick it once again.
Has anybody an idea how to handle the FormulaArray?
