3
votes

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?

1
You can create (and return) an error object using the CVErr() function in StarBasic. But sadly, neither ISERR() nor ISERROR() detect the result as error, both returning FALSE instead. Even using the error code 519 for #VALUE! (with CVErr(519) helps... :-( - tohuwawohu
@tohuwawohu: Yes, I tried returning 519 as well but it did not work. - Jim K

1 Answers

2
votes

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.