Introduction

CONGRATULATIONS ON MAKING TWO EXCELLENT CHOICES! You want to learn programming for Microsoft Excel with Visual Basic for Applications (VBA), and you've purchased this book to teach you. Excel is the most powerful and widely used spreadsheet application in the world. VBA enables you to become much more productive and efficient, while getting your everyday Excel tasks done more quickly and with fewer errors. You'll gain a programming skill that is in high demand, which will improve your value in the workplace and your marketability when searching for employment.

This book covers VBA from the ground up, and assumes you have never programmed Excel before. If you've never recorded or written an Excel macro, this book shows you how. If you've worked with VBA before, this book has examples of programming techniques you might not have seen. The instruction and examples in this book teach VBA concepts that range in levels from fundamental to advanced. The techniques in this book apply just as well to the Excel business power user as to the keeper of the family budget.

VBA is the programming language for Microsoft's popular Office suite of applications, including Excel, Word, Access, PowerPoint, and Outlook. A full section of this book explains how to control each of those applications from Excel with VBA. By the time you complete this book, you will have learned how to record, write, and run your own macros. You'll learn how to make VBA run itself by programming Excel to monitor and respond to users' actions, and how to create friendly, customized interfaces that the users of your workbooks will enjoy.

The future of VBA is solid. Microsoft has confirmed time and again that VBA will be supported in versions of Excel into the foreseeable future, and the programming skills you learn in this book will serve you throughout your career. You'll be able to apply the principles you learn in this book to other tasks that can be automated in Excel and Microsoft's other Office applications. VBA is an enormous programming language, and when combined with Excel, using it is an ongoing, rewarding process of learning something new every day. With this book as your entry into the world of VBA programming, you are well on your way.

Who This Book Is For

This book is for Excel users who have never programmed Excel before. You are an Excel user who has been doing a frequent task manually, and you are ready to automate the task with VBA. You might also be a job seeker, and you want to improve your chances of being hired in this difficult job market by learning a valuable skill. Whether your Excel tasks are large or small, this book is for you. You find out how to use VBA to automate your work by doing anything from recording a simple one-line macro to writing a complex program with a customized, user-friendly interface that will look nothing like Excel. This book has something for everyone, but especially for the person who wants to dive right into VBA from square one and learn to use its powerful programming tools.

What This Book Covers

This book contains 33 lessons, which are broken into five parts:

• Part I, Understanding the BASICs: Part I includes Lessons 1 to 4, introducing you to VBA by providing a historical background and a discussion of what VBA is and what it can do for you. This part familiarizes you with the Macro Recorder and the Visual Basic Editor, where VBA code is maintained.
• Part II, Diving Deeper Into VBA: Part II includes Lessons 5 to 9, which discuss VBA topics including an overview of object-oriented programming, variable declaration, objects and collections, arrays, and options for decision-making.
• Part III, Beyond the Macro Recorder: Writing Your Own Code: Part III includes Lessons 10 to 20. You learn how to write your own macros without help from the Macro Recorder. You become familiar with loops, event programming at the workbook and worksheet levels, charts, PivotTables, user-defined functions, and embedded controls. You learn to program formulas and how to debug your VBA code.
• Part IV, Advanced Programming Techniques: Part IV includes Lessons 21 to 28, and deals with the more advanced topics of UserForms, class modules, add-ins, retrieving external data, and various examples of programming Excel to achieve solutions you might not have thought possible.
• Part V, Interacting with Other Office Applications: Part V includes Lessons 29 to 33, dealing with how to control Word, Outlook, Access, and PowerPoint from Excel.

How This Book Is Structured

My main principle in this book is to teach you what you need to know in VBA. I tried to write this book as if you and I were sitting down in front of your computer, and I was explaining Excel and VBA's technical concepts in an informal tutorial session. The book is structured such that each lesson teaches you the theory of a topic, followed by one or more coded examples, with plenty of screenshots and notes to help you follow along. To avoid redundancy of instruction, the lessons build on each other, so the later chapters assume you've read, or are already familiar with, the material discussed in earlier lessons. I strongly recommend that you watch the videos, which you can find at www.wrox.com/go/excelvba24hour. You will get more out of them than you might imagine because they include bonus information about Excel, such as tips and tricks that will help you manage your workbooks with greater ease and efficiency.

What You Need to Use This Book

What you need is this book and a fully installed version of Microsoft Office. If you only have Excel installed, that will suffice for lessons up to and including Lesson 28. Lessons 29 to 33 deal with controlling other Office applications from Excel. VBA ships with Excel, so you already have all the programming tools you need when you installed VBA with Office. The version of your Windows operating system is not important.

In many examples, different versions of Excel are represented, with Excel's latest version at this writing—version 2013—shown most frequently. If you are using Excel version 2003 or before, you can complete almost all the examples in this book, but it will be easier for you to follow along by using a version starting with 2007—ideally with 2010 or 2013. Almost everything discussed in this book has VBA example code to go along with it, with comments in the code (lines of text in VBA code that start with an apostrophe) that explain what the code is doing, and why. Plenty of screenshots help you see beforehand what to expect, and help you after you've tested your code to confirm you followed the steps correctly.

You need one other thing, which only you can control, and that is a quiet period of time for yourself so you can read this book and view its video Try It lessons uninterrupted. Everyone studies and retains new material differently, and we all live in a busy world. But do what you can to carve out some “you time” as you make your way through the book. You'll find a lot of useful material that will lead you to think of other situations you typically encounter in Excel that can be solved with the concepts you'll be learning.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.

WARNING Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

NOTE Notes, tips, hints, tricks, and asides to the current discussion are offset and placed in italic like this.

As for styles in the text:

• We highlight new terms and important words when we introduce them.
• We show filenames, URLs, and code within the text like so: persistence.properties.
• We present code like this: We use a monofont type with no highlighting for most code examples. We use bold to emphasize code that's particularly important in the present context.
• Text that you need to enter as you work through the Try It sections is written as bold code, as shown here:

  Name it cmdExit and caption it as Exit.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com/go/excelvba24hour. The code snippets from the source code are accompanied by a download icon and note indicating the name of the program so you know it's available for download and can easily locate it in the download file. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

After you download the code, just unzip the file using WinZip or a similar tool. Alternatively, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all...