This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
This is a long article on how to create VBA macros 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 our next articles, we shall dig into how you can write VBA code by yourself.
Recording VBA macros
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.
Recording your actions to create VBA code: The basics
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 shown in the following figure.
You can also click the Record Macro icon on the left side of the status bar to get the Record Macro dialog box.
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, Macro2, and so on.
- 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.
Caution: The shortcut keys you assign to macro take precedence over built-in shortcut keys for another command. For example, if you assign Ctrl+S to a macro, you can’t use this key combination to save your workbook when this macro is available.
- 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; your actions within Excel will be translated to VBA code. When you finish recording the macro, choose Developer ➪ Code ➪ Stop Recording. Or you can click the Stop Recording button on the status bar. This button is displayed in place of Start Recording button while you have started recording your macro.
Note: When you record your actions in Excel using the macro recorder, a new Sub procedure is created. You can’t create a Function procedure by using the macro recorder. Function procedures are created manually.
Recording a macro: A simple example
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:
- Activate an empty cell.
Note: Select the cell that you want to format before you start recording your macro. This step is important because if you select a cell after you have turned on the macro recorder, the cell will be recorded into the macro. In such a case, when you will run the macro, it will always format that particular cell (the cell that is recorded), and it would not be a general-purpose macro.
- Choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box will appear.
- 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.
- Choose Developer ➪ Code ➪ Stop Recording. Or you can also click the Stop Recording button on the status bar.
Examining the 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. VB Editor can be activated in either of two ways:
- Press Alt+F11.
- 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. The code of 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 displayed 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 it is named 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. These 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:
ActiveCell.FormulaR1C1 = “Kawser Ahmed”
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, it will be discussed later.
Testing the macro
Before we’ve recorded 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. It’s really awesome, right? Select a cell of the worksheet and press Ctrl+Shift+N. The macro will immediately enter your name into the cell.
Editing the 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. Press Alt+F11 to activate the VB Editor window. Activate Module1 from the Project window and insert the following statement before the End Sub statement in the code window:
ActiveCell.Font.Bold = True
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.
Download the working file
Happy Excelling 🙂