excel - When to use range.value?

Question

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?

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?

Answer 1

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

As mentioned by Tim Williams in the comments

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