Ask Your Question
0

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

asked 2019-03-18 23:17:10 +0200

GLorieul gravatar image

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 ;)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-03-19 00:25:59 +0200

Lupp gravatar image

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.

edit flag offensive delete link more

Comments

Many thanks! :)

GLorieul gravatar imageGLorieul ( 2019-03-19 01:06:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-18 23:17:10 +0200

Seen: 301 times

Last updated: Mar 19