Reference to current cell from script in LibreOffice Calc

Question

I want to create VBScript for LibreOffice calc which I want to call from cell as formula. I need to reference to the current cell. So I create script MyCell().

function myCell() As String
    Dim oService As Object
    oService = createUnoService("com.sun.star.sheet.FunctionAccess")
    myCell = oService.callFunction("CELL",Array("ADDRESS"))
end function

When I call this directly from cell eg. B2

=MyCell()

it will return always the $A$1 but I need to get $B$2.

What is wrong? How to achieve this?

Before I say "This is impossible", I want to ask you - for what? What are you going to do with this information inside the function? — JohnSUN
My goal is to colorize cells based on HEX color information on it. So i created function fillColor(col) which filling cells on next sheet. I need the row position of current cell to be able to colorize cell on corresponding row. — radio

JohnSUN JohnSUN · Accepted Answer · 2021-04-06T05:15:51

It cannot be done. There are several reasons for this.

Your idea "a function on this sheet will change something elsewhere in the spreadsheet" contradicts one of the concepts of spreadsheets - "the result of a function can only change the cell in which this function is written". Now this is implemented only for the current sheet - the contents of the current sheet are blocked while the function is being recalculated. But you must be prepared for the fact that in one of the next versions the prohibition can be extended to all sheets of the current book (and possibly to all open books) and your algorithm will stop working. It is not known whether this will be done or not, but it is not worth the risk.
There is no mechanism that will allow you to get additional information from within the code about the "caller", about the object that launched the code - the function must receive all the necessary information through the input parameters before starting execution. (This limitation does not apply to Subroutines).
The result of executing a function is a value — a number, string, array of numbers or strings, an object reference, structure, or user-defined type. As you can see, there is no "cell background color" value in this list.

Now that I have listed the main reasons (this is far from all) why this cannot be done, I will show several "dirty hacks" how to not do this.

Pass the coordinates of the current cell to the function as a column, row and sheet using the appropriate Calc functions
Use VBA Compatibility Mode and use the Excel Caller ID Technique
No need to fiddle with one cell, do it with the procedure for the entire sheet at a time - select all the cells on the sheet with color values and color them. And yes, you can call this procedure from within a function. Or assign to a sheet event.
Search the web - the problem of coloring cells by color code has been solved many times

Update. I earnestly ask you - do not do this (there are infinitely many bad programs in the world!):

Function setBkColorTo(cellAddress as String, nColor As Long) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim i As Long 
    setBkColorTo = "Wrong data!"
    On Error GOTO WrongData
    oSheets = ThisComponent.getSheets()
    oCellRangesByName = oSheets.getCellRangesByName(cellAddress)
    For i = LBound(oCellRangesByName) To UBound(oCellRangesByName)
        oCellRangesByName(i).CellBackColor = nColor
    Next i
    setBkColorTo = "Colorized '" + cellAddress + "'"
    Exit Function
WrongData:

End Function

Do not save this function and do not call it as =SETBKCOLORTO("Sheet2.B5:D7;Sheet2.C12:C17;Sheet2.A1";255)

Reference to current cell from script in LibreOffice Calc

1 Answers