| VBA macros come in two forms: Command macro This is code that performs some action that usually has an effect on the program's environment. For example, the macro might insert text into a worksheet cell or change a Word option. In general, you can think of a command macro as being akin to a program menu command. Function macro This is code that returns a result. A function often takes one or more values as inputs, manipulates those values in some way, and then sends back the result of those manipulations. In general, you can think of a function macro as being akin to an Excel worksheet function or a Word formula field.
You can run both command and function macros from within other macros and functions, but I'll ignore that here. Instead, the next two sections show you the techniques you need to use the macro examples provided in this chapter and in Chapter 12. Running a Command Macro To run a command macro in any Office program, follow these steps: 1. | Choose Tools, Macro, Macros, or click Run Macro if you have the Visual Basic toolbar displayed (you can also press Alt+F8). The program displays the Macros dialog box. | 2. | (Optional) If you have a number of macros in different open documents, you can reduce the number of displayed macros by using the Macros In box to select the document that contains the macro you want to work with. | 3. | In the Macro Name list, select the macro you want to run. | 4. | Click Run. |
Adding a Macro to a Custom Menu or Toolbar If you have a macro that you use often, you should assign the macro to a menu command or toolbar button. In Chapter 10, see the section titled "Creating Custom Commands for Macros." |
Using a Function Macro You use functions as part of Excel worksheet formulas or as part of Access expressions that build calculated fields. In Excel, as with the program's built-in functions, you can either type the function into the formula by hand, or you can use the Insert Function method. Assuming you're editing a cell formula, you enter a VBA function into the formula by hand by using the following general format: WorkbookName.xls!FunctionName(arguments) Here, WorkbookName is the filename of the workbook that contains the function, FunctionName is the name of the function, and arguments is the list of values for the arguments accepted by the function. To use the Insert Function method, follow these steps: 1. | Choose Insert, Function to display the Insert Function dialog box. | 2. | In the Or Select a Category list, click All to make sure that all the VBA functions are included in the Select a Function list. | 3. | The VBA functions appear in the list using the same WorkbookName.xls!FunctionName format that I described earlier. Therefore, first look for the workbook name in the list, and then select the function you want. | 4. | Click OK. | 5. | If the function accepts arguments, enter them and click OK. |
In Access, you can enter the function into an expression either by hand or by using the Expression Builder. To insert a function by hand, place the cursor in the query Field cell or form text box and type the function using the following format: FunctionName(arguments) Here, FunctionName is the name of the function, and arguments is the list of values for the arguments accepted by the function. To use the Expression Builder, follow these steps: 1. | Place the cursor in the query Field cell or form text box. | 2. | Click the Build toolbar button to display the Expression Builder dialog box. | 3. | In the left list, open the Functions branch. | 4. | Select the name of your database. | 5. | In the middle list, select the name of the module that contains your function. | 6. | In the right list, double-click the function you want to insert. Access inserts the function into the expression, as shown in Figure 11.1. Figure 11.1. In Access, you can use the Expression Builder to insert a function into an expression. | 7. | (Optional) Fill in the rest of your expression. | 8. | Click OK. |
Using the Example Code To use the macros presented in this chapter and in Chapter 12, you need to copy the code into a VBA module on your computer. A module is an object that you insert into a document. The purpose of a module is to store VBA code, so to work with modules you need to know how to get to the Visual Basic Editor, which I discuss a bit later (see "Working with the Visual Basic Editor"). |