R1C1 Notation

[brym]

Hi! I'm a little puzzled why Excel macro recording in some situations is
using R1C1 notation.
Is there any benefit using that notation type?

And, as I've never really worked with that notation, it sometimes give me
problems.

Especially when trying to use variables to describe a range.

Does a 'translation'-description or other help exist somewhere? Haven't been
able to find one!

Thanks in advance
Birger

 

[Bob Phillips]

Brym,

I personally find the R1C1 notation much easier if you are using variables.
For instance,if I creating a formula that sums this row, column A to the
column immediately to the left of this column I simply do

ActiveCell.FormulaR1C1 = "=SUM(RC1:RC[-1])"

whereas in A1 notation I need
With ActiveCell
.Formula = "=SUM(A" & .Row & ":" & Chr(.Column - 1 + 64) & .Row &
")"
End With

and it gets even more complex.

 

[brym]

Hi Bob! I see what you mean. But I rarely uses formulas like the ones
mentioned here.

Writing vba I find the new notation easier to work with, and thats where
I've had some problems interpretating macro-rec. code into my vba. Probably
because I'm not familiar with it.

It seem to me as if the R1C1 is best in writing formulas but not writing
vba!

Anyhow, I'd love to read some examples, best if they compare the two
techniques. Know any places?

 

[Bob Phillips]

Brym,

Sorry, I don't know where you can find what you want, but going back to the
original topic, I also find R1C1 better in other ways. For instance
Range("A1") is nice and straightforward, but
Cells(1,1) is more useful if you want to use variables again (Cells(myRow,
myCol))
or even a compromise
Cells(1,"A").

I rarely use A1 notation, as I usr variable in which case I use R1C1 or I
use named ranges.

 

[Tim Zych]

Just to add:

The FormulaR1C1 property can be used with A1-style range formulas:

Sub Tester()
Range("B10").FormulaR1C1 = "=RC[-1]"
Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1
Range("K4").Formula = Range("B10").Formula '<- not optimal
End Sub

Range("B10").FormulaR1C1 = "=RC[-1]"
-Puts a formula in B10 relative to itself.

Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1
-Copies the formula from B10 to K3, maintaining relative integrity as if
it was copied and pasted from the clipboard.

Range("K4").Formula = Range("B10").Formula
-Copies the formula from B10 to K3 but does not adjust the references.
This is equivalent to copying the formula from the formula bar and then
pasting it into the destination. Probably not desired.

I think R1C1 notation takes some extra detective work to use. To me, this:

Range("A2").Formula = "=K2*5"
Range("X15:X25").FormulaR1C1 = Range("A1").FormulaR1C1

is easier to figure out than:

Range("A2").FormulaR1C1 = "=RC[9]*5"
'etc

IMO the strength of R1C1 style in VBA comes into play when formulas should
be relative to a range. Then if a column is inserted into the spreadsheet,
you'd have to adjust all of the A1-style formulas in VBA, which could be a
royal pain in the a**. Like Bob said, if formulas are using variables and
are relative, then that's probably the strongest case for R1C1 style.
However, if no variables are being inserted into formulas, why not just
store them on the spreadsheet and use the FormulaR1C1 property to write them
to wherever. This would protect the integrity if a column is inserted in the
destination sheet, because all that would be necessary would be to insert a
column in the formula sheet too to accommodate the adjustment.


Tim