Search code examples
excelvbasortingnon-latin

Non-English characters: How to get VBA comparison to be consistent with Excel formula comparison?

Beyond ordinary Latin characters, Excel somehow does a pretty good job of sorting strings in various alphabets.
< and > in formulae use that same order.
But < and > in VBA use a different order - probably given by Unicode().
The extract below shows the inconsistency between columns B and C.
How can I compare strings in VBA using the same order that is used for sorting?
I am hoping that while X < Y will not give the relevant result, somefunction(X) < somefunction(Y) will do so.

I have found some articles/postings about how to change the sort order, but that is not the issue here.

Comparison.jpg

Apologies for the above being an image - I can't work out how to get Excel data in to SO.
For replication:
The values in column A are: А Б В Г Ґ Д Е Є Ж З И Stop, starting from A2, which is named "first"
The formula in B2 is =IF(A2<A3,"Less than next","Greater than next")
The formula in D2 is =UNICODE(A2)
Column C is populated by the macro:

Sub Compare()
     Range("first").Select
     Do Until ActiveCell.Value = "Stop"
        If ActiveCell.Value < ActiveCell.Offset(1, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "Less than next"
        ElseIf ActiveCell.Value > ActiveCell.Offset(1, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "Greater than next"
        Else
            ActiveCell.Offset(0, 2).Value = "Same as next"
        End If
        ActiveCell.Offset(1).Select
    Loop

End Sub
Solution

  • You can force VBA to use a different comparison method when comparing strings.

    This can be done for a whole module, putting Option Compare Text at the top of the code - if done, you can use the regular comparison operators like < to > without changing your code (Default setting is Option Compare Binary)

    You can also do this indiviually for a single comparison using the function strComp and pass vbTextCompare as third parameter (omitting tge third parameter will let VBA fall back to the defined Option Compare)

    StrComp(cell.Value, cell.Offset(1, 0).Value, vbTextCompare)

    Note that the text sorting option also will see upper and lower case characters as "equal".

    Not 100% sure if those will always get the same results as the Excel compare, but at least for your given examples it did. If you don't trust this, you can fall back to the Evaluate-method that really uses the Excel-engine.

    Option Compare Text

Sub Compare()
    Dim cell As Range
    Set cell = ThisWorkbook.Sheets(1).Range("A2")
    Do Until cell.Value = "Stop"
        Dim formula As String, res As Variant
        formula = """" & cell.Value & """ < """ & cell.Offset(1, 0).Value & """"
        res = Application.Evaluate(formula)
        cell.Offset(0, 1) = getCmpInfostr(res)
        cell.Offset(0, 2) = getCmpInfostr(cell.Value < cell.Offset(1, 0).Value)
        cell.Offset(0, 3) = getCmpInfostr(StrComp(cell.Value, cell.Offset(1, 0).Value))
        cell.Offset(0, 4) = getCmpInfostr(StrComp(cell.Value, cell.Offset(1, 0).Value, vbTextCompare))
        Set cell = cell.Offset(1, 0)
    Loop
End Sub

Function getCmpInfoString(c As Variant)
    If VarType(c) = vbBoolean Then
        c = IIf(c, -1, 1)
    End If
    If VarType(c) <> vbInteger And VarType(c) <> vbLong Then
        getCmpInfostr = "invalid"
    ElseIf c < 0 Then
        getCmpInfostr = "Less than"
    ElseIf c > 0 Then
        getCmpInfostr = "Greater than"
    Else
        getCmpInfostr = "Same"
    End If
End Function

    Obligatory hint for all VBA programming: avoid Select and ActiveCell - see How to avoid using Select in Excel VBA

    The following code shows the different methods - let the code run once with and once without the Option Compare Text option.