Microsoft Excel Power

Lesson 1: The basics of Macros.

The manual approach

This is pretty straightforward, however, I'm assuming that you are familiar with the various formatting options - not necessarily an expert, but that you can find most things; if not, then I would recommend having a general "Using Excel" book handy. I list one in the Introduction that you will find very useful.

So then, the steps that I take to get the headings to look like the {Formatted Report}, are as follows:

• Grab the right edge of column "I" and increase the width until the data appears correctly.
• Highlight cell range B1 thru I2 and then select Font color Blue and Bold.
• Highlight cell range A4 thru N4 and then select (from the Title bar) Format->Cells, then select the Alignment tab and check the "Wrap text" box, then press the Ok button.
• With the range still highlighted, I then select the Fill color of Light Green and set the Borders option to "All Borders"

At the end of these steps we will have the headings looking how we want them.

If I had to do this just as a one time exercise, then I would not write a macro to automate it, as it would take longer to write it that it would to format the data by hand. However, this report is produced almost on a daily basis (and has other formatting needs that are harder to do by hand), so it is worth creating a macro to do it.

Setting up the Environment

Because I write a lot of macros I like to have all of the tools ready and waiting for me in Excel. So I will show you how to add macro Record, Play and Edit options to the tool bars.

Select Tools -> Customize

Fig 1.01 - Customizing your environment.

Then from the Toolbars Tab check the Visual basic box (Fig 1.02). The resulting menu bar will appear - this can then be dragged up into the main menu bar area (Fig 1.03). You can see from mine (fig 1.04) that I do like a lot of tools handy!

Fig 1.02 - Selecting the Visual Basic menu toolbar.

Fig 1.03 - The Visual Basic Menu toolbar.

Fig 1.04 - All of my important tools!

The important buttons on this toolbar are as follows:

Fig 1.05 - The record Macro button.

Fig 1.06 - The Run Macro button.

Fig 1.07 - The Visual Basic Editor button.

Recording the Macro

Before we can record our macro we need top reset the {Unformatted Report} worksheet. To do this, copy the appropriate cells from the {Backup} tab and paste them over the cells in the {Unformatted Report}. Now we are back to where we were - except that column "I" retains the size we changed it to, so I'm going to reduce it a little like the report produces initially. It is always good to have a reset procedure when you are writing and testing macros.

To start the process of recording a macro you just select the Record Macro button from the newly acquired toolbar. You will then see the following dialog box popup:

Fig 1.08 - The Record Macro Dialog box.

In the Macro name box, I specify the name of the macro. It is important that you choose a meaningful name! I sometimes (actually most times) get in the habit of using cryptic names for my macros such as x20m. This means to me, that this is the 20th Excel macro that I have written. But it does not really tell much if anything of what it does. So consequently, I spend a lot of time looking for the macro I need.

You will do better than me in this by giving your macros names that convey what they do. In this example, I have called it Format_Report. Granted, it doesn't say which report, but for now, it will do. The next decision you have to make is where you want to store your macro. In the "Store macro in" drop-down box there are three choices.

"This Workbook" - The macro you create will be associated with this macro, but will only be available when you have this spreadsheet open.

"Personal macro Workbook" - The macro will be stored in your personal collection of macros and will thus be available anytime Excel is open.

"New Workbook" - The macro will be associated with a new workbook.

If you need to have the macro available for a number of different spreadsheets, then choose the Personal macro option, otherwise, you can store the macro in the specific spreadsheet you are working with. Most of the time I store macros in my personal workbook so that they are available as a reference when writing other macros, however, you tend to get a lot of macros stored here.

For this example, I will select the store in This Workbook option. When you hit the OK button, the macro recorder is running and you will see a floating dialog box appear. This is the stop recording button, which you will select when you have finished recording your actions (Fig 1.09).

Fig 1.09 - Stop recording button.

I then follow the same manual steps that I used before to format the headings. When I'm done, I select the stop recording button (Fig 1.09).

Print this page

  1  2  3  4  5  6