Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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 "Brett" wrote in message ... 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/comm...id=5ca71f0f-32 52-4bca-b62d-ae2e9ccce263&dg=microsoft.public.excel.programming |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
The exact limit that I'm referring to has to do with the formulaArray
function and is referred to by Microsoft he http://support.microsoft.com/default...b;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? "Peter T" wrote: 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 "Brett" wrote in message ... 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/comm...id=5ca71f0f-32 52-4bca-b62d-ae2e9ccce263&dg=microsoft.public.excel.programming |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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. *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
Maybe something like:
http://www.dicks-blog.com/archives/2...rmulas-in-vba/ (one line in your browser) Depending on what the formula is, you may be able to rewrite it (=sumproduct()??) Brett wrote: 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" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 255 character limit for strings
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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search group of cells for several sets of character strings | Excel Discussion (Misc queries) | |||
Date Formating and building character strings | Excel Discussion (Misc queries) | |||
testing for character strings within a cell | Excel Worksheet Functions | |||
Finding 13 character codes in text strings | Excel Worksheet Functions | |||
Adding additional rows in VBA based on character strings | Excel Programming |