VBA Excel Formula Limit

[xcelion]

Hi All,

Iam writting a formula using Cells.Formula property.The formula string
has almost 800 characters.But i am not finding any probelm and it's
working fine in both excel 2000 and 2003.But i came to know from the
various web sources that the limit for fomula witten using VBA is 255
characters and the limit when the user types is 1024.But i have no
problem with 800 characters and it's working fine

So any one can please provide an answer for this ?

IS there any limit for the no of characters in formula written using
VBA or is is same as the limit when the user types the formula

Thanks in advance
Xcelion

 

[Paul Martin]

Looking at a posting by John Green in 1999, I find his experimenting
still valid today. You'll get about 900 characters using A1 notation
and 1024 characters using R1C1 notation. I didn't find any difference
using VBA or manual.

Regards

Paul Martin
Melbourne, Australia

 

[Peter T]

Limit of Formula entered as a string manually or with VBA is 1024, though
could be a bit less.

The formula could get much longer than 1024 if, say, it contains renamed
longer sheetnames or linked file names. It will still work fine until you
come to reedit. A truncated and probably erroneous formula will get entered.

If that situation arises close the file without saving. Reopen, shorten
names, edit the formula, then rename the longer names if required.

Regards,
Peter T

 

[Peter T]

If that situation arises close the file without saving.

I should of course have added -

or SaveAs newname

Peter T

 

[Dave Peterson]

One more...

This worked ok for me in xl2003:

Option Explicit
Sub testm()
Dim myFormula As String
Dim iCtr As Long

myFormula = "=A1"
For iCtr = 2 To 200
myFormula = myFormula & "+A" & iCtr
Next iCtr

MsgBox Len(myFormula)

Range("b1").Formula = myFormula
End Sub

Any chance you're looking at .formulaArray?

 

[xcelion]

Hi All,

Thanks all for your replies. Hi Dave ,I was using the not using
forumla array.
For me also there was no problem .I just want to confirm the case of
formula limit


So should i assume the conclusion as

\"LIMIT OF FORMULA ENTERED AS A STRING MANUALLY OR WITH VBA IS 1024\"

Thanks all
Xcelion

 

[Peter T]

So should i assume the conclusion as

\"LIMIT OF FORMULA ENTERED AS A STRING MANUALLY OR WITH VBA IS 1024\"

I would embrace that statement with "Absolute" and "though could be a bit
less."

It's possible to contrive a formula with length of 1024 even with A1
notation (I posted one in this ng of almost that). However others have
reported a slightly lower limit in some scenarios.

Regards,
Peter T