Calc: UDF problems

Creating a macro - Writing a Script - Using the API

Calc: UDF problems

Postby eeigor » Tue Apr 13, 2021 1:18 pm

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   Expand viewCollapse view
    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   Expand viewCollapse view
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 2561 times
Снимок экрана от 2021-04-13 14-21-45.png
Снимок экрана от 2021-04-13 14-21-45.png (977 Bytes) Viewed 2561 times
Ubuntu 18.04 LTS • LibreOffice 7.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: UDF problems

Postby Villeroy » Tue Apr 13, 2021 3:53 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 30157
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: UDF problems

Postby eeigor » Tue Apr 13, 2021 4:18 pm

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.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: UDF problems

Postby Villeroy » Tue Apr 13, 2021 4:25 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 30157
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: UDF problems

Postby eeigor » Tue Apr 13, 2021 4:31 pm

No, Excel is mentioned solely for understanding the problem, since many people know this program as well.
Ubuntu 18.04 LTS • LibreOffice 7.2.1.2 Community
eeigor
 
Posts: 184
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: UDF problems

Postby Villeroy » Tue Apr 13, 2021 7:47 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 30157
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests