Hi Excellers. It is good to go back to basics now and again. So, that is what we are going to do today. Today we are going to get back to where it all begins on that Excel Macro journey. We are going to learn how to create your first macro in just FIVE easy steps. Yes, today lets’ learn how to record an Excel macro.

Do you wonder if macros are for you or have time to learn them?. Well if you use Excel and find yourself carrying out the same repetitive tasks every day week or even month, then maybe those precious minutes (or let’s face it sometimes hours) would be better spent on something more productive. Yes, that formatting can be done a macro, that deleting of those extra columns can be done by a macro and deleting all of those empty rows that keep being generated by your office systems can be obliterated with a few clicks of the mouse. Record A Macro To Do It!.

Preparing To Record Your First Macro.

First of all, before you begin your first recording, you need to make sure you have the tools ready to do it. This means ensuring the Excel Developer Tab is available or visible. The Developer Tab it is usually hidden by default, and as a result, you may not see it. So, if this is the case, follow the steps below to enable the Developer Tab. This will allow you into the world of VBA or Visual Basic for Applications.

How To Enable The Developer Tab.

In Excel 2010 onwards

• Click on the File tab of the ribbon to open the file menu
• Select Options in the menu to open the Excel Options dialog box.
• Click on the Customize Ribbon option in the left-hand window to view the available options in the right-hand window of the dialog box.
• Under the Main Tabs section of the options window select the Developer option.
• Click OK.
• The Developer tab should now be visible in the ribbon in your Excel window

So, you can now navigate to the Developer Window either by

• Developer Tab | Code Group | Visual Basic Editor
• Or using a keyboard shortcut of ALT+F11 (my personal favourite).

 

Time To Record Your First Macro

Step 1. Prepart To Record Your Macro

Now we have our Developer Tab visible we can make use of the available options. There are two ways to create a macro by using one of the tools below

1. The Visual Basic Editor. This method requires you to type the instructions that you want Excel to follow using the Visual Basic for Applications language
2. Use the Recorder. We are creating our first ever macro here, so this is the basic method we will use today.

Once you know what you want to record, either some cell formatting or deleting some columns you are ready to press record. There are two places that you can start recording:-

1. In the Developer Tab | Code Group | Record macro
2. Use The Record button on the bottom left-hand side of the Excel Window

 

Step 2. Name Your Macro.

Once you decide to hit record, you will then be prompted to name your recording. Now, I have written a whole blog post on How To Name Your Excel Macros, if you want to read the rules, guidelines and naming conventions. In summary, however, make sure that the name is as meaningful a possible with no spaces or period between the words. As you record more and more macros you will probably develop your style of naming.

You will need to decide to save your macro in the worksheet you are working on, in a new workbook or in your Personal Macro Workbook. If you choose “New Workbook", Excel opens a new file. You are able to record and save it in that new workbook but, just as in the case of selecting “This Workbook", the macro only works in the file where it was created. If you want to reuse the macro in another workbook then be sure to select Personal Macro Workbook. I use this option if I want to have the macro available at any time once Excel is opened. If you want to know more about the Personal Macro Workbook be sure to check out my recent blog posts on it below.

Create A Shortcut To Your Personal Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

How To Delete Your Personal Macro Workbook

You also have the choice at this stage assign a shortcut key to your macro. If you assign a shortcut it will be in the CTRl+ keyboard combination. Just be mindful of the combinations you chose. In order to avoid any conflict with keyboard shortcuts, I use the CTRL+SHIFT+letter combination. This avoids any issues with overriding any inbuilt shortcuts in Excel.

Once you have all of our options set, you are ready to move onto Stage 4.

 

Step 4. Perform The Actions You Want Your Macro To Record.

This is it, guys. This is where the magic happens. Record all of the actions you want to record. Cell formatting, inserting columns and rows etc etc. Once you have performed all of the actions that you wanted to automate you can finish the recording by hitting stop in either one of the two places that you started the recording, in the Developer Tab | Code Group | Stop Recording or hit the Stop Recording in the button on the bottom left of the Excel window. Phew All done.

 

Step 5. Test Run Your Macro.

This is always my favourite part. There are a number of ways to run your recording. Here are the two main ways to run them:-

1. You can run macros from the Developer Tab. Just hit Developer Tab | Macros and a list of saved ones will pop up in the dialog box. Select the one you want to run from here.
2. If you have assigned a shortcut key combination, then just select that CTRL+ key selection and your macro will run

There are few others ways to runs macros such as directly from the VBA window, by hitting buttons on the Excel spreadsheet that you have attached them to, or you can configure them to run at the time your workbook is opened. It just depends on where and how you want to run them.

A First Excel Macro Recording Walk Though.

I record an Excel macro to insert today’s date into my Excel worksheet and colour the cell Green

That’s it. Why not give it a go. Start simple. It should save you time on the more mundane and routine task in Excel that you repeat over and over again. Let me know in the comments below how what you have recorded and how it has saved you time.

 

What Next? Want More Excel Tips?. Then Read On…

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.