Declaring array variable in VBA

Question

What is the difference in declaring array variable as of these three styles:

Dim MyArr1 As Variant
Dim MyArr2() As Variant
Dim MyArr3()

Are all these three dynamic variables?

I cannot find a link to documentation where those three are compared.

Update. I really cannot see a difference when these two codes are executed:

code 1

Dim MyArr
MyArr = Range("a1:c10").Value

code 2

Dim MyArr() As Variant
MyArr = Range("a1:c10").Value

Does it really matter if we declare array in any of the three ways listed above, as long as we want to assign values to array reading them from a range?

MyArr=Range("A1:C10").Value

Is it so that when the line above is being executed then declaring variable happens just before filling it with values. Just as if we had a hidden line of code:

Dim MyArr(10,3) As Variant '10 rows, and 3 columns

So does MyArr become a static array variable when we read data to it from a range?

Answer 1

Dim MyArr1 As Variant - MyArr1 is variable with variant type
Variant type can hold any data type or object

Below two are one and same. Its dynamic array whose size is not know at design time.

Dim MyArr2() As Variant
Dim MyArr3()

---

Sub ArayTest()

    Dim MyArr1 As Variant
    Dim MyArr2() As Variant
    Dim MyArr3()

    MyArr1 = 10
    MyArr1 = #1/30/2019#

    '--> You cannot directly assign value to array
    'MyArr2 = 20
    'MyArr3 = 30

    '--> Use Redim to set the size of array

    ReDim MyArr2(1)
    MyArr2(0) = "abc"
    MyArr2(1) = 1324

    '--> variant can hold object
    MyArr1 = Array(1, "s", #1/30/2019#)
End Sub

---

Answer 2

The difference between …

• Dim MyArr() or
  Dim MyArr() As Variant (which are the same) and
• Dim MyArr or
  Dim MyArr As Variant (which are also the same)

… is that Dim MyArr() is definitly an array, and you cannot assign a value to it: MyArr = 5 will fail.
While Dim MyArr is completely undefined and can either assigned …

• a value MyArr = 5 (will work)
• or an array MyArr = Range("a1:c10").Value
• or a range Set MyArr = Range("a1:c10")

So be as specific als possible if you declare a variable. That means if you want to do

MyArr = Range("a1:c10").Value

then always make sure it is declared as array Dim MyArr() As Variant which is the most specific option here. So no one can put something else than an array into it. The more specific you are the more save is your code.

Also every coder sees that in a variable Dim MyVar() should be an array while in Dim MyVar they don't know what is expected.