This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations. Recently, I solved an EE question using the LIKE function. In order for this to work properly, I needed the comparisons to be case insensitive.
This prompted me to consider a new item to add to my list of helpful hints.
Create two versions of your utility modules, differing by their case sensitivity
The data in different rows of an Excel worksheet contained first and last names. But sometimes, the case of the data was different and sometimes additional data, such as middle initial or suffix (Sr, Jr, III), might follow.
Example data from problem:
ADAMS GARY A
ANDREWS, JR. CLAYTON D.
By default, the LIKE function does case-sensitive pattern matching. So, my lastname and firstname column comparisons using the LIKE operator failed under the default (case-sensitive) conditions.
rngRow.Cells(1, 8) Like (rngRow.Offset(lngFrom).Cells(1, 8) & "*")
rngRow.Cells(1, 9) Like (rngRow.Offset(lngFrom).Cells(1, 9) & "*")
In this particular problem, I could have used the InStr() function as a work-around to the LIKE operator behavior.
InStr(1, rngRow.Cells(1, 8), rngRow.Offset(lngFrom).Cells(1, 8), vbTextCompare) = 1
InStr(1, rngRow.Cells(1, 9), rngRow.Offset(lngFrom).Cells(1, 9), vbTextCompare) = 1
I could have used one of the VB case-shifting functions to make both strings upper case or lower case. For a few small strings, this might be an acceptable solution. However, both large strings and lots of UCase()/LCase() invocations have performance detractors.
But, to paraphrase Sally Field, "I like the LIKE operator. I really, really like
it." While the LIKE operator may be the poor man's RegEx, but it is a powerful tool to have in your arsenal. It performs faster than the Regular Expression object.
For more information on RegExp use with VB code, check out this excellent article: http://e-e.com/A_1336.html
The LIKE operator can provide a lot of funcationality with minimal coding, which is very important when prototyping applications.
The easy solution was to require the user of my solution code to include an
OPTION COMPARE TEXT
in the General Declarations section. But that meant that ALL comparisons in that module would be case-insensitive. This led me to my latest recommendation...Create Two Modules -- one case-sensitive and the other not.
To illustrate this, create two modules, mod_CS
. The mod_CS packages case-sensitive code operations and mod_CI packages case-insensitive code operations.
Attribute VB_Name = "mod_CS"
Option Compare Binary
Public Function IsLike_CS(parmText As String, parmPattern As String) As Boolean
IsLike_CS = parmText Like parmPattern
Public Function IsEqual_CS(parmA As String, parmB As String) As Boolean
IsEqual_CS = (parmA = parmB)
Public Function InStr_CS(parmText As String, parmFind As String) As Long
InStr_CS = InStr(parmText, parmFind)
Attribute VB_Name = "mod_CI"
Option Compare Text
Public Function IsEqual_CI(parmA As String, parmB As String) As Boolean
IsEqual_CI = (parmA = parmB)
Public Function IsLike_CI(parmText As String, parmPattern As String) As Boolean
IsLike_CI = parmText Like parmPattern
Public Function InStr_CI(parmText As String, parmFind As String) As Long
InStr_CI = InStr(parmText, parmFind)
Now that I've packaged the LIKE operator in two different case-sensitive flavors, I can tweak my original code:
IsLike_CI(rngRow.Cells(1, 8), rngRow.Offset(lngFrom).Cells(1, 8) & "*")
IsLike_CI(rngRow.Cells(1, 9), rngRow.Offset(lngFrom).Cells(1, 9) & "*")
An unexpected benefit of this packaging is a simplification of patterns. If you have created your two modules, and run the following commands in the Immediate window, you should get the same results as this:
?IsLike_CI("Now is the time","[n]*THE*")
?IsLike_CS("Now is the time","[nN]*the*")
Notice that the first letter part of the case-insensitive pattern, [n], is simplified.
While I was playing with this packaging, I added the InStr equivalents. The Instr() function has an optional parameter that forces the comparison to be case-sensitive/insensitive
. However, in order to use this form of the InStr() function, you must code all the parameters.
This Immediate window example shows a case-insensitive invocation of the InStr() function.
?InStr(1, "Now is the time", "IS", vbTextCompare)
The new versions of this function provide a simpler invocation, as illustrated in this Immediate window example:
?InStr_CI("Now is the time","IS")
For simple string equality/inequality operations, you could use the StrComp() function. However, there are some quirks with this function depending on the runtime language setting of the system. As a final test of the Two Modules recommendation, I created two flavors of functions that perform a direct comparison of two character strings.
Making the Modules Classy
One of my earliest maxims is "Intellisense is your friend". Once you've packaged your case-sensitive/insensitive
functions into two modules, you can use intellisense in your code.
Typing a period after the module name displays a list of methods.
Selecting one of the methods and typing an open parenthesis displays the parameters for the selected funtion.
You can further simplify your development by adding a class module to your project and making class methods that invoke these functions.
Public Function IsEqual_CI(ByVal parmA As String, ByVal parmB As String) As Boolean
IsEqual_CI = mod_CI.IsEqual_CI(parmA, parmB)
Public Function IsEqual_CS(ByVal parmA As String, ByVal parmB As String) As Boolean
IsEqual_CS = mod_CS.IsEqual_CS(parmA, parmB)
Public Function InStr_CI(ByVal parmText As String, ByVal parmFind As String) As Long
InStr_CI = mod_CI.InStr_CI(parmText, parmFind)
Public Function InStr_CS(ByVal parmText As String, ByVal parmFind As String) As Long
InStr_CS = mod_CS.InStr_CS(parmText, parmFind)
Public Function IsLike_CS(ByVal parmText As String, ByVal parmPattern As String) As Boolean
IsLike_CS = mod_CS.IsLike_CS(parmText, parmPattern)
Public Function IsLike_CI(ByVal parmText As String, ByVal parmPattern As String) As Boolean
IsLike_CI = mod_CI.IsLike_CI(parmText, parmPattern)
Now, all your functions are packaged in one class and you still have Intellisense.
Once you define a class object variable, in this example CICS, typing the name of the variable displays ALL the functions.
Selecting a function and typing an open parenthesis displays its parameters.
String function optimization
Optimize string handling - Parts I and II
VB string function performance:
Option Compare Text definition: