Calc tries executing user function before sheet is loaded

[stabiloboss]

Hi,

It seems Calc tries to execute user-defined functions before the document is fully loaded. If the function has a reference to ActiveSheet, Calc will throw “object variable not set” errors while loading.

As an example, below function returns the index of “this sheet”. The function works fine if the document is already loaded, but throws an error when the document is being opened.

function getSheetIndex() as integer
Dim oSheet As Object : oSheet = ThisComponent.CurrentController.ActiveSheet
getSheetIndex = oSheet.RangeAddress.Sheet
end function

The errors don’t crash or freeze the application, they just display an error dialog that needs to be closed for the document to resume loading. So the document will eventually load, but depending on the number of user-defined functions, this may require a lot of clicking. And when the document is fully loaded and displayed, Calc will not auto-recalculate those functions, so I have to manually recalculate each one of them (even though Calc is set to autocalculate).

How can I avoid this error? Is there a way to delay the execution of custom functions until the document is fully loaded? Is my function wrong?

I’m using LO 6.4.7.2, downloaded from the LO website, on Linux MInt 19.3

Thanks!

runtimeErrorDemoForum.ods

[Zizi64]

Is this function assigned to a document loading event? Or is it a simple custom cell function?

Can you upload an ODF type sample file here with the embedded macro code?

[stabiloboss]

Hi @Zizi64, thanks for replying. I’ve uploaded a document containing 1 example function. I made this document by stripping down the document I was working on. At some point the error disappeared. This is documented in the example document. I ended up with 1 function that suddenly started working without throwing errors. However, when I copy the cell containing the function to cells on a different row, the errors re-appear. I still think the error is related to the ActiveSheet method, because when I remove the call to ActiveSheet from the function, no more errors are thrown.

[Zizi64]

“I’ve uploaded a document containing 1 example function.”

Where is it?

[stabiloboss]

@Zizi64, no idea where that one went, uploaded it again

[Zizi64]

Thank you, there is an attachment in your question now. I just tried it: It is a very strange behavior. I just made more copies of the yellow cell, saved it, and the result is same…

[Zizi64]

The errer messages are seme, but the modifiied code works after the clicking “OK” on the messages. I am using a helper parameter for the function. It is a NOW() value in the cell A1, what causes the recalculation…

16094976637344463-Zizi64.ods

[stabiloboss]

@Zizi64, thanks, that’s a clever workaround for the recalculation. Like you mentioned, the error messages are still there, which is a bummer since the sheet I’m trying to make would have over 1000 function calls that will throw error an error on loading. That’s a lot of message boxes to close…

[Zizi64]

And you can try to use the Error handlig:

function getSheetIndex(DeteTime as double) as string
 Dim oDoc as object
 Dim oSheet as Object
 Dim oController as object

	On Error Goto ErrorHandler
	' ... undertake task during which an error may occur
	oDoc = ThisComponent
	'xray oDoc
	oController = oDoc.CurrentController
	oSheet = ThisComponent.CurrentController.ActiveSheet
	getSheetIndex = "sheet " & cstr(oSheet.RangeAddress.Sheet)
	Exit function

ErrorHandler: 
    ' ... individual code for error handling
	Exit function
end function

Maybe it is not a perfect solution…

[stabiloboss]

Probably not, but it works Thanks for helping me out (on new year’s day)!