1

I have defined a range variable in VBA consisting of a consecutive block of horizontal cells, say,

Dim myRange As Range
Set myRange = Range("A1:H1")

I want to reference the value in the 3rd position in this range to use it in a calculation (I do not need to change the value in this cell). I've found at least 8 different ways to do this:

myRange(1,3)
myRange(3)
myRange(1,3).Value
myRange(3).Value
myRange.Cells(1,3)
myRange.Cells(3)
myRange.Cells(1,3).Value
myRange.Cells(3).Value

Is there any difference between what these different methods are doing? Which one(s) are preferable and why? I'm particularly confused by what happens when I do myRange.Cells - it seems like that would just return myRange. It's also not entirely clear to me what type of object is returned by myRange(1,3) (or myRange(3), etc.).

kccu
  • 283
  • 2
  • 10

1 Answers1

2

Assuming that you are using these expressions in a non-reference context (without Set), there is implicit .Value at the end of each one that does not have it explicitly. This leaves us with:

myRange(1,3).Value
myRange(3).Value

myRange.Cells(1,3).Value
myRange.Cells(3).Value

Now, these are in fact different.

  • myRange(1,3) queries Range that is at RowIndex 1, ColumnIndex 3 of myRange.

    If myRange happens to have enumeration mode of "cells", the expression will return the cell at those coordinates, but if myRange is enumerated as "rows" or "columns", you will have an exception:

    Dim r As Range

Set r = Me.Range("A1:H1")
Debug.Print r(1, 3).Address  ' $C$1

Set r = Me.Range("A1:H1").Columns
Debug.Print r(1, 3).Address  ' Run-time error 1004

  • myRange(3) queries Range that is the third item of myRange.

    What item means is, again, determined by the enumeration mode of myRange. For "cells", it will return the third cell, counting left to right and then top to bottom. For "rows" or "columns", it will return the entire third row or column:

    Dim r As Range

Set r = Me.Range("A1:H10").Columns
Debug.Print r(3).Address  ' $C$1:$C$10

Set r = Me.Range("A1:H10").Rows
Debug.Print r(3).Address  ' $A$3:$H$3

Set r = Me.Range("A1:H10")
Debug.Print r(3).Address  ' $C$1

    In case the mode is "rows" or "columns", and provided that myRange contains multiple of the counterpart dimension ("columns" or "rows"), the .Value will return a 2D array. Attempting to use it in a scalar context will raise a type mismatch exception.

  • The versions with .Cells remove the uncertainty described above and let you operate on the version of myRange that is enumerated as "cells". You are now guaranteed to have those of the above outcomes that correspond to the "cells" enumeration mode.

In conclusion, when working with ranges of unknown origin, the safest thing to do when you want the third cell of the first row is myRange.Cells(1,3).Value. It also happens to express the intent in the clearest way.

GSerg
  • 76,472
  • 17
  • 159
  • 346