1

For static array, we do this:

Dim arr(10) as variant

If we are not sure of the array size, we do this:

Dim arr()

Redim arr(1 to Some_number)

Or

Redim preserve arr(Some_number)

As you can see, we always need to tell excel beforehand how big the array will be before putting in values.

But, if the values I am going to put in my array are cells (Ranges), then I do not need to define the array size, we put in values directly.

Dim arr()

arr = Range("A1:B100")

Anyone one knows the reason?

Thank you!

liu yubin
  • 11
  • 1
  • 1
    `then I do not need to define the array size` Yes this method dynamically creates the array and will be a two dimensional array. This is helpful when you want to store the range in an array at runtime. One more thing. You can predefine an array and you can loop through the range and store in an array but this way of directly storing the range in an array is much faster. – Siddharth Rout May 29 '21 at 06:11
  • Does this answer your question? [Why am I having issues assigning a Range to an Array of Variants](https://stackoverflow.com/questions/21386768/why-am-i-having-issues-assigning-a-range-to-an-array-of-variants) – Storax May 29 '21 at 06:15
  • You don't even need to declare it as an array. Any Variant variable will receive an array from this call. – Joffan May 29 '21 at 06:50
  • 1
    If you have `arr1` and you do `arr2 = arr1`, then you also did not resize the array. If you know that `Range("A1:B100").Value` 'is already' an array, then you can see that it's the same thing. Try this one-liner: `Debug.Print LBound(Range("A1:B100").Value, 1), UBound(Range("A1:B100").Value, 1), LBound(Range("A1:B100").Value, 2), UBound(Range("A1:B100").Value, 2)`. – VBasic2008 May 29 '21 at 07:15
  • Are you still looking for an answer? I can autbor a correct one if you are. Everything on this page misses the mark. – Excel Hero May 31 '21 at 22:47
  • If you are not interested in an answer this question should be deleted. – Excel Hero Jun 02 '21 at 18:42
  • Thank you everyone for answering the question! It is getting clearer to me and this question can stay here for other people's reference if they have the same confusions. – liu yubin Jun 04 '21 at 00:02
  • If you want a correct answer, then this question should remain open. But as it stands now the question should be closed. This page currently is unhelpful to others. – Excel Hero Jun 04 '21 at 03:55

1 Answers1

0

The reason for this is because range.value is an array of values itself, you are overwriting your empty arr with the range.

This is the same as if you have an array1 with some values and an array2 which is empty. Note than when doing array2=array1, array2 will now have all the values of array1 and also it will have inherited the dimension from array1.

Alex Ibrahim Ojea
  • 60
  • 10
  • 1
    This is not correct. A Range object is definitely not an array. But, the `.Value` property of the Range object does indeed return a SAFEARRAY of Variants. AND the `.Value` property is the default property of the Range object. – Excel Hero May 30 '21 at 05:01
  • Yes you are right, forgot the .value part, just checked the documentation. Thanks. – Alex Ibrahim Ojea May 30 '21 at 08:13
  • 1
    Still not quite right. The variable `arr` is a pointer. The `.Value` property of the Range object copies the values of the range into a new SAFEARRAY of Variants that it generates on the fly. That array is unnamed and has the same *shape* as the range. `arr = ` sets the value of the pointer to the address of the BASE of the new SAFEARRAY. – Excel Hero May 30 '21 at 16:39