| 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: -
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 6.8 shows a function procedure that checks the word count by using IIf to replace the If...Then...Else statement shown earlier. Listing 6.8. 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 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 ,...) | 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. | | value1 , value2 | A list of values from which Choose selects the return value. The values can be any valid VBA expression. | 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. Listing 6.9. 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: 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 ,...) | 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 6.10 puts Switch to work to create a more efficient version of the LetterGrade function. Listing 6.10. 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 < 50, "F", _ rawScore < 60, "D", _ rawScore < 70, "C", _ rawScore < 80, "B", _ rawScore <= 100, "A", _ rawScore > 100, "ERROR! Score greater than 100!") End Function | 