What does VBA check for when you do an equal comparison?

Question

I have 2 excel number cells that look the same, but pivot tables are treating them as different entities. Using =B2=B3 excel formula gives True, but when I test in VBA I get False. Data type and value look the same. I'm thinking it has to be some rounding error, but I expect to see the difference when I print the value with VBA.

My question is besides value and data type, what else does VBA check for during a comparison?

VBA Code

Sub test()
    a = Range("B2").Value
    b = Range("B3").Value
    
    If a = b Then
        Debug.Print ("AWESOME")
    Else
        Debug.Print ("OH NO")
        Debug.Print (a)
        Debug.Print (b)
        Debug.Print (VarType(a))
        Debug.Print (VarType(b))
    End If

End Sub

VBA Output

OH NO
 0.0000000000325 
 0.0000000000325 
 5 
 5

How about try `Debug.Print a-b`? You can also see [this question](https://stackoverflow.com/q/31909506/4996248) if you want to see how to get at the underlying bit patterns to see what the numbers are. — John Coleman, Mar 01 '21 at 10:41
https://stackoverflow.com/questions/235409/compare-double-in-vba-precision-problem — FunThomas, Mar 01 '21 at 10:42

John Coleman · Answer 1 · 2021-03-01T12:20:43.290

0

This is not an answer, but it shows how to make the phenomenon reproducible by others:

Sub test()
    Dim a, b
    Range("B2").Value = Val("3.2500000000000004e-11")
    Range("B3").Value = Val("3.25e-11")
    a = Range("B2").Value
    b = Range("B3").Value
    Range("B4").Formula = "=B2=B3"
    
    If a = b Then
        Debug.Print "AWESOME"
    Else
        Debug.Print "OH NO"
        Debug.Print a
        Debug.Print b
        Debug.Print a - b
        Debug.Print Range("B4").Value
        Debug.Print VarType(a)
        Debug.Print VarType(b)
    End If

End Sub

Output:

OH NO
 0.0000000000325 
 0.0000000000325 
 6.46234853557053E-27 
True
 5 
 5

I had to use Range("B2").Value = Val("3.2500000000000004e-11") since when I tried to simply use Range("B2").Value = 3.2500000000000004e-11 the VBA editor itself rounded the result to 0.0000000000325 in the source file.

The strings I gave are for two successive floats (the mantissa of the one is obtained by adding 1 to the mantissa of the other). It is strange how they compare to equal in the spreadsheet. What is even stranger is that =(B2-B3 = 0) evaluates to False. It almost seems like a bug in how = is implemented as a worksheet operator. Perhaps it is casting values to singles for the purpose of comparisons, though I haven't been able to find documentation that this is what it is doing. This might be a legacy from earlier versions of Excel. Pivot tables are a later technology, which (I think) were implemented after Excel moved to double precision, so would be comparatively immune from legacy code considerations.

edited Mar 01 '21 at 12:20

answered Mar 01 '21 at 11:12

John Coleman

51,337
7
54
119

Thanks, may I ask how did you check that VBA rounded the number to 0.0000000000325 in the source file? Is the source file not the worksheet which hides things? When I did a Debug.Print (a-b) on my cells, I also got the value 6.46234853557053E-27. I will read up more on IEEE 754, I haven't been able to derive the 6.46234853557053E-27 or how you got the next float. Are you able to explain to me why Debug.Print a printed a truncated value, while Debug.Print a-b was able to print to precision? Also, I found that if you double click on your B2 cell in the worksheet, B2 will now equal to B3 value. – Args Mar 02 '21 at 11:15
@Args Just type `Range("B2").Value = 3.2500000000000004e-11` in the editor. As soon as you hit enter, it is replaced by `0.0000000000325 `. What you see at that stage is the actual code in your sub. The editor apparently doesn't support entering `double` literals with that much precision. The next float I got from a Python script I wrote to explore the structure of IEEE floats. `6.46234853557053E-27` is the base 10 approximation of `2^-87`, so `-87` is the base-2 exponent here. – John Coleman Mar 02 '21 at 11:35
@Args My previous comment was mistaken, even though `Range("B2").Value = 3.2500000000000004e-11` is changed to *display* `Range("B2").Value = 0.0000000000325`, VBA is keeping the original value somewhere. This is very strange behavior, since it means that in the editor what you see isn't always what you get -- not a very good property for an IDE to have. There doesn't even seem to be any option to have the editor display float literals with more precision. – John Coleman Mar 02 '21 at 11:42
How did you ascertain that VBA kept the original value in a different location? Was it with Debug.Print a-b? I do wonder if there's a way to view what is REALLY going on with the excel cells, with some tool outside of excel, since worksheet and the VBA editor isn't showing what they have 100%. – Args Mar 03 '21 at 10:52
When I subsequently ran the code, it ran differently than it would have if I had simply typed 0.0000000000325. – John Coleman Mar 03 '21 at 11:00

What does VBA check for when you do an equal comparison?

1 Answers1