MS-Excel / General Formatting

---

Functions That Make Decisions

Much of what we're talking about in this tutorial 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:

• Your code will execute faster.
• You'll have less code to type.
• Your code will be easier to read and maintain.

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)

condition
A logical expression that returns True or False.

TrueResult
The value returned by the function if condition is True.

FalseResult
The value returned by the function if condition is False.

Listing shows a function procedure that checks the word count by using IIf to replace the If...Then...Else statement shown earlier.

A Function That Uses IIf to Test a Document's Word Count

Function DocTooLong() As Boolean
    DocTooLong = IIf(ActiveDocument.Range.Words.Count > 1000, True, False)
End Function

If the number of words exceeds 1000, IIf returns True; otherwise, the function returns False.

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 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 my previous tutorial Building VBA Expression 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,...)

index
A numeric expression that determines which of the values in the list is returned. If index is 1, value1 is returned. If index is 2, value2 is returned (and so on). Note that if index is less than 1 or greater than the number of values in the list, the function returns Null.

value1, value2...

A list of values from which Choose selects the return value. The values can be any valid VBA expression.

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

A Function That Uses the Choose Function to Select from a List of Values

Function DayDeity(weekdayNum As Integer) As String
    DayDeity = Choose(weekdayNum, "Sun", "Moon", _
        "Tiw", "Woden", "Thor", "Freya", "Saturn")
End Function

The Switch Function

Choose is a welcome addition to the VBA function library, but its use is limited because of two constraints:

• You can use Choose only when the index argument is a number or a numeric expression.
• Choose can't handle logical expressions.

To illustrate why the last point is important, consider the Select Case structure used earlier in this tutorial to convert a test score into a letter grade:

Select Case rawScore
    Case Is < 0
        LetterGrade = "ERROR! Score less than 0!"
    Case Is < 60
        LetterGrade = "F"
    Case Is < 70
        LetterGrade = "D"
    Case Is < 80
        LetterGrade = "C"
    Case Is < 90
        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, you can use VBA's Switch function to do the job:

Switch(expr1, value1, expr2, value2,...)

expr1, expr2...
These are logical expressions that determine which of the values in the list is returned. If expr1 is True, value1 is returned. If expr2 is True, value2 is returned (and so on).

value1, value2...
A list of values from which Switch selects the return value. The values can be any valid VBA expression.

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 shows the puts Switch to work to create a more efficient version of the LetterGrade function.

A Procedure That Uses the Switch Function to Convert a Test Score into a Letter Grade

Function LetterGrade2(rawScore As Integer) As String
    LetterGrade2 = Switch( _
        rawScore < 0, "ERROR! Score less than 0!", _
        rawScore < 60, "F", _
        rawScore < 70, "D", _
        rawScore < 80, "C", _
        rawScore < 90, "B", _
        rawScore <= 100, "A", _
        rawScore > 100, "ERROR! Score greater than 100!")
End Function