.jpg "Microsoft Certified Professional")
VIII - Decision Structures
One of the advantages of using Visual Basic for Applications (VBA) rather than macros is the ability to control the order in which actions are performed and allow the code to make decisions based on the conditions it finds as it runs. This is partially done through the event-driven programming structure of VBA. Within procedures, it's done through the use of decision structures.
IF ... THEN ... ELSE
The most basic structure is the IF ... THEN ... ELSE statement. This statement controls the actions of the code depending on one or more conditions. It's basic syntax is:
If <boolean condition> Then
<action>
ElseIf
<action>
Else
<action>
End If
The Else and ElseIf statements are optional but the IF ... THEN and END IF statements are required. Here's an example of IF ... THEN in action:
Public Function StrongPassword (Password as String) as Boolean
If Len(Password) > 7 Then
StrongPassword = True
Else
StrongPassword = False
End If
End Sub
The example above tests the length of the password entered, returns True if it's more than seven characters and False otherwise, thus making it an example of a boolean condition. Obviously, more than size matters when it comes to password strength but this is a start.
The IF .. THEN statement can also support multiple conditions:
If Len(Password) > 7 AND IsNumeric(Left(Password, 1)) Then
StrongPassword = True
Else
StrongPassword = False
End If
This is a variation on the password example where the length must be greater than 7 characters and the first character must be a number. In this case, if both conditions are true, the function will return True. Otherwise it will return false. Aside from the logical AND operator used above, you could also use one of the other logical operators such as OR or XOR to govern the decision made by an IF ... THEN statement.
If I wanted to add a third test for the password, I could write this:
If Len(Password) > 7 AND IsNumeric(Left(Password, 1)) AND NOT IsNumeric(Password) Then ...
Writing it this way is considered bad practice, though, because it's unwieldy (a.k.a. ugly) code and causes VBA to do unnecessary calculations. If the first condition fails, the other two still have to be tested. A better way to write this is to nest the IF ... THEN statements like so:
If Len(Password) > 7 Then
If IsNumeric(Left(Password, 1)) Then
If Not IsNumeric(Password) Then
StrongPassword = True
End If
End If
End If
The three conditions are now split into three decision statements and nested within each other. If the password fails the first test, the others don't even execute and StrongPassword is not set to True unless all three pass.
With nesting, it's still best not to get carried away with too many levels because it can be hard to read and support later but this does allow for more operations. It's also important not to get the levels of nesting tangled by accident. Close the most recently opened decision statement first.
As a final example, IF ... THEN can also test for multiple results from the same condition.
Public Function DayMessage(TestDate As Date) As String
If Weekday(TestDate) = vbSunday Then
DayMessage = "It's a brand new week!"
ElseIf Weekday(TestDate) = vbMonday Then
DayMessage = "Back to work ..."
ElseIf Weekday(TestDate) = vbFriday Then
DayMessage = "T.G.I.F!!!"
Else
DayMessage = "Have a great day!"
End If
End Function
In this example, the function accepts a date value that's passed in and uses the VBA Weekday function to determine the day of the week that it represents and provides the appropriate message. The ElseIf statements test the date against the VBA weekday constants one at a time until a match is found. If no match is found, the final Else statement acts as a catchall.
You can find more information on VBA functions like Weekday, Len and others used in these examples online in the Access help system.
Optimizing Code
When you're writing code, you can often find ways to make the code more efficient, just as you might find better ways of saying something as you're writing a letter or e-mail. In programming, this is referred to as "optimizing" the code. After writing this example, I remembered there was a better way to write it:
Public Function DayMessage(TestDate As Date) As String
Dim DayValue As Integer
DayValue = Weekday(TestDate)
If DayValue = vbSunday Then
DayMessage = "It's a brand new week!"
ElseIf DayValue = vbMonday Then
DayMessage = "Back to work ..."
ElseIf DayValue = vbFriday Then
DayMessage = "T.G.I.F!!!"
Else
DayMessage = "Have a great day!"
End If
End Function
In this variation, notice that the WeekDay function is only called once and the result is assigned to the Integer variable DayValue. Instead of running the Weekday function over and over again, the DayValue variable is tested against the weekday constants which, although they are named, correspond to the numbers 1 through 7. In this case, the change won't make any noticeable difference but if the Weekday function was a large function that took even a couple seconds to run or accessed data tables, the time difference could be very noticeable, especially if the function or method calling it ran repeatedly.
Another advantage would be if the result of the function being called could change from one moment to the next. In this case, you might want to capture the result at a given moment in time and then refer to that result instead of calling the function again. This is where programming goes beyond knowing the commands and syntax and involves design decisions and an understanding of how the system works. I'll talk about this more in the chapter on algorithms.
SELECT CASE
While the above example for the IF ... THEN statement is acceptable and works perfectly well, the ElseIf statement isn't always appropriate for testing all the possible values of a given condition. If there are more than three possible values to test against or you need to carry out the same action or a range of values, a SELECT CASE statement might be the better choice. Here's the basic syntax:
Select Case <statement>
Case <value>
<action>
Case <value>
<action>
..
Case Else
<action>
End Select
Here's the IF ... THEN example rewritten in Case statements and expanded with more options.
Public Function DayMessage(TestDate As Date) As String
Select Case Weekday(TestDate)
Case vbSunday
DayMessage = "It's a brand new week!"
Case vbMonday
DayMessage = "Wake me when Monday's over."
Case vbWednesday
DayMessage = "Halfway there!"
Case vbFriday
DayMessage = "T.G.I.F.!!!!"
Case vbSaturday
DayMessage = "Sleep in today. It's Saturday!!"
Case Else
DayMessage = "Have a great day!"
End Select
End Function
Notice how much cleaner the code looks. It's easier to read and understand. Another advantage is that it evaluates the expression once at the top of the SELECT CASE statement rather than multiple times. This eliminates extra processing and the need for the proxy variable that I declared in the IF...THEN statement. There is still a catchall statement at the end where Case Else handles any result that doesn't match any of the other statements. It's best to get in the habit of using an Else statement since it's possible to leave conditions unhandled without one and this can lead to errors, the cause of which isn't immediately obvious.
As with the IF...THEN statement, only the first matching Case statement is used and the code then continues after the End Select statement. You can put any number of lines for execution after each Case statement. You could even have Case statements with no code to execute if you wanted to take no action for a particular result.
The Case statements also have flexibility in specifying groups or ranges of values. The following statements are all valid assuming that any variables used have been declared.
Case 100 to 199
Case 5, 10, 15, 20, 25 To 30
Case Is < 21, Is > 45
Case "Red", "Yellow", "Blue"
Case #1/1/2010# To #1/31/2010#
Note the use of the Is keyword with the greater/less than operators and the # around the date values. The # is a type declaration character which signals to the code that the value should be treated as a date rather than a division formula. It's used just as the quotation mark is used for strings.
