vba 什么时候使用 range.value？

When to use range.value?

提问by Raghu Ramachandran

If I'm reading/writing to a range, when do I just use the range name and when do I need to use range.value? Does it matter if the range is one cell or multiple cells? Does it matter what the type of the variable is? Or the type of the data in the range?

如果我正在读/写一个范围，我什么时候只使用范围名称，什么时候需要使用 range.value？范围是一个单元格还是多个单元格有关系吗？变量的类型是什么重要吗？或者范围内数据的类型？

For example, should I write a = Range("Test")or should I write a = Range("Test").value?

例如，我应该写a = Range("Test")还是应该写a = Range("Test").value？

Similarly, Range("Test") = aor Range("Test").value = a?

同样，Range("Test") = a或Range("Test").value = a？

回答by Siddharth Rout

In the Excel Rangeobject, the default memberis Value

在 ExcelRange对象中，默认member为Value

So SomeVariable = Range("A1")is same as SomeVariable = Range("A1").Value

所以SomeVariable = Range("A1")是一样的SomeVariable = Range("A1").Value

Similarly Range("A1") = SomeVariableis same as Range("A1").Value = SomeVariable

同样Range("A1") = SomeVariable与Range("A1").Value = SomeVariable

You have to of course be careful when you say a = Range("Test")

你说的时候当然要小心 a = Range("Test")

When you try to store the value from a contiguous range to a Variant variable for example the Testrange refers to say, A1:A10, then you will get an array of values

当您尝试将值从连续范围存储到 Variant 变量时，例如该Test范围指的是，A1:A10，那么您将获得一个值数组

Sub Sample()
    Dim Myar

    Myar = Range("A1:A10").Value

    Debug.Print UBound(Myar)
End Sub

Again in this case Myar = Range("A1:A10").Valueis the same as Myar = Range("A1:A10")

再次在这种情况下Myar = Range("A1:A10").Value与Myar = Range("A1:A10")

If I'm reading/writing to a range, when do I just use the range name and when do I need to use range.value?

如果我正在读/写一个范围，我什么时候只使用范围名称，什么时候需要使用 range.value？

I am not sure what do you mean by when do I just use the range namebut it doesn't matter if you use .Valueor not when you are reading/writing from/to a range. IMHO, it is a good practice to use .Value:)

我不确定你的意思是什么，when do I just use the range name但是.Value当你从/向一个范围读/写时是否使用并不重要。恕我直言，使用它是一个好习惯.Value:)

Does it matter if the range is one cell or multiple cells?

范围是一个单元格还是多个单元格有关系吗？

No it doesn't matter even in this case if you use .Valueor not.

不，即使在这种情况下，您是否使用也无关紧要.Value。

Does it matter what the type of the variable is?

变量的类型是什么重要吗？

Oh Yes! See the array example above

哦是的！请参阅上面的数组示例

Or the type of the data in the range?

或者范围内数据的类型？

Excel cells can store various types of data. From numbers to dates to strings. Since you don't know what that type can be, it is recommended that when working with them, use Variant. Here is a classic example

Excel 单元格可以存储各种类型的数据。从数字到日期再到字符串。由于您不知道该类型可以是什么，因此建议在使用它们时使用Variant. 这是一个经典的例子

Let's say cell A1has this number 123456789

假设单元格A1有这个数字123456789

Now try this code

现在试试这个代码

Sub Sample()
    Dim someVariable As Integer

    someVariable = Range("A1").Value

    Debug.Print someVariable
End Sub

And now try this one

现在试试这个

Sub Sample()
    Dim someVariable As Variant

    someVariable = Range("A1").Value

    Debug.Print someVariable
End Sub

As mentioned by Tim Williams in the comments

正如蒂姆威廉姆斯在评论中提到的

Using .Valueis also useful to "dis-ambiguate" the common "forgot to use Set when assigning an object variable" problem - Dim a: Set a = Range("A1") vs Dim a: a = Range("A1")In the second case always using .Valueclarifies the actual problem

使用.Value也有助于“消除歧义”常见的“在分配对象变量时忘记使用 Set”问题 -Dim a: Set a = Range("A1") vs Dim a: a = Range("A1")在第二种情况下，始终使用可以.Value澄清实际问题