Consider
Dim u As Variant, v() As Variant
u = Range("A1:B3").Value
v = Range("A1:B3").Value
Is there now any difference between u and v? And if not, is it superfluous to declare v as a variant array as opposed to declaring it simply as a variant?
There are differences. Think of a Variant
as being something that exists at a low level on your machine and has an application programming interface (API) to VBA. That low level thing is a VARIANT
and Windows ships with several functions that allow you to manipulate it in many languages; VBA being one of them.
v
is a Visual Basic array of such VARIANT
s, u
is a single one.
One way of spotting the difference is to use VarType(v)
which evaluates to vbArray + vbVariant
. VarType(u)
evaluates to vbEmpty
. You can test this prior to assignment to the range contents.
In your specific case, Excel-VBA is doing something funky (I don't think there is any other term) when assigning to v
: it "knows" that the destination type is an array and performs a slightly different coercion. u
and v
are exactly the same having been set to the value of an Excel.Range
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With