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

Topic

New Reply

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!

Reply | Quote

Replies

Good thing I didn’t buy that Powerbook! I could not live without my string functions.

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.

Reply | Quote

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.

Klaus

Reply | Quote

Hi Klaus,

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

Reply | Quote

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

Reply | Quote

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)”:

MsgBox "blas

Reply | Quote

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)”:

MsgBox "blas

Reply | Quote

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

Reply | Quote

Hi Andrew,

It was nothing special, just

Dim strTest as String
strTest = "Yaddayadda "
strTest = strTest & "more Yadda" 
' and so on...

… 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?).

Klaus

Reply | Quote

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.

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

Reply | Quote

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,” “).

Klaus

Reply | Quote

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,” “).

Klaus

Reply | Quote

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.

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

Reply | Quote

Hi Andrew,

It was nothing special, just

Dim strTest as String
strTest = "Yaddayadda "
strTest = strTest & "more Yadda" 
' and so on...

… 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?).

Klaus

Reply | Quote

Hi Klaus,

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

Reply | Quote

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.

Klaus

Reply | Quote

Good thing I didn’t buy that Powerbook! I could not live without my string functions.

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.

Reply | Quote