This is an article on how to use Macro Recorder in Excel. So, study this tutorial part by part at your own pace.
In two ways you can create macros in Excel:
- Turn on the macro recorder and record your actions. This is the easiest way.
- Enter the code directly into a VBA module. This is a little bit complicated but this will give you extra advantages to automate Excel.
In this article, we shall cover how to create a Macro using the Macro Recorder in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Recording VBA Macros in Excel
We’ve covered here the basic steps to record a VBA macro. In most cases, we can record our actions as a macro and then simply replay the macro; we don’t need to look at the code that’s automatically generated by Excel. If simply recording and playing back macros can do your job, you don’t need to be concerned with the VBA language. But a basic understanding of how things work will help you of course.
Basic Recording Actions to Create VBA Code in Excel
The Excel macro recorder interprets your actions into VBA code. To start the macro recorder, choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box will appear.
You can also click the Record Macro icon on the left side of the status bar to get the Record Macro.
The Record Macro dialog box has several options:
- Macro Name: You will enter a unique name for your macro. Excel displays generic names, such as Macro1, and Macro2.
- Shortcut Key: You can mention a key combination that will execute your macro. The key combination always uses the Ctrl key. You can also press Shift when you enter a letter. For example, pressing Shift while you enter the letter M makes the shortcut key combination Ctrl+Shift+M for your macro.
- Store Macro In: This is the option you can select to choose a location for your macro. You have three choices: the current workbook (This workbook), your Personal Macro Workbook, or a New workbook.
- Description: You can put a brief description of your macro. It is optional.
When you begin recording your actions in Excel, click OK. When you finish recording the macro, choose Developer ➪ Code ➪ Stop Recording. Or you can click the Stop Recording button on the status bar. You’ll see this button in place of the Start Recording button.
Step by Step Procedures for Using Macro Recorder in Excel
This example explains to you how to record a very simple macro that will insert your name in the active cell.
To create this macro, start with a new workbook and follow these steps.
STEP 1: Prepare Macro Recorder
- First, activate an empty cell.
- Choose Developer ➪ Code ➪ Record Macro.
- As a result, the Record Macro dialog box will appear.
- Then, enter a new name for the macro, to replace Excel provided default Macro1 name. For example, you can type MyName in the Macro name field.
- You can assign a shortcut key Ctrl + Shift + N for your macro by entering an uppercase N in the Shortcut Key field.
- Click OK. Clicking OK will close the Record Macro dialog box and begin recording your actions in Excel.
- Type your name into the selected cell, and then press Enter.
STEP 2: Examining Macro
Macro MyName is recorded in a new module named Module1. You have to activate the VB Editor to view the code in this module. We can activate the VB Editor in either of the two ways:
- Firstly, press Alt + F11.
- Or, you can choose Developer ➪ Code ➪ Visual Basic.
- In the VB Editor, the Project window displays a list of all open workbooks, worksheets, and add-ins.
- This list is displayed as a tree diagram, which you can expand or collapse.
- Here, the code of the MyName macro that you have recorded is stored in Module1 in the current workbook.
- Double click Module1 in the module and the code of the macro appears in the Code window.
- The following figure shows the recorded macro in the Code window.
- The macro code should look something like this (in place of my name, you will see your name):
Sub MyName() ' ' MyName Macro ' ' Keyboard Shortcut: Ctrl+Shift+N ' ActiveCell.FormulaR1C1 = "Kawser Ahmed" End Sub
- The macro recorded is a Subprocedure and we name it as MyName.
- The statements in the macro code tell Excel what to do when this macro is executed.
- Notice that Excel inserted some comments at the top of the procedure.
- These comments are the information that you have provided in the Record Macro dialog box.
- The comment lines (which begin with an apostrophe) aren’t really necessary, and deleting them will not affect the performance of the macro.
- If you ignore the comments, you’ll see that this procedure has only one VBA statement:
- This single statement creates the name you typed while recording the macro and it will be inserted into the active cell.
- The FormulaR1C1 part is a property of the Range object.
STEP 3: Testing Macro
Before we recorded the MyName macro, we assigned the macro to the Ctrl + Shift + N shortcut key combination. We want to test the MyName macro now. If you’re in VB Editor, then you can use any of the following methods:
- Press Alt + F11.
- Click the View Microsoft Excel button on the VB Editor toolbar.
- When an Excel workbook is active, activate a worksheet.
- You can run this macro in the workbook that contains the VBA module.
- You can also open an existing workbook or create a new one and run this macro.
- Select a cell of the worksheet and press Ctrl + Shift + N. The macro will immediately enter your name into the cell.
STEP 4: Editing Macro
After recording a macro, you can make changes to it. But you have to know what you’re doing. For example, say you want to make your name bold. You can rerecord the macro, but this modification is simple, so editing the code is more advantageous.
- First of all, press Alt + F11 to activate the VB Editor window.
- Next, activate Module1 from the Project window and insert the following statement before the End Sub statement in the code window:
- The edited macro will be as follows:
Sub MyName() ' ' MyName Macro ' ' Keyboard Shortcut: Ctrl+Shift+N ' ActiveCell.FormulaR1C1 = "Kawser Ahmed" ActiveCell.Font.Bold = True End Sub
- Test this new macro, and you see that it performs as it should.
Henceforth, you will be using a Macro Recorder in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.