FormulaArray problem in VBA

Posted by tracy28 on December 17, 2001 3:09 PM

Hi! This is my first-time post to this board - I just discovered it and I'm hoping someone out there can help me!
I am getting the following run-time error: Unable to set the FormulaArray Property of the Range Class. Based on the documentation I've found, it seems to be due to the 255 character limit on FormulaArray formulas using VBA. Does anyone know of a workaround????

Posted by Jacob on December 17, 2001 5:31 PM

What is your formula? Maybe I can shorten it up. Im not sure if there is a way to make excel accept formulas that are that long.

Jacob

Posted by Juan Pablo G. on December 17, 2001 8:52 PM

One VERY UGLY way of doing that is using the fact that the .Formula property is not limited to 255 characters. The solution is pass it as an ordinary formula and then use sendkeys to Edit the formula and "press" Control Shift Enter, something like:

Sub Test()
Range("C1").Formula = "=SUM(A1:A10+B1:B10)"
Range("C1").Select
Application.SendKeys "{F2}^+{ENTER}"
End Sub

But this SHOULD BE the last resource !!

Juan Pablo G.

Posted by tracy28 on December 18, 2001 9:00 AM

Thanks - I will keep that solution in case of emergencies. In this instance, I was able to rename some of my sheets and cell references to get it under the 255. Thanks for the suggestions! One VERY UGLY way of doing that is using the fact that the .Formula property is not limited to 255 characters. The solution is pass it as an ordinary formula and then use sendkeys to Edit the formula and "press" Control Shift Enter, something like: Sub Test()