3

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?

Improve this question
7
  • you can also do MyArr4 = array() without Dim if you're not using Option Explicit Commented Jan 30, 2019 at 9:51
  • @reportgunner ehhm we should not recommend to switch off Option Explicit we actually should recommend to always turn it on. Commented Jan 30, 2019 at 10:04
  • @Pᴇʜ I am of the opposing opinion, but I was by no means recommending to turn it off. Commented Jan 30, 2019 at 10:40
  • A typed array as 4th "style": Dim MyArr2() As Double Commented Jan 30, 2019 at 10:43
  • 1
    The difference is that Dim MyArr() is definitly an array, and you cannot assign a value to it MyArr = 5 will fail. But 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. Commented Jan 30, 2019 at 11:06

3 Answers 3

Reset to default
3

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Do you mean that MyArr1 is not an array? I can fill it with MyArr1=[NamedRange].Value And NamedRange can consist of any rectangular size of cells, say A1:B10.
MyArr1 is a variable with variant data type
Is it an array variable or not?
@PrzemyslawRemin MyArr1 is Variant that means it can be anything, depending what you put into it. If you put in a string MyArr1 = "ABC" then it is a Variant/String. If you put in an array MyArr1 = Array("a", "b", "c") then it is a Variant/Array. You cannot tell just by its declaration.
@PrzemyslawRemin variant by definition can accept any type. if you assign an array or a result of a function that returns an array to MyArr1 it will be an array.
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.

Improve this answer

Comments

2

Variant can be anything, as we can read in Variant data type:

The Variant data type is the data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static).

The Variant data type has no type-declaration character.

You can use the Variant data type in place of any data type

This basically means that Dim MyArr1 As Variant implies that MyArr1 can also be array of variants.

But last two means that you are declaring Array of "anythings", but only the first can be anything :)

Improve this answer

2 Comments

Can you please explain your sentence But last two means that you are declaring Array of "anythings", but only the first can be anything :)? It sound philosophical:-)
@PrzemyslawRemin Think of Variant as "anything", even as array of Variant or any other datatype. But last two are arrays of variants, so they cannot be Integer etc., but they can be array of Integer, they can be array of anythings, but they cannot be anything (thay are arrays).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.