Download the best tutorial, website and reference tool on Excel

Lesson 3: The Excel Macro Recorder

With the Excel macro recorder you cannot develop a macro that will damage Excel or your computer. The bolder you are in your trials the more you will learn.

I have been developing macros for more than 10 years now and I use the Excel macro recorder daily. Not to learn anymore but to write code (VBA words and sentences) for me. The Excel macro recorder is the best teacher that you can have and will remain the best assistant for the rest of your VBA developer's life. Personally, I use the Excel macro recorder a lot to avoid writing code and making spelling errors.

Print this page and follow the instructions step by step.

First make sure that the macros are not deactivated in Excel. Go to the menu bar "Tools/Macro/Security". Check "Medium". Close Excel without saving the active workbook and re-open it. From now on every time you open a spreadsheet that contains macros you will be asked to "Enable" or "Disable" the macros. If you choose "Disable" you can open the spreadsheet but the macros won't work. If you don't know who is sending the spreadsheet select "Disable".

1- Open Excel.

2- On the menu bar go to "Tools/Macro/Record New macro"

In the following window you see that a macro called "Macro1" will be created. You can change the name if you want. In "Store macro in:" is a list of all the spreadsheets that are open, select "This Workbook" which is the workbook in which you are actually workings.

At this point you can choose to attach the macro to a key from your keyboard. If you do so it means that to get Excel to run the macro you will be creating you will just have to click on the key that you have chosen. For the purpose of this exercise enter capital A in the "Shortcut Key:" text box. Later you will be able to run the macro by just holding the CTRL and the SHIFT keys and clicking on A. (NOTE: Always select a capital letter so that you don't deactivate important functions that are already attached to some lower case letters by Excel).

When you click on "OK" Excel will record a macro named as you specified and attach it to the workbook that you are working in. When you save the spreadsheet that you are working in the macro will be saved at the same time. It also means that Macro1 will disappear from you computer when you delete the spreadsheet.

Recording macros in Excel

3- Click on "OK". A small icon might appear in the middle of the screen leave it there (you can move it). This icon is where you will click to stop the recorder when you are through with the operation.

4- Let's now record a macro with the Excel macro recorder:

Select cell A1 and enter 34
Select cell A2 and enter 55
Select cell A3 and enter the formula =A1 + A2
Select cell A2 and change the color of the font to red
Select cell A1 and change the background color to blue
Select cell A3 and change the size of the font to 24

5- Click on the icon in the middle of the screen to stop the recorder or go to the menu bar "Tools/Macro/Stop Recording"

The macro has been recorded by the Excel macro recorder now let's test it

6- Go to Sheet2

7- There are two ways to run the macro. If you have entered a letter in the "Shortcut Key:" text box at the beginning you can just hold the CTRL and the SHIFT keys and click on the letter that you have chosen. You can also go to the menu bar "Tools/Macro/Macros..." select "Macro1" in the list and click on "Run". The macro is executed.

8- Open another spreadsheet and run the macro again. You can see that the macro that you have created will run in any spreadsheet as long as the spreadsheet in which it has been created is open.

9- Save your workbook as "VBATest1" for the next lessons and close it. Try to run the macro again. It is not there anymore so you can't run it and the shortcut key is free.

You have done it, you have recorded your first macro with the Excel macro recorder and used it, congratulations. Move to lesson 2 and you will be able to look at the macro that you have recorded and then create your own macros.

Download the best tutorial, website and reference tool on Excel