Controlling the Flow of Statements

Visual Basic provides many ways for you to control the flow of statements in procedures. You can call other procedures, loop through a set of statements either a calculated number of times or based on a condition, or test values and conditionally execute sets of statements based on the result of the condition test. You can also go directly to a set of statements or exit a procedure at any time. The following sections demonstrate some (but not all) of the ways you can control flow in your procedures.

Call Statement

Use a Call statement to transfer control to a subroutine.

Syntax

 Call subroutinename [(<arguments>)]

or

 subroutinename [<arguments>]

where <arguments> is

 {[ByVal ByRef] <expression>},...

Notes

The Call keyword is optional, but if you omit it, you must also omit the parentheses surrounding the parameter list. If the subroutine accepts arguments, the names of the variables passed by the calling procedure can be different from the names of the variables as known by the subroutine. You can use the ByVal and ByRef keywords in a Call statement only when you’re making a call to a dynamic link library (DLL) procedure. Use ByVal for string arguments to indicate that you need to pass a pointer to the string rather than pass the string directly. Use ByRef for nonstring arguments to pass the value directly. If you use the ByVal keyword to declare an argument, Visual Basic passes a copy of the argument to the subroutine. The subroutine cannot change the original variable in the calling procedure. If you use the ByRef keyword, Visual Basic passes the actual memory address of the variable, allowing the procedure to change the variable’s value in the calling procedure. (If the argument passed by the calling procedure is an expression, Visual Basic treats it as if you had declared it by using ByVal.)

Examples

To call a subroutine named MySub and pass it an integer variable and an expression, enter the following:

 Call MySub (intMyInteger, curPrice * intQty)

An alternative syntax is

 MySub intMyInteger, curPrice * intQty

Do…Loop Statement

Use a Do…Loop statement to define a block of statements that you want executed multiple times. You can also define a condition that terminates the loop when the condition is false.

Syntax

 Do [{While | Until} <condition>]     [<procedure statements>]      [Exit Do]      [<procedure statements>]  Loop 

or

 Do      [<procedure statements>]      [Exit Do]      [<procedure statements>]  Loop [{While | Until} <condition>]

Notes

The <condition> is a comparison predicate or expression that Visual Basic can evaluate to True (nonzero) or False (zero or Null). The While clause is the opposite of the Until clause. If you specify a While clause, execution continues as long as the <condition> is true. If you specify an Until clause, execution of the loop stops when <condition> becomes true. If you place a While or an Until clause in the Do clause, the condition must be met for the statements in the loop to execute at all. If you place a While or an Until clause in the Loop clause, Visual Basic executes the statements within the loop before testing the condition.

You can place one or more Exit Do statements anywhere within the loop to exit the loop before reaching the Loop statement. Generally you’ll use the Exit Do statement as part of some other evaluation statement structure, such as an If…Then…Else statement.

Example

To read all the rows in the tblCompanies table until you reach the end of the recordset (the EOF property is true), enter the following:

 Dim dbContacts As DAO.Database  Dim rcdCompanies As DAO.RecordSet  Set dbContacts = CurrentDb  Set rcdCompanies = dbContacts.OpenRecordSet("tblCompanies") Do Until rcdCompanies.EOF      <procedure statements>      rcdClubs.MoveNext  Loop

For…Next Statement

Use a For…Next statement to execute a series of statements a specific number of times.

Syntax

 For counter = first To last [Step stepamount]     [<procedure statements>]      [Exit For]      [<procedure statements>]  Next [counter]

Notes

The counter must be a numeric variable that is not an array or a record element. Visual Basic initially sets the value of counter to first. If you do not specify a stepamount, the default stepamount value is +1. If the stepamount value is positive or 0, Visual Basic executes the loop as long as counter is less than or equal to last. If the stepamount value is negative, Visual Basic executes the loop as long as counter is greater than or equal to last. Visual Basic adds stepamount to counter when it encounters the corresponding Next statement. You can change the value of counter within the For loop, but this might make your procedure more difficult to test and debug. Changing the value of last within the loop does not affect execution of the loop. You can place one or more Exit For statements anywhere within the loop to exit the loop before reaching the Next statement. Generally you’ll use the Exit For statement as part of some other evaluation statement structure, such as an If…Then…Else statement.

You can nest one For loop inside another. When you do, you must choose a different counter name for each loop.

Example

To list in the Immediate window the names of the first five queries in the Conrad Systems Contacts database, enter the following in a function or sub:

 Dim dbContacts As DAO.Database  Dim intI As Integer  Set dbContacts = CurrentDb  For intI = 0 To 4      Debug.Print dbContacts.QueryDefs(intI).Name  Next intI

For Each…Next Statement

Use a For Each…Next statement to execute a series of statements for each item in a collection or an array.

Syntax

 For Each item In group      [<procedure statements>]      [Exit For]      [<procedure statements>]  Next [item]

Notes

The item must be a variable that represents an object in a collection or an element of an array. The group must be the name of a collection or an array. Visual Basic executes the loop as long as at least one item remains in the collection or the array. All the statements in the loop are executed for each item in the collection or the array. You can place one or more Exit For statements anywhere within the loop to exit the loop before reaching the Next statement. Generally you'll use the Exit For statement as part of some other evaluation statement structure, such as an If...Then...Else statement.

You can nest one For Each loop inside another. When you do, you must choose a different item name for each loop.

Example

To list in the Immediate window the names of all the queries in the Conrad Systems Contacts database, enter the following in a function or sub:

 Dim dbContacts As DAO.Database Dim qdf As DAO.QueryDef Set dbContacts = CurrentDb For Each qdf In dbContacts.QueryDefs     Debug.Print qdf.Name Next qdf

Caution 

If you execute code within the For Each loop that modifies the members of the group, then you might not process all the members. For example, if you attempt to close all open forms using the following code, you will skip some open forms because you are eliminating members from the group (the Forms collection) inside the loop.

 Dim frm As Form For Each frm In Forms   DoCmd.Close acForm, frm.Name Next frm

The correct way to close all open forms is as follows:

 Dim intI As Integer For intI = Forms.Count - 1, To (0 Step - 1,   DoCmd.Close acForm, Forms(intI).Name Next intI

GoTo Statement

Use a GoTo statement to jump unconditionally to another statement in your procedure.

Syntax

 GoTo {label | linenumber}

Notes

You can label a statement line by starting the line with a string of no more than 40 characters that starts with an alphabetic character and ends with a colon (:). A line label cannot be a Visual Basic or Access reserved word. You can also optionally number the statement lines in your procedure. Each line number must contain only numbers, must be different from all other line numbers in the procedure, must be the first nonblank characters in a line, and must contain 40 characters or less. To jump to a line number or a labeled line, use the GoTo statement and the appropriate label or linenumber.

Example

To jump to the statement line labeled SkipOver, enter the following:

 GoTo SkipOver

If…Then…Else Statement

Use an If…Then…Else statement to conditionally execute statements based on the evaluation of a condition.

Syntax

 If <condition1> Then       [<procedure statements 1>]  [ElseIf <condition2> Then      [<procedure statements 2>]]... [Else       [<procedure statements n>]]  End If 

or

 If <condition> Then <thenstmt> [Else <elsestmt>]

Notes

Each condition is a numeric or string expression that Visual Basic can evaluate to True (nonzero) or False (0 or Null). A condition can also consisit of multiple comparison expressions and Boolean operators. In addition, a condition can also be the special TypeOf…Is test to evaluate a control variable. The syntax for this test is

 TypeOf <Object> Is <ObjectType> 

where <Object> is the name of an object variable and <ObjectType> is the name of any valid object type recognized in Access. A common use of this syntax is to loop through all the controls in a form or report Controls collection and take some action if the control is of a specific type (for example, change the FontWeight property of all labels to bold). Valid control types are Attachment, BoundObjectFrame, CheckBox, ComboBox, CommandButton, CustomControl, Image, Label, Line, ListBox, ObjectFrame, OptionButton, OptionGroup, PageBreak, Rectangle, Subform, TabControl, TextBox, or ToggleButton.

If the condition is true, Visual Basic executes the statement or statements immediately following the Then keyword. If the condition is false, Visual Basic evaluates the next ElseIf condition or executes the statements following the Else keyword, whichever occurs next.

The alternative syntax does not need an End If statement, but you must enter the entire If…Then statement on a single line. Both <thenstmt> and <elsestmt> can be either a single Visual Basic statement or multiple statements separated by colons (:).

Example

To set an integer value depending on whether a string begins with a letter from A through F, from G through N, or from O, through Z, enter the following:

 Dim strMyString As String, strFirst As String, _    intVal As Integer  ' Grab the first letter and make it upper case  strFirst = UCase(Left(strMyString, 1))  If strFirst >= "A" And strFirst <= "F" Then      intVal = 1  ElseIf strFirst >= "G" And strFirst <= "N" Then      intVal = 2  ElseIf strFirst >= "O" And strFirst <= "Z" Then      intVal = 3  Else      intVal = 0  End If

RaiseEvent Statement

Use the RaiseEvent statement to signal a declared event in a class module.

Syntax

 RaiseEvent eventname [(<arguments>)]

where <arguments> is

 { <expression> },...

Notes

You must always declare an event in the class module that raises the event. You cannot use RaiseEvent to signal a built-in event (such as Current) of a form or report class module. If an event passes no arguments, you must not include an empty pair of parentheses when you code the RaiseEvent statement. An event can only be received by another module that has declared an object variable using WithEvents that has been set to the class module or object containing this class.

See the WeddingList.accdb sample database-described in Chapter 20, “Automating Your Application with Visual Basic”-for an example using RaiseEvent to synchronize two forms.

Example

To define an event named Signal that returns a text string and then to signal that event in a class module, enter the following:

 Option Explicit Public Event Signal(ByVal strMsg As String) Public Sub RaiseSignal(ByVal strText As String)     RaiseEvent Signal(strText) End Sub

Select Case Statement

Use a Select Case statement to execute statements conditionally based on the evaluation of an expression that is compared to a list or range of values.

Syntax

 Select Case <test expression>     [Case <comparison list 1>         [<procedure statements 1>]]     ...     [Case Else         [<procedure statements n>]] End Select 

where <test expression> is any numeric or string expression; where <comparison list> is

 {<comparison element>,...}

where <comparison element> is

 {expression | expression To expression |   Is <comparison operator> expression}

and where <comparison operator> is

 {= | <> | < | > | <= | >=}

Notes

If the <test expression> matches a <comparison element> in a Case clause, Visual Basic executes the statements that follow that clause. If the <comparison element> is a single expression, the <test expression> must equal the <comparison element> for the statements following that clause to execute. If the <comparison element> contains a To keyword, the first expression must be less than the second expression (either in numeric value if the expressions are numbers or in collating sequence if the expressions are strings) and the <test expression> must be between the first expression and the second expression. If the <comparison element> contains the Is keyword, the evaluation of <comparison operator> expression must be true.

If more than one Case clause matches the <test expression>, Visual Basic executes only the set of statements following the first Case clause that matches. You can include a block of statements following a Case Else clause that Visual Basic executes if none of the previous Case clauses matches the <test expression>. You can nest another Select Case statement within the statements following a Case clause.

Example

To assign an integer value to a variable, depending on whether a string begins with a letter from A through F, from G through N, or from O, through Z, enter the following:

 Dim strMyString As String, intVal As Integer  Select Case UCase$(Mid$(strMyString, 1, 1))      Case "A" To "F"          intVal = 1      Case "G" To "N"          intVal = 2      Case "O" To "Z"          intVal = 3      Case Else          intVal = 0  End Select

Stop Statement

Use a Stop statement to suspend execution of your procedure.

Syntax

 Stop 

Notes

A Stop statement has the same effect as setting a breakpoint on a statement. You can use the Visual Basic debugging tools, such as the Step Into and the Step Over buttons and the Debug window, to evaluate the status of your procedure after Visual Basic halts on a Stop statement. You should not use the Stop statement in a production application.

While…Wend Statement

Use a While…Wend statement to continuously execute a block of statements as long as a condition is true.

Syntax

 While <condition>      [<procedure statements>]  Wend 

Notes

A While…Wend statement is similar to a Do...Loop statement with a While clause, except that you can use an Exit Do statement to exit from a Do loop. Visual Basic provides no similar Exit clause for a While loop. The <condition> is an expression that Visual Basic can evaluate to True (nonzero) or False (0 or Null). Execution continues as long as the <condition> is true.

Example

To read all the rows in the tblCompanies table until you reach the end of the recordset, enter the following in a function or sub:

 Dim dbContacts As DAO.Database  Dim rcdCompanies As DAO.RecordSet  Set dbContacts = CurrentDb  Set rcdCompanies = dbContacts.OpenRecordSet("tblCompanies") While Not rcdCompanies.EOF      <procedure statements>      rcdCompanies.MoveNext  Wend

With…End Statement

Use a With statement to simplify references to complex objects in code. You can establish a base object using a With statement and then use a shorthand notation to refer to objects, collections, properties, or methods on that object until you terminate the With statement. When you plan to reference an object many times within a block of code, using With also improves execution speed.

Syntax

 With <object reference>      [<procedure statements>]  End With 

Example

To use shorthand notation on a recordset object to add a new row to a table, enter the following:

 Dim rcd As DAO.Recordset, db As DAO.Database Set db = CurrentDb  Set rcd = db.OpenRecordset("MyTable", _    dbOpenDynaset, dbAppendOnly)  With rcd      ' Start a new record      .Addnew      ' Set the field values      ![FieldOne] = "1"      ![FieldTwo] = "John"      ![FieldThree] = "Viescas"      .Update      .Close  End With

To write the same code without the With, you would have to say:

 Dim rcd As DAO.Recordset, db As DAO.Database Set db = CurrentDb  Set rcd = db.OpenRecordset("MyTable", _    dbOpenDynaset, dbAppendOnly)      ' Start a new record      rcd.Addnew      ' Set the field values      rcd![FieldOne] = "1"      rcd![FieldTwo] = "John"      rcd![FieldThree] = "Viescas"      rcd.Update      rcd.Close