Stop R1C1 formulas in VBA

[gbrandreth]

Is it possible to switch the default R1C1 formula recording to A1 style. I can't see an option for it in VBA options?

Thanks

Graham

 

[Sektor]

It means you have currently R1C1 notation. Switch to A1 notation.

 

[Richard Schollar]

Hi Graham

I get R1C1 notation even though I don't have this selected in my options. I am not aware of a means to change this default behaviour (doesn't mean there isn't one though ). Can I ask why you would want to? I use A1 style in the worksheet, but R1C1 beats the heck out of A1 if you want to do any kind of formulas in VBA.

 

[gbrandreth]

Its impossible to read a complex formula in R1C1 style, I find it a lot easier to record the proper formula and amend if required with variables (such as an EndRow variable changing the range of a formula).

I also find it easier to read / change going forward if changes are required (say an extra column needs adding)

 

[Richard Schollar]

It's a bit chunky-clunky but if you have the formula in a cell you can output the formula to the Immediate Window in the VBE (open Immediate Window with Ctrl+G if you don't already have it up) and type:

?ActiveCell.Formula

(this gives you the formula in the currently active cell). This would mean you could copy and paste it into your VBA code?

 

[RoryA]

I don't believe there is a way to turn it off (I think it's what Excel really uses internally). Although it looks confusing at first, I think it's actually easier to use variables (especially column numbers) if you have R1C1 format.

 

[gbrandreth]

OK thanks for the input all.

I may try this on my next project, and I can see it would be faily easy in terms of dynamic ranges, but I'm still concerned if I get a call in 6 months time from the user, saying they have a VB error, that I will regret it trying to debug / recode for process changes.

Thanks

Graham