Use of Variants

**rwgrietveld** · 09-13-2008, 06:13 PM

Hi,
I rather not use Variants.
In my C++ programming time typecasting could cause unexpected and unwanted results.
Can someone point out the (absolute) need for the Variant type.
Gr,
Ricardo

**shg** · 09-13-2008, 06:29 PM

1. To receive an argument that may be of varying type (string, Boolean, number, or arrays of same)

2. To receive an indefinite number of arguments (ParamArray is a variant)

3. To receive the results from certain functions, like MMult() (I'd have thought that a dynamic array of Doubles would work, but I couldn't get it to) or Array()

4. To receive the value of a property that may return Null (e.g., selection.interior.colorindex for a non-homogeneous selection)

5. To receive the keys of a dictionary.

6. ...

**mikerickson** · 09-13-2008, 07:52 PM

6. To be the control variable of a For Each loop

7. To be the return type of a Function that returns an array of variable size.

8. To be a variable that derives its value from a worksheet cell.

9. ...

**shg** · 09-13-2008, 10:27 PM

Mike,

Thanks for continuuing the list -- I think it has sustaining value.

7. To be the return type of a Function that returns an array of variable size.

Not sure I agree with that; a dynamic array can be assigned a variable-size array -- for example, the results of Split. Maybe some narrow-fication?

**mikerickson** · 09-13-2008, 10:33 PM

I was thinking a UDF

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

That last UDF runs into some differences between Mac VBA and Windows VBA, but Variant is the required type for a Mac. (VBA v.5 instead of v.6 ?? )

**shg** · 09-13-2008, 11:57 PM

Mike,

I can't speak to differences between Windows and Mac, but in the second case, in Windows, you can declare

Please Login or Register  to view this content.

**mikerickson** · 09-14-2008, 12:29 AM

That is the part of the difference.
That statement will give an "Expected: end of statement error" to a Mac.
Also, this will work on Windows:

Please Login or Register  to view this content.

but gives a Mac a "Can't assign to array" error. The fix:

Please Login or Register  to view this content.

**rwgrietveld** · 09-14-2008, 07:06 AM

Thank you for all the answers.
Certainly helps.
Last additonal question. have you ever seen that the typecast failed, e.g. for MMult, Cint(), SumProduct, ... Functions?

**mikerickson** · 09-14-2008, 08:44 AM

I'm not sure what you mean by "typecast failed". I've had problems with all those functions, usually with the arguments.

BTW, for my situation, WorkSheetFunction.Sumproduct fails and Evaluate("=Sumproduct...") needs to be used in VB routines.