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.
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
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”
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 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
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.