Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any difference between variant() and variant?

Tags:

excel

vba

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?

like image 253
Excel Developers Avatar asked Dec 02 '14 13:12

Excel Developers


1 Answers

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 VARIANTs, 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.

like image 79
Bathsheba Avatar answered Oct 02 '22 03:10

Bathsheba