Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=392071 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 "xcelion" wrote in message ... 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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=392071 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
If that situation arises close the file without saving.
I should of course have added - or SaveAs newname Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 wrote: 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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=392071 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=392071 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel Formula Limit
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I limit formula results to positive only? | Excel Worksheet Functions | |||
Limit on array formula | Excel Worksheet Functions | |||
Create an if-then formula in Excel to limit column total? | Excel Discussion (Misc queries) | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
How can i work a formula for time limit? | Excel Worksheet Functions |