CODEWITHSUNDEEP
vba
lostinOracle
lostinOracle

Reputation: 408

(VBA) when to use .cell(1,1) or .cell(1,1).value reference

In my macros when I'm referencing to a value found in a cell, I've been inconsistently using either .cell(r,c) or .cell(r,c).value.

dim c as variant 
c = sheet1.cell(1,1) 'or sheet1.cell(1,1).value

Are both references correct or is there a preferred way?

Upvotes: 4

Views: 6152

Answers (2)

John Alexiou
John Alexiou

Reputation: 29274

I always use .Value2 to get the value of a cell (or an array of vables from a range). If I don't use .Value2 it means I want a reference to the Range objects and not the value. For example

Dim r as Range
Set r = Range("A1")

Dim x as Double, i as Integer
x = r.Offset(1,0).Value2

Dim vals() as Variant
vals = r.Offset(1,0).Resize(10,1).Value2

For i=1 to 10
    vals(i,1) = CDbl(i)/10
Next i

r.Offset(1,0).Resize(10,1).Value2 = vals

In addition I dont use the Cell() method because the location of cells may change in the future. I use named ranges with the .Offset() and .Resize() methods to set the range of cells I want read or write values to. So in the above code I would never use Range("A1") but something like Range("my_table") with a named range "my_table" defined on the top left cell of where I interact with.

There is a little known shortcut to get the value of a cell with the [] notation

Dim x as Double
x = [A2]
// This is the same as x = Range("A2").Value2

Upvotes: 0

A.S.H
A.S.H

Reputation: 29352

.Value is the default property of the range object. Therefore, when you assign something like this:

myVar = myRange

it is equivalent to myVar = myRange.Value, because you are assigning a variable not an Object.

However, if you use Set, like this:

Set myObj = myRange

You would be assigning an object reference. The keyword Set tells VBA that you are assigning an object reference. The absence of Set makes VBA conclude that you implicitly want to get the .value, the default property of the range.

It is a good programming practice to use .value explicitly, for two reasons:

1- It makes code more readable, because the reader does not have to guess what is going on implicitly

2- With the advent of VB.net, the keyword Set has disappeared; the syntax for assigning an object or a normal variable becomes the same. For this reason, the default property idiom has disappeared with VB.net. Therefore, good practice is to use .value in VBA as well, because it makes an eventual porting of your code to VB.net much easier.

Upvotes: 6

Related Questions