How to return an error value from a user defined function

[EinsamerBaumimWald]

How can I return an error value from a user defined function for LO Calc with LO Basic?

I saw the following code

Function SheetName(Optional pNo As Long)

Dim oDoc As Object
Dim oSheet As Object
Dim nSheets As Long
If IsMissing(pNo) Then SheetName = ".Parameter.Missing." : GoTo EmergencyExit
On Error GoTo EmergencyExit:
oDoc = ThisComponent
nSheets = oDoc.Sheets.Count()
IF (pNo > 0) AND (pNo <= nSheets) THEN
   oSheet = oDoc.Sheets(pNo-1)
   SheetName = oSheet.GetName()
ELSE
   SheetName = ".No.Result."
EndIf
EmergencyExit:
End Function   'SheetName

here: [Solved] How to get list of sheet names into a sheet (View topic) • Apache OpenOffice Community Forum.

I would like to return a real error instead a text value.
How is this done?

Thank you!

[Ratslinger]

For a good explanation on error handling see section 3.10 (page 89) in Open Office Macros Explained by Andrew Pitonyak. For the PDF version click here.

If this answer resolves your question please tick the (upper left area of answer).

[Lupp]

@Ratslinger: Did you actually find a solution to the original question of this thread based on the mentioned text by Andrew Pitonyak? (I surely may have missed something as i never read the text as a whole.)

[Ratslinger]

@Lupp Unfortunately I was naive when posting the answer and have a better understanding of the question since your original posting here. Had really forgotten this until today. May do some more research myself on this. Will post if anything comes of it.

[Lupp]

The code quoted by the OQ was by me. When I wrote it I actually considered the current question, too, but did not find a way to have the function return an ‘Err:nnn’ error in the way standard functions do. The .FormulaResultType of a cell can only be 1 (number) or 2 (text). In case of standard functions/expressions returning an error the cells getting passed the final result show some relevant properties as follows:

.Type = 3 (meaning "formula")
.FormulaResultType = 1 (meaning "number")
.Value = 0
.Error = (code number of type Long)
.String = (the error message associated with the error's code number)

.Error is read-only and if .String gets an assignment, the property is set by a method afflicting everything else.
Thus: How? The problem is very similar to the one occurring with “additional number format routines” as I once tried. Even if I care for the function to know the cell from which it was called (This is not standard!) and try to “establish” a standard error by a side effect, this will be rejected (read-only .Error) or destroy the functionality of the formula (set .String).
I also could not find any service for the porpose. It seems to be outside of uno.

You may, of course, return an “impossible value” like -1, and display an error message like “Err:502” based on a specific ‘Numbers’ format, but this is just a fake and will not care for the wanted propagation of the error. Returning a text of specific syntax was what I judged to come next to a solution.

(Edit:) To allow for a workaround concerning your problem, please first replace the dots in all the error messages of the quoted code with colons. Since even a silly sheetname can not commence with a colon, you may then delegate the task of producing the applicable error 502 to a standard function using a slightly tricky formula for a workaround:
=SHEETNAME(MySubExpression)&IF(LEFT(CURRENT();1)=":";SQRT(-1);"")
The uinmimicked error 502, if raised by SQRT, will propagate as expected.

Please tell me if you find a way! My former questions concerning this problem in different forums did not come up with a result.

(Edit2 - after a long time:)
Regarding the recent comment by @hhaddow I try to explain the above described workaround by this attached demo.

[PYS]

Hi @Lupp - why not just return the text “#N/A” ?

[Lupp]

@PYS: Because it is not propagated as an error. Ask the cell showing that “error” =ISTEXT(You) and it will answer TRUE, while it will answer FALSE if asked =ISERROR(You). I suppose this behaviour to be the background of the OP’s question.
Of course you may replace the SQRT(-1) with NA() in the described workaround if preferred.

[hhaddow]

I tried this and still got a macro error, alternately instead of running =iferror(SHEETNAMEBYNUMBER(1)),"")
I just replaced the error results with = “” which worked for me, hopefully, it will work for others too.

[eeigor]

Posted the topic on another forum, maybe someone will be interested

Sub TestErrors
   oSheet = ThisComponent.CurrentController.ActiveSheet
   With ThisComponent.CurrentSelection
      On Local Error Resume Next
      .Formula = "=SIGN(""."")"  '=NA() | =FOO()
      If .Error <> 0 Then GoTo Failed
   End With

Failed: Call UnoCopyPasteOnlyValue
End Sub

'   Copies the selection and pastes only the value.
Sub UnoCopyPasteOnlyValue
   Dim document As Object, dispatcher As Object

   With ThisComponent.CurrentController
      document = .Frame
      If Not .Selection.SupportsService("com.sun.star.sheet.SheetCellRange") Then
         Exit Sub
      End If
   End With
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
   dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())
End Sub