Often we think about beginnings not endings. It is only when we get somewhere it is when we realize we hadn’t thought through our destination. Same goes for writing VBA macros. Fortunately the VBA End statement is here to save the day and help us put that full-stop in the right place. Today I want to share a cool VBA tip for properly ending your VBA macros i.e. how to abort a macro.

VBA End vs VBA Exit

The VBA End statement ends the execution of a certain VBA scope.

VBA End statement

The VBA End Statement is used to mark the end of a specific VBA scope:

'End a Function definition, Sub definition, With statement, Select statement, Type definition, Enum defition, If statement
End [Function | Sub | With | Select | Type | Enum | If ]

You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type. The End statement cannot be used within loop, function or procedure to end its execution prematurely. For that we need to use the VBA Exit statement instead.

So to summarize – End marks the end of a specific VBA scope similarly like { } brackets in C# or tabs in Python.

VBA Exit statement

The VBA Exit Statement is used to exit a particular scope earlier than defined by the VBA End Statement.

'Exit earlier a Do-While/Until loop, For loop, Function/Sub, Select statement 
Exit [ Do | For | Function | Select | Sub ]  

In should be used to end the execution of a loop, function or procedure earlier.

See example below to understand the difference between VBA End and VBA Exit:

Sub SomeProcedure
   '...
   If iWantToExit Then 
     Exit Sub 'if the statement is True this will stop any further execution of this Sub
   End if
   '...
End Sub  'This ends the statement of the Sub procedure

Exiting VBA Functions or Procedures

Sometimes however we want to end the execution of a function or procedure early, simply by leaving the current execution scope.

Exit Sub

Ending Sub procedures is very useful. Instead of using the End statement we need to use the VBA Exit statement.

Sub SomeSub()
    '...
    'Your code here
    '...
    Exit Sub 'Exit the Sub without executing code below
    '...
    'This code will not be executed
    '...
End Sub

Exit Function

Similarly for Functions we need to use the Exit statement instead of End.

Function SomeFunction() as Integer
    '...
    'Your code here
    SomeFunction = 1
    Exit Function 'Exit the Function without executing code below
    '...
    'This code will not be executed
    SomeFunction = 2
End Function

The result of the VBA Function above is 1 not 2.

Exit VBA Loop

You can also use Exit to exit a VBA Loop statement:

For i = 0 to 10
   '...
   If wantToexit = True Then 
      Exit For 'Will exit For loop without executing code below before Next i
   End if
   '...
Next i

The result of the VBA Function above is 1 not 2.

End VBA macro / Reset VBA macro

Now here is the trick I want to sell you today. The Exit statement above will allow you to leave the scope of your current VBA Function or VBA Sub. It will not however end the execution of your entire macro. A simple example below:

Sub Main()
    Call SomeSub 
    'Code will execute
    Debug.Print "Main: Hello after Exit Sub!"
End Sub

Sub SomeSub()
    Exit Sub
    'Code will not execute
    Debug.Print "SomeSub: Hello after Exit Sub!"
End Sub

The result:

So you see Exit Sub exiting only the the current scope of the running VBA Sub procedure and not the entire macro!

End – as simple as that

How to exit the execution of the entire macro (the Main Sub)? A simple End will do…

Sub Main()
    Call SomeSub 
    'Code will not execute
    Debug.Print "Main: Hello after Exit Sub!"
End Sub

Sub SomeSub()
    End
    'Code will not execute
    Debug.Print "SomeSub: Hello after Exit Sub!"
End Sub

The result:

The End statement ends the execution of the ENTIRE MACRO.

Use End carefully!

As Andy Pope has rightfully corrected me, the End statement needs to be used with care. As there are consequences…

The VBA End statement works a lot like the VBA Reset button. This is what happens:

• Object events will not be invoked e.g. the Unload, QueryUnload, or Terminate events
• Any running Visual Basic code will be stopped – that means Timers and other running code
• Object references held by other programs are invalidated – if any application is holding a reference to a VBA object it will be invalidated and inaccessible
• Closes any open dialogs / forms (e.g. UserForm or MsgBox)

Using End is a lot like pulling a hand brake in a car. It stops the car, but often so abruptly it might cause it to crash.