LibreOffice Macro always show #NULL! after reopening the file

Question

I wrote a macro in LibreOffice Calc and it is able to run correctly. But if I close the file and reopen, it always show #NULL! instead of the correct value. What am I missing here?

My macro code

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Function Calculate(CalType As String) As Double
'
' Calculate Macro
'

Dim i As Integer
Calc = 0

i = 1

Do While Not IsEmpty(Cells(i, 2))
    If (Cells(i, 3).Value = CalType And (Cells(i,2) = "A" Or Cells(i,2) = "B")) Then
        Calculate = Calculate + Cells(i, 4).Value
    ElseIf (Cells(i, 3).Value = CalType And Cells(i,2) = "C") Then
        Calculate = Calculate - Cells(i, 4).Value
    End If
    i = i + 1
Loop

'
End Function

The calling function will be something like =Calculate(J6)

The file is saved as .ods format.

Please provide an example macro that shows the problem. Also, are you using the .ods format? — Jim K, Aug 30 '16 at 12:48

score 0 · Accepted Answer · answered Aug 31 '16 at 16:42

The Cells call did not work at all for me. It is from VBA, not LO Basic. However I do not think that is the main problem.

LibreOffice expects that user-defined functions will be simple, only accessing the cell that contains the formula. Since the spreadsheet has not been fully loaded yet when the function is called, it is not possible to read other cells.

The workaround is to ignore errors and wait until the document is fully loaded before running the function. Take the following code as an example:

Function ReadOtherCell(row, col)
    On Error GoTo ErrorHandler
    oSheet = ThisComponent.CurrentController.ActiveSheet()
    oCell = oSheet.getCellByPosition(row, col)
    ReadOtherCell = "value is '" & oCell.getString() & "'"
    Exit Function
    ErrorHandler:
        Reset
End Function

Sub RecalculateAll
    ' This is for user-defined functions that need to read the spreadsheet.
    ' Assign it to the "View created" event,
    ' because before that, the spreadsheet is not fully loaded.
    ThisComponent.calculateAll
End Sub

Enter foo in A1, and =ReadOtherCell(0,0) in A2. So far, this has the same problem -- It will fail when the document is first opened.

Now, go to Tools -> Customize. In the Events tab, highlight View created. Press Macro... and find the RecalculateAll function. Then press OK.

Now when the document is closed and reopened, cell A2 should show the result value is 'foo'.

This is derived from B. Marcelly's answer at http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=73090&sid=f92a89d676058ab597b4b4494833b2a0.

And what is `ThisComponent.calculateAll` doing? How does it connect with your `ReadOtherCell` function, which requires parameters `row,col` as input? — rcs, Sep 01 '16 at 03:33
`ThisComponent.calculateAll` causes the formula in A2 to be recalculated, which calls `ReadOtherCell()`. — Jim K, Sep 01 '16 at 04:04
I tried your example and when opening the file it gives error: wrong number of parameters, even though the cell finally show the value. — rcs, Sep 01 '16 at 11:51
In the example, two parameters are being passed to a function that takes two parameters, so it shouldn't cause an error (and it didn't when I tested it). Are you sure that is where the error is coming from? Try it in an otherwise empty spreadsheet. Also, try changing the function and the call to take zero parameters and see what happens. — Jim K, Sep 01 '16 at 12:40

score 0 · Answer 2 · answered Mar 06 '18 at 05:47

0

I had the same problem. I noticed that in the module, i had an empty Sub Main After i 've erased it, the functions started working again

answered Mar 06 '18 at 05:47

Haralampos Statheros

1
1

LibreOffice Macro always show #NULL! after reopening the file

2 Answers2

Linked