CODEWITHSUNDEEP
vbaexcel
sigil
sigil

Reputation: 9546

How to do string comparison in VBA if a string has an inequality operator?

I have an Excel sheet that can have the following string in one of its cells:

Valid Test for ≥ 30% ABCD?

When I capture this value in a variable and output it in the Immediate window, it looks like this:

?strVal
Valid Test for = 30% ABCD?

I want to check whether the cell contains this value, and currently I'm using:

Select Case strVal
    Case "Valid Test for = 30% ABCD?"
        doSomething
    '... other Case statements
    Case Else
        doSomethingElse
End Select

This string match always fails, though, and I get the following results when I investigate in the Immediate window:

?strVal="Valid Test for = 30% ABCD?"
False

?asc(mid(strVal,16,1))
 61 

?asc("=")
 61

So apparently what's happening here is that the "≥" symbol is being rendered in VBA output as "=" both in appearance and ASCII code, but somehow it's still not equivalent to "=" in string comparison. How can I get the string match that I'm looking for?

Upvotes: 0

Views: 766

Answers (1)

Tom Blodget
Tom Blodget

Reputation: 20802

Excel and VBA use Unicode (UTF-16) for string/text values. Unfortunately, the VBA editor does not.

For characters that the VBA editor doesn't support, use ChrW. For Unicode Character 'GREATER-THAN OR EQUAL TO' (U+2265), use ChrW(&H2265) as in

Case "Valid Test for " & ChrW(&H2265) & " 30% ABCD?"

Note: Asc doesn't do what you think it does-It's not ASCII and it isn't a one-to-one conversion. In almost all cases, AscW is what you want; it gives the UTF-16 code unit value the "character", plain and simple as far as VBA is concerned. (Of course, text isn't simple, so Unicode and UTF-16 are correspondingly complex in some areas.)

Upvotes: 4

Related Questions