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 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 VB » If » Then|
|Alt-CIN | Menu: Code VB » If » End If|
|Alt-CIE | Menu: Code VB » 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 VB » If » Is »|
Click this to see the use of built-in functions from the Code VB 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 VB » Select » Case|
|Alt-CSE | Menu: Code VB » 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 VB » Do ... Loop »|
It is also possible to put the stop condition half-way as in the code below.
|Alt-CLX | Menu: Code VB » 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 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
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 VB » VVB » 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 http://msdn.microsoft.com/en-us/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 VB » Error » Exit|