Exit Sub

Sometimes, it's useful to exit a subroutine early. For example, you can have some error checking at the start of a Sub. If a value not what you want, you can bail out. The way you do this is with the words Exit Sub . Here's an example:

Notice that the first line is:

Dim FormatCell As Variant

So we've changed the variable type from As Integer to As Variant . We've changed it because in the real world, the value from ActiveCell could be anything. The user may enter text, for example, instead of numbers. We're going to be testing for this.

The code that tests the value of the FormatCell variable is in an If Statement. This one (the message box should go on one line):

If Not IsNumeric(FormatCell) Then

MsgBox ("This needs to be a number - Exiting Now")

Exit Sub

End If

To test if something is a number you can use the inbuilt function IsNumeric . Between the round brackets of IsNumeric you type whatever it is you want to test. In this case, we're testing the FormatCell variable. Before the IsNumeric function we have Not . We could have done this instead, though:

If IsNumeric(FormatCell) = False Then

The result is the same: If the value in FormatCell is not a number then the code for the If Statement will get executed. The code that gets executed is this:

MsgBox ("This needs to be a number - Exiting Now")

Exit Sub

First, we display a message box for the user. The second line is Exit Sub . On meeting this line, VBA bails out of the subroutine. Any lines below that won't get executed.

You should always try to add as much error checking code as you can. Then use Exit Sub so that your macros don't crash.