何时使用range.value? [英] When to use range.value?
问题描述
如果我正在读/写一个范围,我什么时候使用范围名称,什么时候使用Ineed来使用range.value?如果范围是一个单元格还是多个单元格,是否重要?变量的类型是什么关系?还是数据类型在范围内?有没有最佳实践?
例如,我应该写
`a = Range(Test)`
或者我应该写
`a = Range(Test)。value`
同样,
`Range(Test)= a`
或
`Range(Test)。value = a`
p>在Excel 范围
对象中,默认的成员
是值
所以 SomeVariable = Range(A1)
与 SomeVariable =范围(A1)。值
同样 Range(A1)= SomeVariable
与 Range(A1)相同Value = SomeVariable
你当然要小心当您说 a = Range(Test)
当您尝试从连续范围存储值到一个变量蚂蚁变量例如 Test
range指的是 A1:A10
,那么你将得到一个值数组
Sub Sample()
Dim Myar
Myar = Range(A1:A10 ).Value
Debug.Print UBound(Myar)
End Sub
在这种情况下 Myar = Range(A1:A10)。值
与 Myar = Range(A1:A10 )
如果我正在读/写一个范围,我什么时候使用范围名称我什么时候需要使用range.value?
我不知道你是什么意思,我只是使用范围名称
,但是,如果您在/从/读取/写入文件时,如果您使用 .Value
范围。 IMHO,使用 .Value
是一个很好的做法:)
是否如果范围是一个单元格还是多个单元格?
没有关系,即使在这种情况下,如果你使用 .Value
变量的类型是什么关系? p>
哦,是的!见上面的数组示例
或范围内的数据类型?
Excel单元格可以存储各种类型的数据。从数字到日期到字符串。由于您不知道该类型是什么,建议使用它们时,使用 Variant
。这是一个典型的例子
我们假设单元格 A1
有这个数字 123456789
现在尝试这个代码
Sub Sample )
Dim someVariable As Integer
someVariable = Range(A1)。value
Debug.Print someVariable
End Sub
现在尝试这一个
Sub Sample()
Dim someVariable As Variant
someVariable = Range(A1)。value
Debug.Print someVariable
End Sub
编辑:
正如蒂姆·威廉姆斯所述
使用.Value也可以用dis-ambiguate的常用忘记使用Set当分配对象变量问题 - Dim a:Set a = Range(A1)vs Dim a:a = Range(A1)在第二种情况下,始终使用.Value来澄清实际问题 -
This ought to be simple, but I cant' find a clear answer.
If I'm reading/writing to a range, when do I just use the range name and when do Ineed 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? Is there a best practice for this?
For example, should I write
`a = Range("Test")`
or should I write
`a = Range("Test").value`
Similarly,
`Range("Test") = a`
or
`Range("Test").value = a`
In the Excel Range
object, the default member
is Value
So SomeVariable = Range("A1")
is same as SomeVariable = Range("A1").Value
Similarly Range("A1") = SomeVariable
is same as Range("A1").Value = SomeVariable
You have to of course be careful when you say a = Range("Test")
When you try to store the value from a contiguous range to a Variant variable for example the Test
range refers to say, A1:A10
, then you will get an array of values
Sub Sample()
Dim Myar
Myar = Range("A1:A10").Value
Debug.Print UBound(Myar)
End Sub
Again in this case Myar = Range("A1:A10").Value
is the same as 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?
I am not sure what do you mean by when do I just use the range name
but it doesn't matter if you use .Value
or not when you are reading/writing from/to a range. IMHO, it is a good practice to use .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 .Value
or not.
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
Let's say cell A1
has this number 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
Edit:
As mentioned by Tim Williams
Using .Value is 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 .Value clarifies the actual problem –
这篇关于何时使用range.value?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!