Home
Up

The comparison operator bug

Here's another example of Excel's worksheet functions not operating in the same way as their VBA equivalents.

In cell A1 I put "ABCdef" and gave the cell a range name "AAAA".
In cell B1 I put "ABCDEF" and gave the cell a range name "BBBB"
In cell C1 I put the formula "=AAAA=BBBB", which gave a result of "TRUE".

 
Below is an extract from a workbook which uses the above formula to prove that the result is zero, and then the same figures in the MOD function generating an error.
 

 
   Missing: Microsoft Office Web Components
 
This page requires the Microsoft Office Web Components.

Click here to install Microsoft Office Web Components..

This page also requires Microsoft Internet Explorer 4.01 (SP-1) or higher.

Click here to install the latest Internet Explorer.
 

 
In VBA I created the following sub routine:
  Sub a()
MsgBox Range("AAAA") = Range("BBBB")
End Sub
 
 
It gave the answer "FALSE".
Conclusion: The comparison operator "=" does not work in the same way on a spreadsheet and in VBA.
Reference: