openoffice-calcopenoffice-basic

OpenOffice Calc function return #VALUE

I would like to write a Basic function for Calc that return #VALUE! testable with ISERR().

Function foo()
    foo = #VALUE!
End Function

But that foo function return 0 and not some error #VALUE!. How should I do it?

Solution

  • It looks like #VALUE! is only shown when there is a calculation error in the spreadsheet. So it is not possible to return such an error.

    Instead, cause a #VALUE! error by returning text when a number is expected:

    Function get_number() As Any
    'get_number = 0  'This line will not cause an error.
    get_number = ""  'This line will cause #VALUE! because it is not a number.
End Function

    Set the formula to =ISERR(GET_NUMBER() + 0).

    See https://forum.openoffice.org/en/forum/viewtopic.php?t=44830.