The Case for (case-sensitive) Modules

Introduction

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 Problem

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:
 

Lastname       	Firstname
                      ADAMS      	GARY A
                      Adams      	Gary
                      ANDREWS, JR.	CLAYTON D.
                      Andrews   	Clayton

Select all Open in new window

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) & "*") 

Select all Open in new window

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

Select all Open in new window

Note: 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 Solution

The easy solution was to require the user of my solution code to include an
 

OPTION COMPARE TEXT

Select all Open in new window

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 and mod_CI.  The mod_CS packages case-sensitive code operations and mod_CI packages case-insensitive code operations.

mod_CS Example
 

Attribute VB_Name = "mod_CS"
                      Option Explicit
                      Option Compare Binary
                      
                      Public Function IsLike_CS(parmText As String, parmPattern As String) As Boolean
                        IsLike_CS = parmText Like parmPattern
                      End Function
                      
                      Public Function IsEqual_CS(parmA As String, parmB As String) As Boolean
                        IsEqual_CS = (parmA = parmB)
                      End Function
                      
                      Public Function InStr_CS(parmText As String, parmFind As String) As Long
                        InStr_CS = InStr(parmText, parmFind)
                      End Function

Select all Open in new window

mod_CI Example
 

Attribute VB_Name = "mod_CI"
                      Option Explicit
                      Option Compare Text
                      Public Function IsEqual_CI(parmA As String, parmB As String) As Boolean
                        IsEqual_CI = (parmA = parmB)
                      End Function
                      
                      Public Function IsLike_CI(parmText As String, parmPattern As String) As Boolean
                        IsLike_CI = parmText Like parmPattern
                      End Function
                      
                      Public Function InStr_CI(parmText As String, parmFind As String) As Long
                        InStr_CI = InStr(parmText, parmFind)
                      End Function

Select all Open in new window

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) & "*") 

Select all Open in new window

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*")
                      True
                      
                      ?IsLike_CS("Now is the time","[nN]*the*")
                      True

Select all Open in new window

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)
                      5

Select all Open in new window

The new versions of this function provide a simpler invocation, as illustrated in this Immediate window example:
 

?InStr_CI("Now is the time","IS")
                      5

Select all Open in new window

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.
 

?IsEqual_CI("abba","ABBA")
                      True
                      
                      ?IsEqual_CS("abba","ABBA")
                      False

Select all Open in new window

 

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.

 

'class: clsCICS
                      Option Explicit
                      
                      Public Function IsEqual_CI(ByVal parmA As String, ByVal parmB As String) As Boolean
                        IsEqual_CI = mod_CI.IsEqual_CI(parmA, parmB)
                      End Function
                      
                      Public Function IsEqual_CS(ByVal parmA As String, ByVal parmB As String) As Boolean
                        IsEqual_CS = mod_CS.IsEqual_CS(parmA, parmB)
                      End Function
                      
                      Public Function InStr_CI(ByVal parmText As String, ByVal parmFind As String) As Long
                        InStr_CI = mod_CI.InStr_CI(parmText, parmFind)
                      End Function
                      
                      Public Function InStr_CS(ByVal parmText As String, ByVal parmFind As String) As Long
                        InStr_CS = mod_CS.InStr_CS(parmText, parmFind)
                      End Function
                      
                      Public Function IsLike_CS(ByVal parmText As String, ByVal parmPattern As String) As Boolean
                        IsLike_CS = mod_CS.IsLike_CS(parmText, parmPattern)
                      End Function
                      
                      Public Function IsLike_CI(ByVal parmText As String, ByVal parmPattern As String) As Boolean
                        IsLike_CI = mod_CI.IsLike_CI(parmText, parmPattern)
                      End Function

Select all Open in new window

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.

Reference Material

http://www.aivosto.com/vbtips/instr.html#vbtextcompare
 
Optimize string handling - Parts I and II
http://www.aivosto.com/vbtips/stringopt.html
http://www.aivosto.com/vbtips/stringopt2.html

VB string function performance:
http://www.justvb.net/optimize3.htm

Option Compare Text definition:
http://msdn.microsoft.com/en-us/library/8t3khw5f(VS.80).aspx