What every Excel user needs to know about Macros and VBA in Excel


"Automate-Automate-Automate" said the little red robot

When you start using Excel you’ll probably do all your work manually because you haven’t found the powerful automation built right into Excel. It was the same for me when I first started using Excel. Entering data, copying and pasting results, formatting and charting – you name it, I did it all by hand.

Years later I found out about Macros and VBA and once I got to know VBA, I wonder how I ever got along without it. In this article I’ll introduce you to Macros, quickly show you the Macro recorder and explain the difference between Macros and VBA.

“What’s VBA?” you ask, well read on to find out about this powerful feature of Excel that can help you automate boring, time-consuming tasks.

What’s a Macro?

A Macro is a collection of commands that you can run with a single click or keyboard combination. They can automate almost anything you can do in Excel and even do things you might not have known were possible.

"Hi" drawn by recording a Macro in Excel

"Hi!" drawn by recording and replaying a VB Macro in Excel

In essence, a macro is a computer program that gives automated instructions to the computer. The original macros were a way to use a few characters to represent a lot of instructions. They were called macros because the output was much bigger than the input. (Greek makros = “long, large”)

The use of Macros in Excel saves time and can expand the capabilities of Excel. They can automate repetitive tasks like creating and printing documents that you and your colleagues use regularly.

Macro Security Warnings

You have probably seen macro warnings when opening Microsoft Office documents, and so macros might make you think of scary things such as viruses. Well you should know that most macros are not only harmless but are actually tremendous timesavers. And macros are easier to create and use than you might expect.

Create a Macro in Excel using the Macro Recorder

In Excel you can record Macros using the built-in Macro recorder.


Using the Macro Recorder in Excel 2010 (right click on status bar for options)

You can repeat a macro as many times as you want by asking Excel to replay the macro. Macros may be run from a toolbar button, or menu, from a shape in the workbook, or from another macro.

Excel VBA programming

Microsoft VBA is the programming language that Excel Macros are recorded in. VBA stands for “Visual Basic for Applications”. With VBA you can create visual basic macros for Excel.

If you can record Macros with the Macro recorder, why would you want to use VBA? The truth is that while the Macro recorder is simple to use, it doesn’t create nice code and it can only record straightforward tasks.

VBA allows you to add brains to your macros and turn them into intelligent programs, for example you can ask the user to type in their name and store that in your program. VBA allows you to take advantage of features that can’t be accessed through the standard user interface. Below you can see Option Buttons being used inside a user form to get input from the user.

User-Form in Excel

User Forms can be displayed in Excel using VBA

Excel was the first major Microsoft Office application to use VBA, but VBA is not tied directly to Excel. It works just as well with other VBA-enabled applications, such as Word, Access and PowerPoint. So anyone who knows how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming.

If you know how to write Excel macros in VBA you can quickly transfer that knowledge and learn to write macros in other Office programs such as Word, Access and PowerPoint.

The best way to start learning how VBA communicates with Excel objects is to create some simple macros by using the macro recorder. Eventually, however, you’ll want to move beyond the limitations of the macro recorder and learn VBA.

Excel Macros Tutorial (for Excel 2010)

Want an easy way to learn Macros and VBA in Excel?

Read about my recommended Excel Macros tutorial course that teaches how to use Macros and VBA, with over 3 hours of high quality video.

When I think back to my early days of using Macros and VBA, it would have been great to have a video course like the one I’m going to recommend, because it would have saved me a lot of time and hassle searching for material on the internet.

When you sign up I’ll send you a short series of e-mails with links to 4 FREE sample videos from the recommended course, and then explain what you get if you decide to buy this online learning course. Sign up using the form below.

Image Credits:
Retro Robot courtesy of "Sasan".
10 Responses to What every Excel user needs to know about Macros and VBA in Excel
  1. […] jQuery("#errors*").hide(); window.location= data.themeInternalUrl; } }); } http://www.launchexcel.com – Today, 10:31 […]

  2. Dominique Dillaman
    December 27, 2011 | 8:50 pm

    Appreciate it for helping out, wonderful info.

    • Victor
      December 28, 2011 | 11:46 am

      Hi Dominique, glad the article about Macros and VBA was useful for you. I know a lot of Excel users have heard about Macros and VBA but don’t know what the difference is. Macros and VBA definitely open up so many possibilities with Excel.

  3. Louie Delana
    January 5, 2012 | 11:26 pm

    I appreciate your post, it was interesting and compelling. I have found my way here through Google, I am going to return over again 🙂

    • Victor
      January 9, 2012 | 10:23 pm

      Hi Louie, I do hope you find the time to come back and learn more Excel tips.
      Thanks – Victor

  4. Nick Hersh
    January 8, 2012 | 7:45 pm

    Sup. Just would like to leave a short remark and enquire exactly where you bought your particular weblog layout I might be initiating own blogging site and really like your model.

    • Victor
      January 9, 2012 | 10:33 pm

      Hi Nick – Thanks for asking about my site layout. I’ve put in a lot of effort to make sure it’s clean and inviting, so that people coming to the site looking for Excel information quickly get what they need.

      My site runs on WordPress and uses the Headway Theme, which you can check out here http://www.launchexcel.com/headway-theme. That link is an affiliate link, which means if you decide to buy the Headway theme after clicking on my link I will receive a small commission from the people who make Headway. But rest assured, the only reason I like promoting Headway is that I find it a great way to run my blog. It gives flexibility in layout design without requiring much knowledge of CSS and HTML. And version 3.0 has just come out – it looks awesome!

      It took me a while to choose Headway for this site, and you can read through my decision process at http://www.launchexcel.com/decision-matrix-1/

      Hope this helps, and all the best when you decide to create your own blogging site.
      – Victor

  5. Moriah Tallant
    January 12, 2012 | 1:09 pm

    Awesome writing style!

    • Victor
      January 14, 2012 | 11:51 am

      Hi Moriah, thanks for the compliment on my writing style – I like to use images, video and lists together in one post. It helps to make the post easier to read.

      Hope you enjoy reading the other articles on this site. Here’s a suggestion for another if you want to create bullet points in Excel – http://www.launchexcel.com/bullet-in-excel/

      – Victor

  6. […] You can think of a macro as a tape recording. It remembers word for word what you “say” to Excel. VBA = Visual Basic for Applications. This is the language used by Excel to record macros. I wrote another article on this subject here. […]