Making a Macro Do Simple 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)

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.

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.

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

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).

(Remember? You learn to do this in Part A)

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.

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

c) The Macro dialog box displayed. 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.

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 below 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.

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

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

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

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

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