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) Loop ' 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
InStrRev) that you can use in your own macros. One such
site is http://www.freevbcode.com/ShowCode.asp?ID=17.
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.
Split function also comes in handy when working with string lists, as
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
MsgBox Split(Mid(sBunnies, _ 2, Len(sBunnies) - 2), "/")(1)
Mid function is needed here to trim off the leading and trailing
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
variant of type
Array. Just remember that the array returned by
has an offset of 0.
Simple String Lists, the Split Function, and Lengthy
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
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", _ Item:=vDataFields(1) col.Add Key:="UsesSmartQuotes", _ Item:=vDataFields(2) colStyles.Add Key:=vDataFields(0), _ Item:=col 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
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.
Return to the Windows DevCenter