CODEWITHSUNDEEP
vbaexcel
lyk
lyk

Reputation: 1598

VBA: Case sensitivity Options for MATCH in if-else statment

So I've just learnt from here about the "Option Compare Text" and "Option Compare Binary" to differentiate between case-sensitivity for the .Match function.

Right now, this if-else statement shows what I'm trying to do (albeit with errors):

If dictionaryData.Cells(4, 2).Value = "Yes" Then
    caseSensitive = True
    NetworkNameDict.CompareMode = vbBinaryCompare
    Option Compare Binary
Else
    caseSensitive = False
    NetworkNameDict.CompareMode = vbTextCompare
    Option Compare Text
End If

I need this if else statement to check if the user wants to compare by case-sensitivity. The "Option" is placed in there for my .Match function to work (found later in the codes).

I understand that the "Option" codes has to be typed at the top, but I need this option to stay dynamic due to this option being given to users to set in the spreadsheet.

So my question is, is there any way to somehow do a case-sensitivity setting for the .Match function in an if-else statement?

Upvotes: 1

Views: 1546

Answers (2)

Vasily
Vasily

Reputation: 5782

This two options cannot be used in one module, so the way is to call code from the two separated modules, one with option compare text and another one with option compare binary.
Another approach is to use option compare binary with lcase or ucase comparing as in example below:

Option Compare Binary

Sub test()
    Debug.Print TypeName("YES") & " comparing"
    Debug.Print "YES" = "yes", "case sensitive"
    Debug.Print LCase("YES") = "yes", "not case sensitive"
    Debug.Print "YES" = UCase("yes"), "not case sensitive"
    Debug.Print UCase("Yes") = UCase("yEs"), "not case sensitive"

    Debug.Print TypeName(1.1) & " vs " & TypeName("1.1")
    Debug.Print 1.1 = "1.1", "not case sensitive"

    Debug.Print TypeName(1) & " vs " & TypeName("1")
    Debug.Print 1 = "1", "not case sensitive"

    Debug.Print TypeName(10000001) & " vs " & TypeName("10000001")
    Debug.Print 10000001 = "10000001", "not case sensitive"
End Sub

output in Immediate window will be

String comparing
False         case sensitive
True          not case sensitive
True          not case sensitive
True          not case sensitive
Double vs String
True          not case sensitive
Integer vs String
True          not case sensitive
Long vs String
True          not case sensitive

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Create two separate modules; one called MText with OPTION COMPARE TEXT, and one called MBinary with OPTION COMPARE BINARY, and call the appropriate functions from the correct module as required.

Alternatively, for a more object-oriented approach, create two classes CBinary and CText which implement the same interface of MATCH tests and instantiate the one you require at any given time.

Upvotes: 1

Related Questions