为什么我将一个范围分配给一组变量 [英] Why am I having issues assigning a Range to an Array of Variants

问题描述

我有一些非常简单的代码行问题。让我详细说明一下事实，看看有没有人可以复制这个行为。如果有人可以复制，我想要解释为什么会发生这种情况。

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

  Dim arr（）As Variant 
 arr = Range（A1：A10）
  

这与预期的一样， arr 被赋值为 A1：A10

现在为什么下列代码行不行？

  Dim arr（）As Variant 
 arr = WorkSheets（Sheet1）。Range（A1：A10）
  / pre> 
 
 
我得到一个运行时错误'13'类型不匹配，即使相同的范围成功分配给数组，只是没有Worksheet值。 
 
 
 
但是
  Dim arr As Variant 
 arr =工作表（Sheet1）。范围（A1：A10）
  
 And 
  Dim arr（）As Variant 
 arr = Application.Transpose（Application.Transpose（Worksheets（Sheet1）。Range（A1 ：A10）））
  
 DOES WORK 
 
 
 
在你回答之前，请让我再给你一些事实。
  Dim arr（）As Variant 
 arr =工作表（1）.Range（A1：A10）
  
无效
 
 
 
并使用表单代替工作表也都给出相同的错误
 
 
 
使用 Range（A1：A10）确保它与活动的参考表格相同。Worksheet.Name 按照工作代码，它确实在输出中说 Sheet1 。 
 
 
 
没有其他工作簿打开，所以它不能引用另一个工作簿。
 
 
 
现在最后一点的代码只会增加我的混乱，因为它完全有效！
  Dim arr（）As Variant 
 Dim SampleRange As Range 
 
设置SampleRange =工作表（Sheet1）。范围（A1：A10）
 arr = SampleRange 
  
所以使用相同的范围定义相同的方式在同一个工作表现在工作，当我分配到一个范围变量。并用它！正如预期的那样，无论我如何定义表单，我都可以使用 WorkSheets 和 Sheets 函数（我可以使用索引或工作表的名称，所有工作正常）
 
 
 
如果它帮助任何人，我在Windows XP机器上使用Excel 2007进行测试。我还没有在任何其他机器上测试，但是我打算在2003年和2010年在Windows 7和8上进行测试，还没有机会。
 
 
 
  更新： 不能100％确定这是与阵列相同的确切问题，但从浅视图看来，它似乎是：
 范围（B1：B3）=范围（A1：A3）
  
上面的代码将不起作用，即使填充了A1：A3，日期，数值，字符串，公式等等，它会将空白写入B1：B3 
 
 
 
但是
 范围（B1：B3）值=范围（A1：A3 ）.Value 
  
和
 范围（B1）=范围（A1）
  
 
 
 
 
另外工作是：
  Range（B1：B3）= Application.Transpose（Application.Transpose（Range（A1：A3）））
  
 
 
解决方案
不，这不是一个错误。关键是Value是Range对象的默认属性，那么为什么不隐式使用它呢？你有看过我链接的问题吗？ （FROM  CHAT ）
 
 
发布先前答案的专家已经详细解释了。我会把解释尽量少，因此如果你还有任何问题，请告诉我。 
 
 
 
我们首先了解我们的对象。我创建了这个小表，它清楚地显示了我们处理的内容，这样就没有混淆。
 
 
 
  
 
 
 
您还可以添加 Watch  
 
 
 
  
 
 
 
所以当你说
  arr =范围（A1：A10）
  
 Excel知道默认属性是 .Value 。但是在另一种情况下，它不知道，因为Excel不是心智读者，或者让我们说足够聪明，以了解你是否想使用工作表（Sheet1）。范围（A1：A10） 作为范围或变式 
 
 
 
一旦您将对象明确指定为 Range ，Excel就会知道您想要什么。例如这个工作。
  Dim arr（）As Variant 
 Dim Rng As Range 
 Set Rng =工作表（Sheet1）。范围（A1：A10）
 arr = Rng 
  
 
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, 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")
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. 

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 Sheets in place of Worksheets also all give the same error.

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

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.

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

But 
Range("B1:B3").Value = Range("A1:A3").Value
And 
Range("B1") = Range("A1")
does work!

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

解决方案
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)
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 Watch to see the Type for a particular object as shown in the pic below.



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 Range or a Variant

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屋！