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 Then Else
If statements can be single line or If block. The block can be either with or without an Else
|Alt-CIT | Menu: Code VBA » If » Then|
|Alt-CIN | Menu: Code VBA » If » End If|
|Alt-CIE | Menu: Code VBA » If » 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 to see the difference between the above three ways of inserting If in action.
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:
|Alt-CII | Menu: Code VBA » If » Is »|
Click this to see the use of built-in functions from the Code VBA menu
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 to see the use of a common condition fragment
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
|Alt-CSC | Menu: Code VBA » Select » Case|
|Alt-CSE | Menu: Code VBA » Select » Else|
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
The Do loops let you iterate until a certain condition is set. You can choose:
- when to test the stop condition: at the beginning or at the end of a looping
- either to use a stop
Untilor a continuation
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
EOF end-of-file condition is true
Do Until rst.EOF Loop
|Alt-CL | Menu: Code VBA » Do ... Loop »|
It is also possible to put the stop condition half-way as in the code below.
|Alt-CLX | Menu: Code VBA » Do ... Loop » Exit Do|
Do While True cell = ReadCell If cell.IsEmpty Then Exit Do Process cell Loop
For iterating collections the For statement is used.
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
For more information on For Each see https://msdn.microsoft.com/library/office/gg264596.aspx
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.
|Alt-CVAF | Menu: Code VBA » VVBA » Array » For Next|
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
Lets you jump out of / exit the current procedure, e.g.
Because this control statement is often used in the context of errors it is placed under the main menu entry Errors
|Alt-CLX | Menu: Code VBA » Error » Exit|