OpenOffice calc, can macros update cell content automatically?

Question

I have used a macro I found on a forum online.

It detects the background colour of a indicated cell and returns a numerical value of that background colour. The problem is that it does it only once. When I change the background colour of the indicated cell, the numerical value does not update. To update it I have to change something in the cell where I call on the macro (e.g. delete one character and retype it), and hit enter. Then it updates the numerical value.

Is there a way to get it to update automatically? Alternatively, is there an easier way to get it to update (compared to the described deleting and retyping method), preferably one that works on multiple cells at once.

Code by Villeroy at openoffice forum:

Function CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns color code as number
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_BACKCOLOR = v.CellBackColor
   else
      CELL_BACKCOLOR = v
   endif
End Function

Function getSheetCell(byVal vSheet,byVal lRowIndex&,byVal iColIndex%)
dim oSheet
'   print vartype(vsheet)
   oSheet = getSheet(vSheet)
   if varType(oSheet) <>9 then
      getSheetCell = NULL
   elseif (lRowIndex > oSheet.rows.count)OR(lRowIndex < 1) then
      getSheetCell = NULL
   elseif (iColIndex > oSheet.columns.count)OR(iColIndex < 1) then
      getSheetCell = NULL
   else
      getSheetCell = oSheet.getCellByPosition(iColIndex -1,lRowIndex -1)
   endif
End Function

Answer 1

I'm not sure why the "AutoCalculate" setting doesn't work in this case (tested with LibreOffice 7.0.2). Anyway, you can manually update the macro output for a single cell or for multiple cells:

• for a single cell, hit F9
• for multiple cells, select them and hit CTRL + SHIFT + F9.