+ Reply to Thread
Results 1 to 8 of 8

VB 255 character limit for strings

  1. #1
    Brett
    Guest

    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. #2
    Peter T
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Brett
    Guest

    Re: 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 here:

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    Edward Ulle
    Guest

    Re: 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. #5
    Brett
    Guest

    Re: 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. #6
    Edward Ulle
    Guest

    Re: 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. #7
    Dave Peterson
    Guest

    Re: VB 255 character limit for strings

    Maybe something like:

    http://www.*****-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. #8
    Edward Ulle
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1