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]]] )
| $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 |
| 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 |
R1C1 references are language dependant.
In English: "R10C5" (row 10 column 5), in German: "Z10S5" (Zeile 10 Spalte 5)
#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)
#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)