Handling error of returning function in VBA

Question

I'm struggling a little bit with handling the errors of a returning function in VBA.

I got some code in a sub which calls a function. The function returns 0 if it succeeds and -1 if there is an error.

This is my sub:

Sub mySub

    Dim returnValue as Integer

    returnValue = functionA(...)

    If returnValue = -1 Then
        MsgBox "The following error appeared: " & err.description & ", Errornumber= " & err.number
    Else
        MsgBox "Success"
    End If

End Sub

My function looks like this:

Function functionA(...) as Integer

    On error goto errorHandler
    ' do something

    funtionA = 0

    Exit Function

    errorHandler:
        functionA = -1 

End Function

Now my problem: if the function returns -1 because it was stopped by an error, I cannot get any information from the error object in my calling sub. The errornumber is 0 and the description is emtpy. It seems like the end of the function resets the err object. Is there a smart way of achieving what I want to do?

Thanks in advance! :)

have you attempted to set a global variable to = err.description, so you can use your variable in yoru msgbox? — Cyril
– Cyril, Commented Oct 1, 2019 at 18:14
Error handling is going to track back to the most recent error handling directive. When it encounters the error in the function it handles the error as described and resets the error handling. If you want to see the error details you either need to move your error handling into the sub, manually send back error details from the function, raise the error details IN the function using the same method you already are or like Cyril indicated, save them in a global variable. — Mike
– Mike, Commented Oct 1, 2019 at 18:17
There are two ways of handling errors: returning error codes and raising exceptions. Either is fine, as long as it used consistently in the project. You are taking the worst from both worlds: you are swallowing the exception and returning a meaningless error code. Please don't try to fix this hybrid, instead pick one of the two ways. I suggest you pick exception handling and simply let the exception bubble up to the caller. — GSerg
– GSerg, Commented Oct 1, 2019 at 18:29

Kirill Tkachenko · Accepted Answer · 2019-10-01 18:40:55Z

One approach would be to return the error number instead of -1 and then using it to get the error information

Sub mySub

    Dim returnValue as Integer

    returnValue = functionA(...)

    if returnValue <> 0 Then
        On Error Resume Next
        Err.Raise returnValue 'raise the same error that appeared in functionA to get its details
        MsgBox "The following error appeared: " & Err.Description & ", Errornumber= " & Err.Number
        On Error GoTo 0
    Else
        MsgBox "Success"
    End If

End Sub

Function functionA(...) as Integer

    On error goto errorHandler
    ' do something

    funtionA = 0

    Exit Function

    errorHandler:
        functionA = Err.Number

End Function

A shorter and nicer way is to do all the error handling in the calling procedure. For example:

Sub mySub()
    Dim returnValue as Integer 
    On Error Resume Next
    returnValue = functionA(...) 
    If Err.Number <> 0 Then 
        MsgBox "The following error appeared: " & err.description & ", Errornumber= " & err.number 
    Else 
        MsgBox "Success" 
    End If 
End Sub

The function then becomes

Function functionA(...) as Integer

    ' do something

End Function

freeflow · Accepted Answer · 2019-10-01 18:46:08Z

-1

A much better way of dealing with errors is by emulating the try/catch construct found in other languages. The VBA way of doing this is described in the RubberDuck article on 'Pattern: TryParse'

https://rubberduckvba.wordpress.com/2019/05/09/pattern-tryparse/

answered Oct 1, 2019 at 18:46

freeflow

4,3753 gold badges13 silver badges19 bronze badges

2 Comments

Sandra Rossi Over a year ago

As per Stack Overflow rules, please at least describe the general solution, don't just post a link to an external website.

freeflow Over a year ago

Glad to see you are keeping up to date with posts.

Collectives™ on Stack Overflow

Handling error of returning function in VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related