How to propagate xlErrNull back to Excel formula?

[curiousgeorge408]

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

 

[Bill Renaud]

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.

 

[curiousgeorge408]

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.