Golfing code is the practice or activity of reducing usable, functional code to the smallest number of characters or bytes that you can, while still maintaining the code's original functionality.
I participate in golfing (and other) challenges at
CodeGolf.SE (part of the
StackExchange network), so from time to time, I will set aside some effort to elaborate on certain solutions I provide.
Today's tip can also be found by following
this link.
When you're done reading, see what else you can do with
Strings!
Sometimes it can be useful to break apart a string into individual characters, but it can take a bit of code to do this manually in VBA.
ReDim a(1 To Len(s))
' ReDim because Dim can't accept the non-Const value Len(s)
For i = 1 To Len(s)
a(i) = Mid(s, i, 1)
Next
Instead, you can use a single line, relatively minimal chain of functions to get the job done:
a = Split(StrConv(s, 64), Chr(0))
This will assign your
String s to a
Variant array
a. Be careful, though, as the last item in the array will be an empty string (
""), which will need to be handled appropriately.
Here's how it works: The
StrConv function converts a
String to another format you specify. In this case, 64 =
vbUnicode, so it converts to a unicode format. When dealing with simple ASCII strings, the result is a null character (not an empty string,
"") inserted after each character.
The following
Split will then convert the resulting
String into an array, using the null character
Chr(0) as a delimiter.
It is important to note that
Chr(0) is not the same as the empty string
"", and using
Split on
"" will not return the array you might expect. The same is also true for
vbNullString (but if you're golfing, then why would you use such a verbose constant in the first place?).
