Building Simple Lists Using Strings in VBA

by Andrew Savikas, author of Word Hacks
11/16/2004

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:

Dim sParasToFind as String
sParasToFind = _
	"/Heading 1/List Bullet/Body Text Indent 3/"

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.

Here's the complete macro:

Sub LookForSomeParasUsingAStringList()
Dim sParasToFind As String
Dim para As Paragraph
 
sParasToFind = _
	"/Heading 1/List Bullet/Body Text Indent 3/" 
For Each para In ActiveDocument.Paragraphs
	If InStr(sParasToFind, _
			"/" & para.Style & "/") <> 0 Then
		' Do stuff to para here
	End If
Next para 
End Sub

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:

Sub LookForSomeParasUsingString()
Dim sParasToFind As String
Dim para As Paragraph
 
sParasToFind = _
	";Heading 1;List Bullet;Body Text Indent 3;"
 
For Each para In ActiveDocument.Paragraphs
	If InStr(sParasToFind, _
			";" & para.Style & ";") <> 0 Then
		' Do stuff to para here
	End If
Next para
 
End Sub

Related Reading Word Hacks Tips & Tools for Taming Your Text By Andrew Savikas

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.

Pages: 1, 2

Next Page