databasedev.co.uk - database solutions and downloads for microsoft access

VBA Control Structures

Control Structures in VBA

As with any programming language VBA has over four control structures. Without these structures a programming language is basically useless. Because it wont be able to make intelligent decisions on its own. In this article we discuss some of the control structures that are available in VBA.

If…then…else construct

The If...Then...Else construct evaluates whether a condition is true. It checks whether what you say is true or if it is not. This makes any program intelligent in the sense that it decides on its own if a there is a condition and if it is valid. Any code between if and else execute when the condition evaluates to True and any code between Else and End If execute if the statement evaluates to False. The construct has the following syntax:

If conditionistrue Then
runthiscode
Else
runthiscode
End If

Let’s take a look at some example code. In our example we check if the user filled in their age. The form contains two text fields called agetxt and yeartxt and a button. On the buttons onclick event, add the following code:

'Check if the txtbboxes are filled in
If IsNull(agetxt) Then
MsgBox "Please enter a age"
Else
MsgBox "You entered" & agetxt & " as your age"
End If

We can also use multiple if statements to check if the user entered a age or a year and let the code calculate the age on its own and show us:

Dim yr, age As Integer
'determine which has been filled in
If agetxt > 0 Then
age = agetxt
MsgBox "Your age is " & age
ElseIf yeartxt > 0 Then
yr = 2009 - yeartxt
MsgBox "Your age is " & yr
ElseIf IsNull(agetxt) And IsNull(yeartxt) Then
MsgBox "Please enter either your year of birth or your age"
End If

The first if construct checks if the age has been given, if so, it is displayed. The second construct checks if the year has been given, if so, it subtracts the given year from the current year to determine your age and displays it. In some cases you get multiple if a statement that is so many that it just does not make sense when you try to read it. It is recommended for clarities sake, that the select… case construct be used if the if..then..else construct becomes more then five.

If statements can also be one liners:

If agetxt = 20 then Msgbox “You are very young”

The Immediate If

An Immediate If (IIf) is a variation of an If statement. It is a built-in function that returns one of two values, depending on whether the condition you are testing for is true or false. Below is an example of how it works:

Function checkage(age As integer)
   checkage = IIf(age =20,"You are young", "Getting on a bit, aint ya")
End Function

This function evaluates the age parameter to see whether its value is equal to twenty. If the value is equal to twenty, the code returns the string "You are young" from the function; otherwise, the code returns the string "Getting on a bit, aint ya"

The Select…case

The select..case construct makes reading multiple conditions more clearer than the using multiple if statements:

Select Case age
Dim age As Integer
    age = agetxt.value  
      Case 0
        MsgBox "You Must Enter a age greater than zero"
      Case 10 to 18
        MsgBox "You Are Just a teen"
      Case 19, 20, 21
        MsgBox "You are young"
      Case 22 to 40
        MsgBox "almost there"
      Case Is > 40
        MsgBox "Hmmm…”
      Case Else
        MsgBox "You enter a age"
End Select 

The above construct is easier to read then say:

If age = 0 then 
        MsgBox "You Must Enter a age greater than zero"
Elseif age = 10  OR 18 Then
        MsgBox "You Are Just a teen"
 Elseif age = 19 OR 20 OR 21 Then 
        MsgBox "You are young"
<< snip >>
        MsgBox "You enter a age"

Other control structures include For..Next has the following syntax:

For Counter = 1 To 10
newVal = newval + 1
Next Counter

The example above shows that the newval value gets incremented with each iteration. There are of course more control structures available, but we will not be focusing on them. The control structures covered here will be adequate for most programming needs.

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com