Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Strings (and a bit about Mac compatibility) (V

    Hello,

    If you're interested, please check out my article about using strings in VBA as pseudo-arrays, posted at the windows dev center. The article also includes some info on adding some missing string functions to VBA for a Mac, and about using those string functions to get around the limits on prodcedure lengths in VBA for a Mac.

    Cheers!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    Good thing I didn't buy that Powerbook! I could not live without my string functions. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I only rarely use the delimited string technique you illustrate in your article. I have found it most useful when working with strings of identical length that correspond to numbers, such as Sun|Mon|Tue|Wed|Thu|Fri|Sat, and the months, because you can use a formula to easily retrieve the matching value. But I agree that it also is a great way to test membership in a (modestly-sized) set.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    The limit on lines (or characters?) per procedure mentioned in the article might be pretty much the same in the Windows version.

    I recently had to test some Russian text for prepositions. Since the VBA editor can't deal with cyrillic text, I had to write them out like
    strPreps = "/" & ChrW(&H43C) & ChrW(&H435) & ChrW(&H436) & _
    "/" & ...

    After just a few dozen lines (certainly much, much less than 1000), VBA balked.

    <img src=/S/frown.gif border=0 alt=frown width=15 height=15> Klaus

  4. #4
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    Hi Klaus,

    Can you post a procedure that won't compile? I'd love to take a look.

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    I recently had a problem with some VBA code that contained the combination

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    Hi Andrew,

    It was nothing special, just <pre>Dim strTest as String
    strTest = "Yaddayadda "
    strTest = strTest & "more Yadda"
    ' and so on...
    </pre>

    ... and at some point, I got error messages "Procedure too large".

    I just tried to reproduce the problem, and the procedure had to be larger than 64 kB(?) to raise the error.
    I'm pretty sure my procedure wasn't even close to that size, but maybe I'm wrong.

    Another limit I ran into was the max number of line continuations ("_") in a "line" of code (which seems to be 24?).

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Klaus

  7. #7
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    Hi Klaus,

    Very intreresting problem indeed. (BTW, you're right about the limit on line-continuation characters, though I can't remember the exact number offhand.)

    I was able to replicate the problem you described, and got the same error with less than a thousand lines (as you described). But I know I've written longer procedures before (don't ask), so I took a closer look. My theory is that it's not a limit on the number of characters in the procedure itself, but rather in the compiled byte code. And all those calls to ChrW() must really add up to the compiler.

    I doubt you still need a solution to the problem, but it turns out that adapting the technique I described in my article allows for a much longer procedure (and therefore a longer final string). I stopped testing at 5000+ lines, though I suspect at least 10 times that would still work OK.

    With this version, there's only one explicit call to ChrW(), and that same call is merely repeated with the loop.

    <pre>Sub SuperLongString()
    Dim vCharCodes As Variant
    Dim v As Variant
    Dim strCharCodes As String
    Dim strResult As String

    strCharCodes = strCharCodes & "436 43C 435 "
    strCharCodes = strCharCodes & "436 43C 435 "
    ' and so on ...
    ' ... 5000+ lines worked OK for me
    strCharCodes = strCharCodes & "436 43C 435 "

    ' Remove extra space at the end
    strCharCodes = Trim(strCharCodes)

    vCharCodes = Split(strCharCodes)
    For Each v In vCharCodes
    strResult = strResult & " " & ChrW(CLng("&H" & v))
    Next v

    MsgBox "final string is " & Len(strResult) & " characters long!"

    End Sub
    </pre>


  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    My own suspicion was that the famous "template bloat" might have been responsible for running into the error with a comparatively small macro (that I had edited heavily): The VBA editor might not clean up all those insertions and deletions properly.

    Your suggestion is really clever...
    I had planned to put string ressources like this in a separate Unicode text file next time.

    Another error message I ran into when building strings like this was "Insufficient string space".
    To get around that one, I defined a sufficiently large string early on in the macro (say, str=String(500000," ").

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Klaus

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Stuttgart, Baden-W, Germany
    Posts
    931
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Strings (and a bit about Mac compatibility

    Hi Stuart,

    That's a weird one all right!
    I can reproduce the bug if I change "Tools > Options > Editor Format > Font" to some Far-East font, say "Dotum (Hangul)":<pre>MsgBox "blas

Posting Permissions

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