3

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Cabu
  • 514
  • 2
  • 5
  • 15
  • You can create (and return) an error object using the [CVErr() function](http://cereusapis.com/iserror-cverr-openoffice-basic/) 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)](https://wiki.openoffice.org/wiki/Calc_Error_Codes) helps... :-( – tohuwawohu May 20 '16 at 14:13
  • @tohuwawohu: Yes, I tried returning 519 as well but it did not work. – Jim K May 20 '16 at 14:19

1 Answers1

2

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.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • That's the answer I found before coming here hoping for THE answer. That's really a pity that we need to hack something like that :( – Cabu May 20 '16 at 14:42