3

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!

Community
  • 1
  • 1
Dionysus17
  • 35
  • 2
  • 7

1 Answers1

1

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
Slai
  • 22,144
  • 5
  • 45
  • 53
  • A couple of issues with your code: first, it should be `Next value` not `Next cell`; second, any value of `n` you pass to `Rate(n)` would fail since `Rate` will always be a **2D** array. – Rory Sep 06 '16 at 07:06
  • @Slai Very useful to know how a Variant variable differs from Range variable in this respect. So is there any way to cause a Range variable to generate an error if outside the assigned range (similar to what the Variant variable does)? – Dionysus17 Sep 06 '16 at 07:35
  • @Dionysus17 `Variant` is used for any type - String, Integer, Range, Object, etc. but `.Value` and `.Value2` return array of Variant. I can't think of any way to limit the cells and values that can be accessed only to the ones that are in the assigned range. You can make a function that checks for that with few `If` statements. – Slai Sep 06 '16 at 07:51
  • @Slai Good to know. Thanks very much for your help! – Dionysus17 Sep 06 '16 at 08:04
  • @Dionysus17 I fixed few mistakes and added few more examples. I don't think that you'll like the "solution" I came up with :] – Slai Sep 06 '16 at 12:21
  • @Slai Thanks so much for the additional examples -- and the Areas solution! Very helpful to be introduced to .Value2, Areas, and the different ways of referencing the specified range. I have some questions. 1) Regarding "Range("C3:D4")( 1, 1).Address" -- does the offset (1,1) always refer to the first cell of the range? Is there some reference into that describes use of that syntax as a way to specify the offset? 2) Regarding "Rate.Value2(6, 1)" -- do you mean that "Rate.Value2(1,1) should not give an error? Any reference info that describe use of that ".Value2(1,1)" syntax? – Dionysus17 Sep 08 '16 at 04:58
  • @Dionysus17 1) `(1,1)` reffers to the first cell in the Range and is different from `.Offset(`, but I could not think of better way to describe it. You can do an internet search for `Range._Default` http://dailydoseofexcel.com/archives/2004/07/07/the-strange-object/. 2) `Rate.Value2(1, 1)` to `Rate.Value2(5, 1)` would not give error. http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – Slai Sep 08 '16 at 21:49
  • @Slai Thanks, very interesting info in the links. 1) Syntax like "Range("A1")( 1, 1)" and "Range("A1").Cells(1,1)" would never have occurred to me. Is that something you discover by trial and error, or is there a comprehensive reference that describes all allowable syntax for all Excel objects, properties, etc.? 2) Apparently the "Rate.Value2(1,1) syntax only works for .Value2 but not .Value (e.g., "Rate.Value(1,1) gives an error). Is that just an arbitrary difference or is there some reasoning behind it that I missed? – Dionysus17 Sep 09 '16 at 19:56
  • mostly from experimenting and searching for examples online. There are some good topics on the SO Documentation https://stackoverflow.com/documentation/excel-vba/topics. Range.Value accepts a parameter, that's why https://msdn.microsoft.com/en-us/library/office/ff195193.aspx – Slai Sep 10 '16 at 14:11
  • @Slai .Value / .Value2 -- looks like there are a lot of subtleties. The info at the first link is fantastic, it's exactly what I need. I'll definitely explore this site in more detail (just discovered it a couple of days ago). I'm a novice at VBA, and I really appreciate your help. Thanks again. – Dionysus17 Sep 11 '16 at 04:44