Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 Site http://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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
Your example is a perfectly reasonable use of variant. Variants are a very
powerful feature of VBA. In some other languages that don't have variants you end up doing a lot of work to get around the data type issue. For example if you want a numeric input from your user (who could enter enaything) you end up storing the input in a string and then having to validate the string to be numeric and then coerce the value to a number. The problem with variants is that they tend to be overused. If you can reasonably know the data type of the variable you should explicitly define it that way. It is more efficient but more importantly it is going to make your life easier. If you try to pass a string into a double you will get a simple error message letting you know that there is a problem. If you want a range object then if you define it as type range you will get intellisence drop downs when you use the variable. -- HTH... Jim Thomlinson "brzak" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I don't like variants but...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function - array with 2 variants | Excel Programming | |||
mismatch error and Variants | Excel Programming | |||
can I set row/column label as variants? | Excel Programming | |||
Variants vs ranges | Excel Programming | |||
Array Parameters as Variants Only | Excel Programming |