为什么我在为变量数组分配范围时遇到问题 [英] Why am I having issues assigning a Range to an Array of Variants

问题描述

我在使用一些非常简单的代码行时遇到了一些问题.让我详细说明事实，看看其他人是否可以复制这种行为.如果有人可以复制，我想解释一下为什么会发生这种情况.

I am having a few problems with some VERY simple lines of code. Let me detail the facts and see if anyone else can replicate this behavior. If any one can replicate I would like to get an explanation of why it is happening.

所以让我从一行非常简单的代码开始:

So lets me start with a very simple line of code THAT WORKS:

Dim arr() As Variant
arr = Range("A1:A10")

这符合预期，arr 被分配了 A1:A10

this does as expected, arr is assigned the Values of A1:A10

现在为什么以下代码行不起作用?

now why won't the following line of code work?

Dim arr() As Variant
arr = WorkSheets("Sheet1").Range("A1:A10")

我收到运行时错误13"类型不匹配，即使相同的范围已成功分配给数组，只是没有工作表值.

I get a Run-Time Error '13' Type mismatch, even though the same range was successfully assigned to the array, just without the Worksheet value.

但是

Dim arr As Variant
arr = Worksheets("Sheet1").Range("A1:A10")

还有

Dim arr() As Variant
arr = Application.Transpose(Application.Transpose(Worksheets("Sheet1").Range("A1:A10")))

有效

现在在你回答之前，请让我给你更多的事实.

Now before you answer please let me give you some more facts.

Dim arr() As Variant
arr = Worksheets(1).Range("A1:A10")

不起作用

使用 Sheets 代替 Worksheets 也会出现同样的错误.

and using Sheets in place of Worksheets also all give the same error.

我已经通过使用 Range("A1:A10").Worksheet.Name 按照工作代码确保它与活动引用的工作表相同，它确实说 Sheet1 在输出中.

I have made sure it is the same sheet as the active referenced sheet by using Range("A1:A10").Worksheet.Name Following the working code and it indeed says Sheet1 in the output.

没有其他工作簿处于打开状态，因此它也不能引用另一个工作簿.

No other workbooks are open so it can't be referencing another workbook either.

现在这最后一点代码只会增加我的困惑，因为它完全有效！

Now this last bit of code only adds to my confusion as it totally works!

Dim arr() As Variant
Dim SampleRange As Range

Set SampleRange = Worksheets("Sheet1").Range("A1:A10")
arr = SampleRange

因此，当我将其分配给范围变量时，现在可以使用在同一张纸上以相同方式定义的相同范围.并使用它！正如预期的那样，无论我如何定义工作表，这都适用于 WorkSheets 和 Sheets 函数(我可以使用工作表的索引或名称，一切正常)

So using the SAME RANGE defined the same way on the same sheet now works when I assign it to a Range Variable. and use that! And as expected this works with both the WorkSheets and Sheets function regardless of how I define the sheet (I can use the index or the Name of the worksheet and all work fine)

如果它对任何人有帮助，我正在 Windows XP 机器上使用 Excel 2007 进行测试.我还没有在任何其他机器上测试过它，但我计划在 2003 和 2010 上在 Windows 7 和 8 上进行测试，只是还没有机会.

If it helps anyone, I am testing this with Excel 2007 on a Windows XP machine. I have not yet tested it on any other machines but I plan to test on 2003 and 2010 on Windows 7 and 8, just haven't had the chance yet.

更新: 不是 100% 确定这是否与数组完全相同，但从浅薄的角度来看，它似乎是:

UPDATE: Not 100% sure if this is the same exact issue as with the array but from a shallow view it seems to be:

 Range("B1:B3") = Range("A1:A3") 

上面的代码行不通，即使填充了A1:A3，日期，数值，字符串，公式什么的，都会往B1:B3中写入空格

The above code will not work, even if A1:A3 is populated, dates, numeric values, strings, formula anything, it will write blanks into B1:B3

但是

Range("B1:B3").Value = Range("A1:A3").Value

还有

Range("B1") = Range("A1")

确实有效！

还有:

Range("B1:B3") = Application.Transpose(Application.Transpose(Range("A1:A3")))

推荐答案

不，这不是错误.

重点是 Value 是 Range 对象的默认属性，那为什么不隐式使用呢?你有没有看我链接的问题?(来自聊天)

The point is that Value is the default property of the Range Object, so why isn't it implicitly used? Did you have a look at the question I linked? (FROM CHAT)

之前贴出答案的专家已经解释的很详细了.我会尽量减少解释，因此如果您还有任何问题，请告诉我.

The experts posting previous answers have already explained very well in details. I will keep the explanation to minimal and hence let me know if you still have any questions.

让我们先了解我们的对象.我创建了这个小表格，它清楚地显示了我们正在处理的内容，以免混淆.

Let's understand our objects first. I created this small table which clearly shows what are we handling so that there is no confusion.

您还可以添加 Watch 以查看特定对象的 Type，如下图所示.

You could also add a Watch to see the Type for a particular object as shown in the pic below.

所以当你说

arr = Range("A1:A10")

Excel 知道默认属性是 .Value.但是在其他情况下，它不知道，因为 Excel 不是读心者，或者说足够聪明以了解您是否要使用 Worksheets("Sheet1").Range("A1:A10") 作为 Range 或 Variant

Excel knows that the default property is .Value. However in other case, it doesn't know because Excel is not a mind reader or let's say intelligent enough to understand whether you want to use Worksheets("Sheet1").Range("A1:A10") as a Range or a Variant

一旦您将对象明确指定为 Range，Excel 就会知道您想要什么.例如，这有效.

Once you explicitly specify your object as a Range then Excel knows what you want. For example this works.

Dim arr() As Variant
Dim Rng As Range  
Set Rng = Worksheets("Sheet1").Range("A1:A10")
arr = Rng

这篇关于为什么我在为变量数组分配范围时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！