Best way to compare strings in VBA?

Question

I've been using VBA for the past month now (for my job) and since I've begun using this language/script, there have been several occurances where I have issues comparing strings.. the ways that I've compared strings so far are:

str1 = str2 and str1 <> str2
StrComp(str1, str2, comparisonMethod)
InStr(str1, str2)

Sometimes, before using one of these comparison methods, I'll first apply UCase(str1), or even UCase(Trim(str1)). From my experience so far though, comparing strings in VBA seems to be a rather fickle thing, because I feel like I get inconsistent results.

QUESTION: What is the best procedure for comparing two strings in VBA?

For example: Imagine there are two lists.. one list contains laptop models, the other contains parts numbers. The goal is to associate all part numbers with a model. My results produce only partial associations, meaning that if I have 10 part numbers that should associate with a model, only 5 or 6 actually are. I then take the strings that don't properly compare and compare again in seperate code, and will then get the correct result. This kind of behavior has been somewhat consistent throughout my use of VBA, and is where the "feeling" comes from.

"I feel like I get inconsistent results" - how can you feel such a "binary" thing(?), please add an example that your methods does not return the expected result. — Jonathan Applebaum, Jul 20 '17 at 13:21
A = B is 100% reliable if A and B are in the same case and have the same (if any) leading/trailing whitespace - remember to always ensure those 2 things are true and you will have no problems. — Alex K., Jul 20 '17 at 13:30
@Charles maybe you are dealing with a case like that: https://stackoverflow.com/questions/45120678/classic-asp-trying-to-search-a-string-in-another-string-variable-alternative/45121235#45121235 — Jonathan Applebaum, Jul 20 '17 at 13:35

score 22 · Accepted Answer · edited May 12 '22 at 14:35

First question is if you want to compare case sensitive or insensitive - with other words: is "ABC" = "abc".

The behavior of the =-operator is defined by Option Compare - the default is binary.

Option Compare text
sub foo1
    Debug.print "ABC" = "abc"    ' Prints "True"
end sub

Option Compare binary    ' <-- This is the default!
sub foo2
    Debug.print "ABC" = "abc"    ' Prints "False"
end sub

The StrComp-function gets the same results, but without the need of setting the compare option at the top of the module. Instead, you can give the desired way to compare as parameter:

sub foo3
    Debug.Print StrComp("ABC", "abc", vbBinaryCompare) = 0     ' <-- Prints "False"
    Debug.Print StrComp("ABC", "abc", vbTextCompare) = 0       ' <-- Prints "True"
end sub

if you have leading or trailing blanks in your strings, you always have to use trim - that's the case in any programming language I know.

Using Instr is a rather bad idea to check if two strings as identical, you can use it for substring search. Note that InStr (and also its counterpart InstrRev) are using the option compare setting:

Option Compare text
sub foo4
    Debug.print Instr("ABCDE", "cd")   ' Prints "3"
end sub

Option Compare binary
sub foo5
    Debug.print Instr("ABCDE", "cd")    ' Prints "0"
end sub

Best way to compare strings in VBA?

1 Answers1

Linked