Building Simple Lists Using Strings in VBA

Visual Basic for Applications (VBA), the language used for scripting Microsoft
Word, isn't really known for its string-processing capabilities. You won't even
find regular expression support in VBA (though you can bolt it on by referencing
VBScript's RegExp object, something I discuss in my book, Word Hacks). But
sometimes some string hacking is a quick and convenient way to solve a problem,
and the string functions VBA does provide are often up to the task.

Using Strings for Simple
Lists

I often write macros that look for paragraphs styled with one of several different
styles, such as any paragraph that's styled as Heading 1, List
Bullet, or Body Text Indent 3. A traditional approach to
this would be to build a simple array of those three styles, and then test
each paragraph's style for membership in the array, as the following code shows.
Note that an underscore ( _ ) is the line-continuation character in
VBA, indicating an optional line break.

Sub LookForSomeParas()
Dim vParasToFind() As Variant
Dim k As Integer
Dim bIsInList As Boolean
Dim para As Paragraph
vParasToFind = Array("Heading 1", _
"List Bullet", _
"Body Text Indent 3")
For Each para In ActiveDocument.Paragraphs
bIsInList = False
For k = 0 To UBound(vParasToFind)
If para.Style = vParasToFind(k) Then
bIsInList = True
Exit For
End If
Next k
If bIsInList Then
' Do stuff to paragraph
End If
Next para
End Sub

A different method, and one that'll have you typing less than half the lines
of code, uses a string to store the list of styles, as this snippet shows:

To use this kind of list, a macro would visit each paragraph, then check the
list to see whether the paragraph's style is in it, using the built-in VBA InStr
function. If the string doesn't appear in this string list, InStr
returns 0.

To be sure you don't get a false match when one style's name is actually part
of another's (accidentally flagging Body Text when you're looking
for Body Text Indent 3, for instance), a delimiter is included to mark the beginning
and end of each entry in the string list.

While this may not be the best choice in every situation, it's a handy hack
for quick-and-dirty list-membership tests, and as a bonus, it's more than twice
as fast as the previous method.

I've used a slash ( / ) as the delimiter in this example, because
it seems like a logical choice that most people can quickly recognize as a
separator. But in the case of testing names of Word styles, it's actually not
the best choice. Why? Well, / is a perfectly valid character for use in a style
name, which could cause incorrect results for the membership test. When using
this technique, it's best to choose a character that won't appear anywhere
in any of the entries in the list, or any of the items you'll be
checking for membership in that list. A better choice for this particular example
would be a semicolon, which Word doesn't allow as a style name. Making that
change results in the following:

These string lists are useful in other situations, and I'll come
back to them in the last section of this article. The next section
discusses how to make up for the string-processing shortcomings of Word on
the Macintosh and in Word 97.

String Processing (or the Lack Thereof) with Word for Macintosh and Word 97

Anyone who's tried to develop VBA macros for use with Word for Macintosh knows
that it's a very different landscape than Word for Windows. It can be maddening
to work around all the minor (and major) bugs and glitches in VBA on Word for
the Macintosh. But at a company like O'Reilly, unlike in a more rigid corporate
environment, we need to be able to accommodate authors and editors working
on a variety of platforms and a variety of versions: Word 97, 2000, 2002, and
2003 for Windows; Word X for Mac OS X; and for a few users, even Word on
Linux, using Crossover Office.

One of the biggest gotchas when working with Word for the Mac (and Word 97
for Windows, for that matter) is working around some conspicuously absent string
functions.

With Word 2000, Microsoft included several important string-processing functions
with VBA, including two very useful ones common in other scripting languages,
Split and Join. Split turns a string into an array, separating the string
at a given delimiter, by default a space. Join is Split’s complement,
concatenating an array of strings into a single string and separating the smaller
strings with a given delimiter, also a space by default.

But now it’s 2004, and Word VBA on a Macintosh still doesn’t include
those functions. And of course, anyone still plugging away on Word 97 (I know
more than a few) can’t run macros that use those functions either.

In addition to being invaluable tools for general use in writing Word macros,
these functions are an important part of one solution to another Mac VBA problem,
discussed in the next section, along with those string lists I discussed above.