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.

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers


Recommended Posts
Showing 17 comments
  • Dominique Dillaman

    Appreciate it for helping out, wonderful info.

    • Victor

      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.

  • Louie Delana

    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

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

  • Nick Hersh

    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

      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 https://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 https://www.launchexcel.com/decision-matrix-1/

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

  • Moriah Tallant

    Awesome writing style!

    • Victor

      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 – https://www.launchexcel.com/bullet-in-excel/

      – Victor

  • PC

    Really helpful content. Easy to understand the difference between two.

  • Gary

    Hi Victor,

    Thank you for your blog …

    I have been trying many different methods to change the last occurrence of a text string in a text file and none have worked. Below is an example only of a CNC program that has a G00 Z.5 in the 9th to last line …

    N1492 G00 Z.5
    N1493 Z.688
    N1494 G91 G28 X0. Y0. M05
    N1495 G52 X0. Y0. Z0.
    N1496 G90

    N1497 M30

    I need to change this line only from G00 Z.5 to G00 Z7.0

    It is to increase the height of where the tool goes up, so that the tool doesn’t get snapped off only moving up 1/2 inch. Can you help? I’m using an an Excel macro successfully to globally change other lines, but I can’t change any of the previous occurrences of G00 Z.5

    Thank you,

    • Victor Chan

      Hi Gary – Thanks for your message. I’ve emailed you separately to see if I can help with your VBA code.


  • Amanda

    Hi Victor. I’m impressed with your approach to sharing your skills. So many are either not east to follow or are too basic. I’m hoping you can recommend where I can find tutorials or a publication which is inexpensive on how to learn vba. I do not have a budget to pay for a course as I only need to learn some specific coding to extract data from a data source, apply vlookups and conditional formatting all of which I’m capable in doing manually. The complex element is running the VBA regularly which extracts new and data then moving it to a worksheet to summarise results from which I can then create pivot tables/graphs including trend analysis. I’ve trawled the internet and results are inconsistent so I’m hoping you could provide some suggestions. The output is for reporting on the manament of a large project portfolio hence pursuing the vba route. Thanks in advance

    • Victor Chan

      Hi Amanda!

      Thanks for commenting.

      I think if you’re looking for free then YouTube / Google search is actually an OK place to start. It might be worth investing in a couple books on VBA (e.g. Excel Power Programming with VBA).

      Also consider the tradeoff… if you don’t want to pay for a course then you’re probably going to pick up VBA over a longer time frame.

      For your particular case I think you could post a question on an Excel help forum, or look up Stack Overflow (e.g. https://stackoverflow.com/questions/tagged/excel) if you have a specific task you’d like to achieve.

      – Victor

  • MG

    Good information. I saved your web page for future reference. Thanks.

pingbacks / trackbacks

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.