MS-Excel / General Formatting

---

Creating a Command Macro

Recording macros is limiting because there are plenty of macro features that you can't access with mouse or keyboard actions or by selecting menu options. In Excel, for example, VBA has a couple dozen information macro functions that return data about cells, worksheets, workspaces, and more. Also, the VBA control functions enable you to add true programming structures such as looping, branching, and decision-making.

To access these macro elements, you need to write your own VBA routines from scratch. This is easier than it sounds because all you really need to do is enter a series of statements in a module.

Writing a Command Macro

With a module window open and active, follow these steps to write your own command macro:

1. Open the module you want to use for the function.
2. Place the insertion point where you want to start the macro. (Make sure the insertion point isn't inside an existing macro.)
3. Choose Insert, Procedure. The Visual Basic Editor displays the Add Procedure dialog box appears.
   
   
   
   Use the Add Procedure dialog box to name your new procedure and select the type of procedure you want to insert.
4. Use the Name text box to type the name of the macro. Here are some general guidelines you need to follow:
   
   • The name must be 255 characters or fewer.
   • The first character must be a letter or an underscore (_).
   • You cannot use spaces or periods.
5. In the Type group, make sure the Sub option is activated.
6. Click OK. VBA adds the following code to the module (where ProcedureName is the name you typed in step 3):
   

   Public Sub ProcedureName()
   End Sub

7. Between the Public Sub and End Sub lines, type the VBA statements you want to include in the macro. Press Enter after each statement to start a new line.

Figure shows a simple example where I have added just a single VBA statement:

MsgBox "My Hero!"

In this example, the statement contains VBA's MsgBox function, which is used to display a simple dialog box (the name MsgBox is short for "message box") to the user. (To enhance readability, I pressed Tab once before typing the statement.

Running a Command Macro

The Office applications offer several methods for running your VBA command macros, but you'll use two most often:

• In a module, click anywhere inside the macro, and then either select Run, Run Sub/UserForm or press the F5 key.
• In the Office application, choose Developer, Macros (or press Alt+F8) to display the Macro dialog box. If necessary, use the Macros In list to choose the document that contains the macro with which you want to work. Now use the Macro Name list to click the macro; then click the Run button.
  You can also use the Macro dialog box to jump directly to any command macro that you want to edit using the Visual Basic Editor. In the Macro Name list, click the macro and then click Edit.

If you try this on the example macro shown in Figure above, you see the dialog box shown in Figure below. Click OK to close the dialog box.