Lesson 2 : Making A Macro Do Simple Task

In this Lesson, you’ll learn to…

record a macro

automate a task

1) Recording Your First Macro

You can use Excel build-in macro recorder to translates your actions into VBA macro commands. After you recorded the macro, you will be able to see the VBA code in the Module window. Before you record a macro, plan the steps and commands that you want the macro to perform. Every action that you take during the recording of the macro will be recorded - including the correction that you made.

In this example, we will record a macro that sets the cell background color to light yellow. To record the macro, follow the steps below;

a) Select a Range on a Worksheet and then select Record New Macro...under Tools...Macro (shown in Fig 2.1)

Fig 2.1 - Click for larger image

b) In the Record Macro dialog box, type "SetBackgroundColor" in the Macro Name textbox to set the macro name. Leave all other option by default then click the Ok button. This will start the macro recording. (see Fig 2.2)

Fig 2.2 - Click for larger image

c) In the Background Color Panel, select the Light Yellow color box. This action will set the background of the current selected Range in light yellow color.

Fig 2.3 - Click for larger image

d) To stop the macro recording, click the Stop button (the navy blue rectangle) on the Macro Recorder toolbar.

Fig 2.4 - Click for larger image

You have recorded a macro that set cell background to light yellow.
Now the recorded macro is ready for use. Before we run the macro, let's look into the syntax.

* Press [Alt] and [F11] at the same time to load the Visual Basic Editor. The Visual Basic Editor comes up.
* Expand the Modules folder in the Project Explorer by clicking on the plus (+) sign.
* Double click the Module1 folder to see the sub routine (macro). (Fig 2.5)

(Remember? You learn to do this in Lesson 1)

Fig 2.5 - Click for larger image

As the figure shows, the name of the sub routine is "SetBackgroundColor". The color index for the light yellow is 36. The background pattern is solid.

(Note: The characters in Green with apostrophe in front are just comments and will be ignored by VBA. You can always insert comments in your code by doing so.)

2) Run the Recorded Macro

We can run the macro within the Visual Basic Editor. This time we will run the recorded macro in the worksheet.

a) On any worksheet, select a range. (Fig 2.6)

Fig 2.6 - Click for larger image

b) Run the recorded macro by select Tools...Macro...Macros... or press [Alt] and [F8] at the same time. (Fig 2.7)

Fig 2.7 - Click for larger image

c) The Macro dialog box displayed (Fig 2.8). Since there is only one macro in the module, by default the only macro, SetBackgroundColor is selected. Click the Run button to run the macro.

Fig 2.8 - Click for larger image

d) Cells will now have light yellow background color.

Fig 2.9 - Click for larger image

e) You can edit the macro. In this example we change the :ColorIndex = 3. When we run the macro, range selected will now have red background color.

f) We can add a macro to a toolbar as well. (i) we create a command button by selecting View – Toolbars – Control Toolbox . Figure 2.10 display the control Toolbox. Select the Command Button and embed it on the worksheet. (ii) After that right click on the button and and edit. Type Click Me. (Fig 2.11)

Fig 2.10 - Click for larger image

Fig 2.11 - Click for larger image

g) Right click on the command button and select view Code and this will bring up the VBE. In the Code window, type Module1.SetBackgroundColor

Fig 2.12 - Click for larger image

h) Go back to the worksheet and click on exit design mode (the highlighted icon shown in Fig 2.13). This will add the macro to the commandbutton. When you click on the button, it will run the macro “Module1.SetBackgroundColor”

Fig 2.13 - Click for larger image

You can use the macro execute a repetitive by adding a line of code as …ActiveCell.Offset(1,0).Select… shown below.

Fig 2.14 - Click for larger image

When you click on the button, it will go down a row and set the cell color to light yellow.


3) Summary

This is how you record a macro, assign a macro to a command button and execute a repetitive task.