Relative and absolute Macros

There are two types of macros – Relative Macros and Absolute Macros.
When we record an absolute macro, it will always be conducted in the same position in the worksheet. This is the standard way in Excel. When we record a relative macro, it will be performed at the place where the cursor is at that moment in the worksheet.

To create an absolute macro click the “Start / stop Recording” (1), button at the bottom left in the “Status” bar in Excel 2007 and 2010. In versions 2003 and earlier, you can record the macro once the macro toolbar (2) is open. To stop recording click the “Start / stop Recording” (1) in Excel 2007 and 2010 and in Excel 2003 and earlier, click the “Stop Recording” button (a) in the toolbar.

To record a relative macro, click the “Use Relative References” (3) in the ribbon, in Excel 2007 and 2010. To record a relative macro in Excel 2003 and before, click the second button (b) in the toolbar.

As an example, I have two identical Macros, making one absolute and the other relative.

I will start with the absolute, so I do nothing else than click the “Start Recording” button. This opens the “Macro” dialog box. This is the same for each version. In the top box, define the macro a name. The name of a macro may only contain letters and numbers. No spaces. In the next box, you give a shortcut in order to execute the macro. This is not required, but I think this is easier. I do not always want to open the macro window and click the “Run” button. Note that the shortcut you choose should not already exist in Excel, such as Ctrl C, as it will be overwritten. The safest bet is that when you typed a keyboard shortcut, hold the Shift key on your keyboard pressed. This will always be the shortcut with the Shift key. In the next box give you a description of the macro. Always easy for later reference. Click the OK button. As soon as you click anything in the application now or type something on your keyboard, it will be recorded in the macro. Stop recording the macro when you are done.

As an example, I type Monday in the first cell and click and drag the fill handle for the rest of the days to complete.

I do the same for the relative macro. Now I will just click the “Use Relative References” button in the ribbon or the Macro toolbar.

In the “Macro” I give the macro a name, give it the keyboard shortcut Ctrl Shift R, and give it a description.
Click OK when you’re done.

Same here, I type Monday in the first cell and click and drag the fill handle for the rest of the days to complete. Stop recording the macro when you are done.

When we open the “macro” window (click the Macros button in the ribbon, or in the drop-down menu when you work with Excel 2003 or before), you see two macros listed in the window.

Click the “Edit” button …

… and the “Microsoft Visual BASIC for Applications” (VBA for short) window opens. The shortcut to open or close the VBA window is Alt F11 on your keyboard.

What do we see in this window? In the middle of the VBA window we see the VBA code where the two newly recorded macros are displayed.

The text in green is a comment. Comments are always preceded by the apostrophe character (“). Here you can change texts and it will change nothing in the macro. Changing the shortcut (1) here, for example, this will have no impact on the macro or the shortcut that you must click to run it.

The actual VBA code is shown in black. If you change something here, this will change the macro. For example, I change the text “Monday” (2) in the code to “Swotster”, then the text in each cell with weekdays will be replaced with “Swotster”, when you run the macro.

When you see the code of the two macros, you’ll see that there is little difference between the two. The only difference is where the code determines the reach of the macro.

More about VBA codes in the following lesson.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats