asked on

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

I muss confess that I am a little stomped at this stage... Any help most welcome

Norie

Can you give some examples of non-matching strings?

Perhaps even upload a workbook with sample data.

Alexandre Takacs

ASKER

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 !

ASKER CERTIFIED SOLUTION

Member_2_25505

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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.

Alexandre Takacs

ASKER

I thought that vbTextCompare passed to StrComp would avoid any doubt as of how string are handled

Jim Dettman (EE MVE)

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.

SOLUTION

Member_2_25505

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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.

Alexandre Takacs

ASKER

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

Member_2_25505

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

aikimark

What is the value of the Option Compare statement in your General Declarations section?

Alexandre Takacs

ASKER

Ok my bad - I was not using StrComp correctly :(