vba end

VBA End – Exit or Reset VBA macro

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.00 out of 5)
Loading...

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.

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:

You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type.

VBA Exit statement

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

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

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.

Exit Function

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

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:

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:

The result:

The result of running Exit Sub
The result of running End Sub

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…

The result:

Using the VBA End statement to exit macro execution
Using the VBA End statement to exit macro execution

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 Reset button
The VBA Reset button
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.

Related Posts