How to call LO Calc functions from a LO Basic function (macro)?

[GLorieul]

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

[Lupp]

If 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.

[GLorieul]

Many thanks!