Which reference style is best? R1C1 or A1?

[TinaP]

R1C1 confuses me so I avoid it in the workbook. I like it in VBA, though, mostly because of the flexibility and the fact that I can record the formula.

 

[diddi]

since i use vba the most i go for r1c1, but not on sheets

 

[Jon von der Heyden]

Right, well almost everyone is for A1. So I see one other voted R1C1. Who was that? Bit of support here please!

Despite the responses I'm still going to wean myself off the A1 and get used to R1C1. I disagree with you Tony, R1C1 looks waaaaay cooler.

Statistically speaking, only sociopaths use R1C1 style in the main UI.

Right!

 

[shg]

It's better for seeing the 'identicality' of formulas, for sure.

 

[SydneyGeek]

I prefer to use partial references if I'm going to use R1C1 for creating formulas in VBA; much easier to have something always refer to (say) RC3, instead of RC[-2] which, after some rearranging of the worksheet, needs to be rewritten as RC[-5].

Denis

 

[xenou]

You have support:

Excel Best Practices for Business by Loren Abdulezer begins with chapter 1 and a big endorsement of R1C1 as the right way to go.
I wasn't convinced enough to switch, but I did figure out that switching on the fly to R1C1 was useful for validating formulas as you can tell at a glance if formulas are identical or not as you scan a column/row.

I think I agree with Biff - not cool!

 

[Darren Bartrup]

Definitely A1 all the way, though I do switch to R1C1 when I'm counting lots of columns for a VLOOKUP or OFFSET type formula.

 

[Cbrine]

unless somebody stands behind me with a very big stick - A1

Funny...When I worked in IT, this was my solution to stop the users from doing "STUPID" stuff.

A1 for me, in the sheets and in VBA.

 

[u76200]

I am only five years late on this thread, but also use R1C1 for everything. It is just so much more powerful and more intuitive - I mean it is a coordinate system after all. A1 is ok for small uncomplicated things, but if you have a lot of data columns and rows, R1C1 just shines. Firstly, the formulas never change in a column. It its just so much easier to debug complex spreadsheets. Secondly referring to column CF is totally counter intuitive, C84 if much more intuitive at face value. I also often use absolute references unless I specifically need to have a relative reference. This keeps formulas short and easy to read. I think it is simply a much, much better and more sustainable practice to use R1C1 all the way. I have been programming with Excel from pre VBA days.

 

[jbenfleming]

A1 for the worksheet. But definitely R1C1 for VBA.

Denis

Why exactly do you prefer r1c1 in VBA?