You've had an answer and a half there; the question was deleted October 2013 after deletion votes by community members.
As always, we should use the right tool for the job. In VBA, I think
variants are quite often the right tool. (But just so it's clear, I
too am against the "reckless" use of any language feature.)
First, and in general, "Should we avoid variants?" is just a
VBA-specific version of the larger question "Is static typing better
than dynamic typing?" If there is ever a programming question where
the answer is "it depends", it's that. For example. the authors of
this book clearly like static type systems, but even they
say:
It's important to recognise that each
of these approaches involves
tradeoffs. Very briefly put, the
[static] perspective emphasises
safety, while the dynamically typed
outlook favours flexibility. If
someone had already discovered one way
of thinking about types that was
always best, we imagine that everyone
would know about it by now.
I think many VBA programs fall into the scripting/prototyping
category, and can thus make profitable use of variants. That doesn't
mean that only variants should be used, but they certainly shouldn't
be avoided "just because". That's cargo-cult programming. (It's
not the only cargo-cult you see a lot in VBA. "Every routine needs an
error handler" is another. See
VBA Error "Bubble Up")
It seems like many VBA resources come down very hard against using
variants, and do make blanket statements about how they are best
avoided. My own guess is that this is mainly a trickle-down from the
previous history of VB. VB, as opposed to VBA, was much more
appropriate for the development of large systems where static typing
and object-oriented designs get more useful.
To address the more specific question of whether we should avoid
variants even when we know the data type we're dealing with, I'd say
that the difference between the two approaches in the answers to the
linked question,
VB - Append to array?, speak
for themselves:
arr(i) = Split(arr(i), "=")(1)
vs.
arr(i) = Mid$(arr(i), InStr(arr(i), "=") + 1)
I edited them a bit to clarify the point, but surely the former is
more obvious, and thus "better" in the context of what is basically a
little file-parsing script. The latter probably performs better, but
how often does that matter for this kind of thing? (And if it does
matter, why are you doing it in VBA anyway?)
EDIT: Here is another example of a situation in which I think it's
cleaner to use variants, even though it's possible to do everything
statically. I've simplified it a bit from real code, but hopefully not
too much.
Here is a generic, Variant-using function to find the size of an
array in the first dimension:
Public Function lenOfArr(arr) As Long
Debug.Assert IsArray(arr)
lenOfArr = UBound(arr) - LBound(arr) + 1
End Function
Note the assertion. So we still get a little bit of "type checking",
but only for "is array", and we only get it at run time, in debug
mode, instead of at compile time. In return for this and a performance
hit, though, we get to have a single function in our library of array
utilities. It would certainly be possible to define multiple utilities
like this:
Public Function lenOfStrArr(arr() As String) As Long
lenOfStrArr = UBound(arr) - LBound(arr) + 1
End Function
and get more safety and performance. But honestly, it would be a pain,
at least in the kind of VBA code (mostly supporting models in Excel)
that I write a lot of. And that's the point. Whether or not to use
variants, even when you could avoid them, depends on the context of
what you're trying to do.
Now here is a non-simplified example from real code:
'Filters a collection of worksheets based on a predicate
Public Function shtsFiltered(shts, predName As String) As Collection
Const PROC As String = "shtsFiltered"
Dim dbg As FW_Dbg: Set dbg = mkDbg(MODL_, PROC, shts, predName)
Call dbg.assert(expIsObj(shts))
Call dbg.assert(TypeOf shts Is Sheets Or TypeOf shts Is Collection, "Can only filter a collection of Worksheet objects")
Set shtsFiltered = New Collection
Dim sht As Worksheet
For Each sht In shts
If sheetPredicate(sht, predName) Then
Call shtsFiltered.Add(sht)
End If
Next sht
Call dbg.exiting(shtsFiltered)
End Function
It's using some other library code, but you don't need to know
everything that's going on for this discussion. The purpose of the
routine is to take a collection of Worksheet objects and return a
collection of the ones that meet some criteria. But because of the way
Excel works, it needs to be able to take either an input of type
Sheets, or of type Collection. So that requires a parameter of
type Variant, even though everything else can be explicitly typed.
As with the case of the array length functions above, it would be
possible to define a Sheets version and a Collection version of this routine. But because the routine is non-trivial, that would mean
repeating logic (very bad) just to avoid using variants.
Finally, in VBA used with Excel (and I know you're not questioning
this but it's worth mentioning for completeness), Variant is
practically the native data type. It's almost impossible to write
decent Excel VBA UDFs without using it. For example, I do this all the
time:
Public Function calcSomething(parm)
On Error GoTo EH
If IsError(parm) Then
calcSomething = parm
Exit Function
End If
'Do calculation...
calcSomething = 42 'or whatever...
Exit Function
EH:
calcSomething = CVErr(xlErrValue)
End Function
That's a trivial example, but it should make the point. There are
plenty of more involved examples that make use of variants, such as
arrays of arrays:
How can I "ReDim Preserve" a 2D Array in Excel 2007 VBA so that I can add rows, not columns, to the array?
Variantdata types and under what situations they should be avoided, so maybe the deletion was for being a subjective question.