ConvertFormula Method

Applies To

Application object.

Description

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both.

Syntax

expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

expression Required. An expression that returns an Application object.

Formula Required Variant. A string that contains the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.

FromReferenceStyle Required Long. The reference style of the formula. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1.

ToReferenceStyle Optional Variant. The reference style you want returned. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.

ToAbsolute Optional Variant. Specifies the converted reference type. Can be one of the following XLReferenceType constants: xlAbsolute, xlAbsRowRelColumn, xlRelRowAbsColumn, or xlRelative. If this argument is omitted, the reference type isn't changed.

RelativeTo Optional Variant. A Range object that contains one cell. Relative references relate to this cell.

Example

This example converts a SUM formula that contains R1C1-style references to an equivalent formula that contains A1-style references, and then it displays the result.

inputFormula = "=SUM(R10C2:R15C2)"
MsgBox Application.ConvertFormula( _
    formula:=inputFormula, _
    fromReferenceStyle:=xlR1C1, _
    toReferenceStyle:=xlA1)