Highlighted
Honored Contributor.
Honored Contributor.
1492 views

Excel: FormulaArray

Jump to solution

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
 
Description for it:
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?


 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Honored Contributor.
Honored Contributor.

RE: Excel: FormulaArray

Jump to solution

i found the answer:

i have to use this spelling:

{=Summe(Wenn((G13:G349="Ot")*(H13:H349="Lag");E13:E349))}

with the german words...

View solution in original post

0 Likes
1 Reply
Highlighted
Honored Contributor.
Honored Contributor.

RE: Excel: FormulaArray

Jump to solution

i found the answer:

i have to use this spelling:

{=Summe(Wenn((G13:G349="Ot")*(H13:H349="Lag");E13:E349))}

with the german words...

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.