Ask Your Question

How to return an error value from a user defined function

asked 2016-09-18 00:26:56 +0200

EinsamerBaumimWald gravatar image

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()
   SheetName = ".No.Result."
End Function   'SheetName


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

Thank you!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-09-18 01:15:42 +0200

Ratslinger gravatar image

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

edit flag offensive delete link more


@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.)

Lupp gravatar imageLupp ( 2018-03-04 15:01:34 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2018-03-04 17:44:31 +0200 )edit

answered 2016-09-18 13:07:25 +0200

Lupp gravatar image

updated 2018-03-04 13:08:09 +0200

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:
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.

edit flag offensive delete link more


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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-18 14:54:44 +0200 )edit

@pierre-yves samyn: 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.

Lupp gravatar imageLupp ( 2016-09-18 15:03:57 +0200 )edit

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.

hhaddow gravatar imagehhaddow ( 2018-03-04 11:57:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-18 00:26:56 +0200

Seen: 868 times

Last updated: Mar 04 '18