How to record and test a short macro. This tutorial is for absolute beginners, and will help you get started with Excel programming
If you don't know anything about Excel VBA, where should you start? In this tutorial you'll take these first steps in using Excel VBA:
Watch this short video to see the steps, and the written instructions are below the video.
NOTE: There's another video below, that shows how to record and run a simple macro, to format an Excel file.
Excel can do all kinds of amazing things, without macros. Get to know Excel's powerful built-in features, such as:
If you use those built-in features, you might not need a macro. For example, instead of checking each cell in a column, and manually colouring the cell green if it's over 50, use conditional formatting to highlight the cells automatically.
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.
In this example, you have a list of stationery orders, in a workbook named Orders.xlsx. You can download the sample Orders file, or use a file of your own.
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 follow every morning:
Instead of doing that task manually every day, you could automate it, by creating an Excel 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:
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.
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:
If you make a mistake - no problem! Just stop the recording (instructions below), close the files without saving, and start over again.
Once you finished all the steps, follow these steps to turn off the Macro Recorder and save the macro file.
NOTE: When saving a file that contains macros, you must choose Binary (xlsb) or Macro-Enabled (xlsm) file format in the Save window.
To prepare to test the macro, make sure that the Orders.xlsx workbook is closed.
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.
Follow the steps in this video, to add the Developer tab in Excel 2010.
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.)
Now that the Developer tab is visible, you can get ready to test the macro. Open the Macro File First you'll open the file where the recorded macro is stored, and enable macros, by following these steps:
Now, follow these steps to run the macro, to see if it works the way you want.
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.
To make it easier to run a macro, you can create a keyboard shortcut for it.
Here's another video that shows how to record and run a simple macro in Excel, to automate the task of formatting an Excel file every day.
The video includes a few Excel tips too, like using the F4 key to repeat the last action, and adding a button to the Quick Access Toolbar.
To follow along with the Order Workbook video and tutorial, download the sample Orders file, or use a file of your own. The sample file is in Excel 2007 format, and is zipped.
Last updated: July 10, 2021 7:41 PM