Variant Compare Help

I’m looking at the documentation on variants and it says

But what happens when both operands are variants?
Essentially I want if the variants are strings, to do a string compare but if they’re integers or doubles I want it to instead compare their values.

So far I’m just trying to case it out

if str(v.DoubleValue) = v.StringValue and str(v2.DoubleValue) = v2.StringValue // then Compare their double values
elseif str(v.IntegerValue) = v.StringValue and str(v2.IntegerValue) = v2.StringValue // then Compare their integer values

Is this necessary?

I also see that there’s an Operator_Compare method on the variant object but no examples of how to utilize this.
I’m trying to do a merge sort on an array of variants essentially, but I need to get the Compare working correctly.

IMO its never a good idea to compare two doubles for equality.

is 100.998726252443 the same as 100.9987262524429 ?

You could use format() here to force a certain number of decimal places

if format(v,"0.00") = format(v2,"0.00")

Comparing them as integers:
would this be better?

if    v.IntegerValue  =   v2.IntegerValue

since that already forces the conversion to integer?

I’m not comparing to check equality. I’m comparing so I can sort them into order. It’s actually more helpful if two doubles dont equal each other.

Variants are evil and should be used as sparingly as possible.

If you absolutely MUST use variants, check for their datatype, do an explicit conversion and compare them that way.

Are you leaking there’s going to be type inference?

There won’t be

If you use variants and rely on auto conversions then you get whatever “magic” or pain that brings along
Use explicit type accessors

Essentially I have an bunch of extension methods that work on arrays of objects.


I have a helper method that will turn arrays of primitives into arrays of (variant) objects so that they can be used with these extension methods, but implementing the OrderBy is difficult due to the fact that you don’t know what original type the variants came from and whether to sort them based on Value or String Order.

I wasn’t sure if there a good way to handle this or what the default comparing of variants would do.

Could you refactor to wrap the Variant array into a Class rather than working with a raw Variant Array.

With a class you can add a property to track the original array type which the helper methods would set. Then orderBy() could check that. And your extension methods could be moved to the class.

Class FlexiList list() As Variant sourceType As something End Class

Variant.TypeString TypeInteger etc tells you what the original type was