Page 1 of 1

Calc: UDF problems

Posted: Tue Apr 13, 2021 1:18 pm
by eeigor
I noticed that the UDF function from the cell is called twice when the file is loaded. Why? And the UDF is volatile because it is recalculated when data not related to it changes. How to avoid this?

Another problem: The UDF call is ahead of the file download.
The function on the sheet references the UDF, and when opening the file, the macro line <ThisComponent.lockControllers> causes an error (Controller not avalable yet). How to avoid this error? If you skip the error (resume next), the sheet "hangs" in a semi-drawn state. The macro takes quite a lot of time to process the cells. Perhaps, I am to add a button on demand to call the function...

UPD:
Then the question is purely theoretical. However, the processing of cells is delayed up to 45 seconds, but when called from the module with Controller locked, it takes 7-9 seconds.

Code: Select all

    ThisComponent.lockControllers  'may cause an error when opening a file
    <...>

ExitHere:
    On Error Resume Next
    ThisComponent.unlockControllers
    Exit Function
HandleErrors:
    Msgbox "#" & Err & ": " & Error _
     , MB_ICONSTOP, "macro:REGEX_SELECT()"
    Resume ExitHere
End Function
UPD: In Excel in order to make UDF volatile

Code: Select all

Function My_Func() 
 Application.Volatile 
 ' 
 ' Remainder of the function 
 ' 
End Function

Re: Calc: UDF problems

Posted: Tue Apr 13, 2021 3:53 pm
by Villeroy
Any UDF calculates the passed arguments and nothing else. As soon as you use objects like ThisComponent or StarDesktop in an UDF, the UDF is calling for trouble. While loading the document, there is no controller when the functions are calculated for the first time.

Function MyFunction(sheet_data) AS Double | Integer | Date | String
where sheet_data are doubles, strings, empty strings for blank cells or Null for error values. These are the data you work with.

Re: Calc: UDF problems

Posted: Tue Apr 13, 2021 4:18 pm
by eeigor
Well. But why is UDF called twice?
Does UDF behave itself as volatile always? However, I need to check again, since I did not change other data, but changed the layout: deleted a row, inserted a column...

Re: Calc: UDF problems

Posted: Tue Apr 13, 2021 4:25 pm
by Villeroy
Please, do us all a favour and do all these interesting things with MS Excel.

Re: Calc: UDF problems

Posted: Tue Apr 13, 2021 4:31 pm
by eeigor
No, Excel is mentioned solely for understanding the problem, since many people know this program as well.

Re: Calc: UDF problems

Posted: Tue Apr 13, 2021 7:47 pm
by Villeroy
Application.Volatile is VBA. I can't see where this is documented for LibreOffice.