Control Visual Basic in Microsoft Office Excel 2007 Visual Basic for Applications

Chapter at a Glance

Click to view larger image

The first successful underwater tunnel ever built was begun in 1825. It is the Thames Tunnel. It was a financial disaster at the time, but amazingly it is still in use as part of the London Underground system. The genius behind the the tunnel’s engineering was a man named Marc Brunel. Twenty years before launching the Thames Tunnel, Brunel made a name for himself by devising a way of inexpensively producing the pulley blocks needed to build ships for the British shipping industry. Brunel’s technique later came to be known as an “assembly line,” and Henry Ford turned the invention into an industry, supplying America with Model T cars that cost only $3,500 in today’s dollars.

Repetition can have a dramatic effect on efficiency. Computer programs-including macros that you write-become more powerful when you add a multiplier effect. In this chapter, you’ll learn how to add loops to your macros. And to make those loops more effective, you’ll learn how to create conditional expressions that let the macro make decisions.

Using Conditionals

Recorded macros are not very smart. They can repeat what you did when you recorded the macro, but they can’t behave differently in different circumstances. They can’t make decisions. The only way that you can make your macros “smart” is to add the decision-making ability yourself.

Make a Decision

The Flow text file contains a macro named MoveRight, which looks like this:

Sub MoveRight()
         ActiveCell.Offset(0, 1).Select
End Sub

This macro selects the cell to the right of the active cell and works fine—most of the time.

1. Copy the MoveRight macro from the text file, and paste it into a VBA module in the Chapter07 workbook.

2. With cell A1 selected in the workbook, activate the Visual Basic editor, click in the MoveRight macro, and press F5.

   The macro selects cell B1 in the workbook.

3. In Excel, press Ctrl+Right Arrow to select cell XFD1, the rightmost cell on the first row.

4. In the Visual Basic editor, press F5.

   Visual Basic displays an error.

   

   You can’t select the cell to the right of the rightmost cell. If your macro can’t move to the right, you’d rather have it do nothing than display an error message.

5. In the error message box, click the Debug button to jump to the macro, and then click the Reset button to stop the macro.

6. Insert the statement If ActiveCell.Column < Columns.Count Then after the Sub statement. Indent the statement that changes the selection, and then insert the statement End If before the end of the macro.

   Be sure to indent each statement in such a way as to make it clear which statement is governed by the If statement. Visual Basic doesn’t require proper indentation, but indentation is critical to help you (or someone following after you) interpret the macro the same way that Visual Basic does.

   The revised macro should look like this:

   Sub MoveRight()
        If ActiveCell.Column < Columns.Count Then
            ActiveCell.Offset(0, 1).Select
        End If
   End Sub

   An If statement (a statement that begins with the word If) pairs with an End If statement. The group of statements from the If to the End If is called an If block.

   Visual Basic looks at the expression immediately after the word If and determines whether it evaluates to True or False. This true-or-false expression is called a conditional expression. In a simple If block such as this example, if the value of the expression is True then Visual Basic executes all the statements between the If statement and the End If statement. If the expression is False, Visual Basic jumps directly to the End If statement. You must always put the word Then at the end of the If statement. In this case, the conditional expression tests for whether the current column is less than the total number of columns in the worksheet. You could also compare to a constant—such as 16384 or 2^14—but using object properties allows the macro to work with older versions of Excel (with 256 columns) and also with Excel 2007 (with 16384 columns).

7. Switch back to Excel, select cell XFA1, activate Visual Basic, and then press F5 four or five times.

   The macro moves the active cell to the right until it gets to the last cell. After that it does nothing, precisely according to your instructions.

   

The macro recorder will never create an If block. This kind of decision is pure Visual Basic, and you must add it yourself. Fortunately, adding an If block is easy.

1. Figure out a question that has a “yes” or “no” answer. In this example, the question is, “Is the column number of the active cell less than 256?” You can turn this question into the true-or-false conditional expression in an If statement.

2. Put the word If in front of the conditional expression, and put the word Then after it.

3. Figure out how many statements you want to execute if the conditional expression returns a True value.

4. Put an End If statement after the last statement that you want controlled by the If block.

By using If blocks, you can add intelligence to your macros.

Make a Double Decision

Sometimes—such as when you’re preventing an error—you want your macro to execute only if the conditional expression is True. Other times, you want the macro to behave one way if the expression is True and a different way if the condition is False.

For example, suppose that you want a macro that moves the active cell to the right, but only within the first five columns of the worksheet. When the active cell gets to the fifth column, you want it to move back to the first cell of the next row. In this case, you want the macro to carry out one action if the cell column is less than five (move to the right) and a different action if it isn’t (move down and back). You can make the macro choose between two options by adding a second part to the If block.

1. Switch to the Visual Basic editor, and copy the MoveRight macro. Change the name of the new copy to FiveColumnWrap.

2. In the FiveColumnWrap macro, change the expression Columns.Count to 5 in the If statement.

3. Add the statement Else before the End If statement, and press Enter.

4. Press Tab, and add the statement Cells(ActiveCell.Row + 1, 1).Select after the Else statement.

   The revised macro should look like this:

   Sub MoveRight()
       If ActiveCell.Column < Columns.Count Then
           ActiveCell.Offset(0, 1).Select
       End If
   End Sub

   The Else statement simply tells Visual Basic which statement or statements to execute if the conditional expression is False.

   TIP

   Several different statements would select the first cell of the next row. For example, here are a few alternatives:

   Rows(ActiveCell.Row + 1).Cells(1).Select
   
   ActiveCell.EntireRow.Cells(2, 1).Select
   
   ActiveCell.Offset(1, 0).EntireRow.Cells(1).Select.

   They all get from the same starting point (the ActiveCell) to the same destination. When you write macros, you often have multiple alternatives. You simply choose the one that is easiest to understand.

5. Press F5 repeatedly to execute the macro.

   You see the selection move to the right and then scroll back to column A, much as a word processor wraps to the next line.

   

An If block can contain a single part, executing statements only when the conditional expression is True, or it can have two or more parts, executing one set of statements when the conditional expression is True and a different set when it’s False.

Ask Yourself a Question

In Chapter 2, "Make a Macro Do Complex Tasks", you created a macro that asked you to enter a date. You used the Visual Basic InputBox function to do that. The InputBox function is excellent for asking a question, but you must be careful about what happens when you click the Cancel button.

The Flow text file contains a macro named TestInput that prompts for the date. The code in this macro should look familiar.

Sub TestInput()
    Dim myDate As String
    myDate = InputBox("Enter Month in MMM-YYYY format")
    MsgBox "Continue the macro"
End Sub

The macro prompts for a date. It then displays a simple message box indicating that it’s running the rest of the macro.

1. Copy the TestInput macro from the text file, and paste it into a module in the Chapter07 workbook in the Visual Basic editor.

2. Click in the TestInput macro. Press F5 to run the macro, type Nov-2007 for the date, and then click OK.

   The message box appears, simulating the rest of the macro.

   

3. Click OK to close the message box.

4. Press F5 to run the macro again, but this time click Cancel when prompted to enter the date.

   The message box still appears, even though your normal expectation when you click Cancel is that you’ll actually cancel what you started.

5. Click OK to close the message box.

   You need a conditional expression where a True result means that you want the macro to continue. An appropriate question is, “Did the user enter anything in the box?” since clicking Cancel is the same as leaving the box empty: Whether you click Cancel or leave the box empty, the InputBox function returns an empty string (equivalent to two quotation marks with nothing between them). The operator <> (a less-than sign followed by a greater-than sign) means “not equal;” it’s the opposite of an equal sign.

6. Before the MsgBox statement, enter the statement If myDate <> “” Then. Before the End Sub statement, enter End If. Indent the statement inside the If block.

   The revised macro should look like this:

   Sub TestInput()
       Dim myDate As String
       myDate = InputBox("Enter Month in MMM-YYYY format")
       If myDate <> "" Then
           MsgBox "Continue the macro"
       End If
   End Sub

7. Press F5 and test to make sure the macro properly handles an input value. Type a date, and click OK.

   The macro “continues.”

8. Click OK to close the message box.

9. Now run the macro again, but this time click Cancel when prompted for a date.

   The macro stops quietly.

Whenever you allow user input in a macro, you must be sure to check whether the user took the opportunity to cancel the macro entirely.

Test for a Valid Entry

Testing for an empty string checks to see whether the user clicked the Cancel button, but it does not help you determine whether the value entered into the box is valid. You can add a second test to check the input value.

1. Run the TestInput macro again, but this time type hippopotamus in the input box, and click OK.

   The macro continues—the same as it would have if you had entered a date.

   

2. Click OK to close the message box.

   This behavior could be a problem. You need to check whether the box is empty, but you also need to check for a valid date. Visual Basic has an IsDate function that will tell you whether Visual Basic can interpret a value as a date. However, you want to check for a date only if the user didn’t click Cancel. This calls for nested If blocks.

3. Change the macro to look like this:

   Sub TestInput()
       Dim myDate As String
       myDate = InputBox("Enter Month in MMM-YYYY format")
       If myDate <> "" Then
           If IsDate(myDate) Then
               MsgBox "Continue the macro"
           Else
               MsgBox "You didn't enter a date"    
           End If
       End If
   End Sub

   Be sure to indent each statement in such a way as to make it clear which statement is governed by which If or Else statement.

4. Run the macro at least three times. Test it with a valid date, with an invalid entry, and by clicking Cancel.

   The valid and invalid entries should display the appropriate messages. Clicking Cancel or leaving the box empty should display no message.

Using the InputBox function can be a valuable way of making a macro useful across a wide range of circumstances. You must be careful, however, to check the result of the InputBox before you continue the macro. Typically, you need to check for three possibilities: valid input, invalid input, and Cancel. An If block—and sometimes a nested If block—can make your macro smart enough to respond to all the possible options.

Ask with a Message

The Visual Basic MsgBox function is handy for displaying simple messages. As its name implies, this function displays a message box. The MsgBox function can do much more than that, however. It can ask questions, too. Many times, when a macro asks a question, all it needs is a simple “yes” or “no” answer. The MsgBox function is perfect for yes-or-no questions.

Suppose that you have two macros. One is a long, slow macro named PrintMonth, and the other is a short, quick macro named ProcessMonth. You find that you often accidentally run the slow one when you intend to run the quick one. One solution might be to add a message box to the beginning of the slow macro that asks you to confirm that you intended to run the slow one.

The Flow text file includes a macro named CheckRun. You’ll enhance this macro to see how to use a MsgBox function to ask a question. The macro looks like this before you start:

Sub CheckRun()
    MsgBox "This takes a long time. Continue?"
    MsgBox "Continue with slow macro…"
End Sub

1. Copy the CheckRun macro from the text file into a module in the Chapter07 workbook.

2. Click in the CheckRun macro, and press F5 to run it. Click OK twice to close each message box.

   The first message box appears to ask a question, but it has only a single button. To ask a question, you must add more buttons.

   

3. Move the cursor to the end of the first MsgBox statement. Immediately after the closing quotation mark, type a comma.

   As soon as you type the comma, Visual Basic displays the Quick Info for the MsgBox function. The first argument is named Prompt. That’s the one in which you enter the message you want to display. The second argument is named Buttons. This is an enumerated list of values. The default value for Buttons is vbOKOnly, which is why you saw only a single OK button when you ran the macro before.

   

   Click to view larger image

   Along with the Quick Info box, Visual Basic also displays the Auto List of possible values for the Buttons argument. You want the buttons to ask the question in terms of yes or no.

4. Scroll nearly to the bottom of the list, select vbYesNo, press Tab, and then press F5 to run the macro.

   The first message box now has two buttons.

   

5. Click Yes to close the first message box, and then click OK to close the second one.

   The message box asks a question, but it totally ignores your answer. You need to get the answer from the MsgBox function and use that answer to control the way the macro runs.

6. Type the statement Dim myCheck As VbMsgBoxResult at the beginning of the macro.

   When you know a variable will contain only the value from an enumerated list, you can use the name of the list when you declare the variable. When you later write a statement to test the value of the variable, Visual Basic will display the list of possible values for you.

7. At the beginning of the first MsgBox statement, type myCheck = and then put parentheses around the argument list of the MsgBox function.

   The revised statement should look like this:

   myCheck = MsgBox("This takes a long time. Continue?", vbYesNo)

   Important

   When you use the return value of a function such as MsgBox, you must put parentheses around the argument list. When you don’t use the return value, you must not use parentheses.

8. Insert these three statements before the second MsgBox statement:

   If myCheck = vbNo Then
       Exit Sub
   End If

   Important

   When you create a conditional expression using the result of the MsgBox function, you must not check for True or False. MsgBox has many types of buttons it can display, so it has many types of answers. If you use vbYesNo for the Buttons argument, MsgBox will always return either vbYes or vbNo. Neither of these enumerated values equals False, so comparing the result to False would be the same as always clicking Yes. When you test for a value that comes from an enumerated list, always be sure to use the appropriate enumeration constant.

   The Exit Sub statement causes Visual Basic to stop the current macro immediately. To avoid making your macros hard to understand, you should use Exit Sub sparingly. One good use for Exit Sub is when you cancel the macro at the beginning, as in this case. The finished macro should look like this:

   Sub CheckRun()
       Dim myCheck As VbMsgBoxResult
       myCheck = MsgBox("This takes a long time. Continue?", vbYesNo)
       If myCheck = vbNo Then
           Exit Sub
       End If
       MsgBox "Continue with slow macro..."
   End Sub

9. Test the macro. Run it and click Yes, and then run it and click No. Make sure the rest of the macro runs only when you click Yes.

A message box is a powerful tool for asking simple questions. The MsgBox function is also a good example of how to use parentheses around argument lists: use parentheses if you use the return value of the function; otherwise, don’t use them.