How to propagate xlErrNull back to Excel formula?

  • Thread starter curiousgeorge408
  • Start date
C

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
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top