How do I make the UDF volatile?

eeigor · October 31, 2021, 10:55am

A1: =GetSearchCriteriaOption()&T(NOW())
doesn’t help.

Details:

Function GetSearchCriteriaOption() As Boolean
'''	Calls: Tools.GetSearchCriteriaProperty()

	Dim sNodePath$
    sNodePath = "/org.openoffice.Office.Calc/Calculate/Other"
    GlobalScope.BasicLibraries.LoadLibrary("Tools")
    GetSearchCriteriaOption = GetRegistryKeyContent(sNodePath).getByName("SearchCriteria")
End Function

Unfortunately, I cannot change the option value without restarting the application (old bug). So I want to use that value, but it doesn’t update in cell A1 in the sheet when I change the option value manually.

I’ll add more. This option (“Search criteria = and <> must apply to whole cells”) affects the operation of the advanced filter, but does not affect other Calc functions. Very bad. And I want to make the advanced filter and functions work in a similar way, so I insert the substring “.*” before and after the criterion, if the option is not set (for functions).

GrahamLees · October 31, 2021, 11:25am

Does pressing the F9 key not recalculate changed formulas in the current sheet?

eeigor · October 31, 2021, 11:43am

Without a doubt, yes.

GrahamLees · October 31, 2021, 11:58am

well, that saves the restart at least… the other is to refresh (bump) the bug report.

sokol92 · October 31, 2021, 2:03pm

See MatchWholeCell. This document property can be changed by a macro.

eeigor · October 31, 2021, 4:01pm

@sokol92, thanks a lot. This solved my specific option problem.
MatchWholeCell=SearchCriteria

Function ToggleMatchWholeCell()
	Dim bMatchWholeCell As Boolean
	bMatchWholeCell = ThisComponent.getPropertyValue("MatchWholeCell")
	ThisComponent.setPropertyValue("MatchWholeCell", Not bMatchWholeCell)
End Function

However, the question of UDF volatility remained.

Excel: Link

sokol92 · October 31, 2021, 4:45pm

You can emulate it like this:

Function MyVolatileFunction(arg, dummy)
 Msgbox "MyVolatileFunction called"
 MyVolatileFunction=arg+1
End Function

Formula for cell B1: =MYVOLATILEFUNCTION(A1; NOW())

Change any cell in the worksheet - you will see a message.

erAck · November 2, 2021, 9:12pm

No, good. It’s confusing enough already.

mariosv · November 3, 2021, 11:03pm

As I remember it affects calc functions, years ago it was seen in report bug about the slowness with VLOOKUP with M Meeks, and with the option marked the function works faster.

eeigor · November 4, 2021, 6:19am

@mariosv, yes, it affects.
I have to be clear about this. The option works correctly with functions only if the criterion is recognized as a regular expression. When using the Advanced Filter, you must explicitly set the “Regular Expressions” option (checkbox), but for the function criterion, you need to add the recognition characters of the regular expression. For example, you need to select records by gender of a person (female/male): enter “male” (literal), it will find only them, enter “male$”, then it will search depending on the value of the “Search criteria = and <> must apply to whole cells” option.

Search criteria option == ThisComponent.getPropertyValue(“MatchWholeCell”)

@sokol92, thanks again for the tip about the MatchWholeCell property.

Agree: it’s confusing enough already. True, a little off topic.