Cells that report being different until F2 + Enter

[GJL65]

When two cells report being different from each other until you activate them (F2) and press Enter, and then they report being the same as each other, what was the difference between them really, and is it possible to detect that difference without F2+Enter?

I'm comparing many extremely large sheets -- several million cells in the used range (sheet.UsedRange), and many of them over 100MB -- and reporting any differences found. Often only a few differences are found among the several millions cells.

To make the comparison faster, for each sheet being compared I'm setting the used range to a variant, turning the variant into a variant array:
Dim myVariantArray as Variant 'do NOT declare as array.
myVariantArray = mysheet.UsedRange

But sometimes it reports two cells -- two Variants in the Variant array -- as being different (VariantA = VariantB returns False) when I don't see any difference:
-- They are the same string (same characters).
-- They have the same number of characters (len).
-- Each character has the same Asc values and the same AscW values.
-- They have the same Typename and the same VarType.

When I go back to the source cells on the sheets from which the Variants in the array were made, the two cells also report as being different from each other (cellA = cellB returns False) and I find all of the above plus:
-- They have the same number formatting.

But if I just press F2+Enter, they cells report as being the same as each other (cellA = cellB returns True).

Using SendKeys to send F2 + Enter isn't a practical solution because the user will be running a report in the background that does comparisons like that on many sets of extremely large sheets, while the user does other work in the foreground.


So is there any other way to detect the fact that they are the same without doing the F2+Enter?

I'm especially interested in what is different in the Variants. In one example I'm looking at, they report a VarType of 5, indicating Double, and their Typename is also Double. So I don't think there is an object hiding in the Variant with some mysterious complexity. What is the difference, really, between those two Variants?


Thanks,

Greg

[Pete_UK]

I think the difference is that Excel is treating one as a number and the other as a string (that just looks like a number). Within Excel you can force one to the other either by adding zero (or multiply by one) to the one that is stored as text, or concatenating "" to the one stored as a number, so using your example, try these in different cells:

=cellA*1 = cellB
=cellA+0 = cellB
=--cellA = cellB
=cellA&"" = cellB
=""&cellA = cellB

and the other way round.

Hope this helps.

Pete

[GJL65]

Hi Pete,

Thanks, but it would be many times faster to just use CStr or CDbl on both, to ensure both are strings or doubles, before the comparison.

I'm more interested in why the *Variants* don't compare as equal. It isn't as if one is "stored as a number" while the other is "stored as a string". The Variants are not stored in the worksheet at all; they exist only while the code is running. Both have the same data type -- the same Typename and the same VarType.

Yet they don't compare as equal (VariantA = VariantB returns False). So what is the difference between them??

Greg