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

Retro-Robot

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

Macro-Recorder-Excel-2010

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

GET BETTER AT EXCEL

Recommended Posts
Contact Us

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

Not readable? Change text.