FormulaArray..... HELP !!!

[Pierre]

Basicely, here is my problem : try to run this sub in a new sheet :

Sub test()
Range("D10:N10").Select
Selection.FormulaArray =
=fpfp(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a
14,a15,a16,a17,a18,a19,a20,a21,a22)"
End Sub


As the function fpfp is not defined, it should return a #NAME.

Now, add another argument at the end of the function call : a23
The string is not very long : for sure less than 100 chars.

Then you should obtain an msgbox runtime error 1004 ("Unable to set th
FormulaArray property of the Range class")

So my simple question is : How can I do it ?????
:confused

 

[Charles Williams]

see response in previous thread:

Its about 256 characters in R1C1 mode, which is what it uses internally.

Even Excel2003 still seems to have this limit.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Pierre]

Thanks a lot William.

So basicely, I am stuck !!!

#?!£$%# Excel !!!
It is very annoying !

They should focus on this kind of problem instead of adding some ne
fancy functionalities !!!! :mad

 

[Dana DeLouis]

So basically, I am stuck !!!

I really don't understand it, but I remember "something" about this. Can't
find it in my notes. Here is just an idea. (General idea, and not A1:A24)

Basically, you need...

Sub Test()
Range("D10:N10").FormulaArray = "=fpfp($a$1,$a$2,$a$3,$a$4,...$a$23,$a$24)"
End Sub


Sub Bad()
Dim rng
With Range("D10:N10")
rng = [a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14,
a15, a16, a17, a18, a19, a20, a21, a22, a23, a24].Address(False, False)
.FormulaArray = Replace("=fpfp(#)", "#", rng)
End With
End Sub


Sub Good()
Dim rng
With Range("D10:N10")
rng = [a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14,
a15, a16, a17, a18, a19, a20, a21, a22, a23, a24].Address
.FormulaArray = Replace("=fpfp(#)", "#", rng)
End With
End Sub

 

[Charles Williams]

Like Dana, I seem to remember a bypass existed to the max 256 character
formula problem but I cant find it ...

some ideas that may help:
- use short names for the ranges.
- use absolute rather than relative because its shorter in R1C1 mode.
- use short sheet names (rename afterwards if required)


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm