Oh, I forgot one other possibility you can make use of. If you do not have a
long list of items, you can also use the Choose function...

Dim X As Long
For X = 1 To 4
Debug.Print Choose(X, "Monday", "Wednesday", "Saturday", "Sunday")
Next

--
Rick (MVP - Excel)


"brzak" wrote in message
...

Thanks Rick,

That's one less place I need to use the variant type. That last
example is probably the one I would most favour, only one extra line
of code my original variant example, but a lot clearer.

I agree Jim, I've also seen it overused, can make it messy and
unnecessarily slow.

One other interesting use of the variant that has been as a method to
work out the type of something.

That's instead of trawling through the sometimes unnavigable help file
- would be nice if search functionality was improved so taht you could
"Windows-Live-search" it... or not :)


On Oct 10, 12:43 am, "Rick Rothstein"
wrote:
You could always do your loop either of these ways without needing a
Variant
variable...

For X = 0 To 3 ' for Option Base 0; 1 To 4 for Option Base 1
Print Array("Monday", "Wednesday", "Saturday", "Sunday")(X)
Next

For X = 0 To 3 ' Split always returns a zero-based array
Print Split("Monday Wednesday Saturday Sunday")(X)
Next

Although that last one would be slightly more efficient if done this
way...

Dim Days() As String
Days = Split("Monday Wednesday Saturday Sunday")
For X = 0 To 3 ' although you can use UBound(Days) if unsure of how many
elements
Print Days(X)
Next

--
Rick (MVP - Excel)

"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