Customized function is not updating cell properties

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
rosajo
Posts: 3
Joined: Sat Jun 14, 2008 4:10 pm

Customized function is not updating cell properties

Post by rosajo »

Hi everyone,

I need your help to figure out what's happening or what am I doing wrong!
when I run a macro from the menu Tools -> Macros -> Run Macro ..., I've got what I expected but, if I call a function from a cell, on which it calls the macro itself, I dont have what I expect.

The following example explains better the issue. In this example, I putted the background color of the cell A1 as red and the cell C1 has a call to the function like this:
=SETCOLORF(D1)
so that when I put somethind on Cell D1, the function setColorF is called automatically.

Code: Select all

Function setColorf ( ) 

	setColorM
	
End Function


Sub setColorM

	Dim oDocument      as Object
	Dim oController    as Object
	Dim oConfSheet     as Object
	Dim oSelectorSheet as Object
	Dim oLocalCell     as Object
	Dim oRemoteCell    as Object
	Dim iRow           as Integer

	oDocument = ThisComponent
	oController = oDocument.GetCurrentController
	oSheets = oDocument.Sheets

	oSelectorSheet = oController.getActiveSheet()

	oLocalCell  = oSelectorSheet.GetCellByPosition(0,0)
	oRemoteCell = oSelectorSheet.GetCellByPosition(0,1)

	oRemoteCell.CellBackColor = oLocalCell.CellBackColor

End Sub
Now, at the end I expect the Cell A2 become red when I fill D1 with something. The fact is, it ins't updating. It just does it if I run the macro setColorM from the menus.
The function is not updating the background color!

Test case: clear the background color of Cell A2 and fill the cell D1 with something.


Can you tell me what have I done wrong or Is there some answer I should attend to?

Thanks in advance.
OOo 2.3.X on Mac OSx Leopard + Ms Windows
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Costumized function is not updatind cell properties

Post by Villeroy »

This can not work. Cell functions can take arguments by value (you never get references to cells). The CurrentSheet may be another one than the sheet holding the function whenever you load the document or do a hard calculation (Ctrl+Shift+F9). Functions have to return a value or an array of values. They are not meant to trigger macros. Things like this should be implemented as wizards where some dialog collects ranges before performing actions on these ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
rosajo
Posts: 3
Joined: Sat Jun 14, 2008 4:10 pm

Re: Customized function is not updating cell properties

Post by rosajo »

Thank you Villeroy,

In fact, this works (at least part of it). If you add the following line in the setColorM macro, you'll see that this macro is triggered from the function.

Code: Select all

MsgBox "Hello"
Allow me to change slightly the issue. Let's say that I created a SpreadSheet named "Conf".
Then, I created a function with a parameter and with a returning value. The logic inside it is similar to the setColorM macro, the diference is that I don't call the active sheet but the "Conf" Sheet directly.
Furthermore, the parameter in the function is optional in order to the same be triggered upon menu ( Tools -> Macros -> Run Macro ... )

Code: Select all

Function setColorX ( optional alpha As String ) As String

   Dim oDocument      as Object
   Dim oController    as Object
   Dim oConfSheet     as Object
   Dim oSelectorSheet as Object
   Dim oLocalCell     as Object
   Dim oRemoteCell    as Object
   Dim iRow           as Integer

   oDocument = ThisComponent
   oController = oDocument.GetCurrentController
   oSheets = oDocument.Sheets

   oSelectorSheet = oSheets.getByName("Conf")

   oLocalCell  = oSelectorSheet.GetCellByPosition(0,0)
   oRemoteCell = oSelectorSheet.GetCellByPosition(0,1)

   oRemoteCell.CellBackColor = oLocalCell.CellBackColor

   setColorX = alpha

End Function
With this "Conf" Spreadsheet example, I putted the background color of the cell A1 as red and the cell C1 has a call to the function like this:
=SETCOLORX(D1)
so that when I put somethind on Cell D1, the function setColorX is called automatically.
Test case: clear the background color of Cell A2 and fill the cell D1 with something.

Now, the question is, why is that cell A2 is only filled when I trigger the macro upon menu and not when I fill Cell C1 with something?
The issue is, I wanted to change cell properties upon a cell change.

Thanks,
OOo 2.3.X on Mac OSx Leopard + Ms Windows
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Customized function is not updating cell properties

Post by Villeroy »

Are you aware of styles and cell function STYLE?
Funny games with URLs
http://user.services.openoffice.org/en/ ... php?id=667
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
pitonyak
Volunteer
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: Customized function is not updating cell properties

Post by pitonyak »

You can not modify cells in the same sheet that calls a function. This is intentional, yet annoying behaviour. I must admit that I am a bit surprised that you can not change cell attributes, I only expected this to be related to the cell value.

I believe that the reason is related to how Calc works internally. The behaviour is similar to "load everything from a sheet, run all calculations, write the values back". Anything you change is not shown.

that is my opinion as to the cause.
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
Post Reply