ID: 1897

	[x] Attachment Details

The Case for (case-sensitive) Modules

	[x] EE Approved
	EE Approval is worth 4,000 points, assigned to Articles that are considered a valuable resource in the zone they're published.

aikimark

Posted on 11/03/09 at 10:01 AM

0 of 0 members found this article helpful

0: 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

1: 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:

1:
2:
3:
4:
5:
6:
Lastname       	Firstname
ADAMS      	GARY A
Adams      	Gary
ANDREWS, JR.	CLAYTON D.
Andrews   	Clayton

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

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.

2: The Solution

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

mod_CS Example

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
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

mod_CI Example

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
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

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:

1:
2:
3:
4:
5:
6:
?IsLike_CI("Now is the time","[n]*THE*")
True
 
?IsLike_CS("Now is the time","[nN]*the*")
True

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

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

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.

1:
2:
3:
4:
5:
6:
?IsEqual_CI("abba","ABBA")
True
 
?IsEqual_CS("abba","ABBA")
False

4: 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.

Intellisense-1.JPG (4 KB) (File Type Details)

module intellisense example 1

Selecting one of the methods and typing an open parenthesis displays the parameters for the selected funtion.

Intellisense-2.JPG (6 KB) (File Type Details)

module intellisense example 2

You can further simplify your development by adding a class module to your project and making class methods that invoke these functions.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
'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

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.

Intellisense-3.JPG (8 KB) (File Type Details)

class intellisense example 1