How to propagate xlErrNull back to Excel formula?
 

The VBA code below conceptionally does what I want, namely: propagate
an error raised in a "lower" subroutine back the Excel formula that
called the function. But it does not work as intended unless I remove
the "as String" declaration for the function (i.e. make the function
type Variant). Not surprisingly, the function fails with #VALUE, not
#NULL.

Is there some way to propagate the error back from a non-variant
function, whatever it might be?

Conceptual VBA code ....

Function testit(arg) As String ' invoked by =testit(x), where x is
0 or 1
On Error GoTo goterror
Call testit2(arg)
MsgBox "testit okay"
testit = arg
Exit Function

goterror:
MsgBox "testit error " & Err.Number
testit = CVErr(Err.Number)
End Function


Sub testit2(arg)
Call testit3(arg)
MsgBox "testit2"
End Sub


Sub testit3(arg)
If arg Then Err.Raise xlErrNull
MsgBox "testit3"
End Sub

How to propagate xlErrNull back to Excel formula?
 

Function testit must be declared As Variant for it to be able to return
error values.

Function testit(arg) As Variant

In general, I usually end up declaring UDFs (User-Defined Functions) as
Variant anyway, just to allow for returning error values, if needed.

--
Regards,
Bill Renaud

How to propagate xlErrNull back to Excel formula?
 

On Dec 1, 8:25 pm, "Bill Renaud"
wrote:
Function testit must be declared As Variant for it to be able to return
error values.

I suspected that is the case. Thanks for the confirmation.