Wednesday, 2 January 2013

How to exit from a sub / How to stop executing a sub while running a macro


Everyone knows we can use the key combination CTRL + BREAK to stop running the execution of macros.

But while coding you will come across the following scenarios.

1. How to stop a macro in between a sub without executing further codes.

2. How to exit the execution upon one condition

3. How to stop running the current sub and return to the main sub


Solution : We have two key words 'Exit Sub' and 'End'

When to use this ?

Very simple ......

If we use 'END' in codes, all the executions will be stopped. No more codes will be processed.

But, The 'Exit Sub' can be used to stop running a sub and return to the place where it's called.

Eg :  Here we have 2 subs, The Main Sub calls the Process1 sub while running. In Process1 sub, we have a condition. If condition does not meet, it will return to the main sub and execute further codes. If I would have used 'END' instead of Exit Sub, the macro will stop running and no more codes will be processed.

Sub Main()
     Process1
     codes ....
     codes...
End Sub

Sub Process1()
If Activecell.value <> 1 Then Exit Sub
Codes .....
Codes.....
End Sub