Hi Charles,

Yes, that's the main thing I use them for, in fact the microsoft blog
on excel recently posted a comparison of the various methods of
dealing with data on the worksheet, they have some nice tables showing
differences in time. that's he

http://blogs.msdn.com/excel/archive/...-in-excel.aspx

What about in the example i posted - what would your approach be
there?

Thanks

Nice website btw


On Oct 9, 12:56*pm, "Charles Williams"
wrote:
Although using variants is slower and uses more memory than using more
tightly defined datatypes most of the time you will not notice any speed
difference.

The major reason for using Variants is that an Excel cell, like a variant,
can contain many different kinds of data (Empty, string, double, Error,
boolean etc), and is often the result of a formula that can return multiple
datatypes (double or error for instance).

So it is simpler and more efficient to get the values from a range of Excel
cells into a variant rather than try to handle the cells one by one and
determine their datatypes and then put them into the appropriate VBA
variable.

Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"brzak" wrote in message

...

Form what i hear / read, there are many reasons not to use a variant.
I've found couple of nice uses for it though and would like to get
some feedback on what other people think.

One example i would have thought where it's use would be allowable
would be if there is a case where a loop through a group is required
that does not follow any simple pattern.

Say four of the seven days in the week required traversing:

* *Sub Using_Variant()
* * * *Dim Day As Variant
* * * *For Each Day In Array("Monday", "Wednesday", "Saturday",
"Sunday")
* * * * * *Debug.Print Day
* * * *Next Day
* *End Sub

seems tidier than:

* *Sub Without_Using_Variant()
* * * *Dim Day(1 To 4) As String
* * * *Dim n As Integer
* * * *Day(1) = "Monday"
* * * *Day(2) = "Wednesday"
* * * *Day(3) = "Saturday"
* * * *Day(4) = "Sunday"
* * * *For n = LBound(Day) To UBound(Day)
* * * * * *Debug.Print Day(n)
* * * *Next n
* *End Sub

I'd be interested to see what other more experienced people might
think.

What is the point of a variant? It must exist to serve some purpose?
To me it seems a bit lazy to use it, as you should know what you want
- is it to overcome incompatibilities between the existing data types?

And getting back to the above example, if the day name was used
extensively, even though the variant takes a string and has type
Variant/String - would the loss in performace mean that those extra
few lines at the start would be worth it.

The second option would be more attractive if it were possible to do
this:

* *Dim Day(1 to 4) as String
* *Day() = Array("Monday", "Wednesday", "Saturday", "Sunday")

but no, the number of lines taken up is dictated by the number of
elements in the array...

Thanks for listening1