In Calc, if your language is set to English and run the following macro, everything works and a random number between 0 and 100 is shown. Sub FunctionAccessTest Dim fa As Object fa = CreateUnoService("com.sun.star.sheet.FunctionAccess") result = fa.callFunction("RANDBETWEEN", Array(0, 100)) MsgBox result End Sub However, if you change the UI language (via Tools - Options - Language settings) to a language that is not English, the macro above will not run and a NoSuchElementException exception will be raised. Interestingly changing RANDBETWEEN for RANDBETWEEN.NV in the macro above will work. But both of them should work (RANDBETWEEN and RANDBETWEEN.NV in any language). This bug affects a method from the ScriptForge library, more specifically the GetTempName method from the FileSystem service, which relies on calling RANDBETWEEN. Because of this bug, GetTempName only works in English, but fails in other languages. Below is a sample code for testing the GetTempName error. Sub TestTempName GlobalScope.BasicLibraries.LoadLibrary("ScriptForge") fs = CreateScriptService("FileSystem") MsgBox fs.GetTempName() End sub In summary, the problem appears to be in the handling of the RANDBETWEEN function by the FunctionAccess service and not in ScriptForge itself. This is why I reported this as a Calc bug. System info Version: 7.3.4.2 / LibreOffice Community Build ID: 30(Build:2) CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: pt-BR Ubuntu package version: 1:7.3.4-0ubuntu0.22.04.1 Calc: threaded
RANDBETWEEN.NV has been added with: 716655820d69a0d6aaa2714cb4f12bae1aa2b862 Resolves: tdf#127831 implement RAND.NV() and RANDBETWEEN.NV() non-volatile Same as RAND() and RANDBETWEEN() but not recalculating on every change, just the normal expression recalculation. I wonder if it may be related with: https://bugs.documentfoundation.org/show_bug.cgi?id=127831#c20 https://bugs.documentfoundation.org/show_bug.cgi?id=127831#c24
Shouldn't be related. I think it's related to RANDBETWEEN() being implemented as an Add-In function, which RANDBETWEEN.NV() is not, but I didn't investigate further.
I confirm the bug on next french version: Version: 7.3.5.2 / LibreOffice Community Build ID: 30(Build:2) CPU threads: 12; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: fr-BE (fr_BE.UTF-8); UI: fr-FR Ubuntu package version: 1:7.3.5~rc2-0ubuntu0.20.04.1~lo1 Calc: threaded Additional info: as an alternative to changing the UI's language, the bug appears and disappears also by changing in the options the language of the Calc functions only: Tools - Options - LibreOffice Calc - Formulas - Use the English function names I will anyway prepare a patch for 7.5 and 7.4 replacing RANDBETWEEN by RANDBETWEEN.NV where appropriate in the ScriptForge libraries.
(In reply to Jean-Pierre Ledure from comment #3) > I will anyway prepare a patch for 7.5 and 7.4 replacing RANDBETWEEN by > RANDBETWEEN.NV where appropriate in the ScriptForge libraries. What do you mean by that?
... that the FunctionAccess service is used in some places of the (Basic) code of ScriptForge. In 2 places, the RANDBETWEEN function is invoked. I plan to replace them by RANDBETWEEN.NV. This should avoid the occurrence of the incident as described by Rafael in relation with SF_FileSystem.GetTempName() method. The root cause however should not be repaired.
A workaround is to use the internal international programmatic name: com.sun.star.sheet.addin.Analysis.getRandbetween (case insignificant so COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRANDBETWEEN works as well) that is found for any language.
(In reply to Eike Rathke from comment #6) > A workaround is to use the internal international programmatic name: > com.sun.star.sheet.addin.Analysis.getRandbetween > (case insignificant so COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRANDBETWEEN > works as well) that is found for any language. Hi Eike, thanks for taking interest in this issue. I actually did not know RANDBETWEEN was an Add-in function, because in the Help it is not listed in the corresponding Add-in Functions help page: [1] https://help.libreoffice.org/master/en-US/text/scalc/01/04060115.html?DbPAR=CALC#bm_id3152871 And [2] https://help.libreoffice.org/master/en-US/text/scalc/01/04060116.html?&DbPAR=CALC&System=UNIX However, the following help page [3] does state that RANDBETWEEN is an Add-in function. [3] https://help.libreoffice.org/master/en-US/text/sbasic/shared/calc_functions.html?&DbPAR=CALC As a matter of fact, there are several Add-in functions in [3] that are not in [1] or [2]. Maybe we could make it clearer in the Help pages for these functions that they're Add-in functions through a <note> tag in their corresponding help pages. For instance, the page for YEARFRAC [4] could have a general note saying that "This function is implemented as a Calc Add-in function. Read [3] for more information." [4] https://help.libreoffice.org/master/en-US/text/scalc/01/func_yearfrac.html?DbPAR=CALC#bm_id3148735 Such <note> can be reused in other help pages.
(In reply to Eike Rathke from comment #6) > A workaround is to use the internal international programmatic name: > com.sun.star.sheet.addin.Analysis.getRandbetween > (case insignificant so COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRANDBETWEEN > works as well) that is found for any language. BTW this strategy does work an any UI language: Sub AnalysisAddinTest Dim oAnalysis As Object oAnalysis = CreateUnoService("com.sun.star.sheet.addin.Analysis") result = oAnalysis.getRandbetween(0, 100) MsgBox result End Sub
Actually I guess this is what Eike had suggested in comment #6. This also works in any UI language. Sub FunctionAccessTest_v2 Dim fa As Object fa = CreateUnoService("com.sun.star.sheet.FunctionAccess") result = fa.callFunction("com.sun.star.sheet.addin.Analysis.getRandbetween", Array(0, 100)) MsgBox result End Sub
Jean-Pierre Ledure committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6814e4375c9c1e17b6a09dbbd0651034542dec54 ScriptForge - tdf#150203 Use RANDBETWEEN.NV i.o. RANDBETWEEN It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Eike Rathke from comment #2) > it's related to RANDBETWEEN() being implemented as an Add-In function Note however, that many add-in functions are now standardized ODF functions: RANDBETWEEN [1], DEC2HEX (bug 82610), ... They need to be accessible using their ODF names, in addition to their add-in names (maybe even re-implement them as normal citizens, and have their add-in names as compatibility alias). Having ODF-defined functions behave as it is nw is highly inconsistent and unexpected. [1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#RANDBETWEEN
(In reply to Mike Kaganski from comment #11) Sorry for the noise, I didn't notice Eike already assigned to himself.
More noise: ;) Note that using the programmatic name for Add-Ins is always correct though because they don't even have to have an English function name, just some display name that may be in any language, especially if it's a local Add-In. Having to use the programmatic name in this case here (Calc "standard" and even ODFF function names) of course is unexpected and having to know which functions are implemented as Add-In is even worse. There's a list though https://help.libreoffice.org/7.4/en-GB/text/sbasic/shared/calc_functions.html?&DbPAR=SHARED under "UNO Service Names for Analysis Add-In Functions". Only almost complete as the DateFunc and Pricing Add-Ins are missing. See also https://help.libreoffice.org/7.4/en-GB/text/scalc/01/04060111.html?&DbPAR=CALC https://help.libreoffice.org/7.4/en-GB/text/scalc/01/04060115.html?&DbPAR=CALC https://help.libreoffice.org/7.4/en-GB/text/scalc/01/04060116.html?&DbPAR=CALC
(In reply to Eike Rathke from comment #13) > Only almost complete as the DateFunc and Pricing Add-Ins are missing. Oh, it even has those in the following sections.. (In reply to Rafael Lima from comment #7) > I actually did not know RANDBETWEEN was an Add-in function, because in the > Help it is not listed in the corresponding Add-in Functions help page: Indeed, there it (and others) is/are missing.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8690d8878248320b0b706e9d6f7b8fa89ed903c4 Related: tdf#150203 Pricing, there is only one compatibility name, en-US It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8dc76b13458fac1a30787f8bbab117a4a9508ab5 Resolves: tdf#150203 Gather AddIn English names and provide for FunctionAccess It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/137713 for 7-4
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/7231a2362cce8f036586c744c775d9555086de5f Resolves: tdf#150203 Gather AddIn English names and provide for FunctionAccess It will be available in 7.4.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Eike Rathke from comment #14) > (In reply to Eike Rathke from comment #13) > > Only almost complete as the DateFunc and Pricing Add-Ins are missing. > Oh, it even has those in the following sections.. BTW: I didn't find the EDATE in the "add-in functions" list, even though it's listed in https://help.libreoffice.org/master/en-US/text/sbasic/shared/calc_functions.html. FTR: the patches here also fix this behavior: when using a localized UI, and enabling "Use English function names" in Calc formula settings, one needs to restart LibreOffice before English add-in functions become available to type in formula bar. Thanks!
Not having to restart LO for English AddIn names is rather fixed by the changes for bug 135993, not these ones here; they went in together hence you saw it fixed. If with "didn't find the EDATE in the "add-in functions" list" you meant it doesn't appear in the two pages https://help.libreoffice.org/master/en-US/text/scalc/01/04060115.html https://help.libreoffice.org/master/en-US/text/scalc/01/04060116.html that's because those list only functions of com.sun.star.sheet.addin.Analysis, but EDATE and other date functions are in com.sun.star.sheet.addin.DateFunctions and there's also the pricing OPT_... com.sun.star.sheet.addin.PricingFunctions that don't appear there. Probably worth a documentation bug.