Microsoft Office 2000/Visual Basic Programmer's Guide

The Join Function

After you've finished processing an array that's been split, you can use the Join function to concatenate the elements of the array together into a single string again. The Join function takes an array of strings and returns a concatenated string. By default it adds a space between each element of the string, but you can specify a different delimiter.

The following procedure uses the Split and Join functions together to trim extra space characters from a string. It splits the passed-in string into an array. Wherever there is more than one space within the string, the corresponding array element is a zero-length string. By finding and removing these zero-length string elements, you can remove the extra white space from the string.

In order to remove zero-length string elements from the array, the procedure must copy the non-zero-length string elements into a second array. The procedure then uses the Join function to concatenate the second array into a whole string.

Because the second array isn't created by the Split function, you need to size it manually. It's easy to do, however—you can size it initially to be the same size as the first array, then resize it after you've copied in the non-zero-length string elements.

Function TrimSpace(strInput As String) As String
   ' This procedure trims extra space from any part of
   ' a string.

   Dim astrInput()     As String
   Dim astrText()      As String
   Dim strElement      As String
   Dim lngCount        As Long
   Dim lngIncr         As Long
   
   ' Split passed-in string.
   astrInput = Split(strInput)
   
   ' Resize second array to be same size.
   ReDim astrText(UBound(astrInput))
   
   ' Initialize counter variable for second array.
   lngIncr = LBound(astrInput)
   ' Loop through split array, looking for
   ' non-zero-length strings.
   For lngCount = LBound(astrInput) To UBound(astrInput)
      strElement = astrInput(lngCount)
      If Len(strElement) > 0 Then
         ' Store in second array.
         astrText(lngIncr) = strElement
         lngIncr = lngIncr + 1
      End If
   Next
   ' Resize new array.
   ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

   ' Join new array to return string.
   TrimSpace = Join(astrText)
End Function

This procedure is available in the modStrings module in VBA.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

To test the TrimSpace procedure, try calling it from the Immediate window with a string like the following:

? TrimSpace("  This  is  a  test  ")

Tip   To see the elements in each array while the code is running, step through the procedure, and use the Locals window to view the values contained in each variable.