Is it possible to get and set formulae in a Calc cell using R1C1 rather than A1 strings? I found that even if the option to use R1C1 is set in the Tools | Options menu, the value of Cell.Formula is still in A1 format.
As I recall from many years ago using VBA there was a FormualR1C1 (or something like it) property, and when
setting up a formula which references many items on the same row or column it’s a lot easier to use (IMHO).
Even under Option VBAsupport 1 no formula property contains R1C1 style references though a property .formulaR1C1 exists.
The basic info concerning references is, however, coded in numbers like in RC style for the sake of efficiency. You may get R1C1 info from a cell containing references by evaluating the .Tokens property where every reference occurring in the formula is represented by a token with OpCode 0 and Data being one structure or a sequence of two structures of type com.sun.star.sheet.SingleReference containing 7 fields of type Long.
I do not know a way to set this property.
Edit 0: Refer to the answer by @librebel
Edit 1:
I got a new look at the context from that and would like to pay back with a bit of code which demonstrates how to set a formula in RC-style for a cell even without having Calc in the respective mode.
See this attached demo.
(Now also a slightly enhanced demo.)
Edit 2: The relevant code is
Sub setRCformula(pRCformula As String, pCell As Object)
pCell.SetTokens(tokenizeRCformula(pRCformula, pCell))
End Sub
Function tokenizeRcformula(pRCformula As String, pCell As Object ) As Object
Dim hParser As Object
hParser = ThisComponent.CreateInstance( "com.sun.star.sheet.FormulaParser" )
hParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1
tokenizeRcformula = hParser.ParseFormula(pRCformula, pCell.CellAddress)
End FunctionHmmmm. Neither of these links opened when I downloaded them. But they opened OK directly from my mail client. Most odd. I’m still too busy to do much about this yet, maybe over the weekend.
(later) must have been a download issue - tried downloading again and they work.
Strange. The two links don’t point to an obscure server, but are internal to ths site (ask.libreoffice.org/upfiles/). They were created with the paperclip tool, and both of them work for me as usual. (My version too fresh? It’s V 6.0.2.1.)
Don’t worry! And: more haste, less speed.
Thanks - I’ve had time to look at it and it seem to answer the question. So I won’t need to change the option.
How on earth do you manage to find these things out? The API documentation is all of the kind “Thing A does B”, when what you need is “Which thing(s) do B?”.
Quoting @ptoye: “How on earth do you …”
Partly it’s curiosity, partly the conviction that there must be a way combined with a bit of anger not to know it. In addition there are hints taken from contributions not solving a problem quite completely, like in this case. Thanks to @librebel again! Some experience I have meanwhile, too.
Anyway it’s kind of fun - and some spare time due to the fact that I’m retired.
In fact I didn’t read much but “think-and-try” in this case.
Hello @ptoye,
i reproduced this behaviour on my LibreOffice version 5.4.5.1:
Excel R1C1;oCell.getFormula(), it still gives the formula in Calc A1 notation ( e.g. “=A$1” ), like you said;oCell.setFormula() passing a string in R1C1 notation, the formula shows up in lowercase characters, and the cell yields an Err.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
Thanks - I’ve no time now but will try later today with luck. Does this depend on the Tools | Option being set to R1C1-type formulae? In which case is there a way of getting/setting this option? I won’t be able to guarantee which way the option will be set when the macro is called.
i updated my original answer to include a method for setting this option by macro.
( see EDIT )
I wouldn’t pass @librebel over, but the parser service he used has its own setting concerning the reference style and does not work depending on the setting for the Calc document. You may find demonstrated exactly this in connection with setting the tokens for a cell, and by that also getting the formula converted to default style, in the demo I attached to my reworked answer.
See also: https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1AddressConvention.
Thanks Lupp, but that link gives me an Error 404!
Working down the answers, it seems that Lupp has provided a solution which doesn’t rely on the formula option being set. But thanks anyway - I may need the info about getting & setting options in the future. And all information is useful, except soap opera plots.
Hello,
Please see
R1C1 reference style with LibreOffice.
Also see you have post there - Using R1C1-type formulae in Calc macros.
You should note this when cross posting.
Yes, I should have noted the cross-posting. Sorry. But no answers at all on the LibO Nabble.
The link you gave me doesn’t have any answers, only cross-purpose argument and a bit of a flame war.
We should stop saying sorry to each other 