Excel Record Macro

 

What is Macro and what is Excel Record Macro function?

Macro is a set of actions that is intended to be repeating all over again. Macro is not a word just for Excel, you can find many other software help users record a set of actions for Windows or even for specific games. The first Macro I used was EZ Macro about 15 years ago, which helps me repeat a set of actions in an online game endlessly in order to get my skill points up. Today I still use keyboard and mouse that can record Macro.

Excel is very kind to have a built-in Record Macro function, which allows you to record actions and turn them into Excel VBA code. You can quickly learn VBA by studying how the code is written for each action, but not everything can be recorded. For example, if you want to type 1 in A1, 2 in A2, 3 in A3, etc until A1000, you still need the programming knowledge to do the loop automatically, instead of recording the action manually.

Finding the Excel Record Macro button

Excel 2003

Navigate to Tools > Macro > Record New Macro

Excel 2007

Right click anywhere on the menu bar > Customize Quick Access Tool Bar > Popular > check the “Show Developer Tab in Ribbon” box

Click on the Developer tab, you will find “Record Macro” button

Excel 2010, 2013

Right click anywhere on the menu bar  > Customize the Ribbon > check “Developer” box

Click on the Developer tab, you will find “Record Macro” button

For Excel 2007 and onwards

You will also find another button called “Use Relative References” below “Record Macro” button.

If this option is off, all Range address are hard coded, such as Range(“A1”), but I can’t say it is “absolute” because it has no $ sign. If this option is on, all Range address are relative using OFFSET Method in VBA.

To understand what is OFFSET Property, click to read the below article

Excel VBA OFFSET Property for Range

Record Macro

After clicking on the “Record Macro” button, you will see the following box.

record_Macro

Macro Name A name which should not have space, should not be a name already defined by you anywhere in this workbook or a reserved keyword
Shortcut key You can execute the Macro using Shortcut key
Store Macro in You are recommended to leave it default as “This Workbook”, unless you want to save the Macro in a separate Project or Workbook
Description This description will be coped to theVB code as comments

Click on OK button to start Recording, click on the stop button to stop recording.

Run recorded Macro

Under Developer tab, click on “Macro” button

record_Macro_002

You will find a list of Macro you have created, click on “Run” button to run the Macro.

Run Run the Macro immediately
Step Into Open Visual Basic Editor and jump to the VBA code of Macro where you can edit the code. Each time you press F8, one line of code will execute for debugging purpose.
Edit Open Visual Basic Editor and jump to the VBA code of Macro where you can edit the code. You can still press F8 to run each line of code, but “Step Into” button saves you the first click of F8, which set focus to the first line of Macro.
Delete Delete Macro
Options  Modify description of Macro (where you can find it in VBA code) or change the Short Cut key

You can also press Alt+F11 directly to enter into Visual Basic Editor, double click on the “Modules” folder on the left, then double click the Module under it. You will find all your Macro. You can click on the green triangle on the tool bar to run the Macro directly.

If you cannot find the Section “Project – VBAProject”, click on “View” on the menu bar, then select “Project Explorer”.

record_Macro_003

Save the workbook containing Macro

For Excel 2003, you can save the workbook as XLS

For Excel 2007 and later, you have to save the workbook as XLSM  (Macro enabled-workbook), otherwise you will lose all the Macro as you reopen the workbook.

 

Outbound References

https://www.youtube.com/watch?v=Q_HQGHPBYoo

https://www.youtube.com/watch?v=KKJj78LoFaA

 

 

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *