Programming Excel with VBA and .NET

Name

Application.ConvertFormula(Formula, FromReferenceStyle, [ToReferenceStyle], [ToAbsolute], [RelativeTo])

Synopsis

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

Argument	Description	Settings
Formula	The formula you want to convert.	Must be a valid formula beginning with an equals sign
FromReferenceStyle	The XlReferenceStyle of the formula.	xlA1 xlR1C1
ToReferenceStyle	The XlReferenceStyle style you want returned. If this argument is omitted, the reference style isn’t changed; the formula stays in the style specified by FromReferenceStyle.	xlA1 xlR1C1
ToAbsolute	The converted XlReferenceStyle. If omitted, the reference type isn’t changed. Defaults to xlRelative.	xlAbsolute xlAbsRowRelColumn xlRelRowAbsColumn xlRelative
RelativeTo	The cell that references are relative to. Defaults to active cell.	Range object

The following code converts a formula to R1C1 style relative to cell A1:

Sub TestConvertFormula(  )
    Dim str As String
    str = "=Sum(A1:A20)"
    Debug.Print Application.ConvertFormula(str, xlA1, xlR1C1, _
      xlRelative, [a1])
End Sub