Unleashing the power of Excel with VBA

Visual Basic for Applications (VBA) combined with Microsoft Excel is probably the most powerful tool available to you. VBA is sitting on the desktops of 850 million users of Microsoft Office, and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, for example, you can have VBA do that task for you in a matter of seconds.

Barriers to entry

There are two barriers to learning successful VBA programming. First, Excel’s macro recorder is flawed and does not produce workable code for you to use as a model. Second, for many who learned a programming language such as BASIC, the syntax of VBA is horribly frustrating.

The macro recorder doesn’t work!

Microsoft began to dominate the spreadsheet market in the mid-1990s. Although it was wildly successful in building a powerful spreadsheet program to which any Lotus 1-2-3 user could easily transition, the macro language was just too different. Anyone proficient in recording Lotus 1-2-3 macros who tried recording a few macros in Excel most likely failed. Although the Microsoft VBA programming language is much more powerful than the Lotus 1-2-3 macro language, the fundamental flaw is that the macro recorder does not work when you use the default settings.

With Lotus 1-2-3, you could record a macro today and play it back tomorrow, and it would faithfully work. When you attempt the same feat in Microsoft Excel, the macro might work today but not tomorrow. In 1995, when I tried to record my first Excel macro, I was horribly frustrated by this. In this book, I teach you the three rules for getting the most out of the macro recorder.

No one person on the Excel team is focused on the macro recorder

As Microsoft adds new features to Excel, the individual project manager for a feature makes sure that the macro recorder will record something when you execute the command. In the past decade, the recorded code might work in some situations, but it often does not work in all situations. If Microsoft had someone who was focused on creating a useful macro recorder, the recorded code could often be a lot more general than it currently is.

It used to be that you could record a command in any of five ways and the recorded code would work. Unfortunately, today, if you want to use the macro recorder, you often have to try recording the macro several different ways to find a set of steps that records code that reliably works.

Visual Basic is not like BASIC

Two decades ago, the code generated by the macro recorder was unlike anything I had ever seen. It said this was “Visual Basic” (VB). I have had the pleasure of learning half a dozen programming languages at various times; this bizarre-looking language was horribly unintuitive and did not resemble the BASIC language I had learned in high school.

To make matters worse, even in 1995 I was the spreadsheet expert in my office. My company had forced everyone to convert from Lotus 1-2-3 to Excel, which meant I was faced with a macro recorder that didn’t work and a language that I couldn’t understand. This was not a good combination of events.

My assumption in writing this book is that you are pretty talented with a spreadsheet. You probably know more than 90% of the people in your office. I also assume that even though you are not a programmer, you might have taken a class in BASIC at some point. However, knowing BASIC is not a requirement—it actually is a barrier to entry into the ranks of being a successful VBA programmer. There is a good chance that you have recorded a macro in Excel, and there’s a similar chance that you were not happy with the results.

Good news: Climbing the learning curve is easy

Even if you’ve been frustrated with the macro recorder, it is really just a small speed bump on your road to writing powerful programs in Excel. This book teaches you not only why the macro recorder fails but also how to change the recorded code into something useful. For all the former BASIC programmers in the audience, I decode VBA so that you can easily pick through recorded macro code and understand what is happening.

Great news: Excel with VBA is worth the effort

Although you probably have been frustrated with Microsoft over the inability to record macros in Excel, the great news is that Excel VBA is powerful. Absolutely anything you can do in the Excel interface can be duplicated with stunning speed in Excel VBA. If you find yourself routinely creating the same reports manually day after day or week after week, Excel VBA will greatly streamline those tasks.

The authors of this book work for MrExcel Consulting. In this role, we have automated reports for hundreds of clients. The stories are often similar: The IT department has a several-month backlog of requests. Someone in accounting or engineering discovers that he or she can import some data into Excel and get the reports necessary to run the business. This is a liberating event: You no longer need to wait months for the IT department to write a program. However, the problem is that after you import the data into Excel and win accolades from your manager for producing the report, you will likely be asked to produce the same report every month or every week. This becomes very tedious.

Again, the great news is that with a few hours of VBA programming, you can automate the reporting process and turn it into a few button clicks. The reward is great. So hang with me as we cover a few of the basics.

This chapter exposes why the macro recorder does not work. It also walks through an example of recorded code and demonstrates why it works today but will fail tomorrow. I realize that the code you see in this chapter might not be familiar to you, but that’s okay. The point of this chapter is to demonstrate the fundamental problem with the macro recorder. This chapter also explains the fundamentals of the Visual Basic environment.