VB 255 character limit for strings

[Guest]

This bug, which is well reported and even acknowledged my MS, has no business
existing in the year 2006. Is there any work-around or patch available?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ccce263&dg=microsoft.public.excel.programming

 

[Peter T]

For the benefit of novices who might stumble on this thread, VBA does NOT
have a string length limit of 255.

In Excel there are string length limitations ranging from 255 to 32K
depending on what you are using or doing. These limits are not directly
related to VBA.

Not sure what the limit is in VBA, several million at least (for what
purpose) and possibly only limited by resources.

Regards,
Peter T

This bug, which is well reported and even acknowledged my MS, has no business
existing in the year 2006. Is there any work-around or patch available?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=5ca71f0f-32
52-4bca-b62d-ae2e9ccce263&dg=microsoft.public.excel.programming

 

[Guest]

The exact limit that I'm referring to has to do with the formulaArray
function and is referred to by Microsoft here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213181

The article made it seem like the issue was because VBA is storing the
formulas as strings. I'm sorry if I mispoke, but the issue still remains.
Why is there a character limit anywhere for anything?

 

[Edward Ulle]

As noted in the Microsoft article, this limitation does not apply to
Formula.

So simply loop through each cell of range and apply the formula.

For Each cCell in Range("A1:B3")
cCell.Formula="=SomeLongFormula()"
Next

 

[Guest]

I've tried that, but since the call is for formula instead of formulaArray,
it then copies the formula and implements it in excel as if one pressed enter
instead of ctrl+shift+enter.

 

[Edward Ulle]

Are we talking about the same thing?

The FormulaArray method can populate a range with the same formula.

Or are you talking about Array Formulas which work on such things as a
two dimensional array? In which case I believe the CNTRL-SHIFT-ENTER is
simply used to manually select a range a cells for the Array Function.

 

[Dave Peterson]

Maybe something like:

http://www.dicks-blog.com/archives/2005/01/10/entering-long-array-formulas-in-vba/
(one line in your browser)

Depending on what the formula is, you may be able to rewrite it
(=sumproduct()??)

 

[Edward Ulle]

In the example pointed out by Dave, much of the formula are ranges. The
equation can be reduced to something like this.

As long as you know the limitation of a programming environment there
are almost always ways around it.

Option Explicit

Sub Test()

Dim X_TABLE_FORMULA As String
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range

Set r1 = Worksheets("HISTORIC_DATA").Range("E5:E1130")
r1.Name = "Range1"
Set r2 = Worksheets("HISTORIC_DATA").Range("C5:C1130")
r2.Name = "Range2"
Set r3 = Worksheets("HISTORIC_DATA").Range("H5:K1130")
r3.Name = "Range3"
Set r4 = Worksheets("HISTORIC_DATA").Range("B5:B1130")
r4.Name = "Range4"

X_TABLE_FORMULA =
"=SUM(IF(B$1=Range1,IF($A2=Range2,Range3,0)))/SUM(IF(B$1=Range1,IF($A2=R
ange2,Range4,0)))"
Range("A1").FormulaArray = X_TABLE_FORMULA

End Sub