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