goto block not working VBA

Question

Summary: I want to do some basic error-handling

Problem: When I step through the code my "Error" block data gets run even when there isn't an error

-I'm pretty new to error handling in VBA and don't understand why the code in the Error block is run aside from me directing the code to enter the block. Thanks in Advance!

Code:

Function getReports()

    startJournal = Sheets("Runsheet").Range("B5")
    endJournal = Sheets("Runsheet").Range("E5")

    If startJournal = 0 Or endJournal = 0 Then

        GoTo Error

    End If

    'bunch of code

Error:
    MsgBox ("Error Statement")

End Function

[This post](http://stackoverflow.com/questions/6028288/properly-error-handling-in-vba-excel/6039718#6039718) on error handling may be helpful. — ray, Sep 22 '12 at 02:38

shahkalpesh · Accepted Answer · 2012-09-21T19:30:33.387

7

You need Exit Function before the error label.
i.e. the code should hit the label (eh) only in case of error & exit otherwise.

Function getReports() 
on error goto eh
    startJournal = Sheets("Runsheet").Range("B5")
    endJournal = Sheets("Runsheet").Range("E5")

    If startJournal = 0 Or endJournal = 0 Then

        GoTo Error

    End If

    'bunch of code

Exit Function

eh:
    MsgBox ("Error Statement")

End Function

Looking at your code, you could write it as

Function getReports(startJournal as integer, endJournal as integer) as Boolean
    If startJournal = 0 Or endJournal = 0 Then
        msgbox "startJoural or endJournal should not be 0."
        exit function  '** exiting will return default value False to the caller
    End If

    'bunch of code
getReports = True
End Function

On the caller's side

if getReports(Sheets("Runsheet").Range("B5"), Sheets("Runsheet").Range("E5")) then
   call faxTheReport   '** This function will be called only if getReports returns true.
end if

edited Sep 21 '12 at 19:30

answered Sep 21 '12 at 19:17

shahkalpesh

33,172
3
63
88

is this how most people deal with this problem? – Mike Kellogg Sep 21 '12 at 19:18
this is how it is suppose to be done http://msdn.microsoft.com/en-us/library/t2at9t47(v=vs.80).aspx +1 @shahkalpesh – Adriaan Stander Sep 21 '12 at 19:19
2

Indeed it is, although I think the code should be `on error goto eh` and not `on error goto eh:` – LittleBobbyTables - Au Revoir Sep 21 '12 at 19:19
@MikeKellogg: It's the only way, in VBA. – HardCode Sep 21 '12 at 19:20
@LittleBobbyTables The problem is it technically isn't error handling. Because an error is actually being avoided by the if statement so i can't use on error goto – Mike Kellogg Sep 21 '12 at 19:20
In general yes. Although one can check for specific errors in error handlers and show user friendly message or do a resume/resume next. – shahkalpesh Sep 21 '12 at 19:20
Thanks all for the quick response! So quick I can't accept the answer yet :D – Mike Kellogg Sep 21 '12 at 19:21
1

@MikeKellogg - what you're doing isn't really error handling either; technically you're doing branching. In my mind, error handling invokes the `Err` object. – LittleBobbyTables - Au Revoir Sep 21 '12 at 19:23
1

@LittleBobbyTables `The problem is it technically isn't error handling` – Mike Kellogg Sep 21 '12 at 19:35
@Mike: This is more of validation than error (thrown by runtime). Thanks for accepting. – shahkalpesh Sep 21 '12 at 19:36

score 1 · Answer 2 · answered Sep 21 '12 at 19:38

Here is how I generally deal with errors in my VBA code. This was taken from code in a class that automates an instance of Internet Explorer (the IE variable). The Log is used to inform the user of what's going on. The variable DebugUser is a boolean which I set to true when I'm the one running the code.

Public Sub MyWorkSub()

    On Error GoTo e

    Nav "http://www.somesite.com"

    DoSomeSpecialWork

    Exit Sub
e:
    If Err.Number = -2147012894 Then
        'timeout error
        Err.Clear
        Log.Add "Timed Out... Retrying"
        MyWorkSub
        Exit Sub
    ElseIf Err.Number = -2147023170 Or Err.Number = 462 Or Err.Number = 442 Then
        RecoverIE
        Log.Add "Recovered from Internet Explorer Crash."
        Resume
    ElseIf Err.Number = 91 Then
        'Page needs reloading
        Nav "http://www.somesite.com"
        Resume 'now with this error fixed, try command again
    End If

    If DebugUser Then
        Stop 'causes break so I can debug
        Resume 'go right to the error
    End If

    Err.Raise Err.Number

End Sub

goto block not working VBA

2 Answers2

Linked