An Aside on VBA and InStrRev

I had an interesting assignment come my way today, and I want to take a moment to write about it here. I will get back to the task of describing the General Ledger budgeted financial statement queries soon.

I have a copy of VB & VBA in a Nutshell by O'Reilly Publishers. I have not had much use for it recently, because the report design tool uses only VBScript, not pure VB or VBA, as its script platform. Like most O'Reilly books of its type, it provides useful insight into the "whys" of the language, and gives "Programming Tips & Gotchas" for most of the language elements. For one function in VB6, however, the writers clearly thought it had no practical use. The function is InStrRev, and their "Programming Tip" is this:

"The usefulness of a function that looks backward through a string for the occurrence of another string isn't immediately apparent."

This seems like a courteous way of saying, "This function is useless." Ah, but I beg to differ. In fact, the task I was given today was greatly simplified (if not in fact made possible) by this function. Today I was tasked with figuring out a way to take a single column of data in Excel with City-State-ZIP (strCSZ) value and separate it into three columns, one for each part of the address.

The strCSZ value had no punctuation, so only spaces separated each element. Also, the state is always just the two-letter abbreviation. At first blush, one could think that breaking the string into pieces by finding the space character would work, but this fails because some city names are actually two words, separate by a space. Working forward from the first character of the string, one could never know if the first space divides the city from the state or if it divides the first word of the city from the second word.

However, working backward from the last character of the string, the first space would always be the breaking point between the state and the ZIP code. Once that is a known value (calculated with the InStrRev function), it is a simple matter of using Mid three times to get each piece of the address. Here is a bit of the script I wrote:

intSpace = InStrRev(strCSZ, " ")

strCity = Mid(strCSZ, 1, intSpace - 5)

strState = Mid(strCSZ, intSpace - 3, 2)

strZIP = Mid(strCSZ, intSpace + 1, 10)

I want to thank the VB6 developers for including InStrRev, even though its usefulness was not "immediately apparent." It sure came in useful to me!