How to use relative names in Excel VBA

Question

Many "advanced" (aka: VBA) excel tutorials on the web or even excel's vba help encurage us to use the

Range("B2:B10")

method (to be precise: object) for selecting cells or getting values. In the same place they often add it's totally ok to use predefined names as well:

Range("valuesabove")

On the other hand I fell in love with the incredible power of relatively defined cell names. They make it so much easier to write and handle big composite formulas, and basically to refer to nearly anything.

However, relative names don't work in the Range("valuesabove") method the way we are used to it.

Relative names are typically used on worksheets to refer to the presently chosen cell or the cell in which they are utilised.

This is not true for the Range() object in VBA. Range is a function that is related to a WorkSheet object, often the ActiveSheet. But A1 in the left upper cell of ActiveSheet represents ActiveSheet. Range proves to be relevant to this, as well. And for this reason, relative names ("one column to the left, two rows above") do not work with it but absolute names ($C$23) do.

So, I have the following question: How can I use relative names effectively in VBA?

Answer 1

It appears you are looking for Range.Offset() http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx

However, you could do it as:

'Your example Range(Col_B_in_current_row) as
Range("B" & ActiveCell.Row).Select

'Your example Range("B2:B10") -> Range("valuesabove") as
Range("B2:B10").Offset(-1, 0).Select

Just seems like a relatively simple syntax already exists for this.