vba 为什么我在为变量数组分配范围时遇到问题

Why am I having issues assigning a Range to an Array of Variants

提问by user2140261

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")

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

这正如预期的那样，arr被分配了值A1:A10

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

现在为什么下面的代码行不起作用？

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

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.

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

But

但

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

And

和

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

DOES WORK

有效

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

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

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

Does Not Work

不起作用

and using Sheetsin place of Worksheetsalso all give the same error.

并使用Sheets代替Worksheets也都给出相同的错误。

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

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

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

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 WorkSheetsand Sheetsfunction regardless of how I define the sheet (I can use the index or the Name of the worksheet and all work fine)

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

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.

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

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

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

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

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

上面的代码不起作用，即使填充了A1：A3，日期，数值，字符串，公式什么的，它也会将空白写入B1：B3

But

但

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

And

和

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

doeswork!

确实有效！

Also working is:

还工作的是：

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

回答by Siddharth Rout

No it is not a bug.

不，这不是错误。

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)

关键是 Value 是 Range 对象的默认属性，那么为什么不隐式使用它呢？你有没有看我链接的问题？（来自聊天）

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.

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

You could also add a Watchto see the Typefor a particular object as shown in the pic below.

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

So when you say

所以当你说

arr = Range("A1:A10")

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 Rangeor a Variant

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

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

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

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

回答by L42

Let me clarify my comment.
It can't fit to comment to i post it as answer just to at least clear my point.

让我澄清一下我的评论。
不适合评论我将其作为答案发布，至少只是为了澄清我的观点。

Dim arr As Variant '~~> you declare arr as Variant as what Tim said

what does it mean?
It means that arrcan take on any form (eg. integer, string, array, object and all the other Variable Type)

这是什么意思？
这意味着arr可以采用任何形式（例如整数、字符串、数组、对象和所有其他形式Variable Type）

Dim arr() as Variant '~~> you declare arr() as array which may contain Varying `Data Type`

what does it mean?
It means that arr()array variable can store different Datatypes.
That excludes Objectsor Collection of Objects.

这是什么意思？
这意味着arr()数组变量可以存储不同的Data类型。
那不包括Objectsor Collection of Objects。

Now, why the following works:

现在，为什么以下工作：

1. Dim arr() As Variant: arr = Range("A1:A10")
2. Dim arr() As Variant: arr = Sheet1.Range("A1:A10")
3. Dim arr() As Variant: arr = Sheets("Sheet1").Range("A1:A10").Value

This also works:

这也有效：

4. Dim arr() as Variant
   Dim rng as Range

   Set rng = Sheets("Sheet1").Range("A1:A10")
   arr = rng

Above works because you are not trying to assign Collections of Objectsinto an array.
Instead, you are assigning a specific entity or value.
Rangeis an object but not a Collection of Objects.
No.1 example is direct without accessing Sheets Collection Object.
Same is true with
No.2 since you work with Sheet1which is a SheetObject but not Collection of Sheet Objects.
No.3 is self explanatory, you assign .Valueto an arrarray.
No.4 works because rngis already a Rangeobject by Setwhich again is not a Collection of Objects.

以上有效，因为您没有尝试分配Collections of Objects到数组中。
相反，您正在分配特定的实体或值。
Range是一个对象但不是一个Collection of Objects.
第一个例子是直接没有访问Sheets Collection Object. No.2
也是如此，
因为您使用的Sheet1是SheetObject 而不是Collection of Sheet Objects.
No.3 是不言自明的，您分配.Value给一个arr数组。
No.4 有效，因为rng它已经是一个Range对象Set，它又不是Collection of Objects.

So this:

所以这：

Dim arr() As Variant

arr = Sheets("Sheet1").Range("A1:A10")

doesn't work because Excel will read this as trying to assign Objectfrom Sheets Collection of Objectsand thus error occurs.
I hope this makes sense a bit.

不起作用，因为Excel将看这是试图分配Object的Sheets Collection of Objects，因此会出现误差。
我希望这有点道理。

回答by PatricK

I would say an Array of Something is not the same as a Something, since this Something can be an Array of some other things. If you define something as an Array, what you assign to it has to be an Array, be it an Array of Number, Text, Range, Chart objects, etc.

我会说一个Something的数组与一个Something不同，因为这个Something可以是一个其他一些东西的数组。如果您将某物定义为数组，则分配给它的内容必须是数组，无论是数字、文本、范围、图表对象等的数组。

When things work that we don't expect, I believe it's the built-in data type conversion that makes things easy for us most of the time. This conversion may have to be a direct object, not properties of an object.

当事情出乎我们的意料时，我相信是内置的数据类型转换让我们在大多数情况下都变得容易。此转换可能必须是直接对象，而不是对象的属性。

For example, Rows and Cols are of type Long, but you can throw the Byte/Double type at it:

例如， Rows 和 Cols 是Long类型，但您可以将 Byte/Double 类型扔给它：

Cells(1,1.5)gives value of Cells(1,2)

Cells(1,1.5)给出值 Cells(1,2)

You don't have to convert 1.5 to Long; Excel does it all in background for you.

您不必将 1.5 转换为 Long；Excel 会在后台为您完成所有操作。

When you define an array of something and assign things to it, Excel does type matching behind the scene and set values when possible.

当您定义一个数组并为其分配内容时，Excel 会在幕后进行类型匹配并在可能的情况下设置值。

Check with these in the Immediate window:

在“立即”窗口中检查这些：

?typename(Range("A1:A10").Value)gives you Variant()<-- this is why it works on Dim arr() As Variantwithout any issues

?typename(Range("A1:A10").Value)给你Variant()<-- 这就是为什么它可以正常工作Dim arr() As Variant而没有任何问题

?typename(Range("A1:A10"))gives you Range. But when you assign it to arrwhere Dim arr() As Variant, Excel converts the Range to an Array using the values of that Range.

?typename(Range("A1:A10"))给你Range。但是，当您将其分配给arrwhere 时Dim arr() As Variant，Excel 会使用该范围的值将范围转换为数组。

However, Excel seems to fail converting if it doesn't have direct access to the object, unless you created memory for it. For example:

但是，如果 Excel 不能直接访问对象，则它似乎无法转换，除非您为它创建了内存。例如：

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

The above code is all fine, but it cannot convert and assign arr = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")in one go, unless you throw an array to it (ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Valueis of type Variant()).

上面的代码都很好，但它不能arr = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")一次性转换和分配，除非你向它抛出一个数组（ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Value类型为Variant()）。