Thinking In Windows

Elements, Collections, Objects, Properties, Methods, and Procedures

Automating Repeated Tasks

As you work in the Microsoft applications (e. g., Excel, Word, Access), you often find that you perform certain tasks routinely.  For example, regularly updating reports, plotting data on a chart, or applying special formats can require you to repeat the same sequence of actions and commands.  You can automate many of these tasks, saving you time and effort, by using the powerful Visual Basic programming language which is built into the various applications.  And, the best part about this process, you don't need to understand computer programming to begin to put Visual Basic to work.

The Microsoft application teams for the various products included a Macro Recorder - a built in tool that creates Visual Basic code for you.

How Macros Simplify Tasks

The Microsoft Excel application automates tasks with macros.  A macro is a series of commands that Microsoft Excel carries out automatically.  For example, to format a range of cells you might choose the Cells command from the Format menu; select the Font tab; select a font name, style, and size; and then choose the OK button.  With a macro, you can combine all of these tasks and accomplish them with a single step.

By recording your own macros, you can tailor Microsoft Excel to your own needs and work even more efficiently.  Any sequence of actions that you perform can be recorded.  Then you can play back, or run, the macro to repeat your recorded actions automatically.  Once you record a macro, you can also assign it to a menu item or a button.  Then running a macro is as simple as choosing the menu item or clicking the button.

The Recording Process

In Microsoft Excel, the Macro Recorder stores actions you perform or commands you choose as you work.  The Macro Recorder works similarly to a tape recorder.  Just as a tape recorder records what you say, the Macro Recorder records what you do.  When you run the macro, the recorded sequence of instructions tells Microsoft Excel what to do.  The Macro Recorder plays back what you did, just as a tape recorder plays back what you said.

Recording a Macro

So, the first step in automating a task in Microsoft Excel is to record a macro.

Example

The procedures which follow lead you through the process of recording a macro that changes a date and initializes fields in a report that is used weekly.  You begin by opening the Weekly_Statistics.xls file (available at http://www.ediguys.net/download/Weekly_Statistics.xls) in Excel and saving it twice as an Excel file on your C:\ drive (in a folder of your choice and with two different filenames of your choice).

    To begin recording the example macro

  1. From the Microsoft Excel Tools menu, choose Macro, and then choose Record New Macro
  2. In the Macro Name box, type a name (this name has to be all one word) for this macro which will be meaningful to you
  3. In the Description box, type a meaningful description (note that you can also assign a shortcut key [Ctrl + any other key] to use to run the macro, if you wish)
  4. Let the "Store Macro in:" field default to "This Workbook"
  5. Choose the OK button (notice that the "Stop" button appears on the spreadsheet; this means that the Macro Recorder is ready to record)
To begin recording, choose the date field in row 1 column C that you want to modify by moving the cursor to that field and clicking on it.  Then, press F2 to "edit" that field - notice that the field changes to a MM/DD/CCYY format and that the cursor changes to an "insertion" point at the end of the field.  Now make the necessary modifications to change the date - e. g., left arrow five characters, backspace twice, and enter the date of the next Friday (you may also have to left arrow over to the month item, backspace one or two characters and enter a value one more than the value that was originally there).  When you press "Enter" the MM/DD/CCYY format returns to the "written out" date format.

Next, down arrow 4 times and right arrow once to the intersection of the first data entry cell for the "Requisitions Done" row and "Monday" column (approximately D6).  Then, press F2 to "edit" that field - change the value to 0.  Now, hit enter.  Do an up-arrow.  Now, do a Cntl+C to "Copy" the value in that cell to the clipboard and then "Paste" that value into the four adjacent cells to the right by highlighting them (Shift+Right Cursor) and doing a Cntl+V.  Because cells in the corresponding rows - D7 through I7 and D9 through I9 - have formulae in them, they also were set to zero.

Next, do the same thing for the rows D8 through H8 and D10 through H10.

Next, down arrow 1 time and left arrow four times to the intersection of the first data entry cell for the "Hours Filing" row and "Monday" column (approximately D11).  Then, press F2 to "edit" that field - change the value to 0.00.  Now, hit enter.  Do an up-arrow.  Now, do a Cntl+C to "Copy" the value in that cell to the clipboard and then "Paste" that value into the four adjacent cells to the right by highlighting them (Shift+Right Cursor) and doing a Cntl+V.  Now, return to cell D11 and this time highlight all cells D11 through H11 (Shift+Right Cursor) and do a Cntl+C to "Copy" the values in those cells to the clipboard. Then, go down and back to cell D12 and highlight the range D12 through H14 and "Paste" the values from the clipboard into the highlighted cells utilizing Cntl+V.  Note that all the cells in that range now contain the value 0.00.

Next, down arrow 7 times and left arrow 6 times to the intersection of the first data entry cell for the "This Week" row and "Phone calls" column (approximately B21).  Now, select the range of cells from that point to the "end" of the data entry cells (approximately Z25) by holding down the shift key, down arrowing 5 times, and (with the shift key still held down) right arrowing 24 or so times.  You can let go of both the arrow and the shift keys now.  Notice, again, how you highlighted the range, which means it is "selected."  Now, to "clear" the data values in that selected range, you simply hit the Delete key!

And, finally, in the spreadsheet move the cursor back to cell D6.  Then, press the "Stop" button to stop recording the macro.

Running a Macro

After you record a macro, you can play back, or run, the macro at any time; Microsoft Excel carries out all the commands stored in the macro.

    To run the example macro

  1. You are either in the Excel Spreadsheet file in which you created the macro, or you need to get into it.
  2. Now, you need to "open" the second file that you saved above (when you recorded the macro you completed making the appropriate changes in the first file; we want to run the macro on the second file that hasn't yet been changed)
  3. From the Tools menu, choose Macro, and then choose the Macros dialogue (Macros . . . ).
  4. The Macro Name / Reference box, should contain only the name of the macro you created in the first file.
  5. You want to run the macro in this second "workbook," so in the "Macros in:" pull-down menu choose "This Workbook"
  6. Choose the Run button.
When the macro completes running against this workbook, you can see that in one step the macro modified all of the dates in the spreadsheet, and initialized the cells in all of the ranges of cells that you selected.  And, the cursor is at the first data entry position for "This Week" and you are ready to begin your work!

Remember, once you record a macro, you can attach it to a menu item or a button or other graphic object, making it as accessible and convenient as the Microsoft Excel built-in menu commands or buttons.  (After next week's lesson, we'll take a couple of weeks to finish off this part of the workshop by learning how to add a macro to the tools menu, assign a macro to a button on a spreadsheet, assign a macro to a button on a toolbar, and assign a macro to a graphic object.)

If you have a question, then click here

Return to main menu