Adding Control Structures

Control structures determine the flow of you program. The basic elements are conditional statements - If, Select, Switch and iterations (looping) - For, While, Until. Code VBA gives support for inserting such statements in two ways: 1. using the Code VBA menu and 2. using the Code Builders dockable window.

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

If

If Then Else

if menu

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 vba 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 VBA 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 VBA Collection type, or collections in the type library of your Office application. Below is the sample code too loop over the worksheets in the active workbook using Excel.

Dim wb As Workbook: Set wb = Application.ActiveWorkbook
Dim ws As Worksheet
For Each ws In wb.Sheets
    
Next ws

Click this start demo create for each code in Excel VBA to see create For Each code using the Excel VBA add-on.code vba If demo

For more information on For Each see https://msdn.microsoft.com/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 https://msdn.microsoft.com/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