Introduction

Getting Results with VBA

As corporate IT departments have found themselves with long backlogs of requests, Excel users have discovered they can produce the reports needed to run their business themselves using the macro language Visual Basic for Applications (VBA). VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. This is both a good and bad thing. On the good side, without waiting for resources from IT, VBA helps you figure out how to import data and produce reports in Excel. On the bad side, you are now stuck importing data and producing reports in Excel.

What Is in This Book?

You have taken the right step by purchasing this book. I can help you reduce the learning curve so that you can write your own VBA macros and put an end to the burden of generating reports manually.

Reduce the Learning Curve

This Introduction provides a brief history of spreadsheets. Chapter 1 introduces the tools and confirms what you probably already know: The macro recorder does not work. Chapter 2 helps you understand the crazy syntax of VBA. Chapter 3 breaks the code on how to work efficiently with ranges and cells. By the time you get to Chapter 4, you will know enough to begin using the 25 sample user-defined functions in that chapter.

Chapter 5 covers the power of looping using VBA. The case study in this chapter creates a program to produce a department report, and then wraps that report routine in a loop to produce 46 reports.

Chapter 6 covers R1C1-style formulas. Chapter 7 looks at what changed in Excel VBA from Excel 2003 to Excel 2010. In the past, it was fairly straightforward to create VBA code that would run on any of the recent versions of Excel. Unfortunately, with the sweeping changes in Excel 2007 and Excel 2010, it became significantly more difficult to create this VBA code. Chapter 8 covers names. Chapter 9 includes some great tricks that use event programming. Chapter 10 introduces custom dialog boxes that you can use to collect information from the human using Excel.

Excel VBA Power

Chapters 11 through 13 provide an in-depth look at charting, Advanced Filter, and pivot tables. Any report automation tool will rely heavily on these concepts. Chapter 14 includes 25 code samples designed to exhibit the power of Excel VBA.

Chapters 15 through 18 handle data visualizations, web queries, sparklines, and automating another Office program such as Word.

Techie Stuff Needed to Produce Applications

Chapter 19 shows how to use arrays to build fast applications. Chapters 20 and 21 handle reading and writing to text files and Access databases. The techniques for using Access databases enable you to build an application with the multi-user features of Access while keeping the friendly front end of Excel.

Chapter 22, as it examines classes and collections, covers VBA from a Visual Basic programmer’s point of view. Chapter 23 discusses advanced userform topics. Chapter 24 teaches some tricky ways to achieve tasks using the Windows application programming interface. Chapters 25 through 27 deal with error handling, custom menus, and add-ins.

Does This Book Teach Excel?

Microsoft believes the average Office user touches only 10 percent of the features in Office. I realize everyone reading this book is above average, and I have a pretty smart audience at MrExcel.com. Even so, a poll of 8,000 MrExcel.com readers shows that only 42 percent of smarter-than-average users are using any one of the top 10 power features in Excel.

I regularly present a Power Excel seminar for accountants. These are hard-core Excelers who use Excel 30 to 40 hours every week. Even so, two things come out in every seminar. First, half the audience gasps when they see how quickly you can do tasks with a particular feature such as automatic subtotals or pivot tables. Second, someone in the audience routinely trumps me. For example, someone asks a question, I answer, and someone in the second row raises a hand to give a better answer.

The point? You and I both know a lot about Excel. However, I will assume that in any given chapter, maybe 58 percent of the people have not used pivot tables before and maybe even fewer have used the “Top 10 Filter” feature of pivot tables. With this in mind, before I show how to automate something in VBA, I briefly cover how to do the same task in the Excel interface. This book does not teach you how to do pivot tables, but it does alert you when you might need to explore a topic and learn more about it elsewhere.

The Future of VBA and Windows Versions of Excel

Seven years ago, there were many rumblings that Microsoft might stop supporting VBA. There is now plenty of evidence to indicate that VBA will be around in Windows versions of Excel through 2025. When VBA was removed from the Mac version of Excel 2008, a huge outcry from customers led to it being included in the next Mac version of Excel.

Microsoft has stated that in Excel 15, which is the next version of Excel, it will stop providing support for XLM macros. These macros were replaced by VBA in 1993, and 17 years later, they are still supported. There is a chance that Microsoft will introduce a new programming language for the macro recorder in Excel 15. Assuming Microsoft continues to support VBA for 17 years after Excel 2012, you should be good through the mid-to-late 2020s.

However, you can see Microsoft’s lack of commitment to VBA. Office 2003 offered a few features, such as the Research Pane and SmartTags, which could only be automated with Visual Basic.Net. The charting macro recorder, which was not finished in time to ship with Excel 2007, is included in Excel 2010.

The tools that you learn today will be good for the next 15 years. Even if Microsoft decides to scrap VBA in favor of another language, your coding skills will most likely transfer to the new platform.

Versions of Excel

This Third Edition of VBA and Macros is designed to work with Excel 2010. The previous editions of this book covered code for Excel 97 through Excel 2007. In 80 percent of the chapters, the code for Excel 2010 will be identical to code in previous versions. However, there are exceptions. For example, Microsoft offers new sorting logic, and charts have changed completely. In addition, the conditional formatting and data visualization tools in Chapter 15 are brand new. With Excel 2010, pivot tables offer new calculation options and slicers. The XML examples in Chapter 17 will work only with Excel 2003 or newer.

Differences for Mac Users

Although Excel for Windows and Excel for the Mac are similar in their user interface, there are a number of differences when you compare the VBA environment. Certainly, nothing in Chapter 24 that uses the Windows API will work on the Mac. The overall concepts discussed in the book apply to the Mac, but differences will exist. You can find a general list of differences as they apply to the Mac at http://www.mrexcel.com/macvba.html.

Special Elements and Typographical Conventions

The following typographical conventions are used in this book:

• Italic—Indicates new terms when they are defined, special emphasis, non-English words or phrases, and letters or words used as words

• Monospace—Indicates parts of VBA code such as object or method names, and filenames

• Italic monospace—Indicates placeholder text in code syntax

• Bold monospace—Indicates user input

In addition to these typographical conventions, there are several special elements. Each chapter has at least one case study that presents a real-world solution to common problems. The case study also demonstrates practical applications of topics discussed in the chapter.

In addition to the case studies, you will see New icons, Notes, Tips, and Cautions.

Code Files

As a thank you for buying this book, the authors have put together a set of 50 Excel workbooks that demonstrate the concepts included in this book. This set of files includes all the code from the book, sample data, additional notes from the authors, and 25 additional bonus macros. To download the code files, visit this book’s web page at http://www.quepublishing.com or http://www.mrexcel.com/getcode2010.html.

Next Steps

Chapter 1 introduces the editing tools of the Visual Basic environment and shows why using the macro recorder is not an effective way to write VBA macro code.