CODEWITHSUNDEEP
excelvba
Walnussbär
Walnussbär

Reputation: 796

Handling error of returning function in VBA

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! :)

Upvotes: 2

Views: 1966

Answers (2)

freeflow
freeflow

Reputation: 4355

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/

Upvotes: -1

Kirill Tkachenko
Kirill Tkachenko

Reputation: 426

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

Upvotes: 1

Related Questions