Changing cell background color in LibreOffice

Question

I am using LibreOffice 3.5.4.2. I would like to change the background color of cells based on various conditions. As a minimal example, I have the following macro/function defined:

function bgcolor()
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   

Doc = ThisComponent
Sheet = Doc.Sheets(1)

Cell = Sheet.getCellByPosition(0, 0)
REM Cell.CellBackColor = RGB(50,60,70)
bgcolor=Cell.CellBackColor
end function

I execute the function by entering =BGCOLOR() into a cell. The cell in which that formula is present returns the color value of the first cell (0,0) or A1 on sheet 1, as expected.

However, I cannot get the function to change the background color of cell A1. The cell background color does not change when I remove the REM line in the example above to set the background color.

How can I set the background color of a cell with a function in LibreOffice?

(I read about using "styles", but did not look further at this because I need to set many different background colors and did not want to make many different styles. It is possible to manually change the background color without using styles, so I thought it would be possible to do the same programmatically.)

Answer 1

First, there is nothing wrong with your macro in general. The single line to set the CellBackColor is correct as stated. The problem is that the function is called from the sheet that you are attempting to modify. A function is not allowed to modify the sheet from which it is called. So, if you call your function from sheet 1 and then try to change the background color of a cell in sheet 1, that will fail. If, however, you attempted to change the background color of a cell on sheet 0 when calling from sheet 1, that will work as expected.

Answer 2

the line should be

cell.cellbackcolor = RGB(50,60,70) 

(no "REM" there of course, that creates just commented line)

consider also the parameter of sheets to be 0 instead of 1 if you have only one sheet

for other interesting properties, see cell properties