I wanted to point out that I now prefer to pass a cell or a range (as references) to a user function based on VBA support. This is much simpler than the ways it can be done in “pure LibO API”.
In addition it is simple based on passed ranges to write the function equally capable of handling single cells and ranges needing array-output.
I also wanted to give a choice between the three main representations of the 24-bit-colors used by LibO, which are HEX (as a string), RGB (as a string), and the number of type Long simply return to cCalc as aresult of type ‘Number’.
Another remark: Any function for cell introspection like this one should not be misused on a permanent basis to work with sheets wrongly keeping data coded by attributes instead of plain data types. If someone has such a sheet functions reading attributes should exclusively be used to declutter them.
Suggested code:
Option VBAsupport 1 REM Helps to easier get access to referenced cell ranges.
Function cellBackgroundColor(pVBArange, Optional pCtrl As String, Optional pDummy)
REM pDummy can be used to trigger recalculations. (Volatile or otherwise. AutoCalc won't work.)
Dim res()
cellBackgroundColor = res
On Local Error Goto fail
If IsMissing(pCtrl) Then pCtrl="RGB"
If (pCtrl=0) OR (pCtrl="") Then pCtrl="RGB"
cellRg = pVBArange.CellRange
uC = cellRg.Columns.Count - 1
uR = cellRg.Rows.Count - 1
Redim res(uR, uC)
fa = createUnoService("com.sun.star.sheet.FunctionAccess")
For c = 0 To uC
For r = 0 To uR
j_BC = cellRg.getCellByPosition(c, r).CellBackColor
Select Case Ucase(pCtrl)
Case "D"
res(r, c) = j_BC
Case "RGB"
res(r, c) = "(" & Red(j_BC) & ";" & Green(j_BC) & ";" & Blue(j_BC) & ")"
Case "H"
res(r, c) = "0x" & fa.callFunction("DEC2HEX", Array(j_BC, 6))
End Select
Next r
Next c
cellBackgroundColor = res
fail:
End Function
Example file: ask242690getCellBackColor_1.ods
