Home > Macros > Basics > Record

Record and Test an Excel Macro

How to record and test a short macro. This tutorial is for absolute beginners, and will help you get started with Excel programming

Introduction

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:

1. Understand what Excel can do without macros
2. Think of a simple, repetitive, Excel task that you have to repeat every day
3. Use the Macro Recorder to automate that simple routine

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.

What Can Excel Do Without Macros?

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 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.

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 programming language, 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:

1. Open the orders file
2. Filter the list for binder orders
3. Copy the binder orders
4. Create a new workbook
5. Paste the binder orders into the new workbook.

Instead of doing that task manually every day, you could automate it, by creating an Excel macro.

Get Ready to Record

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.

Start Recording

Once everything is in position, you can get ready to start recording.

1. Open a new blank workbook, which is where you'll store the macro.
2. At the bottom left of the Excel window, click the Record Macro button.



3. In the Record Macro dialog box, type a one word name for the macro, CopyDailyRecords



4. 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.



5. 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.



6. The Shortcut Key is also optional. Leave that empty for now, and you can create a shortcut later.
7. 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:

1. Open the orders file - Orders.xlsx
2. On the Data sheet in the Orders file, use an AutoFilter to view the binder orders
3. Copy the filtered binder orders, including the heading row.
4. Create a new blank workbook
5. Paste the binder orders into the new workbook, in cell A1 on Sheet1.

If you make a mistake - no problem! Just stop the recording (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.

NOTE: When saving a file that contains macros, you must choose Binary (xlsb) or Macro-Enabled (xlsm) file format in the Save window.

1. Click the Stop Recording button at the bottom left of the Excel window.



2. Close the workbook where you pasted the binder orders, without saving the changes.
3. Close the Orders.xlsx workbook, without saving the changes
4. Save the workbook where you stored the macro:
   • Name: BinderCode.xlsm
   • File Type: Excel Macro-Enabled Workbook *.xlsm or Excel Binary Workbook *.xlsb
5. 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.

This video shows the steps, and there are written instructions below the video.

To add the Developer tab in Excel:

1. Right-click on the Ribbon, and click Customize Ribbon
2. Add a check mark beside Developer, in the list at the right.
3. 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.)

1. On the Ribbon, click the Developer tab, and in the Code group, click Macro Security.
2. In the Macro Settings category, under Macro Settings, click Disable all macros with notification
3. Click OK.
4. 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. Open the Macro File First you'll open the file where the recorded macro is stored, and enable macros, by following these steps:

1. Open the file (BinderCode.xlsm) where you stored the macro that you recorded.
2. If a security warning appears at the top of the worksheet, click the Options button.



3. 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.

1. On the Ribbon, click the Developer tab, and in the Code group, click Macros.



2. In the Macro dialog box, click the macro that you want to run - CopyDailyRecords.
3. Click Run.

If your recorded macro work as expected, great! You can close all 3 workbooks used by the macro, without saving the changes.

But, if you see an error message, click the End button, and try recording the macro again. Maybe it will work this time.

Or, go to the Edit a Recorded Macro section below, and see if your macro code needs a minor change or two.

Create a Macro Shortcut

To make it easier to run a macro, you can create a keyboard shortcut for it.

1. On the Ribbon, click the Developer tab, and in the Code group, click Macros.
2. In the Macro dialog box, click the macro for which you want to create a shortcut- CopyDailyRecords.
3. Click Options.



4. In the Macro Options window, click in the Shortcut Key box
5. Press the Shift key, and type a letter to use for your shortcut --R in this example.
   NOTE: The Shift key is not required, but it's safer to use it, to avoid overwriting one of the built-in Excel shortcuts.



6. Click OK to complete the shortcut
7. Click Cancel in the Macros window, to close it

Edit a Recorded Macro

After you record a macro, you might need to make minor adjustments to the VBA code, so that the macro is more flexible. For example, change cell references that the macro recorded.

Watch this video to see the steps, and see the written instructions below the video

Open the Macro Workbook

When you recorded the macro, you selected a workbook where you wanted to store the macro. If that workbook has been closed, follow these steps to open it again:

• In Excel, open the workbook where you stored the Excel VBA macro code.
  • If you stored the macro in the Personal Macro workbook, it should already be open, and hidden from view.
• If a security warning appears at the top of the worksheet, click the Options button.
• Then click Enable This Content, to allow the workbook’s macros to run.

Find the Excel VBA Code

Next, follow these steps, to go to the recorded code.

• On the Ribbon, click the Developer tab, then click Macros.
• In the Macro dialog box, click on the name of your macro.
• At the right of the dialog box, click the Edit button.

• The Excel Visual Basic Editor (VBE) opens, showing the code that you recorded.
  • Your code might look different from the sample shown below.

Excel Visual Basic Editor

• At the right in the VBE is the Code Window. The cursor is flashing near the top of the code for your recorded macro.
• At the left, you should see a list of files, in the Project Explorer Window.

In the Code Window, you can edit the text, just as you would in Microsoft Word, or Notepad.

In the Project Explorer Window, you can select an object and see any code that it contains.

In the screen shot above, Module 1 is highlighted, in the VBA Project for our workbook, named MacroCopyProduct.xlsm.

Check the Recorded Code

The Excel Macro Recorder created some code, while we performed the steps in our process. In my example, these were the steps:

1. Open the orders file, named StationeryShort2007.xlsx
2. Filter the list on the Data sheet, to show only the Binder orders
3. Copy the Binder orders
4. Create a new workbook
5. Paste the Binder orders into the new workbook.

Here’s how those steps look, when written in Excel VBA by the Macro Recorder.

Change the Recorded Code

The Excel Macro Recorder is a great tool for getting started with Excel VBA. Sometimes you can leave the code exactly as is, and it will run fine every time you need it. Most times though, the recorded code needs to be modified, and we’ll start with a simple change.

When recording the code, I selected a specific range, “A1:J50”, which is used in two lines of the code. If new rows of data are added, the code won’t include them.

To accommodate for an increase in rows, we could change the 50 to 500. Then, if rows are added, they’ll be included in the filter. There are more sophisticated ways to deal with a range that changes size, but this works for now.

Test the Changes

After you have changed the recorded code, follow the steps below, to close the VBE.

Use the Menu:

• In the VBE toolbar, at the top left, click the File menu
• Next, in the drop down menu, click the Close and Return to Microsoft Excel command

OR, use the shortcut

• To close the VBE, use the keyboard shortcut - Alt + Q

Try the Macro Again

After the VBE is closed, run the macro again, to test the changed code.

• On the Ribbon, click the Developer tab, then click Macros.
• In the Macro dialog box, click on the name of your macro.
• At the right of the dialog box, click Run.

If the revised macro worked well, you can save the workbook that stores the macro.

But, if there are still problems with the macro:

• If an error message appeared, click the End button
• Go back and try a different change to the code.

Record Macro to Format Excel File

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.

I made this video because one of my clients was tired of manually formatting a daily list of customer information, and asked me for help. So, I set up a workbook with fake data, that was similar to the Excel file setup that she was formatting.

Next, I made this video, to show her how to record an Excel macro, while she did the daily steps. The video has a few Excel tips too, like using the F4 key to repeat the last action, and adding a button to the Quick Access Toolbar, so it's easy to run a macro.

Get the Workbook

• Orders Macro: 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 xlsm format, and is zipped.
• NEXT - More Excel Macros: Next, if you'd like to see more examples of simple Excel macros, go to the Show Messages with Excel Macros page. Show helpful messages to people who are using the workbooks that you created.

More Tutorials

Excel VBA - Add Code to Workbook

Show Messages with Excel Macros

Create an Excel UserForm

Excel UserForm With ComboBoxes

Excel VBA ComboBox Lists