Apache OpenOffice Community Forum

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

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.

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.

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.

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.