How to return an error value from a user defined function

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

How to return an error value from a user defined function

Post by eeigor »

LO Calc

The solution from @Lupp with a "supplement" to the formula:
Eg =SHEETNAME(MySubExpression)&IF(LEFT(CURRENT();1)=":";SQRT(-1);"")
Discussed here

A lot of time has passed, but the problem remains. Maybe there are other solutions... (?)

Let's remember how this is implemented in Excel.

Code: Select all

Function UDF()
    On Error GoTo Failed
    <...>

Failed:
    UDF = CVErr(xlErrValue)  'returns an err object (!)
End Function
First select any sheet cell. Then run the test and check the result using the functions below.
ISERROR(); ERRORTYPE(); ISNUMBER(); ISTEXT() -> in the following example, these functions return: TRUE; 519; FALSE; FALSE

Code: Select all

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
We initiated the error we needed, copied and pasted it as an error. Because there is no other way to force write the error to the cell, I do not know.
For example (see here)
=SQRT(-1) will give Err:502
=INDIRECT("AMK26") will give #REF! error (524)
=FOO() will give #NAME? error (525)
={1;{2}} will give Err:533
={1+2} will give Err:539
etc.
But I only need one error in the UDF - this is an error when returning the result: #VALUE! error (519).

Now the cell contains a real error. That's just a message window pops up (in principle, you can disable it)...
Is it possible to replace the dispatcher command ".uno:PasteOnlyValue" with another one without calling the dispatcher? The special paste hasn't been moved to UNO yet, has it? Is there an analog? All in connection with the task at hand.

UPD:
Description of CVErr(expression):
Converts a string expression or numeric expression to a variant expression of the sub type "Error". Nothing like that!

This does not happen: the error object is not returned.
oCell.Error displays the number of error, e.g. 519 (read only),
oCell.String/oCell.Formula contains its text, e.g. "#Value!",
but ISERROR() returns FALSE.
Attachments
Снимок экрана от 2021-06-15 11-57-58.png
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Post Reply