IN THIS INTRODUCTION
As corporate IT departments have found themselves with long backlogs of requests, Excel users have found that 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, you’ve probably been able to 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.
You’ve taken the right step by purchasing this book. I can help you get up the learning curve so that you can write your own VBA macros and put an end to the burden of generating reports manually.
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 efficiently work with ranges and cells.
By the time you get to Chapter 4, you will know enough to put to immediate use the 25 sample user-defined functions in that chapter.
Chapter 5 covers the power of looping using VBA. In Valerie’s case study, after we wrote the program to produce the first department report, it took only another minute to wrap that report routine in a loop that produced all 46 reports.
Chapter 6 covers R1C1-style formulas. Chapter 7 takes a look at what changed in Excel VBA from Excel 2003 to Excel 2007. In the past, it was fairly easy to create VBA code that would run on any of the recent versions of Excel. Unfortunately, with the sweeping changes in Excel 2007, this will become significantly more difficult. Chapter 8 covers names. Chapter 9 has 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.
Chapters 11 through 13 provide an in-depth look at charting, Advanced Filter, and pivot tables. Any report automation tool is going to rely heavily on these concepts.
Chapter 14 includes another 25 code samples designed to exhibit the power of Excel VBA.
Chapters 15 through 18 handle data visualizations, Web queries, XML, and automating another Office program such as Word.
Chapter 19 shows you 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 yet keep the friendly front end of Excel.
Chapter 22 covers VBA from the point of view of a Visual Basic programmer. It teaches you about classes and collections. Chapter 23 discusses advanced userform topics. Chapter 24 teaches you 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.
Microsoft believes the average Office user touches only 10 percent of the features in Office. I realize everyone reading this book is above average. I think that I have a pretty smart audience at MrExcel.com. 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 do a Power Excel seminar for accountants. These are hard-core Excelers who use Excel 30 to 40 hours every week. Again, 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, I am routinely trumped by someone in the audience. Someone will ask a question, I will answer, and someone in the second row will raise a hand and give a better answer. The point? You and I both really know a lot about Excel. However, I will assume that in any given chapter, maybe 58 percent of the people haven’t used pivot tables before and maybe even less have used the “Top 10 Filter” feature of pivot tables. Before I show you 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 that you might want to go explore something and learn it elsewhere.
Four years ago, there were a lot of rumblings that Microsoft might stop supporting VBA. There is now a lot of evidence that VBA will be around in Windows versions of Excel through 2015. (The future is not so certain for the Macintosh version of Excel.) Microsoft Office Excel 2007 was released on January 30, 2007. Microsoft is saying that in the next version of Excel (Excel 14), it will stop providing support for XLM macros. These macros were replaced by VBA 14 years ago, but they are still being supported. At the 2005 MVP Summit, members of the Office development team predicted support for VBA for another 10 to 15 years. There is even talk of an improvement to the Visual Basic Editor in Excel 14.
Still, 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. In Excel 2007, the macro recorder works for about 50 percent of charting commands but fails to record a significant amount of charting.
The tools that you learn today will be good for the next 10 years. If Microsoft decides to scrap VBA in favor of Visual Studio Tools for Office (VSTO) or some other tool, you will likely be able to transfer your coding skills to the new platform.
This second edition of VBA and Macros for Microsoft Office Excel 2007 is designed to work with Excel 2007. Our previous edition covered code for Excel 97 through Excel 2003. In 80 percent of the chapters, the code for Excel 2007 will be identical to code in previous versions. There are exceptions. Microsoft offers new sorting logic. Charts have changed completely. The conditional formatting and data visualization tools in Chapter 15 are brand new. Pivot tables have changed slightly. The XML examples in Chapter 17 will only work with Excel 2003 or newer. 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 www.mrexcel.com/macvba.html.
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 filenamesItalic monospace—. Indicates placeholder text in code syntaxBold monospace—. Indicates user input
In addition to these text conventions, there are also several special elements. Each chapter has at least one case study, which shows you real-world solutions to common problems and practical applications of topics discussed in the text. In addition to these case studies, you will also see New icons, Notes, Tips, and Cautions.
Features which are new or significantly different in Excel 2007 are marked with this icon.
Note
Notes provide additional information outside the main thread of the chapter discussion that might still be useful for you to know.
As a thank-you for buying this book, the authors have put together a set of 50 Excel workbooks demonstrating the concepts in this book. This set of files includes all of the code from the book, sample data, additional notes from the authors, plus 25 bonus macros. To download the code files, visit this book’s page at www.quepublishing.com or www.mrexcel.com/getcode2007.html.
Chapter 1 introduces the editing tools of the Visual Basic environment and shows you why using the macro recorder is not an effective way to write VBA macro code.