Confused about string comparison in VBA

I having some issue in a VBA (Excel) app I am currently working on.

At some point I have to retrieve some values in a database via SQL query and compare them a some values in a cell.

Something like (pseudo code)

if QueryResult = CellValue then DoSomething

Open in new window


Well my issue is that despite seemingly having equal value the "=" operator will not work as expected, ie. the comparison will return FALSE.

So I've gone one step further and wrote this function

Function sComp(prmString1 As String, prmString2 As String) As Boolean

Dim locResult As Boolean
Dim locStr As String
Dim i As Integer

    locResult = StrComp(prmString1, prmString2, vbTextCompare)
    locStr = "Comparing [" & prmString1 & "] " & Len(prmString1) & " with [" & prmString2 & "] " & Len(prmString2) & " result " & CStr(locResult)
    addLogEntry prmEntry:=locStr, prmClass:="clsSMSE", prmPK:=PK
    For i = 1 To Len(prmString1)
        addLogEntry prmEntry:=i & ":" & Asc(Mid(prmString1, i, 1))
    Next i
    For i = 1 To Len(prmString1)
        addLogEntry prmEntry:=i & ":" & Asc(Mid(prmString2, i, 1))
    Next i
        
    
    sComp = locResult

End Function

Open in new window

which uses the StrComp function and logs the details of what is happening

However, despite running a text compare (which should be more "lax") and actually having a byte by byte equality the stringy still don't match...

Log output
I muss confess that I am a little stomped at this stage... Any help most welcome
LVL 1
Alexandre TakacsCTOAsked:
Who is Participating?
 
Randy BristowSenior Systems AnalystCommented:
Are you using the function right?

(StrComp("abc", "ABC", vbTextCompare) = 0) » True
(StrComp("abc", "abc", vbTextCompare) = 0)  » True
(StrComp("abc", "ab", vbTextCompare) = 0)   » False

http://codevba.com/visual-basic-source-code/vb-string/checking_if_two_strings_are_equal.htm#.WstXZeSouUk
0
 
NorieVBA ExpertCommented:
Can you give some examples of non-matching strings?

Perhaps even upload a workbook with sample data.
0
 
Alexandre TakacsCTOAuthor Commented:
Well the example above is exactly that

The code tries to compare "Germany" (from SQL) with "Germany" (from Worksheet) and returns false.

Then I do a byte by byte dump off all characters in both strings and they match. Yet the compare does not !
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
At the top of the module, look at the statement for:

 Option Compare

 What is it?   This statement is what controls how strings are compared.   The normal setting is "Database", but that sometimes will not give you what you want.

Jim.
0
 
Alexandre TakacsCTOAuthor Commented:
I thought that vbTextCompare passed to StrComp would avoid any doubt as of how string are handled
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, that is correct.   It would override the option statement (didn't catch that in the code).  

 Still something to be aware of though.   First thing I always go to when two matching strings are not equivalent.

Jim.
0
 
Randy BristowSenior Systems AnalystCommented:
The result will be -1, 0, 1, or null. Which value are you getting back?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So in place of this:

if QueryResult = CellValue then DoSomething

 Your are calling your  sComp() with those actual values?   If so, then that is a puzzler.....are you doing any conversions of the raw data, or are you doing the equals check directly on the values?

Jim.
0
 
Alexandre TakacsCTOAuthor Commented:
Your are calling your  sComp() with those actual values?   If so, then that is a puzzler.....are you doing any conversions of the raw data, or are you doing the equals check directly on the values?

Yes

I have since added a call to application.WorksheetFunction.Clean on both strings to no avail

The result will be -1, 0, 1, or null. Which value are you getting back?

Hmm let me check - I thought StrComp returns a boolean
0
 
Randy BristowSenior Systems AnalystCommented:
A Boolean false value is 0. This function returns 0 if you have a match (true).

string1 is less than string2 = -1
string1 is equal to string2 = 0
string1 is greater than  string2 =1
string1 or string2 is Null = Null

http://codevba.com/visual-basic-source-code/vb-string/checking_if_two_strings_are_equal.htm#.WstXZeSouUk
1
 
aikimarkCommented:
What is the value of the Option Compare statement in your General Declarations section?
0
 
Alexandre TakacsCTOAuthor Commented:
Ok my bad - I was not using StrComp correctly :(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.