Microsoft Office Tutorials and References

In Depth Information

The
ConvertFormula
method also lets you convert from the A1 notation to the R1C1 nota

tion. It will also allow you to change a formula’s reference type from absolute to relative or

vice versa.

In the
CreateNames
routine, the
ConvertFormula
method converts a simple cell address from

one reference type to another. As the name implies, you can pass a more complex formula for

conversion, such as this example, which computes the daily average for the first two Mondays

in January using the Y2001ByQuarters workbook.

strA1Formula = Application.ConvertFormula( _

Formula:=" =AVERAGE(R5C2:R5C13, _

R12C2:R12C13), fromReferenceStyle:=xlR1C1, _

toReferenceStyle:=xlA1)

The
ConvertFormula
method has the following syntax:

expression
.ConvertFormula(
Formula, FromReferenceStyle, ToReferenceStyle,

ToAbsolute, RelativeTo
)

●

Expression

A required expression that returns an
Application
object.

●

Formula

A required variant that provides the formula to be converted as a string.

●

FromReferenceStyle

A required integer that matches one of the two
xlReferenceStyle

constants.

●

To ReferenceStyle
An optional integer that matches one of the two
xlReferenceStyle

constants. If not supplied, the reference style is not changed.

●

To Absolute
An optional integer that matches one of the
xlReferenceType
constants.

If omitted, the reference type is not changed.

●

RelativeTo
An optional variant that returns a range object that points to a single cell.

Relative references relate to this cell.

Table 8-3 lists the available
xlReferenceStyle
and
xlReferenceType
constants you can use with

the
ConvertFormula
method.

Table 8-3.
Constants Used with the
ConvertFormula
Method

Constant

Integer

xlReferenceStyle

xlA1

1

xlR1C1

-4150

xlReferenceType

alAbsolute

1

xlAbsRowRelColumn

2

xlRelRowAbsColumn

3

xlRelative

4