<

The Case for (case-sensitive) Modules

Published on
15,500 Points
5,600 Views
4 Endorsements
Last Modified:
Awarded

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

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

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

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

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

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

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

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

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

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

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

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.
module intellisense example 1Selecting one of the methods and typing an open parenthesis displays the parameters for the selected funtion.
module intellisense example 2You 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

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.
class intellisense example 1Selecting a function and typing an open parenthesis displays its parameters.
class intellisense example 2

Reference Material


Case-Sensitivity Traps
http://www.aivosto.com/vbtips/instr.html#vbtextcompare
 
String function optimization
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
4
Author:aikimark
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.