Integrating Excel and Access

Goto Statement

Generally, avoid the Goto statement when you can. However, there are times when a condition is met and you want to skip down to a line of code. I find the best way to do this is to use a line label, which you create by using a string of characters that begins with a letter and end with a colon. Here is an example that takes input from a user, notifying him and exiting the procedure if a number is entered out of range. Notice that you have to use Exit Sub prior to the line label in this example, or the user would get both messages. However, that is not necessarily the case in all situations. In this example, a simple If statement would work, but I hope that you can see where the Goto statement would be helpful in other situations. As a final note, the code goes to the line label called errln if there is an error or if the number entered is out of range.

Dim x As Integer
Dim inpstr As String
On Error GoTo errln
inpstr = InputBox("Enter a number between 1 and 10", "Input Required")
x = CLng(inpstr)
 
If x < 1 Or x > 10 Then GoTo errln
 
MsgBox "You entered " & x & " which is between 1 and 10", vbInformation
Exit Sub
errln:
  MsgBox "You entered a number out of range or entered text.", vbInformation