-1

Although the answer to the question in title may appear at first glance to be "yes", the following VBA behavior seems to indicate it is "no". The key word to understand my question is "explicitly".

I am using VBA7 on a Windows 10 pro, x64-based PC, with Option Base 1 to start array indexes from 1

If I declare a 1D array and then try to assign a range to it, as in the following example

Dim marray(1 To 11)
marray = Range(Cells(3, 1), Cells(3, 11))

I get the error message:

"".

The same error happens if I declare a 2D array with only one row like this

Dim marray(1 to 1, 1 To 11)

However, if I define just a variable (i.e. not an array) and then assign the range to it, as in the following example

Dim marray
marray = Range(Cells(3, 1), Cells(3, 11))

I get no error and I notice the VBA automatically creates a variant array of dimension (1 to 1, 1 to 11) as indicated in the following watch

enter image description here

So VBA decides that my array (variant) variable is an array of dimension (1 to 1, 1 to 11) as soon as I assign the range to it; however if I explicitly declare my variable to be an array VBA gives me an error. Can anybody explain me WHY? (It would allow me a deeper comprehension of how this strange language works)

Guille
  • 326
  • 2
  • 10
  • Part of the explanation, I think, is that you can't assign a [whole array](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/can-t-assign-to-an-array) to another array. The other part might be that Excel offers to you that you can populate an array in the way that you declare a variable with data type variant and then assign it like you did. – Storax May 28 '23 at 16:51
  • May [this](https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm) also helps – Storax May 28 '23 at 16:58
  • @freeflow: One could also debug and add `Range(Cells(3, 1), Cells(3, 11)).value2` to the watch window. In this way one could watch _live_ what's going on, at least partly. – Storax May 28 '23 at 17:09

1 Answers1

3

The error message says that you can't assign to an array. However, it would be more accurate to say that you can't assign to a fixed array, since you can assign to a dynamic array...

Dim marray() As Variant
marray = Range(Cells(3, 1), Cells(3, 11)).Value

Or, as you've discovered, you can assign to a Variant variable...

Dim marray As Variant
marray = Range(Cells(3, 1), Cells(3, 11)).Value
Domenic
  • 7,844
  • 2
  • 9
  • 17
  • Thanks to Domenic and Storax I got the answer. I omit the property "value" after the range because I thought it was the default. In fact, on https://learn.microsoft.com/en-us/office/vba/api/excel.range.value you can read: "The default member of Range forwards calls without parameters to Value. Thus, someRange = someOtherRange is equivalent to someRange.Value = someOtherRange.Value." But it seems it is not true in my example. MORAL: not trust too much on default values... – Guille May 28 '23 at 17:40
  • Actually, in both cases, it's not necessary to specify the Value property. However, it's a good idea to do so, at least for clarity. – Domenic May 28 '23 at 17:49
  • @Guille https://stackoverflow.com/a/21411922/11683 – GSerg May 28 '23 at 17:52
  • @Domenic https://stackoverflow.com/q/17733541/11683 – GSerg May 28 '23 at 17:57
  • @GSerg It looks like I misunderstood the comment. Yeah, in that case, it does matter. Thank you very much, I really appreciate it. Cheers! – Domenic May 28 '23 at 18:11