How do you return an error object from a function in VBA?

Question

I have a function where the intention is to return either a string to signify that the function has completed and retrieved the required value or an error to indicate there has been an issue.

To see whether I could do this, I bashed together the following:

Private Function one()

    Debug.Print TypeName(two(False))

End Function

Private Function two(b As Boolean) As Variant
    Dim e As New ErrObject

    If (b) Then
        two = True
    Else
        two = e.number
    End If

End Function

Now, this fails at two = e.number because you don't appear to be able to set an error number in this way - the syntax is incorrect.

I could use err.raise but then the intention is to pass the whole error object back as I can then have a custom number and fill in my own description etc...

How do you actually pass the err object back? Can you actually do it and is this the most effective way in achieving what I am setting out to do?

Thanks

I think all you can do in VBA is Raise an error - you cannot directly create an Err object cpearson.com/Excel/ErrorHandling.htm. Your ErrObject seems to be VB.Net — Tim Williams
– Tim Williams, Commented Oct 13, 2017 at 21:19
@TimWilliams no, ErrObject is absolutely VBA: Err is a function, a member of the VBA.Information module, that returns an ErrObject. An instance of the ErrObject class cannot be created with the New keyword. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 13, 2017 at 21:22

Mathieu Guindon · Accepted Answer · 2017-10-13 21:34:38Z

You don't. Err is a globally-scoped Function from the standard library VBA.Information module, that returns an ErrObject instance.

the intention is to pass the whole error object back as I can then have a custom number and fill in my own description

You don't need to pass the whole error object back to throw custom errors.

Const ERR_SomethingBad = vbObjectError + 42
Err.Raise ERR_SomethingBad, "MyModule.MyProcedure", "Uh-oh"

Err is already in global scope - you don't need to pass error objects around... and in fact, you simply can't. Because the ErrObject class isn't creatable. This:

Dim e As ErrObject
Set e = New ErrObject

Throws run-time error 429 "ActiveX can't create object".

The very idea behind errors (and exceptions, if you're any familiar with languages that feature exceptions), is that they replace the old archaic "return status code" way, e.g.:

''' DON'T DO THIS, IT's 2017!!!
Public Function DoSomething(ByVal p1 As Double, ByRef outResult As Double) As Long
    If p1 = 0 Then
        DoSomething = 11 'return code for division by zero error
        Exit Function
    End If
    outResult = 42 / p1
    DoSomething = 0 'return code for OK - everything went well!
End Function

With this pattern every procedure is a Function that returns an error code; the actual return value is passed as a ByRef / out parameter, and the caller would have to do this before they can trust the result:

Dim result As Double
Dim e As Long
e = DoSomething(42, result)
If e = 0 Then
    ' happy path
Else
    MsgBox Error$(e) ' error path
End If

The On Error statement streamlines the code by taking most error-handling concerns out of the "happy path".

Collectives™ on Stack Overflow

How do you return an error object from a function in VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related