The Excel Macro Recorder is a very useful tool included in MS Excel VBA. With the Excel Macro Recorder you can record a task you perform with Excel. You can execute the task over and over with the click of a button which can save you a lot of time.
The Macro recorder records every step you execute with Excel. This is good news if you want to automate repetitive tasks. All you have to do is record a specific task once.
The Macro Recorder comes as a great help when you don’t know how to write VBA code from scratch.
To record, run and edit a recorded macro, follow the below steps:
1. Click on the Developer tab
2. Click on Record Macro. See the picture below. Each of the commands you perform will be saved into the macro.
3. Here you can give your macro a name and you can enter a shortcut for your macro (both are optional). Whenever you create new macro a new module is created under worksheets. This modules can be seen through Visual Basic Editor ( read How to use Visual Basic Editor in Excel VBA ?)
There are 2 options to save your macro:
i. you can store the macro in the module within the workbook or
ii. you can store the macro in Personal Macro workbook
If you choose to store your macro in Personal Macro Workbook, the macro will be available to all your workbooks (Excel Files). This is because Excel stores your macro in a hidden workbook that opens automatically when Excel starts. If you choose to store your macro in New Workbook, this will create a new workbook and the macro will be saved in the same. If you choose to store your macro in This Workbook, the macro will only be available in the current workbook.
4. Click on OK.
We will now record a macro that changes the format of Cells to Number with zero decimals.
5. Right mouse click on the active cell (selected cell). Be sure not to select any other cell! Then click on Format Cells…
6. Choose Number format, change Decimal places to 0 and click OK.
7. Click stop recording
8. Open Visual Basic Editor by Clicking on Visual Basic in above image or using key board shortcut Alt+F11
9. I had named the macro as test, when we clicked on record macro. you will see the below code in VBA module.
Sub test()
‘ test Macro
Columns(“A:A”).Select
Selection.NumberFormat = “0.00”
End Sub
10. Close the visual basic editor and return to your excel sheet. Enter some sample data, like i have updated below under column A:
| 7.28 |
| 6.56 |
| 6.80 |
| 6.24 |
| 7.68 |
11. To run our macro to get the desired output we have programmed, Click Macros (refer image in point 7) or use keyboard shortcut and press Alt+F8. This will open a pop-up window as shown below.
12. Choose macros in This Workbook if it is not selected by default, as we have chose to save the macro under current workbooks module. Now you will able to see available macros in the current open workbook. In above screenshot you can see the only available macro is test, which we just completed recording.
13. Select test and click on Run button, this will execute the recorded macro and we can see the output in column A (where we have entered the sample data).
| 7 |
| 7 |
| 7 |
| 6 |
| 8 |
So i hope, the above post will help you to record your own macro, edit using the VBA editor, save it and run repetitive tasks to avoid manual errors and improve efficiency.
Thank you for reading. Happy Learning !