7

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Raghu Ramachandran
  • 157
  • 1
  • 1
  • 9

1 Answers1

13

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

TylerH
  • 20,799
  • 66
  • 75
  • 101
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    Nice one Siddharth. I also like explciitly specifiying `.Value` since it helps beginners reading the code and helps differentiate with the old nugget `.Value2` (see here Raghu http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) – CuberChase May 29 '15 at 22:02
  • 1
    Great stuff Siddharth :) To complement the above, if you are more like me than Siddharth, then using qualifications like .value, even when not strictly necessary, will help you keep track of what data you intended to grab and to troubleshoot when you get type-mismatch errors. I also prefer to avoid use of Variant for the same reason (and for the extremely minor benefit of using less memory). Obviously if you have grown out of periodically making these errors I would say Siddharth's advice is the way to go. – puzzlepiece87 May 29 '15 at 22:05