Here's another example of Excel's
worksheet functions not operating in the same way as their VBA
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
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.
In VBA I created the following sub
MsgBox Range("AAAA") = Range("BBBB")
It gave the answer "FALSE".
Conclusion: The comparison operator
"=" does not work in the same way on a spreadsheet
and in VBA.