Variants look the same but report being different -- how are they different?

[GJL65]

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 myVariantArrayA as Variant 'do NOT declare as array.
myVariantArrayA = mysheet.UsedRange

But sometimes it reports two cells -- each a Variant in one of the two Variant arrays -- 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 as the correstponding character in the other string.
-- 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.Value = cellB.Value returns False) and I find all of the above plus:
-- They have the same number formatting.

If I press F2+Enter on the cells, the cells report as being the same as each other (cellA.Value = cellB.Value 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? And, is it possible to detect the difference in code?


Thanks,

Greg

[YvorL]

Hi!

You only have to compare values or there are some other things?
How are these cells filled? Manually/different macros, etc.

[JapanDave]

Where is the data coming from? Is it being imported?

[GJL65]

Hi yvorL,

You only have to compare values or there are some other things?

I'm only comparing values, but if the mystery difference is a result of a difference in the source, then I may need to report that there may be a difference there.

How are these cells filled? Manually/different macros, etc.

They are exported from a database called Apptio. I export tables from the Dev and Stage environments that should be identical, and report any differences found. Differences are often found.


Thanks,

Greg

[GJL65]

Hi JapanDave,

Where is the data coming from? Is it being imported?

They are exported from a database called Apptio.


Thanks,

Greg

[JapanDave]

If that is the case then they are most likely different in some way. IME you will have to clean the data first, then compare it. Can you post a sample workbook with the actual data?

[YvorL]

If that is the case then they are most likely different in some way.

I agree.
Try Trim, or something like that to get rid of any possible difference.

[Norie]

What sort of data is it?

Is it all text?

How exactly are you comparing the data?