Avoiding Variants

[Opv]

I was reading recently that the use of Variants should be avoided when variables can otherwise be defined with more specificity. I have used Variants to split strings so that I can manipulate the substrings as needed. I just learned that I can accomplish the same thing without declaring a variant variable by simply using the Split function, i.e:

[VBA]
Dim r As Range
Set r = ActiveSheet.Range("A1")
MsgBox Split(r.Value)(0) ' or any number within the substring count
'Or
MsgBox Split(r.Value)(UBound(Split(r.Value)))
[/VBA]

Would splitting the strings as shown above be any better or worse than declaring a variant variable and then splitting the string?

[mikerickson]

The best would be to declare a variable and assign the Split to that

[VBA]Dim myArr as Variant

myArr = Split(r.Value)

MsgBox myArr(0)
MsgBox myArr(Ubound(myArr))[/VBA]This avoids the slowing induced by repeatedly executing the Split function.

Its the traditional trade-off between speed and memory. In modern computers, the large amount of available memory makes speed more valuable.

Optimizing by tightening up data types is a later step in the optimization process.

[Opv]

The best would be to declare a variable and assign the Split to that

[vba]Dim myArr as Variant

myArr = Split(r.Value)

MsgBox myArr(0)
MsgBox myArr(Ubound(myArr))[/vba]This avoids the slowing induced by repeatedly executing the Split function.

Its the traditional trade-off between speed and memory. In modern computers, the large amount of available memory makes speed more valuable.

Optimizing by tightening up data types is a later step in the optimization process.

Thanks. Makes sense.

[snb]

there's no difference between:

[VBA]
sub tst()
sn=Split("AA|bb", "|")
end sub
[/VBA]

and

[VBA]Sub tst()
Dim sn As Variant
sn = Split("AA|bb", "|")
End Sub[/VBA]
So declaring is redundant

In this case the resulting vartype is different:
[VBA]
Sub tst()
Dim sn() As String
sn = Split("AA|bb", "|")
End Sub
[/VBA]
But the use of this array isn't.

[Opv]

Thanks. I have been quite confused as to the distinction (as well as the advantages/disadvantages) between conventional arrays and so-called variant arrays, i.e., why and how they are different and what it means in terms of efficiency, if anything. The only noticable difference I've found is that I can force the LBound in a conventional array to 1 with Option Base 1, whereas I can't with a regular variant array. Since I can accomplish the same thing functionally with either, is one preferred over the other?

[Aflatoon]

A Variant can contain anything, including an array. An Array can be of any type, including Variant. A variant containing an array is not the same as an array of type Variant (the latter is always an array, the former could be anything at all).

If you can type your variables to something other than Variant, I recommend it but sometimes a Variant just makes life very easy (such as creating an array from a range). I suggest you always declare your variables (even Variants), if only to catch typos.

[mikerickson]

There is a slight performace increase in declaring variable. Declaring non-variants gives you the most boost, but even declared variants are faster than undeclared variants.

The biggest reason to declare variables is that Option Explict requires it and, without Option Explicit, debugging the mis-spelling of variable names becomes a big issue.

[Opv]

If you can type your variables to something other than Variant, I recommend it but sometimes a Variant just makes life very easy (such as creating an array from a range).

Interesting that you should mention that. It sounds like that might be the answer to why I've experienced problems creating an array from a range and resorted to the use of variants to begin with. I wonder what the distinction is between a string from a range.value vs a manually created string.

[Opv]

There is a slight performace increase in declaring variable. Declaring non-variants gives you the most boost, but even declared variants are faster than undeclared variants.

The biggest reason to declare variables is that Option Explict requires it and, without Option Explicit, debugging the mis-spelling of variable names becomes a big issue.

Thanks as always for your insight.