View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] curiousgeorge408@hotmail.com is offline
external usenet poster
 
Posts: 85
Default 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