Excel - Macros Lesson One

A macro is a stored collection of keystrokes that can be replayed to perform a repetitive task. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands.  Visual Basic (Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) stores each macro in a new module attached to a workbook.

Planning a macro

The first step to creating a macro is to make a list of the tasks you want the macro to perform. Then you figure out which sequence of keystrokes you will use to perform these tasks.  For the very first step you should always select a cell in the worksheet where you want the macro to begin. Without this step, you can get unintended results.

Recording macros

When the list of planned keystrokes is completed, you are ready to record the macro. Access the macro dialog box from the Tools menu, Macro submenu, Create New Macro submenu. Fill in the description of your macro, and then start executing your planned series of keystrokes. If you make a mistake when you record the macro, corrections you make are also recorded. When you have completed the macro, press the stop button.

Making a macro easy to run

You can run a macro by choosing it from a list in the Macro dialog box. To make a macro run whenever you click a particular button or press a particular key combination, you can assign the macro to a toolbar button, a keyboard shortcut (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), or a graphic object on a worksheet.

To create an option button and to assign a macro to it, follow these steps:

  1. Click Option Button on the Forms (not the Controls toolbar) toolbar, and then draw the outline of the button on the sheet.
  2. Select any cell in the worksheet.
  3. Right-click the option button, and then click Assign Macro.
  4. In the Assign Macro dialog box, click the name of the macro that you want to assign to the option button, and then click OK.
  5. When you click the option button, the macro will run.

Managing your macros

Once you've recorded the macro, take a look at it in the Visual Basic Editor to see how its code works. On the Tools menu, point to Macro and then select Macros to find the one you recorded. Select it and click Edit to see the code.

After you record a macro, you can view the macro code with the Visual Basic Editor (Microsoft Visual Basic Editor: An environment in which you can edit macros that you've recorded and write new macros and Visual Basic for Applications programs.) to correct errors or change what the macro does. For example, if you wanted the text-wrapping macro to also make the text bold, you could record another macro to make a cell bold and then copy the instructions from that macro to the text-wrapping macro.

Tasks:

We will create a simple macro, test it, break it, and then repair it. We will create a command button to run the macro.

 

Create the macro.

  1. Open a new workbook
  2. Position the cursor in cell A1 of Sheet1.
  3. Open the macro dialog box from the Tools menu, Macro submenu, Create New Macro option.
  4. Name the Macro “Sheet3”
  5. Describe the macro: “This macro will move the cursor to Cell A1 in Sheet3.” Click Ok.

The record macro toolbar will appear.

  1. Click on cell A1 in Sheet1.
  2. Click on the Sheet3 tab.
  3. Click on cell A2.
  4. Press the CTRL and Home keys.
  5. Type the word Done in cell A1. Press Enter.
  6. Use the up arrow key to return the cursor to cell A1
  7. Press the CTRL and B keys
  8. Press the centering icon
  9. Press the stop button on the record macro toolbar.

 

Run and test the macro.

  1. Erase the contents of cell a1 in sheet 3.
  2. Return the cursor to any cell in sheet 1
  3. Open the macro dialog box from the Tools menu, Macro submenu, Macros option.
  4. Click on the Sheet3 macro.
  5. Click on Run.

 

Break the macro.

  1. Rename the sheet3 tab to help
  2. Erase the contents of cell a1 in sheet 3.
  3. Return the cursor to any cell in sheet 1
  4. Open the macro dialog box from the Tools menu, Macro submenu, Macros option.
  5. Click on the Sheet3 macro.
  6. Click on Run.

You’ll see a message, “Run Time Error ‘9’ Subscript out of range.”

  1. Click on End.

What happened? The name of the worksheet tab has changed, so the macro won’t run.

 

Fix the macro.

  1. Open the macro dialog box from the Tools menu, Macro submenu, Macros option.
  2. Click on the Sheet3 macro.
  3. Click on Edit.
  4. Click on Modules and then on Module 1 to view the macro code:
  5. Find the word “Sheet3” and change it to “Help”.
  6. Close the window.
  7. Test the macro. It should run correctly now.
  8. We could have also fixed the macro by renaming the test tab to Sheet3.

 

Assign a command button to our macro.

 

  1. Open the Forms toolbar.
  2. Position the cursor in cell B4 of Sheet1
  3. Click on the Button command on the forms toolbar.
  4. Draw the button so it covers a 2x2 grid: cells B4, C4, B5 and C5.
  5. When you release the mouse, the Assign Macro dialog box should open. Click on the name of your macro. Click OK.
  6. Edit the text on the button to say “Help”
  7. Test the button by clicking on it.

 

That’s it. You’re done! Go here for the next lesson  Macros Lesson 2