Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 19 Mar 2019 01:06:31 +0100How to call LO Calc functions from a LO Basic function (macro)?https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/From within the implementation of a LibreOffice function (i.e. macro), I would like to test whether the content of a cell (passed as a parameter) is a number or not.
Function myFunction(a,b,c)
If not ISNUMBER(a) Then
myFunction = "NaN"
Else
REM Calculations here
myFunction = result
End If
End Function
I've found the LibreOffice Calc function `ISNUMBER()` that does exactly what I want, but I don't know how to call it from the implementation of my LibreOffice Basic function… Upon execution, the implementation given above would show a message box with the error message `BASIC runtime error. Sub-procedure or function procedure not defined.`
I've also come across the LO Basic function `isNumeric()` but it does not have exactly the same behavior. In particular, when using a cell with `#DIV/0!` as argument, `isNumeric(#DIV/0!)`returns `TRUE` whereas `ISNUMBER(#DIV/0!)` returns `FALSE`. I want the latter behavior.
The behavior I want is:
- True if a number e.g. "3" or "3.1415"
- False if the content is a string e.g. "hello"
- False if it is a LO Calc error e.g. "Error: 510"
- False if mathematical errors e.g. "#N/A!" or "#DIV/0!" (that's the difficult part! See paragraph below…)
Eventually, regardless of my current problem (how to call the `ISNUMBER()` function), I would (also ;) ) love a general answer that could give a general procedure as to how to call LibreOffice Calc functions from the implementation of a LibreOffice Basic function.
Thanks in advance for your answers ;)
Mon, 18 Mar 2019 23:17:10 +0100https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/Answer by Lupp for <p>From within the implementation of a LibreOffice function (i.e. macro), I would like to test whether the content of a cell (passed as a parameter) is a number or not.</p>
<pre><code>Function myFunction(a,b,c)
If not ISNUMBER(a) Then
myFunction = "NaN"
Else
REM Calculations here
myFunction = result
End If
End Function
</code></pre>
<p>I've found the LibreOffice Calc function <code>ISNUMBER()</code> that does exactly what I want, but I don't know how to call it from the implementation of my LibreOffice Basic function… Upon execution, the implementation given above would show a message box with the error message <code>BASIC runtime error. Sub-procedure or function procedure not defined.</code></p>
<p>I've also come across the LO Basic function <code>isNumeric()</code> but it does not have exactly the same behavior. In particular, when using a cell with <code>#DIV/0!</code> as argument, <code>isNumeric(#DIV/0!)</code>returns <code>TRUE</code> whereas <code>ISNUMBER(#DIV/0!)</code> returns <code>FALSE</code>. I want the latter behavior.</p>
<p>The behavior I want is:</p>
<ul>
<li>True if a number e.g. "3" or "3.1415"</li>
<li>False if the content is a string e.g. "hello"</li>
<li>False if it is a LO Calc error e.g. "Error: 510"</li>
<li>False if mathematical errors e.g. "#N/A!" or "#DIV/0!" (that's the difficult part! See paragraph below…)</li>
</ul>
<p>Eventually, regardless of my current problem (how to call the <code>ISNUMBER()</code> function), I would (also ;) ) love a general answer that could give a general procedure as to how to call LibreOffice Calc functions from the implementation of a LibreOffice Basic function.</p>
<p>Thanks in advance for your answers ;)</p>
https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/?answer=187377#post-id-187377If you already have a cell object you can check for the properties `.Type` and `.FormulaResultType`.
If you passed a cell reference to the formula in Calc and your user function does not specify a type for the respective parameter, the parameter gives access to a Variant variable which you can can ask for its actual type with the help of the Basic function TypeName().
Any subroutine (even running for a non-Calc compoinent) can create a service for calling Calc functions.
To call a function you need to pass its name as a string and its arguments as an array.
Example:
Sub test()
fa = createUnoService("com.sun.star.sheet.FunctionAccess")
MsgBox(fa.callFunction("ISNUMBER", Array("NaN")))
MsgBox(fa.callFunction("ISNUMBER", Array(fa.callFunction("PI", Array())))
End Sub
If you want to learn more and to understand better, you should study the famous texts by Andrew Pitonyak. You find them **[here](http://www.pitonyak.org/oo.php)**.Tue, 19 Mar 2019 00:25:59 +0100https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/?answer=187377#post-id-187377Comment by GLorieul for <p>If you already have a cell object you can check for the properties <code>.Type</code> and <code>.FormulaResultType</code>. <br>
If you passed a cell reference to the formula in Calc and your user function does not specify a type for the respective parameter, the parameter gives access to a Variant variable which you can can ask for its actual type with the help of the Basic function TypeName(). </p>
<p>Any subroutine (even running for a non-Calc compoinent) can create a service for calling Calc functions. <br>
To call a function you need to pass its name as a string and its arguments as an array. <br>
Example: </p>
<pre><code>Sub test()
fa = createUnoService("com.sun.star.sheet.FunctionAccess")
MsgBox(fa.callFunction("ISNUMBER", Array("NaN")))
MsgBox(fa.callFunction("ISNUMBER", Array(fa.callFunction("PI", Array())))
End Sub
</code></pre>
<p>If you want to learn more and to understand better, you should study the famous texts by Andrew Pitonyak. You find them <strong><a href="http://www.pitonyak.org/oo.php">here</a></strong>.</p>
https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/?comment=187379#post-id-187379Many thanks! :)Tue, 19 Mar 2019 01:06:31 +0100https://ask.libreoffice.org/en/question/187373/how-to-call-lo-calc-functions-from-a-lo-basic-function-macro/?comment=187379#post-id-187379