Adding Control Structures

In the following sections the different VB control statements for conditional branching and loops are given a short introduction, followed by by examples of use and how you can use Code VB to speed up your code writing in this area.

If

If Then Else

If statements can be single line or If block. The block can be either with or without an Else

The code below is the result of the third - Alt-CIE:

If IsNumeric(strNumber) Then
    MsgBox Prompt:=strNumber & " Yen = " & CSng(strNumber) * 0.0096 & "USD"
Else
    GoTo AskNumber
End If

Click this start demo showing three ways to insert if block to see the difference between the above three ways of inserting If in action.code vb If demo

Is - common condition tests

When the If statement is inserted, the cursor is as the position where you enter the condition. You can write your own test, or select one of the built-in tests available under Is:

Click this start If insertion demo to see the use of built-in functions from the Code VB menu inserting a built-in test function

Special If fragments

Some condition use cases are so common that we added fragments so that you can insert them in one go - for example:

Dim strNumber As String
    If Len(strNumber) = 0 Then
        
    End If

Click this start if fragment demo to see the use of a common condition fragment inserting a commonly used if with test

Select

Another way to have your code handle different conditions is using the Select Case statement. Use this if your processing depends on the different possible values of a variable

The example below shows you can put multiple values after each other, separated by a comma, to have them share the same Case code.

Using the Alt-CSE adds an Case Else line at the end of the Select block, which means 'for all other cases do ...'

Dim msg As VbMsgBoxResult
msg = MsgBox("You don't have access - Retry?", vbYesNoCancel)
Select Case msg
Case vbYes
    GoTo TryAccessNetwork
Case vbNo, vbCancel
    Exit Sub
End Select

Do While

The Do loops let you iterate until a certain condition is set. You can choose:

  1. when to test the stop condition: at the beginning or at the end of a looping
  2. either to use a stop Until or a continuation While condition

Concerning 2. you will choose what comes most natural. Best is to avoid using a Not in the condition. As an example, the code below - read records from the recordset Until the EOF end-of-file condition is true

Do Until rst.EOF

Loop

Exit Do

It is also possible to put the stop condition half-way as in the code below.

Do While True
    cell = ReadCell
    If cell.IsEmpty Then Exit Do
    Process cell
Loop

For

For iterating collections the For statement is used.

For Each Next

The For Each Next block is used with collections. These collections may be either VB Collection type, or collections in the type library of your Office application.

For more information on For Each see http://msdn.microsoft.com/EN-US/library/office/gg264596.aspx

For i= ... To

Another iterating approach uses numeric values as the collection to work with. This collection is defined by the lower and upper bound and optionally a 'step' value.

Iterating an array

A common use for this For loop is to process the elements in an array. The below menu selection automatically inserts this code.

Dim iArrayForNext As Long
For iArrayForNext = LBound(arr) To UBound(arr)
    Debug.Print arr(iArrayForNext)
Next

Removing selected items from listbox - a use of Step - 1

Removing selected items from a listbox in Access starting with the first would cause the other items to loose their selection state. For that reason start with the highest index item.

For intIndex = ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(intIndex) Then
    ListBox1.RemoveItem intIndex
End If

For more information on For Next see http://msdn.microsoft.com/en-us/library/office/gg251601.aspx

Exit Procedure

Lets you jump out of / exit the current procedure, e.g. Exit Property

Because this control statement is often used in the context of errors it is placed under the main menu entry Errors