Referencing the calling Cell in a Macro Function.

Keyboard macros or custom scripts

Referencing the calling Cell in a Macro Function.

Postby JohnnyPL » Sat Apr 03, 2021 11:21 pm

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
JohnnyPL
 
Posts: 1
Joined: Sat Apr 03, 2021 10:57 pm

Re: Referencing the calling Cell in a Macro Function.

Postby Lupp » Sun Apr 04, 2021 12:41 am

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 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3137
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing the calling Cell in a Macro Function.

Postby eeigor » Sun Apr 04, 2021 7:37 am

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 1907 times
Ubuntu 18.04 LTS • LibreOffice 7.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm

Re: Referencing the calling Cell in a Macro Function.

Postby eeigor » Sun Apr 04, 2021 8:26 am

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 1905 times
Ubuntu 18.04 LTS • LibreOffice 7.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm

Re: Referencing the calling Cell in a Macro Function.

Postby Lupp » Sun Apr 04, 2021 1:11 pm

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 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3137
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 4 guests