Conditional formatting - calculate relative formulas

Creating a macro - Writing a Script - Using the API

Conditional formatting - calculate relative formulas

Postby joesch » Fri May 01, 2020 11:11 am

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   Expand viewCollapse view
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 487 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
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Conditional formatting - calculate relative formulas

Postby Zizi64 » Fri May 01, 2020 1:04 pm

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Conditional formatting - calculate relative formulas

Postby joesch » Fri May 01, 2020 1:52 pm

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: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Conditional formatting - calculate relative formulas

Postby joesch » Fri May 01, 2020 2:20 pm

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
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Conditional formatting - calculate relative formulas

Postby Zizi64 » Fri May 01, 2020 2:29 pm

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Conditional formatting - calculate relative formulas

Postby MrProgrammer » Fri May 01, 2020 3:51 pm

joesch wrote:But how can I calculate (by macro) the formula "IF(SUM(G8;H16;E19;K9;myNamendRange)=12)" from the existing values (.Formula1, .SourcePosition)?
You should first explain why do you want to do that. That is, explain your ultimate goal, not the step. Perhaps there is a better way to accomplish the goal. XY Problem

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)                   The 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
Without knowing your goal, I can't say if you will also need to parse myNamedRange and recursively any names ranges it uses.

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3985
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests