Stop 2007 from recording formulas with Relative Referencing

[EagleOne]

2007

This s/b intuitive but ...

No matter how I set the "Relative" button in 2007 related to Developer>Record Macro, XL records in
relative mode.

Where is the configuration setting to stop this?

TIA EagleOne

 

[EagleOne]

To be more clear,

I am referring to looking at the generated VBA code in the code window.

Both settings generate a formula as Activecell.FormulaR1C1

EagleOne

 

[Niek Otten]

Hi Eagle One,

<Both settings generate a formula as Activecell.FormulaR1C1>

That has nothing to do with relative or absolute; it is just the reference style.

What exactly are you trying to do and what behavior of Excel stops you?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| To be more clear,
|
| I am referring to looking at the generated VBA code in the code window.
|
| Both settings generate a formula as Activecell.FormulaR1C1
|
| EagleOne
|
| (e-mail address removed) wrote:
|
| >2007
| >
| >This s/b intuitive but ...
| >
| >No matter how I set the "Relative" button in 2007 related to Developer>Record Macro, XL records in
| >relative mode.
| >
| >Where is the configuration setting to stop this?
| >
| >TIA EagleOne

 

[EagleOne]

I am attempting to develop a procedure. Part of the procedure is to enter a formula into a cell. I
have the formula written. While the recorder is recording the formula, I want the macro to show the
actual formula not the relative formula.

Currently, I must replace the recorded formula i.e., ActiveCell.FormulaR1C1 = _ "=ABS(R[2]C[-5])"
with ActiveCell.Formula = "=ABS(O2)"

 

[Niek Otten]

<"=ABS(O2)">

That is a relative formula, in A1 Reference Style. Absolute would be

=ABS($O$2)

=ABS(O2) and =ABS(R[2]C[-5]) are only two different representations of the same internal formula.

The Relative button influences recording actions, like selecting cells, not the formulas.

I don't think you can have Excel record formulas in macros in A1 Reference Style.
If you want to know what the formula looks like in the worksheet, use

?Activecell.Formula

in the immediate window.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



|I am attempting to develop a procedure. Part of the procedure is to enter a formula into a cell. I
| have the formula written. While the recorder is recording the formula, I want the macro to show the
| actual formula not the relative formula.
|
| Currently, I must replace the recorded formula i.e., ActiveCell.FormulaR1C1 = _ "=ABS(R[2]C[-5])"
| with ActiveCell.Formula = "=ABS(O2)"
|
|
| >Hi Eagle One,
| >
| ><Both settings generate a formula as Activecell.FormulaR1C1>
| >
| >That has nothing to do with relative or absolute; it is just the reference style.
| >
| >What exactly are you trying to do and what behavior of Excel stops you?

 

[EagleOne]

Well, I knew that I could insert a formula into a W/S cell with VBA using Reference Style.
It is amazing that I did not realize that I need to two-step to get the reference formula. To me,
at least, it is better documentation of what I doing in VBA to see the formula as "typically"
displayed by the user.

Thanks for your time and knowledge