Unable to set FormulaArray property of the Range class

[tempest0123]

I am trying to update cell formulas (R1C1) using the FormulaArray
property and I noticed that if the cell formulas contains more than
one references to external workbooks, I will get this "Unable to set
FormulaArray property of the Range class" error message.

For example:
Suppose I want to update cells A1 and C3 with the following formulas
respectively:
='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1
='\\someserver\somewhere\[file_1.xls]Sheet1'!R3C3

The following code will work and the formulas will be inserted into
cells A1 and A3.
Dim strFormulas() as String
Redim strFormulas(1 to 3, 1 to 3)
Range(Cells(1,1), Cells(1,3).FormulaArray = strFormulas

However, if I use a formula such as the following that has more than
one external references, I will get an error message.
='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1+'\\someserver\somewhere\[file_1.xls]Sheet1'!R10C10

It looks to me this is some kind of bug in Excel VBA because the
formulas I'm trying to insert is valid and all external references are
fine.

It would be greatly appreciated if anyone can provide a solution to
this issue.

Thanks.

 

[Tom Ogilvy]

Your string is 100 characters, so I assume it is only representative of the
string you are trying to use. FormulaArray will only accept a string of 255
characters - so perhaps that is your problem.

I haven't tried it, but put in a dummy sheet named AB C (with a space)

then put in your array formula in code using something like

"='AB C'!R1C1+'AB C'!R10C10"

then in your code use the range Find method to replace AB C with the
external file argument
\\someserver\somewhere\[file_1.xls]Sheet1

 

[tempest0123]

Your string is 100 characters, so I assume it is only representative of the
string you are trying to use. FormulaArray will only accept a string of 255
characters - so perhaps that is your problem.

I haven't tried it, but put in a dummy sheet named AB C (with a space)

then put in your array formula in code using something like

"='AB C'!R1C1+'AB C'!R10C10"

then in your code use the range Find method to replace AB C with the
external file argument
\\someserver\somewhere\[file_1.xls]Sheet1

Thanks for the quick reply.
And you are right about the maximum string length.