# Function Reference

# _Excel_ConvertFormula

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

#include <Excel.au3>
_Excel_ConvertFormula ( $oExcel, $sFormula, $iFromStyle [, $iToStyle = Default [, $iToAbsolute = Default [, $vRelativeTo = Default]]] )

## Parameters

$oExcel | Excel application object
$sFormula | String containing the formula to convert
$iFromStyle | The reference style of the formula. Can be any of the XlReferenceStyle enumeration
$iToStyle | [optional] A XlReferenceStyle enumeration specifying the reference style to be returned. If omitted, the reference style isn't changed
$iToAbsolute | [optional] A XlReferenceType which specifies the converted reference type. If this argument is omitted, the reference type isn't changed
$vRelativeTo | [optional] A Range object or a A1 range that contains one cell. Relative references relate to this cell. If omitted A1 is used

## Return Value

Success: | the converted formula as a string.
Failure: | Empty string and sets the @error flag to non-zero.
@error: | 1 - $oExcel is not an object or not an application object 2 - $vRelativeTo is not an object or a valid A1 range

## Remarks

R1C1 references are language dependant.
In English: "R10C5" (row 10 column 5), in German: "Z10S5" (Zeile 10 Spalte 5)

## Example

### Example 1

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an new workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Translate an A1 range to a R1C1 range and vice versa
    Local $sFormula = "C4:G12"
    ; Relative: A1 to R1C1
    Local $sR1C1 = _Excel_ConvertFormula($oExcel, $sFormula, $xlA1, $xlR1C1, $xlRelative)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Error converting formula to R1C1 style." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ; R1C1 to A1
    Local $sA1 = _Excel_ConvertFormula($oExcel, $sR1C1, $xlR1C1, $xlA1, $xlRelative)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Error converting formula to A1 style." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ; Show result
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Conversion type: Relative" & @CRLF & "  A1 to a R1C1 style: " & $sFormula & " to " & $sR1C1 & @CRLF & "  R1C1 to A1 style: " & $sR1C1 & " to " & $sA1)

    ; Absolute: A1 to R1C1
    $sR1C1 = _Excel_ConvertFormula($oExcel, $sFormula, $xlA1, $xlR1C1, $xlAbsolute)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Error converting formula to R1C1 style." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ; R1C1 to A1
    $sA1 = _Excel_ConvertFormula($oExcel, $sR1C1, $xlR1C1, $xlA1, $xlAbsolute)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Error converting formula to A1 style." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ; Show result
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 1", "Conversion type: Absolute" & @CRLF & "  A1 to a R1C1 style: " & $sFormula & " to " & $sR1C1 & @CRLF & "  R1C1 to A1 style: " & $sR1C1 & " to " & $sA1)

### Example 2

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an new workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Translate an formula from A1 to R1C1 style and use a relative reference
    Local $sFormula = "=SUM(C4:G12)", $sReference = "B2"
    Local $sConverted = _Excel_ConvertFormula($oExcel, $sFormula, $xlA1, $xlR1C1, $xlRelative, $oExcel.Range($sReference))
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 2", "Error converting formula." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_ConvertFormula Example 2", "Translate an A1 formula to a R1C1 and use " & $sReference & " as relative reference:" & @CRLF & $sFormula & " to " & $sConverted)