Strange ways comparisons occur

Question

This is something strange that I've come across looking at this question here: Issue with VBA Excel number formatting I thought I might expand on it and see if anyone can explain.

Lets say I have an Excel document with 5 cells in column A all with the value 1234

• The first is formatted with the format string "000000000"
• The second is formatted using Excel's "Number" format
• The third is formatted as Excel's "General" format
• The fourth is formatted as "Text"
• The fifth is formatted as "Text" but is actually the string "000001234"

As such, the excel table looks like this

     A    |
----------+-
 000001234|
   1234.00|
      1234|
1234      |
000001234 |

Now I run the following code, base slightly on the question mentioned above.

Sub test3()
    Dim rng As Range
    Dim cl As Range
    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For Each cl In rng.Cells
        If 1234 = "000001234" Then
            cl.Offset(0, 1).Value = "A"
        End If
        If 1234 = cl.Value Then
            cl.Offset(0, 2).Value = "B"
        End If
        If "000001234" = cl.Value Then
            cl.Offset(0, 3).Value = "C"
        End If
    Next cl

End Sub

And the output is as follows

     A    | B | C | D |
----------+---+---+---+-
 000001234| A | B |   |
   1234.00| A | B |   |
      1234| A | B |   |
1234      | A | B |   |
000001234 | A | B | C |

The column B makes sense. VBA can compare Strings as if they're numbers, and by extensions the column C makes sense as well, in each case, we're either comparing numbers to numbers as in the first 3 rows, or doing a similar string comparison in the last 2 rows.

However the column D gets me and is basically my question.

If 1234 = "000001234" and 1234 = cl.Value then why doesn't "000001234" = cl.Value in all cases?

Answer 1

Type of cl.Value is either vbDouble (first 3 sample rows) or vbString (other two), see VarType() function. But during the comparison, there is type casting.
Therefore

• comparing values to number 1234 will always succeed, becaue doubles and strings in cl.Value are converted to number (integer) before comparison. Leading zeros get insignificant during conversion to number.
• comparing values to string "000001234" will succeed only in case of string 000001234 because cl.Value is converted to string before comparison so for your first sample row "000001234" <> "1234" (see what happens to cl.Value: 000001234 → 1234 → "1234"). For other rows, it is similar: 1234.00 → 1234 → "1234" and this is again not equal to "000001234" etc.