Conditional formatting - calculate relative formulas

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Conditional formatting - calculate relative formulas

Post by joesch »

Hello,

In conditional formatting (in Calc) there are the properties .Formula1 and .SourcePosition.

Is there a simple way to convert such formulas to different cell references?

Practically speaking, a function to which I can pass the parameters:

-position of the current cell
-Formula (Formula1 in the conditional format)
-SourcePosition (of the conditional formatting)

and which then returns the current formula.

Let me give the following example to explain this:

1. I select cells D10:D15 (so D15 is the active cell of the selection)
2. I call the Format Conditional Formatting dialog and specify the following formatting:
Screenshot1.gif
3. I insert a row and a column (marked pale yellow in the screenshot) and then mark E11 and call Format Conditional Formatting again and see
Screenshot2.gif
(All correct.)

If I now read out the conditional formatting from E11:

Code: Select all

With ThisComponent.Sheets.getByIndex(2).GetCellRangeByName("E11").ConditionalFormat(0)
	Msgbox "Formula1: " & .Formula1 & CHR(13) & _
	"Operator: " & .Operator & CHR(13) & _
	"Source-Sheet: " & .SourcePosition.Sheet & CHR(13) & _
	"Source-Column: " & .SourcePosition.Column & CHR(13) & _
	"Source-Row: " & .SourcePosition.Row & CHR(13)
End With
I get:
Screenshot3.gif
Screenshot3.gif (5.56 KiB) Viewed 2109 times
But how can I calculate (by macro) the formula "IF(SUM(G8;H16;E19;K9;myNamendRange)=12)" from the existing values (.Formula1, .SourcePosition)?
I don't mean how I can calculate the value of the formula, I mean how I can get the formula itself (as string)

Please keep in mind: the formulas can be much more complex and it might be difficult to do the conversion in a way that all parts to be converted (i.e. cell names) are separated, then converted and the formula is reassembled. (imho)


Greetings,
joesch
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Conditional formatting - calculate relative formulas

Post by Zizi64 »

You can call the Cell functions from a macro code, but you must pass the parameters as an Array.
viewtopic.php?f=20&t=636
viewtopic.php?f=25&t=9947
https://forum-test.openoffice.org/en/fo ... 74&t=72284

To create the Array inside the relevant parameters, you must parse the Formula string.


(I suppose it, recreating the IF() and the SUM() is not the real task, but it is a sample only...)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Conditional formatting - calculate relative formulas

Post by joesch »

You can call the Cell functions from a macro code, but you must pass the parameters as an Array.
I know this and has nothing to do with my question. My question is about conditional formatting.


Greetings,
joesch
joesch
Posts: 53
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Conditional formatting - calculate relative formulas

Post by joesch »

joesch wrote:
You can call the Cell functions from a macro code, but you must pass the parameters as an Array.
I know this and has nothing to do with my question. My question is about conditional formatting.
I can also put my question a little differently, maybe it will be more understandable:

In cell E16 there would be the formula:

=IF(SUM(G13;H21;E24;K14;myNamendRange)=12)

If I now (manually) copy this cell/formula and paste it into cell E11 (paste special, only formula), Calc will automatically adjust the formula and the formula (in E11) will be

=IF(SUM(G8;H16;E19;K9;myNamendRange)=12)

My question is this:
How can I determine this formula in E11 by macro (without copying the formula) if I know the formula in E16?

It is clear that I can extract the cell addresses (G13, H21, ...) from the formula and convert every single address (it is known that I am looking for the address which is 5 lines higher - e.g. G13--->G8 ) and then reassemble the formula, but this seems to me to be too difficult for complex formulas.
(Searched is NOT the result of the formula, searched is the formula as string: "=IF(SUM(G8;H16;E19;K9;myNamendRange)=12)".)



greetings,
joesch
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Conditional formatting - calculate relative formulas

Post by Zizi64 »

It is clear that I can extract the cell addresses (G13, H21, ...) from the formula and convert every single address (it is known that I am looking for the address which is 5 lines higher - e.g. G13--->G8 ) and then reassemble the formula, but this seems to me to be too difficult for complex formulas.
If you do not want to use the built-in features and mechanisms (like the copy/paste, etc.), then you must write difficult codes to reassemble the formulas based on the row/column number of the source and the target cell.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Conditional formatting - calculate relative formulas

Post by MrProgrammer »

joesch wrote:Is there a simple way to convert such formulas to different cell references?
joesch wrote:How can I determine this formula in E11 by macro (without copying the formula) if I know the formula in E16?
I think there is no simple way. You would first need to parse the formula, which would be possible, but difficult. Some cases you'd need to consider:
=IF(SUM(G13;H21;E24;K14;myNamedRange)=12)                   Your original example (valid but strange IF function — no then/else)
=IF(SUM(G13.H21.E24.K14.myNamedRange)=12)                   Don't adjust parts of long defined name
=IF(SUM(G$13;$H21;$E$24;K14;myNamedRange)=12)               Don't adjust absolute references to rows/columns
=IF(SUM(G13();H21();E24();K14   (;;);myNamedRange())=12)    Don't adjust user-defined function names
=IF(SUM('G13';'H21';'E24';'K14';'myNamedRange')=12)         Don't adjust row/column labels
=IF("""""SUM(G13;H21;E24;""&K14&"";myNamedRange)=12""""")   Don't adjust text;  Is K14 text or not?
=IF(SUM(G13;H21;1.E24;K14;myNamedRange)=12)                 Don't adjust number 1E+24
=IF(SUM(G13.G13;H21.H21;E24.E24;K14.K14;myNamedRange)=12)   Don't adjust sheet names
=IF(SUM(((((G13))));++++H21;E24!E24;myNamedRange)=12)       Terms may use parentheses or operators
However perhaps you can get Calc to do the work for you if your macro makes a copy of the sheet, copies the formula on that sheet to a new cell, extracts the new formula, and deletes the temporary sheet.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply