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: "" and sets @error.
@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)