VBA strange behavior of comparison result between String and Variant

[Prome3]

Hi friends,
the other day, someone posted an issue on the web asking why the statement "i < ActiveSheet.Rows.Count" returns False, in which the variable "i" is declared as a String and is assigned an integer value 3.
I have been thinking about this problem for two days and still can't figure out why. Here is a small demo sub procedure that can illustrate what I'm trying to explain.

Option Explicit

Sub CompareStringWithVariant()
    Dim a As String
    Dim b As Variant
    a = 3
    For b = 1 To 30
        Debug.Print "3 < " & CStr(b) & ": " & (a < b)
    Next b
End Sub

Specifically, I wanted to find some explanation in [MS-VBAL], the official VBA language specification.
[MS-VBAL]: VBA Language Specification
Any help would be appreciated.

 

[nemmi69]

why are you using 'a' as a string? Convert 'a' to integer

Option Explicit

Sub CompareStringWithVariant()
    Dim a As String
    Dim b As Variant
    a = 3
    For b = 1 To 30
        Debug.Print "3 < " & CStr(b) & ": " & (CInt(a) < b)
    Next b
End Sub

 

[RoryA]

It's converting the Variant argument to text, then doing a character by character comparison, left to right.

 

[Jon Peltier]

why are you using 'a' as a string? Convert 'a' to integer

Option Explicit

Sub CompareStringWithVariant()
    Dim a As String
    Dim b As Variant
    a = 3
    For b = 1 To 30
        Debug.Print "3 < " & CStr(b) & ": " & (CInt(a) < b)
    Next b
End Sub

Note: it's better to use a Long. VBA internally convert an Integer to a Long, does any calcs, then converts back to Integer. Saves two type conversions.

Same with Singles and Doubles.

 

[Prome3]

why are you using 'a' as a string? Convert 'a' to integer

Option Explicit

Sub CompareStringWithVariant()
    Dim a As String
    Dim b As Variant
    a = 3
    For b = 1 To 30
        Debug.Print "3 < " & CStr(b) & ": " & (CInt(a) < b)
    Next b
End Sub

The original question was raised by someone else, not me. And I've never written code like that before (like "a" as String here.) I know how to write "correct" code, and I know how to modify code like the demo above to run as expected. The problem is: WHY it worked THAT WAY. What's happening when comparing a numeric String with a Variant of subtype Long?

 

[Prome3]

It's converting the Variant argument to text, then doing a character by character comparison, left to right.

We can infer from the results of the demo that the Variant is converted to String before being compared with another string variable. But the behavior of VBA statements should confirm to its own specification, right? I hope someone can point out in the [MS-VBAL] doc where this strange behavior originated. The closest I can get by now is this statement: "Before evaluating the relational operator, its non-Null operands undergo Let-coercion to the operator’s effective value type." With the left operand being a String, the right operand a Variant with initial value Empty, then before evaluating, the right operand is coerced to a string, hence the strange behavior. But I'm still not quite sure about that.

 

[RoryA]

There is nothing, as far as I am aware, in the spec that actually explains this behaviour, since the table of effective value types for the operator does not include an option for Variant for either of the operands, and nothing states how a Variant operand should be Let coerced for an operator whose declared type is Variant.

Personally I am more interested in what actually happens than what should happen according to documentation. Especially if that documentation has already been revised several times.

 

[rlv01]

We can infer from the results of the demo that the Variant is converted to String before being compared with another string variable. But the behavior of VBA statements should confirm to its own specification, right? I hope someone can point out in the [MS-VBAL] doc where this strange behavior originated. The closest I can get by now is this statement: "Before evaluating the relational operator, its non-Null operands undergo Let-coercion to the operator’s effective value type." With the left operand being a String, the right operand a Variant with initial value Empty, then before evaluating, the right operand is coerced to a string, hence the strange behavior. But I'm still not quite sure about that.

It's a string comparison, not a numerical comparison. When one term of the comparison is a string, then all terms are treated as type string for purposes of the comparison. I don' t know that I would call it "strange". To me it seems like a rational design choice to deal with sloppy typing on the the part of the programmer.

Sub CompareStringWithVariant()
    Dim a As String, c As String
    Dim b As Variant
    a = 3
    
    For b = 1 To 30
        c = b
        Debug.Print a & " < " & CStr(b) & ": " & (a < b) & " / " & (a < c) & " [" & TypeName(b) & "/" & TypeName(c) & "]"
    Next b
End Sub

 

[Prome3]

It's a string comparison, not a numerical comparison. When one term of the comparison is a string, then all terms are treated as type string for purposes of the comparison. I don' t know that I would call it "strange". To me it seems like a rational design choice to deal with sloppy typing on the the part of the programmer.

Sub CompareStringWithVariant()
    Dim a As String, c As String
    Dim b As Variant
    a = 3
   
    For b = 1 To 30
        c = b
        Debug.Print a & " < " & CStr(b) & ": " & (a < b) & " / " & (a < c) & " [" & TypeName(b) & "/" & TypeName(c) & "]"
    Next b
End Sub

Totally agree that it is a rational design choice. In fact, I think many of the design choices in VBA are rational. VBA is not a strongly typed language(just like javascript), so the language itseft has to make many tough choices trying to figure out the programmer's intent.
Yet as to this statement:

When one term of the comparison is a string, then all terms are treated as type string for purposes of the comparison.

I don't think it work like that. Take a look at this code:

Sub CompareNumericStringWithLong()
    Dim a As String
    Dim b As Long
    a = 3
    b = 20
    Debug.Print a < b
End Sub

Here it's the variable 'a' get converted to Long data type before comparing to 'b', not the other way around.