VBA - Record A Macro

It's been a while we touched VBA. Let's do a quick recap on what we discussed on this topic so far. VBA is an object oriented language, everything inside Excel is treated as an object. Objects have properties, methods and events. You can change the attributes of objects by changing its properties, you can perform actions using methods of objects.

Excel has an object structure called Object Model. We need to follow this structured hierarchy to reach the object we want to work with. We also saw the short-cuts to reach the desired object without following the lengthy code lines.

Enough already, let's follow the best way to learn VBA coding i.e. using the built-in recorder to analyse code. So we will go ahead now and record a macro first. What actions should we record? How about recording selecting few cells and colouring them in bright red and apply border to them.

Let’s create a new workbook and save it as VBA Learning and select Excel Macro-Enabled Workbook in Save as type field (only in Excel 2007 or later). We will use this book to do all our exercises going forward.

Record Your First Macro

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

This action opens up a dialogue box named Record Macro. Before moving on to record a macro let's analyse what this box contains.

Record Macro box in Excel 2007

Macro Name: You can provide a name to the macro you are recording. By default, names given by Excel are Macro1, Macro2 etc. You can define a new name that says what the code does. Remember, you have to follow couple of rules while naming the macro. Just like Named Ranges, name should always begin with a letter or underscore. Name should not contain spaces or invalid characters in-between. There are few names reserved by Excel so you should not use them. Macro names which are already used with in the same workbook will also be not allowed.

Shortcut key: Assign a key combination so that you can just press them and your macro is run. You can avoid multiple clicks to access the macro this way. You can use a combination of Ctrl, Shift and any one number, alphabet or special symbol keys on your keyboard, Ctrl key is to be used mandatorily. But remember to be careful while selecting your short-cut keys. Macro short-cut keys override Excel built-in functionality of those keys. For example, if you use Ctrl+S as short-cut key for your macro, then pressing these keys will always run the macro within this workbook but you will lose the Excel save command using Ctrl+S.

Store macro in: This is a drop-down list having three options. You have option to save the macro in ThisWorkbook, New Workbook or Personal Macro Workbook.

This Workbook refers to the workbook you are trying to create the macro. You will use this option if you want to keep the code you are writing in the workbook you are working in.

As the name suggests, New Workbook refers to a new workbook. Code will be saved under a new workbook that will be created once you record the macro.

Selecting Personal Macro Workbook keeps the code in a central workbook. Code saved this way will be available across any of the Excel file that you open.

Description: Enter a quick overview of your macro for future reference. You can enter anything you wish here and this field is not mandatory. If you are using Excel 2003, you can see a line of description already entered by Excel. In my system, it is this line: Macro recorded 21/02/2014 by Hari Krishna Popuri.

Fine! Let’s fill these fields with our data.

1. Enter "Macro Name" as MyFirstMacro (I am avoiding spaces; you can also name something like My_First_Macro, but decide one way and have a standard naming convention across your projects. It looks neat)
2. For "Shortcut key", click in white space next to Ctrl+ and press Shift key and Q.
3. Let the "Store macro in" field contain This Workbook
4. Enter a "Description", This is my first macro.

Record Macro dialogue box should look like below at this point.

Click OK. Remember, from now on, all your actions in Excel will be recorded. So we have to do precisely what we want to do. Avoid unnecessary clicks here and there etc.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier.

2010 Screenshot

2003 Screenshot

This means your macro is being recorded. Excel 2003 will also show a new floating menu ‘Stop Recording’ with two buttons. One button is blue coloured like the one in Excel 2007 status bar. You can click this button to stop recording the macro. The other button is ‘Relative Reference’. In Excel 2007, you can access this option from View ribbon > Macros drop-down button > Use Relative References.

Remember our post on Absolute and Relative references? In Macros, by default absolute references are used. You have to click this relative reference option if you want to use relative references so that the actions you recorded will be coded depending on the relative position of the active cell. For now, let’s stick to default option and see what happens.

Now that our macro is being recorded, click on cell A1 to A10, apply background colour red. Apply ‘All Borders’. My sheet now looks like below.

Click on “Stop Recording” button now (or blue button in Excel 2007). You have successfully recorded a macro.

Now let’s test it. We used Absolute References while recording our macro. So every time you run this macro, no matter which cell is selected currently, cells A1:A10 of active sheet will always be coloured in red and bordered.

To test this;

1.  Go to Sheet2 and click on cell B10 (or anywhere you like)
2. Run the macro by pressing key combination we chose (Ctrl+Shift+Q). You can also go by menu/ribbon way.

If everything goes fine, cells A1:A10 in Sheet2 must have been coloured in red and bordered. Try this with different cells selected but result should always be same. Do you also see this way?

Use Relative References 

Let's record another macro using relative references. We will name it MySecondMacro

1. Click on cell A1
2. Click on View > Macros > Relative References
3. Select cell A1:A10, record another macro with name MySecondMacro
4. Give shortcut of Ctrl+Shift+W
5. Enter description of your choice

Now let’s test it. We used relative references and cell A1 was selected before recording macro. Once the macro started recording, from A1 we selected 10 cells below and coloured them red and bordered. So every time you run this macro, wherever the cursor is that cell and 10 cells below should be coloured and bordered.

To test this; 

1. Go to Sheet2 and click on cell B2
2. Run the macro by pressing key combination we chose (Ctrl+Shift+W). You can also go by menu/ribbon way.

If everything goes fine, cells B2:B11 in sheet2 must have been coloured in red and bordered. Try selecting different cells and check.

By recording your actions, you can automate most of the tasks without even knowing any code. In the next post, we will see the code behind these 2 macros and analyse it to get better understanding on what’s happening behind the curtains. Bye till then!!