Hello @ptoye,

i reproduced this behaviour on my LibreOffice version 5.4.5.1:

- the option “Tools : Options : LibreOffice Calc : Formula : Formula Options : Formula Syntax” should be set to
`Excel R1C1`

;

( This visibly changes the cell adresses in the GUI to R1C1 notation )
- When calling
`oCell.getFormula()`

, it still gives the formula in Calc A1 notation ( e.g. “=A$1” ), like you said;
- When calling
`oCell.setFormula()`

passing a string in R1C1 notation, the formula shows up in lowercase characters, and the cell yields an Err.

( if you now place the cursor at the end of this lowercase formula inside the formula editfield, and then type a SPACE and ENTER, the formula is correctly displayed in uppercase letters and the cell shows the correct formula result );

Please try the following workarounds for this:

**1. Setting the formula in R1C1 notation:**

Instead of using `oCell.setFormula()`

passing a formula string in R1C1 notation, you could use `oCell.FormulaLocal = “=R1C1”`

( where “=R1C1” is your formula ).

Then it is correctly displayed in uppercase letters, and the cell shows the correct formula result.

**2. Getting the formula in R1C1 notation:**

Instead of using `oCell.getFormula()`

, you could use the below specified Basic function `getFormula_R1C1()`

:

```
Function getFormula_R1C1( oCell As Object ) As String
REM Returns the Formula for the specified Cell in R1C1 notation.
Dim oDoc As Object : oDoc = ThisComponent
Dim oParser As Object : oParser = oDoc.createInstance( "com.sun.star.sheet.FormulaParser" )
oParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1
getFormula_R1C1 = oParser.printFormula( oCell.getTokens(), oCell.CellAddress )
End Function
```

**EDIT 2018-03-15**

**3. settting the Formula Address Convention option to **`Excel R1C1`

:

To programmatically set the option from “Tools : Options : LibreOffice Calc : Formula : Formula Options : Formula Syntax” to `Excel R1C1`

, call : `setFormulaSyntaxAddressConvention( 2 )`

```
Sub setFormulaSyntaxAddressConvention( Optional iAddressConvention )
REM Sets the option from "Tools : Options : LibreOffice Calc : Formula : Formula Options : Formula Syntax" to the specified AddressConvention.
REM <iAddressConvention> : member of Constant Group com.sun.star.sheet.AddressConvention;
REM Can be one of : -1=UNSPECIFIED;
REM 0=OOO ( Calc A1 );
REM 1=XL_A1 ( Excel A1 );
REM 2=XL_R1C1 ( Excel R1C1 );
REM 3=XL_OOX;
REM 4=LOTUS_A1.
REM If no argument is passed, the AddressConvention is set to Calc A1 : com.sun.star.sheet.AddressConvention.OOO;
If IsMissing( iAddressConvention ) Then iAddressConvention = com.sun.star.sheet.AddressConvention.OOO
Dim aProps(1) As New com.sun.star.beans.PropertyValue
aProps(0).Name = "nodepath"
aProps(0).Value = "org.openoffice.Office.Calc/Formula/Syntax"
aProps(1).Name = "enableasync"
aProps(1).Value = True
Dim oConfig As Object : oConfig = createUnoService( "com.sun.star.configuration.ConfigurationProvider" )
Dim oFormulaSyntax As Object
oFormulaSyntax = oConfig.createInstanceWithArguments( "com.sun.star.configuration.ConfigurationUpdateAccess", aProps() )
oFormulaSyntax.replaceByName( "Grammar", iAddressConvention )
oFormulaSyntax.commitChanges()
oConfig.flush()
End Sub
```

HTH, lib