Unable to set FormulaArray property of the Range class

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.

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


--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> 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\[f
ile_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.
>

On Mon, 17 Oct 2005 15:08:45 -0400, "Tom Ogilvy" <[email protected]>
wrote:

>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.