CODEWITHSUNDEEP
excelvba
Dionysus17
Dionysus17

Reputation: 35

Excel 2007 VBA Range Variable References Cells Outside Assigned Range

I've searched everywhere but haven't been able to figure out why a range variable is able to reference cells outside the assigned range.

For example, if I write the code below:

 Dim Rate as Range
 Set Rate = Range("A1:A5") 

 For Each Cell In Rate
      Debug.Print Cell.Value
 Next Cell

 Range("H6").Value = Rate(6).Value
 Range("H7").Value = Rate(7).Value

The above routine will only print out the 5 values in "A1:A5" -- BUT the last 2 statements cause the values in "A6" and "A7" to get stored in "H6" and "H7".

Since the variable "Rate" has only been assigned to "A1:A5", why is it able to reference other cells in column A (i.e., "A6" & "A7")?

Am I doing something wrong? Any insight would be greatly appreciated!

Upvotes: 3

Views: 553

Answers (1)

Slai
Slai

Reputation: 22876

Nothing wrong, it's just how it works. I personally like this "feature".
If you rather get an error, then you can use something like 

 Range("H6").Value = Rate.Value2(6, 1)  ' .Value2 because .Value(6, 1)

because Rate.Value2 will give a 5 by 1 variant array. Or use the .Value array instead of the Range:

 Dim Rate ' as Variant
 Rate = Range("A1:A5").Value    ' Variant/Variant(1 To 5, 1 To 1)

 Debug.Print Rate(5, 1) ' ok
 Debug.Print Rate(6, 1) ' Run-time error '9': Subscript out of range

or 

 Dim Rate as Variant
 Rate = Application.Transpose(Range("A1:A5"))   ' Variant/Variant(1 To 5)

 Debug.Print Rate(5) ' ok
 Debug.Print Rate(6) ' Run-time error '9': Subscript out of range

In your case Rate(6) is short for Rate.Cells(6, 1) which is similar to .Offset. For example:

 Debug.Print Range("C3:D4")( 1,  1).Address   ' prints "$C$3"
 Debug.Print Range("C3:D4")(-1, -1).Address   ' prints "$A$1"

The only way I can think of getting error with Range is to use Areas instead:

Dim Rate As Areas
Set Rate = Range("a1,a2,a3,a4,a5").Areas

Debug.Print Rate(5) ' ok
Debug.Print Rate(6) ' Run-time error '9': Subscript out of range

Upvotes: 1

Related Questions