Ask Your Question
1

How to use R1C1 formulae in Calc macros?

asked 2018-03-13 17:09:02 +0200

ptoye gravatar image

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).

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2018-03-13 23:42:10 +0200

Lupp gravatar image

updated 2018-03-17 14:20:47 +0200

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 Function
edit flag offensive delete link more

Comments

Hmmmm. 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.

ptoye gravatar imageptoye ( 2018-03-15 16:53:38 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-03-15 17:09:48 +0200 )edit

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?".

ptoye gravatar imageptoye ( 2018-03-17 13:17:58 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-03-17 13:33:45 +0200 )edit

And, of course, I did not answer the get-part of the question sufficiently. That was @librebel .

Lupp gravatar imageLupp ( 2018-03-17 13:51:13 +0200 )edit
2

answered 2018-03-14 16:25:09 +0200

librebel gravatar image

updated 2018-03-15 14:59:22 +0200

Hello @ptoye,

i reproduced this behaviour on my LibreOffice version 5.4.5.1:

  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 )
  2. When calling oCell.getFormula(), it still gives the formula in Calc A1 notation ( e.g. “=A$1” ), like you said;
  3. 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

edit flag offensive delete link more

Comments

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.

ptoye gravatar imageptoye ( 2018-03-15 11:01:57 +0200 )edit

i updated my original answer to include a method for setting this option by macro. ( see EDIT )

librebel gravatar imagelibrebel ( 2018-03-15 15:02:49 +0200 )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/....

Lupp gravatar imageLupp ( 2018-03-15 15:18:20 +0200 )edit

Thanks Lupp, but that link gives me an Error 404!

ptoye gravatar imageptoye ( 2018-03-17 13:03:00 +0200 )edit

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.

ptoye gravatar imageptoye ( 2018-03-17 13:21:27 +0200 )edit
0

answered 2018-03-13 20:50:11 +0200

Ratslinger gravatar image

updated 2018-03-13 21:17:11 +0200

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.

edit flag offensive delete link more

Comments

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.

ptoye gravatar imageptoye ( 2018-03-14 10:24:46 +0200 )edit

@ptoye Sorry I hadn't read your question close enough.

Ratslinger gravatar imageRatslinger ( 2018-03-14 17:12:35 +0200 )edit

We should stop saying sorry to each other :)

ptoye gravatar imageptoye ( 2018-03-15 16:51:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-13 17:09:02 +0200

Seen: 204 times

Last updated: Mar 17