1. Technology
Amazing Splits
Check out this useful technique using the Split function!
 Have a technique you would like to share?
Tell Us About It!
 

From the Grand Wizard, yea verily, the Vizier of VB, Peter Zilahy Ingerman, PhD, comes this absolute gem of VB programming. I had to scratch my head for a few minutes to figure out why it works. But I have verified it in VB 6, VBA for 2000 and 2003 (actually, Excel in those environments) and VB.NET 1.1. Furthermore, I Googled for about a half hour trying to find this tip somewhere else. As far as I can tell, this is an original.

It is cool.

Here's the basic suggestion as received from Peter.

Suppose you had an array of strings, each one of which had several sub-strings, all delimited by some separator (say "|", for example).

Suppose, further, that you were interested in running down this array of strings in order to examine, say, the second substring in, counting from 0 and from the left.

Peter decided to try an experiment, and discovered, much to his pleasure, that it worked. The expression:

split("A|B|C|D", "|") (1)

very nicely yields the string "B"!

Note - The complete VB 6 code using a command button is ...

Private Sub Command1_Click()
Dim myStr As String
    myStr = Split("A|B|C|D", "|")(1)
    MsgBox myStr
End Sub

So ... why does this work? To answer that question, let's first be sure we understand the Split function.

Split() lets you split a delimited string into an array (variant in VB 6 and VBA). By default, Split() uses a space as the delimiter. So, the following code:

myArray = Split("Oh what a beautiful morning!")

... creates an array of five items where each element of the array holds a single word from the string. A very handy and underappreciated function!

For example, Peter also created this useful permutation to use Split. Suppose we have a sentence, written according to the usual rules for sentences (that is, words are separated by spaces, and punctuation marks are next to words with no intervening space). Then suppose this sentence is in a string myStr. Then the number of words in the sentence is simply this statement.

Ubound(Split(myStr)) + 1

Note: Again using a command button and the paragraph above as a string to test with, the complete VB 6 code is ...

Private Sub Command1_Click()
Dim myStr As String
    myStr = "For example, <the paragraph follows ...>
    MsgBox UBound(Split(myStr)) + 1
End Sub

This yields the number 62, the number of words in the paragraph. (Count 'em yourself and see!)

But since we're depending on particular characteristics of words in a paragraph, it's possible for things to 'go wrong'. For example, if we use two spaces between words as we all learned to do in typing class forty years ago (at least, that's what I was taught - forty years ago), we get a much larger number because each space delimits - even when they occur together. To solve this problem, you have to replace every instance of two spaces with one. Something like this:

UBound(Split(Replace(myStr, Space$(2), Space$(1)))) + 1
And, to catch spaces that might occur before and after the words, we might want to use:
UBound(Split(Trim$(Replace(myStr, Space$(2), Space$(1))))) + 1

Depending on how carefully we want to count words, 'fixes' like this could be added for quite a while (for example, two spaces together will be counted correctly, but three will not). But as Peter wrote, "There are limits to even my pedantry."

Microsoft's documentation seems to be, as usual, written only for those who already understand the function since it's not that easy to understand. Since the Microsoft documentation is almost as much of a problem as understanding Split itself, let's consider our use of split in the context of their documentation. This program was copied from Microsoft's site:

Dim intX As Integer
' Declare and populate an integer array.
Dim countersA(5) As Integer
For intX = 0 To 4
   countersA(intX) = intX
Next intX
' Declare and populate a string array.
Dim countersB(5) As String
For intX = 0 To 4
   countersB(intX) = "hello" & CStr(intX)
Next intX
Dim arrX(2) As Variant
' Declare a new two-member array. 
' Populate the array with other arrays.  
arrX(1) = countersA()   
arrX(2) = countersB()
' Display a member of each array.
MsgBox arrX(1)(2)   
MsgBox arrX(2)(3)

As you will see if you run this code, each MsgBox displays one of the elements of the first array, arrX. Applying this knowledge to Peter's little gem:

  1. Split creates a four element array filled with "A", "B", "C", and "D"
  2. The second number in parenthesis pulls out the appropriate element.

Now ... Peter and I have a running gun battle about VB 6 versus VB.NET. So, just to keep Peter slightly humble, let me point out that in VB.NET, Split is a member of both the String and RegEx (Regular Expressions) classes and can be overloaded with a variety of parameter strings, including:

String.Split(Char[])
String.Split(Char[], Int32)
Regex.Split(String, Int32)
Regex.Split(String, Int32)
Regex.Split(String, Int32, Int32)
Regex.Split(String, String)
Regex.Split(String, String, RegexOptions)

The one that lets VB.NET duplicate VB 6 is next from the bottom. So if Dr. Ingerman can find clever little tricks like this with the (A-hem ... ) "limited" capabilities of VB 6, I can't even imagine what he's going to find in VB.NET.

Thanx to the Vizier of VB, Peter Zilahy Ingerman, PhD!

©2014 About.com. All rights reserved.