2

What's difference between two declarations Dim Array_1() as Variant and Dim Array_2 as Variant in VBA ?

If I then create arrays using above declarations :

Array_1 = Range("A1:A10")
Array_2 = Range("A1:A10")

in locals windows I see different descriptions for Array_1 and Array_2. I seems that Array_2 contain values of undefined type. Could I ReDim Array_2 ?

Community
  • 1
  • 1
Qbik
  • 5,885
  • 14
  • 62
  • 93
  • 2
    Simply put: Dim Array() as variant is creating an array containing values that are of the type Variant Dim Array as variant is just creating a variable that can contain a variant value which may also be an array (but really can be almost anything). – Tom Aug 10 '15 at 10:13
  • 1
    See also [How do I set up a “jagged array” in VBA?](http://stackoverflow.com/q/9435608/119775) – Jean-François Corbett Aug 10 '15 at 10:16
  • 2
    Also, Array is an existing function in VBA, so actually `dim Array as variant` won't work. Try something like `Dim arrayTest as variant`. – vacip Aug 10 '15 at 10:21
  • 1
    Unless you expand your question, the answer is just going to be, "One's an array, and the other isn't." – Jean-François Corbett Aug 10 '15 at 10:41
  • 3
    [Here](http://www.cpearson.com/excel/passingandreturningarrays.htm) is one of the differences. If you want to pass an "array" of any type to a function, you pass a single `Variant` instead of an array of `Variant`s. In the latter case, you have to pass an array of `Variant` objects, while in the former you can pass an array of any type. In addition, arrays are passed by reference while variables are passed by value. – Ioannis Aug 10 '15 at 11:26

1 Answers1

-1
Dim Arraytest() as Variant

Declare's a variable called Arraytest which is setup as an array The above can be used like below

ReDim Arraytest(9)

or

ReDim Arraytest(1 To 10)

To setup an array of 10 values - Arraytest(0) to Arraytest(9) in the first case and Arraytest(1) to Arraytest(10) in the second case.

As opposed to :

Dim Arraytest as Variant

Declare's a variable called Arraytest which is setup as a single varaible - note the second code can not be used as an array

Note: Array can not be used as a variable name - used Arraytest in the above

99moorem
  • 1,955
  • 1
  • 15
  • 27
  • 1
    @R3uK 0,1,2,3,4,5,6,7,8,9 is 10 values? why did you change redim Array(10) to 9? as "0" is counted as 1? – 99moorem Aug 10 '15 at 10:00
  • 1
    Yup, there is indeed 10 values between 0 and 9, like there is 10 between 1 and 10. I corrected it as your next line was pretty clear about this. – R3uK Aug 10 '15 at 10:06
  • 4
    *can not be used as an array*... yet `Dim Array as Variant : ReDim Array(1 To 10)` works fine. – Jean-François Corbett Aug 10 '15 at 10:14
  • 3
    Variant can be anything - even an array! `Dim arrayTest As Variant : arrayTest = Array(1, 2, 3)` This works fine. – vacip Aug 10 '15 at 10:19
  • 2
    Did you actually try this code? [Array](https://msdn.microsoft.com/en-us/library/aa262675(v=vs.60).aspx) is a VBA function, and `Dim Array() as Variant`, with or without parentheses gives a compile error.. – Ioannis Aug 10 '15 at 11:11
  • @99moorem Better, but still not ok. The second code **can** be used as an array, as a variant **can** hold arrays. – vacip Aug 11 '15 at 09:57