If you don’t know anything about creating macros with Excel Visual Basic for Applications (VBA), where should you start? In this article you’ll take these first steps in using Excel VBA: Record a Macro.
- Understand what Excel can do without macros
- Think of a simple, repetitive, Excel task that you have to repeat every day
- Use the Macro Recorder to automate that simple routine
What Can Excel Do Without Recording a Macro?
Excel can do all kinds of amazing things, without macros. Get to know Excel’s powerful built-in features, such as:
- Conditional Formatting
- Data Validation
- Pivot Tables
- AutoFilters and Advanced Filters
If you use those built-in features, you might not need to record a macro. For example, instead of checking each cell in a column, and manually coloring the cell green if it’s over 50, use conditional formatting to highlight the cells automatically.
Identify an Excel Task to Automate
If you use Excel every day, you probably have a few tasks that you repeat daily, weekly or monthly. To get started with Excel VBA, you could focus on one of those tasks, and try to automate it: Record a Macro. In this example, you have a list of stationery orders, for binders, pens, and other items, in a workbook named Orders.xlsx. You can create a small sample Orders file, with columns for Sales Date, Item Sold, Quantity, Unit Price and Total Cost. Every day, in your imaginary job, you open that Orders file and filter the list of orders, to find all the orders for binders. Then you copy the orders, and paste them into a new workbook. Here’s a summary of the steps that you might follow every morning:
- Open the orders file
- Filter the list for binder orders
- Copy the binder orders
- Create a new workbook
- Paste the binder orders into the new workbook.
Instead of doing that task manually every day, you could automate it, by using Record a Macro to create the Excel VBA code, which is also known as a macro.
Get Ready to Record a Macro
Now that you’ve decided to automate this task, you’ll use Excel’s Macro Recorder tool to create the Excel VBA code. Before you start recording, you’ll get everything into position. For example:
- Do you want the macro to open a specific workbook, or will that workbook already be open?
- Should you select a cell or worksheet before the macro runs, or will selecting the cell be part of the macro?
In this example, you want the macro to open the Orders workbook for you, and then filter and copy the data. So, the Orders workbook should be closed when you start recording. You don’t need to select a specific cell or worksheet before recording; any cell selection will be done during the macro recording.
Once everything is in position, you can get ready to start recording.
- Open a new blank workbook, which is where you’ll store the macro.
- At the bottom left of the Excel window, click the Record Macro button.
- In the Record Macro dialog box, type a one word name for the macro, CopyDailyRecords
- From the ‘Store Macro In’ drop down, select This Workbook as the workbook where you’d like to store the VBA code. Later, you can open this workbook every morning, to run the macro.
- In the Description box, you can type a brief note about what the macro will do. This is optional, and you can leave the Description box empty, if you prefer.
- Click OK, to start recording.
Perform the Macro Steps
While the Macro Recorder is on, you’ll perform the steps that you want to automate. In this example, these are the steps that you should do now:
- Open the orders file – Orders.xlsx
- On the Data sheet in the Orders file, use an AutoFilter to view the binder orders
- Copy the filtered binder orders, including the heading row.
- Create a new blank workbook
- Paste the binder orders into the new workbook, in cell A1 on Sheet1.
If you make a mistake – no problem! Just stop the recording (see the instructions below), close the files without saving, and start over again.
Stop the Recording
Once you finished all the steps, follow these steps to turn off the Macro Recorder and save the macro file.
- Click the Stop Recording button at the bottom left of the Excel window.
- Close the workbook where you pasted the binder orders, without saving the changes.
- Close the Orders.xlsx workbook, without saving the changes
- Save the workbook where you stored the macro: Name: BinderCode.xlsm; File Type: Excel Macro-Enabled Workbook *.xlsm
- Close the BinderCode.xlsm file.
Get Ready to Test the Macro
To prepare to test the macro, make sure that the Orders.xlsx workbook is closed.
Add the Developer Tab
To run the macro, you’ll use the Developer tab on the Excel Ribbon. You can add the Developer tab to the Excel Ribbon, if it’s not there already:
- Click the Microsoft Office Button, and then click Excel Options.
- Click the Popular category, and add a check mark to Show Developer tab in the Ribbon
- Click OK, to close the Excel Options window.
Macro Security Settings
If you haven’t run macros before, you might need to change your macro security level. (You may have to clear this with your IT department.)
- On the Ribbon, click the Developer tab, and in the Code group, click Macro Security.
- In the Macro Settings category, under Macro Settings, click Disable all macros with notification
- Click OK.
If you changed the setting, close the workbook, and then reopen it.
Test the Macro
Now that the Developer tab is visible, you can get ready to test the macro. First you’ll open the file where the recorded macro is stored, and enable macros, by following these steps: Open the file (BinderCode.xlsm) where you stored the macro that you recorded.
- If a security warning appears at the top of the worksheet, click the Options button.
- Click Enable This Content, to allow the workbook’s macros to run, and click OK.
- Run the Recorded Macro
Now, follow these steps to run the macro, to see if it works the way you want.
- On the Ribbon, click the Developer tab, and in the Code group, click Macros.
- In the Macro dialog box, click the macro that you want to run – CopyDailyRecords.
- Click Run.
If you see an error message, click the End button, and try recording the macro again. If everything went as expected, great! You can close all 3 workbooks used by the macro, without saving the changes.
Try Recording Another Macro
Now that you know the steps to record a macro and testing a macro in Excel 2007, find another task that you would like to automate, and record the steps, to create another macro. You can save hours every week, by automating your Excel tasks, whenever possible.
Edited by Rich Moyer
Rich Moyer is Principal Consultant (retired) of Spaho Consulting