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.
The VBA End statement
The VBA End statement ends the execution of a certain VBA scope.
The VBA End statement is used with any scope in VBA.
End [Function | Sub | With | Select | Type | Enum | If ]
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.
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 '... 'This code will not be executed '... End Sub
Similarly for Functions we need to use the Exit statement instead of End.
Function SomeFunction() as Integer '... 'Your code here SomeFunction = 1 Exit Function 'This code will not be executed SomeFunction = 2 End Function
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
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 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.