<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

The Case for (case-sensitive) Modules

Published on
15,437 Points
5,537 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
Comment
Author:aikimark
  • 4
  • 4
  • 2
10 Comments
LVL 6

Expert Comment

by:930913
This is a very inefficient way of doing what the programmer should be do.

If the programmer does not understand how to do this, they must surely still be learning VB and hence rather than use this, learn what this is doing.
0
LVL 48

Author Comment

by:aikimark
@930913

If an experienced VB programmer wanted to mix both case sensitive and case insensitive behavior from the LIKE function in the same program, how would you suggest they accomplish that?

I'm not sure I understand your "inefficient" reference.
0
LVL 6

Expert Comment

by:930913
The LIKE function should never be used. Rather, the instr function be used. If case insensitivity is needed, converting both (or one if the other will always be either) to upper or lower case.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 48

Author Comment

by:aikimark
There are pattern-matching scenarios that are available with the LIKE operator that can not be implemented with a single InStr() function.  

>>converting ... to upper or lower case.
I've seen different performance figures on this, depending on the length of the strings.
0
LVL 50

Expert Comment

by:DanRollins
I was called out on this as a newbie programmer:  My case-insensitive text file search routine (in 8086 ASM) worked fine, but the senior programmer at Peter Norton Computing told me; "You realize, don't you, that you are upshifitng the entire document..." (on a 4.77 Mhz PC, you really can't waste that many CPU cycles).  

He showed me how to upshift the target data only when the first part of the string matched.  The performance difference was astounding on large files.
0
LVL 6

Expert Comment

by:930913
I made a test where each method had to find "f*n*" in "Find" (case insensitive).
Using the like method, 100m iterations took 24395 milliseconds; using the instr method, 100m iterations took 20228 milliseconds.
I again ran another test, but this time, rather than using lcase, I used the option vbTextCompare. This took 16338 milliseconds.

Whilst one like statement can be really simple to use, replacing it with many instr statements, would be ~33% faster.

DanRollins, if you could please enlighten us as to how to implement what you say.
0
LVL 50

Expert Comment

by:DanRollins
I think it's safe to say that if you look at the underlying ASM code for case-insenstive Instr or LIKE, it is already using something like what I hand-coded  some thirty years ago :-)
0
LVL 48

Author Comment

by:aikimark
@930913

>>I made a test where each method had to find "f*n*" in "Find" (case insensitive).
You created a test where two InStr() functions could match the pattern.  My earlier comment and point to using the LIKE operator is that you can do a match that can NOT be done with InStr() functions without a great deal of teeth gnashing and hair pulling.

>>Using the like method, 100m iterations ...
Performance measurement it isn't just about iterations, you have to evaluate different scenarios, such as long strings, mixed case strings, placement of near-miss patterns in the string, and different placement of exact match substrings.

>>Whilst one like statement can be really simple to use, replacing it with many instr statements, would be ~33% faster.  
I'm not sure if you are showing performance times for TWO InStr() functions, which is required to match the pattern you are testing.  If not, then two InStr() functions (32k ms) are more than a single LIKE operator (24k ms).

Even if your InStr() times are for a pair of InStr() functions, "many instr statements" is an erroneous conclusion.  It fails the reader (performance evaluator) in deciding how many InStr() functions one might use in place of a single LIKE operator.

=================
For case insensitive searching, even the native InStr() function can be beat.  Consider this VBSpeed article:
http://www.xbeat.net/vbspeed/c_InStr.htm

Despite my proclivity towards highly optimized operations and code, I will use a less optimal strategy to accomplish other development goals, such as rapid prototyping and flexibility.
0
LVL 6

Expert Comment

by:930913
It isn't that hard; perhaps you could write an article guiding people on how to do so.

I agree, it was just a simple test. Further investigation may turn up a different result, but until then, I will assume that my results stand.

The 33% reduction was for both statements against the like.

Without thinking much into it, I would suggest one instr per continuous, non wildcard pattern. (i.e. "*123*5*78*" would require 3.)

===============
I tried that, it took ages, I eventually broke it and it was only a quarter of the way through. When I restarted it, it died, taking my project with it.
0
LVL 48

Author Comment

by:aikimark
@930913

Thank you for the idea, but I'll pass.  It isn't really worth the effort, since an InStr() isn't good for pattern matching for something equivalent to

  strThing Like "*[e-m]?[PTS]*WTF*[1-9][3-6][!a-h]*"

Patterns can be sufficiently sophisticated that I would need to use a RegEx parser instead of the LIKE operator.

All of this InStr() versus LIKE performance discussion isn't important to this article, which has to do with having two flavors (case sensitive/insensitive) of string comparison functions.
0

Featured Post

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Join & Write a Comment

As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month