英文:
Excel VBA: can a range be EXPLICITLY assigned to an array variable?
问题
尽管乍看之下，标题中的问题的答案可能是“是”，但以下的VBA行为似乎表明答案是“否”。理解我的问题的关键词是“显式”。
我在Windows 10专业版，x64位PC上使用VBA7，使用Option Base 1来从1开始设置数组索引。
如果我声明一个一维数组，然后尝试将一个范围分配给它，就像下面的示例一样：
Dim marray(1 To 11)
marray = Range(Cells(3, 1), Cells(3, 11))
我会收到错误消息：
如果我声明一个只有一行的二维数组，就像这样：
Dim marray(1 to 1, 1 To 11)
同样会出现相同的错误。
然而，如果我只定义一个变量（而不是数组），然后将范围分配给它，就像下面的示例一样：
Dim marray
marray = Range(Cells(3, 1), Cells(3, 11))
我不会收到错误消息，而且我注意到VBA会自动创建一个维度为（1 to 1, 1 to 11）的变体数组，如下面的监视所示：
因此，VBA在我将范围分配给变量时决定我的数组（变体）变量是一个维度为（1 to 1, 1 to 11）的数组；然而，如果我明确声明我的变量为数组，VBA会给我一个错误。有人能解释为什么吗？（这将帮助我更深入地理解这种奇怪的语言）
英文:
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
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)
答案1
得分: 3
错误消息说你不能对数组赋值。然而，更准确地说，你不能对一个固定数组赋值，因为你可以对一个动态数组赋值...
Dim marray() As Variant
marray = Range(Cells(3, 1), Cells(3, 11)).Value
或者，正如你已经发现的，你可以对一个变体变量赋值...
Dim marray As Variant
marray = Range(Cells(3, 1), Cells(3, 11)).Value
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论