Referencing the calling Cell in a Macro Function.

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
JohnnyPL
Posts: 1
Joined: Sat Apr 03, 2021 10:57 pm

Referencing the calling Cell in a Macro Function.

Post by JohnnyPL »

Hi,
I am trying to write Basic code that process information then depending on the result to change the fore color of the cell.

For example:-
In the spreadsheet cells: =MyFunction(A1+B1)

In Basic....
Public Function MyFunction(InputValue as Single) as String
.
.
. Code to Process the InputValue
.
.
'I have no idea how to reference the calling cell to change its color
If Result1 then
'Code to change the ForeColor to Red
Else
'Code to change the ForeColor to Blue
End if
End Function

Any ideas?
Regards JohnnyPL
Johnny
OpenOffice 4.1.7 Win8.1
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing the calling Cell in a Macro Function.

Post by Lupp »

To change a cell of the sheet for which currently a formula is evaluated by side-effects of user code called from the formula is not supported.
Playing around with lots of direct formatting is against the style concept anyway.

Define the cell styles you need, and apply ConditionalFormatting, or use the STYLE() function if an acctual aasignment of the style is needed.

AOO has no means by which code running for a cell formula can learn which cell it is working for - except you pass the elements of the .CellAddress (or equivalent information) via parameters.
Also: The assumption that there always is a "calling Cell" i not valid for array-evaluation.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Referencing the calling Cell in a Macro Function.

Post by eeigor »

JohnnyPL wrote:I have no idea how to reference the calling cell
Of course, you can refer to the calling cell, but this is a bad decision.
JohnnyPL wrote:...depending on the result to change the fore color of the cell
Dynamically Setting Up the Cell Format
1) 2 conditions are used: if the result is less than 0 than the font color is red, otherwise it is blue.
E.g. Using the STYLE() function with numbers
C1: =SUM(A1:B1)+STYLE(IF(CURRENT()<0;"CharRed";"CharBlue"))

The styles "CharRed" in C2 and "CharBlue" in C1 are applied.
NOTE: The mentioned styles need to be created.

2) The same can be achieved by means of conditional formatting, as @Lupp wrote.
See screenshot. The second condition just changes the style.
Attachments
Снимок экрана от 2021-04-04 08-45-01.png
Снимок экрана от 2021-04-04 08-34-59.png
Снимок экрана от 2021-04-04 08-34-59.png (3.58 KiB) Viewed 4772 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Referencing the calling Cell in a Macro Function.

Post by eeigor »

Using the STYLE() function with text
=IF(A1=B1;"="&T(STYLE("EqualStyle"));IF(A1>B1;"▼"&T(STYLE("RedStyle"));"▲"&T(STYLE("GreenStyle"))))

NOTE: In my locale, the list is separated by semicolons. The mentioned styles need to be created.
Attachments
Снимок экрана от 2021-04-04 09-32-14.png
Снимок экрана от 2021-04-04 09-32-14.png (4.49 KiB) Viewed 4770 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing the calling Cell in a Macro Function.

Post by Lupp »

eeigor wrote:Of course, you can refer to the calling cell, ...

And (of course) it will only work the way described in a recent LibreOffice (about V 7.0 or higher) under the Option VBAsupport 1 which was enhanced for LibO (V7.0 or higher e.g.), and is now even used as a basis for the new ScriptForge modules.
At the same time Basic code I wrote in 2018 for LibO working as expected till V 6.5 is now broken.
In short: I doubt if TDF will be able to analyze and fix regressions of the kind soon. Issues in the range are even difficult to report as bugs. Concerning the case I talked of (recursive usage of an array needing to be passed by reference through the levels) I already wasted >1 h trying in vain to create a simple example for the demonstration. (The original code is too complex and context-dependent.)

Took the time to check and found it was more complicated.
The alternative way to use a parameter for which a cell range is passed under Option VBAsupport 1
-1- even worked in LibbO 3.3 (the starting version), but only after Basic ran it once in debug mode.
-2- LibO versions of the 4. till 6. series accept it, but don't support other VBA features only available in 7.0+
-3- My AOO 4.1.7 doesn't accept it, and Basic reports an error "Objct variable not set".


Anyway: The questioner's signature tells AOO 4.1.7 to be his software.
And in addition: Even LibO withdrew some relaxing concerning the side-effects of user code.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply