Length Limit for Excel Formula in VBA?

[ewong90]

Hi!

I have this really long Excel formula that I need to add to my VBA but for some reasons, it feels like there is a length limit for Excel formulas in VBA?

Thanks

 

[RoryA]

Welcome to the forum.

There's a 255 character limit for the FormulaArray property, but otherwise the limit is the same as if you entered it manually.

 

[ewong90]

Thanks RoryA!

So I'm actually able to put in 990 characters so far in the VBA (I used the Len formula to count the total characters) and the issue arises when I am not able to add in any more characters past 1000-mark.. Do you know a way or a go-around where I can include all of the formula in VBA?

Thanks!

 

[RoryA]

Are you trying to add it all as one line? If so, break it up by concatenating parts, or using a line continuation.

 

[ewong90]

It's a really long formula- not sure how many characters but def more than 1000 characters. How do you use line continuation though?

 

[RoryA]

Like this:

activecell.formula = "first bit of formula," & _
                                       "second bit of formula," & _
                                       " third bit etc."

 

[ewong90]

Oh that would work- but how do I get VBA to recognize "first bit of formula" is etc etc and "second bit of formula" is etc etc?

 

[RoryA]

It doesn't matter what bits go in where, as it all gets concatenated back into one string before it's put in the cell. You can break lines up in the middle of words if you want.

 

[ewong90]

Tried it but doesn't seem to be working. It has runtime error 1004 where it says application-defined or object-defined error. My formula basically goes like this.

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""ABCD"",""ABCD"",IF......," & _
"IF(AND(RC[-43]=""123456"",IF.....," & _
"IF(AND(RC[-43]=""123456"",IF.....," & _
"IF(AND(RC[-43]=""123456"",IF.....," & _

 

[shg]

Put the formula in a string variable and print it to the Immediate window. If it's valid, then you should be able to copy it from there, put Excel in R1C1 mode, paste it in the cell, and see it work.

If you can't, you need to modify the code until you can.