Feb. 14, 2011, 4:32 p.m.
posted by dante
Functions That Make Decisions
Much of what we're talking about in this chapter involves ways to make your procedures cleaner and more efficient. These are laudable goals for a whole host of reasons, but the following are the main ones:
This section looks at three powerful VBA functions that can increase the efficiency of your procedures.
The IIf Function
You've seen how the decision-making prowess of the If...Then...Else structure lets you create "intelligent" procedures that can respond appropriately to different situations. However, sometimes If...Then...Else just isn't efficient. For example, suppose you're writing a document that can't be longer than 1,000 words and you want to devise a test that will alert you when the document's word count exceeds that number. Here's a code fragment that includes an If...Then...Else structure that performs this test:
Dim DocTooLong As Boolean If ActiveDocument.Range.Words.Count > 1000 Then DocTooLong = True Else DocTooLong = False End If
In Word, the ActiveDocument.Range.Words.Count property tells you the total number of words in the active document. As it stands, there's nothing wrong with this code. However, it seems like a lot of work to go through just to assign a value to a variable. For these types of situations, VBA has an IIf function that's more efficient. IIf, which stands for "inline If," performs a simple If test on a single line:
IIf (condition, TrueResult, FalseResult)
Listing 6.8 shows a function procedure that checks the word count by using IIf to replace the If...Then...Else statement shown earlier.
Function DocTooLong() As Boolean DocTooLong = IIf(ActiveDocument.Range.Words.Count > 1000, True, False) End Function
The Choose Function
In the previous section, I showed you how the IIf function is an efficient replacement for If...Then...Else when all you need to do is assign a value to a variable based on the results of the test. Suppose now that you have a similar situation with the Select Case structure. In other words, you want to test a number of possible values and assign the result to a variable.
For example, you saw in Chapter 4 that VBA's Weekday function returns the current day of the week as a number. Here's a procedure fragment that takes the day number and uses a Select Case structure to assign the name of the deity associated with that day to the dayDeity variable:
Dim dayDeity As String Select Case Weekday(Now) Case 1 dayDeity = "Sun" Case 2 dayDeity = "Moon" Case 3 dayDeity = "Tiw" Case 4 dayDeity = "Woden" Case 5 dayDeity = "Thor" Case 6 dayDeity = "Freya" Case 7 dayDeity = "Saturn" End Select
Again, this seems like way too much effort for a simple variable assignment. And, in fact, it is too much work, thanks to VBA's Choose function. Choose encapsulates the essence of the preceding Select Case structure—the test value and the various possible results—into a single statement. Here's the syntax:
Choose(index, value1, value2,...)
Listing 6.9 shows a function called DayDeity that returns the name of a day's deity by using Choose to replace the Select Case structure shown earlier.
Function DayDeity(weekdayNum As Integer) As String DayDeity = Choose(weekdayNum, "Sun", "Moon", _ "Tiw", "Woden", "Thor", "Freya", "Saturn") End Function
The Switch Function
To illustrate why the last point is important, consider the Select Case structure used earlier in this chapter to convert a test score into a letter grade:
Select Case rawScore Case Is < 0 LetterGrade = "ERROR! Score less than 0!" Case Is < 50 LetterGrade = "F" Case Is < 60 LetterGrade = "D" Case Is < 70 LetterGrade = "C" Case Is < 80 LetterGrade = "B" Case Is <= 100 LetterGrade = "A" Case Else LetterGrade = "ERROR! Score greater than 100!" End Select
At first blush, this structure seems to satisfy the same inefficiency criteria that I mentioned earlier for If...Then...Else and Select Case. In other words, each Case runs only a single statement and that statement serves only to assign a value to a variable. The difference, though, is that the Case statements use logical expressions, so we can't use Choose to make this code more efficient.
However, we can use VBA's Switch function to do the job:
Switch(expr1, value1, expr2, value2,...)
Switch trudges through the logical expressions from left to right. When it comes across the first True expression, it returns the value that appears immediately after the expression. Listing 6.10 puts Switch to work to create a more efficient version of the LetterGrade function.
Function LetterGrade2(rawScore As Integer) As String LetterGrade2 = Switch( _ rawScore < 0, "ERROR! Score less than 0!", _ rawScore < 50, "F", _ rawScore < 60, "D", _ rawScore < 70, "C", _ rawScore < 80, "B", _ rawScore <= 100, "A", _ rawScore > 100, "ERROR! Score greater than 100!") End Function