Created attachment 136258 [details] The demo mentioned in the report If the SEARCH function of Calc is called from BASIC user code it ignores the setting of 'Enable regular expressions in formulae' (the value of the property HOST-USE-REGULAR-EXPRESSIONS) and only searches for literals. I did not yet test if the other functions specified to evaluate the mentioned property show the bug too. I also did not test if different SDK also produce the misbehaviour. This is a REGRESSION. All the versions of LibO I could test up to V 5.2.3.3 did not show the bug. AOO up to V4.1.3 also works as expected. The oldest version I could test whih showed the bug was V5.3.4.2. The bug is still present in V5.4.1.2. The OS I tested on is Windows 10. The bit-width of LibO V5.4.1.2 is 64. All the other versions I tested are from PortableApps packages and use 32 bit-width. Steps to reproduce: A 1 Open the attached example with Calc of V5.2.3.3 or older. 2 Check for 'Enable regular expressions in formulae' being ENABLED. 3 Verify the expected results in column D as correct. 4 Compare with the results of the user function in column C. 5 Check the function code. 6 Close the file without saving. B 1 Open the file again with V5.3.4.2 or more recent. 2 (As above) 3 Compare columns C and D. Verify the claimed errors.
This seems to be related to the new default being Wildcards instead of Regexp, for Excel compatibility (see https://wiki.documentfoundation.org/ReleaseNotes/5.3#Option_settings). The scratch environment in which the function call is executed is initialized with default options, not configuration options, to have the same default behaviour through API calls. You can easily force regular expressions by setting the corresponding RegularExpressions property of the com.sun.star.sheet.SpreadsheetDocumentSettings service exposed by the com.sun.star.sheet.FunctionAccess service, i.e. in the attached document's case a simple fa.RegularExpressions = 1 does it, it overrides the Wildcards property, see also https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1SpreadsheetDocumentSettings.html#a04e61cf6d299d96820ace9b67d2acedb We maybe could merge this setting from the current document where the macro is executed, but that would need detection first whether the property was already set via the service to not override that one.. Explicitly setting the property nevertheless is the cleaner approach if you know the function should handle regexp as in this case.
(In reply to Eike Rathke from comment #1) > This seems to be related to the new default being Wildcards instead of > Regexp, for Excel compatibility ... > ... The > scratch environment in which the function call is executed is initialized > with default options, not configuration options, to have the same default > behaviour through API calls. Thanks. I didn't know that. It's the better way, of course, as it allows to call a SEARCH (or similar) based function via the FunctionAccess service from any type of document. > ... > We maybe could merge this setting from the current document where the macro > is executed, but that would need detection first whether the property was > already set via the service to not override that one.. I now did that by the user code based on the properties of the respective document (in my case ThisComponent), checking in advance if the properties 'RegularExpressions' and 'Wildcards' exist, which only should be the case if the service com.sun.star.sheet.SpreadsheetDocument is supported, and the running version is >= 5.2. > Explicitly setting the property nevertheless is the cleaner approach if you > know the function should handle regexp as in this case. Yes, that's clearly the better way. Thanks again. One question remaining nonetheless: Where are the ways SEARCH or other functions supporting 'Wildcards' should work specified? I couldn't find anything of value. Regarding the above I would suggest to set the bug RESOLVED NOTABUG.
If needed, the RegularExpressions and Wildcards should be obtained from the current document's SpreadsheetDocumentSettings and be propagated to the FunctionAccess, if a BASIC or API function needs to follow the current document's setting. Closing.