Access Database Design and Programming, Second Edition

Exiting a Procedure

VBA provides the Exit Sub and Exit Function statements, should we wish to exit from a procedure before the procedure would terminate naturally. For instance, if the value of a parameter is not suitable, we may want to issue a warning to the user and exit, as Example 11.5 shows.

Example 11-5. Using the Exit Sub Statement

Sub DisplayName(sName As String)
   If sName = "" then
      Msgbox "Please enter a name."
      Exit Sub
   End If 
   MsgBox "Name entered is " & sName
End Sub

While we are on the subject of exiting, we should comment on the use of the End statement, which will terminate a procedure. Simply put, you should almost never use the End statement in VBA programming, since it produces a rather abrupt termination of a program. (I never like to say never.) Here is a partial list of what happens when the End statement is executed:

• Code execution stops abruptly, without invoking the Unload, QueryUnload, or Terminate event of any forms in the application, which means that forms are not given the opportunity to prevent the program from terminating or from performing any necessary cleanup.

• All module-level variables and all static local variables are reset. (Nonstatic local variables go out of scope, as expected.) Objects created from class modules are destroyed.

• Files opened using the Open statement are closed.

While there may be some rather specialized situations in which this behavior is desirable, you will no doubt recognize such a situation if and when it arises. In the meantime, ...