Calc: UDF problems
Posted: 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.
UPD: In Excel in order to make UDF volatile
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
Code: Select all
Function My_Func()
Application.Volatile
'
' Remainder of the function
'
End Function