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

问题描述

我有code的一些非常简单的线条一些问题。让我来详细的事实，看看别人可以复制此行为。如果任何一个可以复制的，我想知道为什么它正在发生的解释。

所以，让我先从一个非常简单的code线的作品：

 暗淡ARR（）为Variant
ARR =范围（A1：A10）。
 

这确实不如预期，改编分配的值 A1：A10

现在为什么不下面一行code的工作？

 暗淡ARR（）为Variant
ARR =工作表（工作表Sheet1）的范围。（A1：A10）。
 

我得到一个运行时错误'13'类型不匹配，即使在同一范围内成功地分配到阵列，只是没有工作表值。

但

 暗淡改编为Variant
ARR =工作表（工作表Sheet1）的范围。（A1：A10）。
 

和

 暗淡ARR（）为Variant
ARR = Application.Transpose（Application.Transpose（工作表（工作表Sheet1）范围（A1：A10）））
 

不工作

您现在答案之前，请让我给你一些更多的事实。

 暗淡ARR（）为Variant
ARR =工作表（1）.Range（A1：A10）。
 

不起作用

和在工作表使用表也都给予了同样的错误。

我已经确定这是同一工作表为活动引用表使用范围（A1：A10）。Worksheet.Name 继工作$ C $在输出c和它确实说工作表Sheet1 。

没有其他工作簿是开放的，因此也不能引用另一个工作簿。

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

 暗淡ARR（）为Variant
昏暗SampleRange由于范围设置SampleRange =工作表（工作表Sheet1）的范围。（A1：A10）。
ARR = SampleRange
 

因此​​，使用定义在同一张纸上以同样的方式相同的范围时，我把它分配给范围变量现在工作。并使用它！正如预期的这个作品同时与工作表和表功能，无论我如何定义表（我可以使用索引或工作表的名称和所有工作正常）

如果它可以帮助任何人，我用Excel 2007中的Windows XP计算机上测试此。我还没有测试它在任何其他机器，但我计划测试于2003年和2010年对Windows 7和8，一直还没机会呢。

更新：的不是100％肯定，如果这是完全相同的问题，因为与数组但是从粗浅的看法似乎是：

 范围（B1：B3）=范围（A1：A3）
 

以上code将无法正常工作，即使A1：A3被填充，日期，数值，字符串，公式什么，将会写入空白到B1：B3

但

 范围（B1：B3）值=范围（A1：A3）。价值
 

和

 范围（B1）=范围（A1）
 

的确实的工作！

另外工作是：

 范围（B1：B3）= Application.Transpose（Application.Transpose（范围（A1：A3）））
 

解决方案

没有它是不是一个错误。

  

问题的关键是该值是Range对象的默认属性，那么为什么是不是隐式地使用？你看看我联系的问题吗？ （FROM <一个href=\"http://chat.stackoverflow.com/rooms/46174/discussion-between-user2140261-and-simoco\">CHAT)

专家上面已经详细解释得非常好。我将解释保持最小，因此让我知道如果你还有任何问题。

让我们先来了解我们的对象。我创造了这个小桌子上，清楚地显示什么是我们处理这样，没有混乱。

您还可以添加一个手表看键入特定对象，如下面的图片。

所以，当你说

  ARR =范围（A1：A10）。
 

Excel中知道，默认属性是 .value的。然而，在其他情况下，不知道是因为Excel是不会读心术或假设足够的智能来了解你是否要使用工作表（工作表Sheet1）的范围。（A1：A10）。 为范围或变

一旦你明确指定你的对象作为范围 Excel稍后知道你想要什么。例如，这工作的。

 暗淡ARR（）为Variant
昏暗RNG由于范围
设置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 above 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屋！