Relative references, recoaring macro, VBA

Question

I am recording a macro in VBA using relative references, I first select cell A1. The code that is generated is this:

 ActiveCell.FormulaR1C1 = "First Cell"
 ActiveCell.Offset(1,1).Range("A1").Select
 ActiveCell.FormulaR1C1 = "Second Cell"
 ActiveCell.Offset(1,0).Range("A1").Select

What I am wondering is why we in the second and fourth cell get ".Range.("A1)"? Should't the relative references behave in such a way that we just start with the starting cell, we could have started somewhere else, so "A1" don't need to appear in the code? I tried remave ".Range("A1")" from the second line, and the macro seemed to do the same thing?

Answer 1

When you select a range with relative references turned on, the macro recorder does two things:

1. It determines the offset from the active cell that will be the top-left cell of the newly selected range. If you go from A1 to B1, it builds ActiveCell.Offset(0,1) to move zero rows down and one column to the right.
2. It determines the width and height of the newly selected range and builds the command for the height and width in A1 notation. If you go from A1 to B1, it calculates the new range to be 1x1. In A1 notation a 1x1 selection relative to the active cell is "A1", so it continues to build the command as ActiveCell.Offset(0,1).Range("A1")

They had to build the macro recorder to do this in two steps because you could select more than one cell. If you went from A1 to B1:C7. You get

ActiveCell.FormulaR1C1 = "First Cell"
ActiveCell.Offset(0, 1).Range("A1:B7").Select

That translates into "start in a cell that's zero rows and one column to the right of the active cell, then as if that were the top-left cell of the whole worksheet, select a range two columns wide and seven cells tall.

The confusing part about it is the A1 notation. People only think of cell references like A1 to refer to the whole worksheet. So a reference like C6 will always be the third column and sixth row.

But in VBA, you can use A1 notation starting from any cell. You could select cell K10, then in the Immediate Window enter:

?activecell.Range("b2").Range("b2").Range("b2").Address

and you would get $N$13. Each of those B2 references mean move one column right and one row down. Three of them in a row does that three times. The key to this is that it starts with a Range object, namely ActiveCell. If you started with a sheet, like Sheet1 or ActiveSheet, then the A1 notation would be relative the top-left of the sheet.

Why does it work without it?

It only works without the Range("A1") piece if you select a single cell. If you select more than one cell, you can still remove the Range piece, but the code won't work the same.

Another important reason it works without the Range piece is because the Offset property returns a Range object. There are a lot of properties that return Range objects including Range, Offset, Resize, and Cells. Any property that returns a Range object can be followed by .Select because Select is a property of the Range object.

Why does the recorder include it for single cells?

Nobody knows except the programmer who created the macro recorder. They could have just as easily computed that the newly selected range was a single cell and stopped at ActiveCell.Offset(0,1).Select, but it was probably just easier to do both steps regardless of the height and width of the new selection.