Vba help

[VbaHell]

Hello All

Is there a way of changing the way the VBA code is displayed.

If I record a macro like A4=a1+A2+A3

The code shows ActiveCell.FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
which can be difficult to read
Can you change this to state the actual cell reference.

 

[tweedle]

In XL2007
It's
-Excel Options / Formulas
--Working with formulas
---Uncheck the "R1C1 Style reference" option

 

[MrKowz]

Hello All

Is there a way of changing the way the VBA code is displayed.

If I record a macro like A4=a1+A2+A3

The code shows ActiveCell.FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
which can be difficult to read
Can you change this to state the actual cell reference.

VBA natively uses R1C1, and this is not an option that you can toggle for the recorder, sadly. I would suggest manually editing the code so that it reads how you prefer:

ActiveCell.Formula = "=A1+A2+A3"

 

[tweedle]

...and Tweedle learns something new today.
Thank You MrKowz!

 

[VbaHell]

Thank you for you replies

 

[Rick Rothstein]

You can get VB to help you straighten that formula out. In code returned by the Recorder is this...

ActiveCell.FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"

First, select an empty cell on any worksheet. Next, Copy/paste that entire code line into the Immediate Window and hit the Return key to execute it. Next, execute this line of code in the Immediate Window...

? ActiveCell.Formula

Now, go back int the code window and delete the characters "R1C1" from the code line and replace all that R/C stuff with the last printout from the Immediate Window.