Calc: UDF problems

Creating a macro - Writing a Script - Using the API
Post Reply
eeigor
Posts: 194
Joined: Sun Apr 12, 2020 10:56 pm

Calc: UDF problems

Post 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
Attachments
Снимок экрана от 2021-04-13 14-20-42.png
Снимок экрана от 2021-04-13 14-20-42.png (13.05 KiB) Viewed 4951 times
Снимок экрана от 2021-04-13 14-21-45.png
Снимок экрана от 2021-04-13 14-21-45.png (977 Bytes) Viewed 4951 times
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
User avatar
Villeroy
Volunteer
Posts: 30614
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: UDF problems

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 194
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: UDF problems

Post 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...
Last edited by eeigor on Tue Apr 13, 2021 4:27 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
User avatar
Villeroy
Volunteer
Posts: 30614
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: UDF problems

Post by Villeroy »

Please, do us all a favour and do all these interesting things with MS Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 194
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: UDF problems

Post by eeigor »

No, Excel is mentioned solely for understanding the problem, since many people know this program as well.
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
User avatar
Villeroy
Volunteer
Posts: 30614
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: UDF problems

Post by Villeroy »

Application.Volatile is VBA. I can't see where this is documented for LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply