Do you want to automate highly repetitive yet simple tasks in Microsoft Excel? Are you a beginner Excel VBA user looking to sharpen your VBA scripting skills? Microsoft Excel macro recorder can help.

Read on to learn how to record a VBA macro in Excel with step-by-step illustrations. Here, you’ll also learn the best tips and tricks to level up your Excel VBA macro scripting skills.

What Is Recording a VBA Macro in Excel?

Suppose you do data entry in your supply chain business. You do the following routinely on an Excel spreadsheet named Supply Chain Data Master after getting paper invoices, bills, and vouchers from several parties:

1. After opening the workbook, you create a new worksheet for the current day.
2. Assign column headers like Product ID, Product Name, Supplier Name, Cost, Inventory, and so on.
3. Standardize date formats (e.g., DD/MM/YYYY or MM/DD/YYYY) for clarity.
4. Format numeric fields consistently for readability.
5. Implement formulas for automatic calculations (e.g., total order value and inventory updates).
6. Protect cells containing formulas or critical information to prevent accidental changes.
7. Finally, you save the file and close the workbook.

In Excel, these are considered as repetitive tasks. You can use coding in Visual Basic for Applications (VBA) programming language to automate such tasks.

What if you don’t know VBA scripting? Do you stay away from the privilege of Excel automation and take a hit on data analytics and visualization productivity? Of course not! For you, Microsoft introduced the VBA macro recording feature in Excel.

Using the macro recording feature, you can record all the tasks you do, as mentioned earlier, from step 1 through step 7. The next day onwards, you simply press a hotkey. Excel shall execute the tasks in less than a second.

Also, if you’ve just started learning Excel VBA, you can use the macro recorder to record complex activities on an Excel worksheet. Then, you can use the Excel VBA Editor to look into the script and understand how to use several VBA components, like Subroutines, Functions, Variables, Control Structures, Objects, and more.

In a nutshell, the Excel macro recorder allows you to create reusable VBA scripts for a set of actions you do visually on the software. You can set access control to those macros to give access to the scripts to select a few and transfer the macro to a different workbook easily.

Excel macro recording is only available on Excel desktop editions like Excel for Windows and Mac. You can’t use this feature on Excel for the web or Excel online.

How to Prepare Excel for Macro Recording

First, you need the Developer tab in your Excel desktop app to access the Record Macro command button. So, open any Excel workbook and press Alt + F + T to launch the Excel Options dialog.

On Excel Options, go to the Customize Ribbon category and checkmark the checkbox for the Developer tab under the Customize the Ribbon column. Click OK on the dialog to save the changes.

Now, you can access Record Macro in Excel from the Developer tab > Code commands block. Alternatively, press Alt + L + R to start the recording process.

While on the Developer tab, you also might want to check if macro execution is enabled or not for your Excel software. Click on the Macro Security button and ensure the Enable VBA macros is the present selection.

How To Record a VBA Macro

So, you downloaded raw data from your store inventory software. Now, you’ve imported the database into Excel for formatting and data analytics.

The screenshot above represents an imaginary dataset you want to format, analyze in Excel, and apply macro recording. So, the next day you can do the same on a new database extracted from the inventory app without performing the steps.

Make sure you’re highly familiar with the steps you want to include in the macro recording. Now, go to the workbook and navigate to the Developer tab. Click on the Record Macro button.

On the Record Macro dialog that opens, enter the following details carefully:

1. Macro name: A task name you can remember, like format&filter.
2. Shortcut key: This is the hotkey that’ll execute the macro when you press the key combinations. For example: Ctrl + Shift + F.
3. Store macro in: From the drop-down, you can choose This Workbook, New Workbook, and Personal Macro Workbook. Choose the last one if you want to access the macro in all Excel workbooks on your PC.
4. Description: Write an elaborate description of the macro so anyone using it can easily understand the underlying tasks of the macro.

Once you’re done entering details into the Record Macro dialog, click OK to start the Excel VBA macro recording process. You might also notice that the Record Macro button has changed to Stop Recording.

Now, do the formatting, filtering, and other tasks on the dataset as you would do regularly.

Once you’re done, click the Stop Recording button to save the Excel VBA macro.

The next day, when you download another inventory report, export the dataset to an Excel worksheet. Simply press the Excel VBA macro hotkey to apply the formatting and filtering to the new dataset.

The VBA macro will only work within the range it has been recorded earlier. In the current example, it’s A1:E11.

Best Tips for Recording VBA Macro

1. Before starting the recording, have a clear understanding of the tasks you want the macro to automate. This helps in a more efficient recording process.
2. Create a step-by-step workflow on a piece of paper or digital note-taking app. Follow the steps when recording a macro to avoid errors. If you make an error, Excel will record it in the macro so you’ll have to start again from scratch.
3. Break down complex tasks into smaller, manageable steps. This makes it easier to record and understand the macro.
4. Ensure that your data is clean and well-organized before recording. This helps in creating a more reliable and effective macro.
5. When recording, use relative references instead of absolute ones. This makes the macro more adaptable to different situations.

Accessing the Script of the Recorded Macro

You might need to access the underlying VBA script of the macro you just recorded to learn how scripting works, copy the macro to a new workbook, or add more steps to the existing macro. Here’s how:

Press Alt + F11 to bring up the Excel VBA Editor tool.

Now, go to the Project Explorer on the left-side panel and expand the list for the VBAProject (Personal.XLSB).

Double-click the Modules folder and then Module1 to open the VBA script.

To add more steps or sharpen your VBA skills, you can go through the script to understand functions, objects, etc.

If you need to reuse the script to create a macro in another workbook, copy the content of the module window. Then, read the following article to learn how to create a VBA macro from a script:

📒 Read More: How To Use The VBA Code You Find Online

Limitations of Excel VBA Macro Recording

When you record a VBA macro it ignores the following objects or steps on Excel:

• If your action involves inputting data into a dialog box (e.g., saving a file with a specific name or setting advanced options), the Macro Recorder won’t capture these interactions.
• While simple repetitions (like copying a range multiple times) can be recorded, more complex loops and iterations are generally beyond the capability of the Macro Recorder.
• If your task involves dynamically selecting a range based on specific criteria or conditions, you’ll likely need to write the VBA code manually.
• Actions triggered by events (such as worksheet change events or workbook open events) are generally not recorded by the Macro Recorder.
• Some actions performed using the Ribbon or toolbars might not be recorded.

Conclusions

Now you know how to record a macro in Excel. Follow the steps and tips mentioned above to create a cool Excel macro to enhance your productivity. Not just productivity and automation, Excel macro also enables you to reduce human error.