Is there a difference between option () and option?

Question

Consider

Dim u As Variant, v() As Variant u = Range("A1:B3").Value v = Range("A1:B3").Value 

Is there any difference between u and v? And if not, is it unnecessary to declare v as a variant array, and not just declare it as an option?

Answer 1

There are differences. Think of Variant as something that exists at a low level on your computer and has an application programming interface (API) for VBA. This low-level thing is Variant , and Windows comes with several features that allow you to manipulate it in many languages; VBA is one of them.

v is an array of Visual Basic of such Variant s, u is one.

One way to tell the difference is to use VarType(v) , which evaluates to vbArray + vbVariant . VarType(u) has the value vbEmpty . You can check this before assigning to the contents of the range.

In your specific case, Excel-VBA does something funky (I don’t think there is any other term) when assigning v : it “knows” that the type of destination is an array and does a slightly different compulsion. u and v exactly the same as the value of Excel.Range .