oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Building Simple Lists Using Strings in VBA
Pages: 1, 2

As an example of how handy that Split function can be, let’s take a look at style aliases in a document. A style alias is an additional name you can give to a style. For example, given the style Heading 1, you could add an alias, h1, so that you can just type h1 into the Styles pull-down menu on the Formatting toolbar to apply the Heading 1 style. In that case, the Heading 1 style would be listed as Heading 1,h1.

Style aliases are added to a style name by putting a comma at the end of the style name, followed by an alias. You can even add multiple aliases, such as Heading 1,HeadA,h1.

It’s often helpful to remove the aliases from all the styles in a document, particularly before exporting the document to another program, such as QuarkXPress or FrameMaker, which may not interpret them correctly. The Split function makes it very easy to do, as the following macro shows:

Sub RemoveAllStyleAliases
Dim sty As Style
For Each sty In ActiveDocument.Styles
	sty.NameLocal = Split(sty.NameLocal, ",")(0)
Next sty
End Sub

The above code uses the Split function to get only the part of the style name before the first comma (which is the first element in the array returned by Split, accessed by its offset, 0), and it’s a handy utility function to include in a distributed template. But if some of those people you’re distributing it to are running Word on a Mac or are still on Word 97, they’ll be greeted with compilation errors when they try to use the macro.

So what’s the solution? Create your own versions of those important string functions. Here’s one version of a Split function written using VBA that will work on a Mac or with Word 97:

Function Split(ByVal strIn As String, _
	Optional ByVal strDelim As String = " ", _
	Optional ByVal lCount As Long = -1) _
		As Variant
Dim vOut() As Variant
Dim strSubString As String
Dim k As Integer
Dim lDelimPos As Long

k = 0
lDelimPos = InStr(strIn, strDelim)

Do While (lDelimPos)
	' Get everything to the left of the delimiter
	strSubString = Left(strIn, lDelimPos - 1)
	' Make the return array one element larger
	ReDim Preserve vOut(k)
	' Add the new element
	vOut(k) = strSubString
	k = k + 1
	If lCount <> -1 And k = lCount Then
	Split = vOut
	Exit Function
	End If
	' Only interested in what's right of delimiter
	strIn = Right(strIn, (Len(strIn) - _
	(lDelimPos + Len(strDelim) - 1)))
	' See if delimiter occurs again
	lDelimPos = InStr(strIn, strDelim)

' No more delimiters in string. 
' Add what's left as last element
ReDim Preserve vOut(k)
vOut(k) = strIn

Split = vOut
End Function

And here’s a Join function, again written using code that will work with Word on a Mac or with Word 97:

Function Join(ByVal vIn As Variant, _
	Optional ByVal strDelim As String = " ") _
		As String

Dim strOut As String
Dim k As Long
Dim lUpperBound As Long

lUpperBound = UBound(vIn)
For k = LBound(vIn) To (lUpperBound - 1)
	strOut = strOut & vIn(k) & strDelim
Next k

' Don't want to add delimiter after last element
strOut = strOut & vIn(lUpperBound)
Join = strOut
End Function

Fortunately, you don’t really have to write all of your own from scratch. Several web sites offer versions of these and other useful VB6 string functions (such as Replace and InStrRev) that you can use in your own macros. One such site is Note that if you want to use the string functions from that particular site with Word on a Mac, you’ll need to remove the Optional bCompare argument, and all the related code, from each one that uses it.

The Split function also comes in handy when working with string lists, as discussed earlier.

These string lists act like pseudo-arrays, without the extra code needed to build up a proper array. The lists still behave a lot like arrays, and can even be accessed by offset. For example, given the list:

sBunnies = "/flopsy/mopsy/cottontail/"

you can get to mopsy by using the Split function, as the following snippet shows:

MsgBox Split(Mid(sBunnies, _
			2, Len(sBunnies) - 2), "/")(1)

The Mid function is needed here to trim off the leading and trailing "/" from sBunnies. Once that's done, you can just use Split to get at the elements of the pseudo-array by offset. And once you've used the Split function, your list actually becomes a "real" array, because that's what Split returns—a variant of type Array. Just remember that the array returned by Split always has an offset of 0.

Simple String Lists, the Split Function, and Lengthy Collection Assignments

Like many distributed custom Word templates, the one we use at O'Reilly includes a significant amount of validation code. These procedures are used to help ensure that authors stick to a certain group of styles, to control which styles are available based on which book series the author is writing for, and to control certain template features, like context-sensitive formatting, which I discuss in my book.

All of this validation means that the template needs to "know" quite a bit about which styles meet certain criteria. That information isn't part of the built-in Word Styles collection, of course, so it's stored in a separate, custom-built class. The class is created dynamically when it's needed, using information stored in a Collection, kind of like the following simplified example:

Sub CollectionDemo()
Dim colStyles As Collection
Dim col As Collection
Set colStyles = New Collection
Set col = New Collection

col.Add Key:="AllowedInTemplate", Item:=True
col.Add Key:="UsesSmartQuotes", Item:=True
colStyles.Add Key:="Heading 1", Item:=col

MsgBox colStyles("Heading 1")("UsesSmartQuotes") 
' Above MsgBox Displays "True"
End Sub

The styles collection is then used to initialize the custom class, which allows other macros in the template to query that class for information on a particular style, such as whether paragraphs using that style should use "smart" (curly) or "straight" quotation marks.

When the custom class needs to contain information on the 100 or so styles used in the template, and each style has a dozen or so of these custom properties, it requires a very lengthy collection assignment statement, on the order of 1,000 lines or more. Not too much fun to type out, but Word VBA for Windows handles it just fine. But try a collection assignment that long on a Mac, and you get a cryptic error message. As best I could deduce, there was a limit to the number of lines of code I could include in a single procedure with VBA on a Mac. Breaking up the single, massive collection assignment into several smaller routines eliminated the error, but it felt like a kludge.

The fix I finally settled on—perhaps still a kludge, but at least a more interesting one—was to use a string list, like the ones described in the first section, "Using Strings for Simple Lists." All of the data about the styles is kept in a single string list, like this:

Dim sList As String
sList = sList & ";;Heading 1;;True;;True;;"
sList = sList & ";;Heading 2;;True;;False;;"
sList = sList & ";;Body Text;;False;;True;;"

Note that when presented like this, our string list begins to strongly resemble a table, which is the goal.

With this method, the data about each style takes up just one line of code, instead of the several needed to add it to the collection directly, like in the CollectionDemo example above. So how do you get the data from a "table" like this into a collection? By parsing it with the Split function, as shown in the following macro:

Sub StringListToLoadCollectionDemo()
Dim sDataTable As String
Dim colStyles As Collection
Dim col As Collection
Dim vDataRows As Variant
Dim v As Variant
Dim vDataFields As Variant
Set colStyles = New Collection
' Only need one line of code needed per style
sDataTable = sDataTable & _
	";;Heading 1;;True;;True;;"
sDataTable = sDataTable & _
	";;Heading 2;;True;;False;;"
sDataTable = sDataTable & _
	";;Body Text;;False;;True;;"
' All the code below stays the same, 
' regardless of the number
' of syles added to the "data table" above.

' Remove the leading and trailing ";;" 
' from the big string, otherwise first 
' and last row of the "data table" will 
' have one extraneous pair of semicolons, 
' either at beginning or end
sDataTable = Mid(sDataTable, 3, _
				Len(sDataTable) - 4)
' Parse the "data table"
vDataRows = Split(sDataTable, ";;;;")
For Each v In vDataRows
	 vDataFields = Split(v, ";;")
	 Set col = New Collection
	 col.Add Key:="AllowedInTemplate", _
	 col.Add Key:="UsesSmartQuotes", _
	 colStyles.Add Key:=vDataFields(0), _
	 Set col = Nothing
Next v
MsgBox colStyles("Heading 1")("UsesSmartQuotes") 
' Above MsgBox Displays "True"
End Sub

With this method, adding a new "field" of data about a particular style requires adding only one line of code, within the collection-assignment For Each loop. So as was the case with the string lists discussed in the first section, this method will save you some typing. Note also that, as with the bunny rabbit example in the previous section, you need to trim off the leading and trailing separator.

This certainly isn't anyone's idea of an ideal solution, but it is a quick and dirty way to compress a lengthy collection assignment to the point that Word on a Mac will at least process it. The big irony here is that the key to the fix here, the Split function, isn't included by default in Word VBA on a Mac.

While string processing with VBA probably won't ever be as good as some other (let's face it—most other) scripting languages, the string functions VBA does offer can sometimes provide interesting ways to reduce coding time, improve code performance, and even present solutions to cross-platform development obstacles.

Andrew Savikas is the VP of Digital Initiatives at O'Reilly Media, and is the Program Chair for the Tools of Change for Publishing Conference.

Return to the Windows DevCenter