All geek questions in one place

When to use range.value?

If I read / write in a range, when do I just use the name of the range and when do I need to use range.value? Does it matter if the range is a single cell or multiple cells? Does the type of the variable matter? Or a data type in a range?

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 ?

+7
source share
1 answer

In a Range Excel member object, the default is Value

Therefore, SomeVariable = Range("A1") same as SomeVariable = Range("A1").Value

Similarly, Range("A1") = SomeVariable is the same as Range("A1").Value = SomeVariable

Of course, you have to be careful when you say a = Range("Test")

When you try to save a value from a continuous range to a Variant variable, for example, the Test range refers to, say, A1:A10 , then you 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 Myar = Range("A1:A10") is the same as Myar = Range("A1:A10")

If I read / write in a range, when do I just use the name of the range and when do I need to use range.value?

I'm not sure what you mean when do just use the range name but it doesn’t matter if you use .Value or not when you read / write from / to a range. IMHO, this is a good practice to use. .Value :)

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

No, even then it doesn’t matter if you use. .Value or not.

Does the type of the variable matter?

Oh yeah! See the example array above

Or a data type in a range?

Excel cells can store various types of data. From numbers to dates and strings. Since you do not know what type it is, it is recommended to use Variant when working with them. 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

Now try this one

 Sub Sample() Dim someVariable As Variant someVariable = Range("A1").Value Debug.Print someVariable End Sub

As Tim Williams noted in the comments

Using .Value also useful for "eliminating the ambiguity of the" general "forgot to use Set when setting an object variable" task - Dim a: Set a = Range("A1") vs Dim a: a = Range("A1") In the second case, always used .Value clarifies the actual problem

+10
source

Source: https://habr.com/ru/post/988204/

More articles:


All Articles