Visual Basic for Applications (VBA) is a powerful tool that enables you to automate operations in the Microsoft Office applications and in other applications that host VBA. By automating operations using VBA, you can save yourself and your colleagues huge amounts of time and effort. Getting more work done in less time is usually good for your disposition and self-esteem, and it can do wonderful things for your job security and career.
This book shows you how to program VBA, using the Microsoft Office 2010 applications for specific examples. You can apply the principles you learn in this book to any other VBA-enabled application as well.
Throughout this book you'll find many code examples, mostly written in VBA, but in Chapter 31, written in XML. Rather than type in the code, you'll save yourself time (and debugging headaches) if you copy the code from this book's web page and paste it into the VBA Editor (or in Chapter 31, into the Office Custom UI Editor). You can find all the code from this book—accurate, fully tested, and bug free—at this book's website, www.sybex.com/go/masteringvba2010.
You can use VBA to automate almost any action that you can perform interactively (manually) with an Office 2010 application. For example, in Word, you can use VBA to create a document, add text to it, format it, and edit it. In Excel, you can integrate data from multiple workbooks into a single workbook. In PowerPoint, you can automatically create a custom presentation including the latest data drawn from a variety of sources. And in Access you can create new tables and populate them with data.
VBA performs actions faster, more accurately, more reliably, and far more cheaply than any human. As long as you can define firm conditions for making a decision, VBA can also make decisions for you. By adding decision-making structures and loops (repetitions) to your code, you can take it far beyond the range of actions that any human user can perform.
Beyond automating actions you would otherwise perform manually, VBA gives you the tools to create user interfaces for your code—message boxes, input boxes, and user forms, graphical objects that you can use to create forms and custom dialog boxes.
Using VBA, you can also create a custom application that runs within the host application. For example, you could build within PowerPoint a custom application that automatically creates a presentation for you.
In addition, you can use VBA to access one application from another application. For example, Word can't do much in the way of mathematical calculations on sets of data: that's Excel's specialty. So when working with VBA from Word, you can write code that starts an Excel session, performs some calculations, and then puts the results into a Word document. Similarly, you could send objects from Excel to PowerPoint using VBA. You get the picture.
Because VBA provides a standard set of tools that differ in capability according to the specializations of the host application, once you've learned to use VBA in one application, you'll be able to apply that knowledge quickly to using VBA in another application. For example, you might start by learning VBA in order to manipulate Excel and then move on to using your VBA skills with Outlook. You'll need to learn the components of the Outlook application because they're different from the Excel components, but you'll be able to quickly apply your VBA knowledge without a problem.
As with any programming language, getting started with VBA involves a learning curve—but you can use the Macro Recorder tool built into Word and Excel to learn VBA more quickly. This book uses the Macro Recorder as the jumping-off point for you to start creating code. The book takes you through recording macros and then teaches you to edit recorded code before delving into the essentials of VBA syntax. From there, you work your way into more complex topics.
And Word, because it's the most popular Office application and because it has the most sophisticated and efficient programming tools, is used for many of the examples in this book. But there are plenty of examples showing how to program Excel, PowerPoint, Outlook, and even Access. And code that works in one Office 2010 application will generally work with other applications in the suite—with little or sometimes no modification.
This book teaches you how to use VBA to automate your work in Office 2010 applications. For its general examples, the book focuses on Word, Excel, Outlook, and PowerPoint because those are the Microsoft Office applications that you're most likely to have and because they have less eccentric programming tools and strategies than Access. The last part of the book continues the discussion of how to program these four applications, but also increases coverage of Access.
Part 1 of the book, "Recording Macros and Getting Started with VBA," comprises the following chapters:
Chapter 1 shows you how to record a macro using the Macro Recorder in Word and Excel. You also learn several ways to run macros and how to delete them.
Chapter 2 introduces you to the VBA Editor, the application in which you create VBA code (either by editing recorded code or by writing code from scratch) and user forms. The second half of this chapter discusses how you can customize the VBA Editor so that you can work in it more efficiently.
Chapter 3 shows you how to edit recorded macros, using the macros you recorded in Chapter 1. You learn how to step through and test a macro in the VBA Editor.
Chapter 4 teaches you how to start writing code from scratch in the Visual Basic Editor. You create a procedure for Word, one for Excel, and a third for PowerPoint.
Part 2, "Learning How to Work with VBA," contains the following chapters:
Chapter 5 explains the essentials of VBA syntax, giving you a brief overview of the concepts you need to know. You also practice creating statements in the VBA Editor.
Chapter 6 shows you how to work with variables and constants, which are used to store information for your procedures to work on.
Chapter 7 discusses how to use arrays. Arrays are like super-variables that can store multiple pieces of information at the same time.
Chapter 8 teaches you how to find the objects you need to create your procedures. You learn how to correctly write code involving objects by employing the Macro Recorder, the Object Browser, and the Help system. And you see how to use object variables to represent objects. Finally, you explore the uses of object models.
Part 3, "Making Decisions and Using Loops and Functions," consists of the following chapters:
Chapter 9 describes how to use VBA's built-in functions—everything from string-conversion functions through mathematical and date functions to file-management functions.
Chapter 10 shows you how to create functions of your own to supplement the built-in functions. You create functions that work in any VBA-enabled application, together with application-specific functions for Word, Excel, and PowerPoint.
Chapter 11 shows you how to use conditional statements (such as
Ifstatements) to make decisions in your code. Conditional statements are key to making your code flexible.Chapter 12 covers how you can use loops to repeat actions in your procedures: fixed-iteration loops for fixed numbers of repetitions and indefinite loops that match their number of repetitions to conditions you specify. You also learn how to avoid creating infinite loops, which can cause your code to run either forever or until your computer crashes.
Part 4, "Using Message Boxes, Input Boxes, and Dialog Boxes," has the following chapters:
Chapter 13 shows you how to use message boxes to communicate with the users of your procedures and let them make simple decisions about how the procedures run. You also explore input boxes, which are dialog boxes that give the user a way to supply information the procedures need.
Chapter 14 discusses how to use VBA's user forms to create custom dialog boxes that enable the users to supply information, make choices, and direct the flow of your procedures.
Chapter 15 discusses how to build more complex dialog boxes. These include dynamic dialog boxes that update themselves when the user clicks a button, dialog boxes with hidden zones that the user can reveal to access infrequently used options, dialog boxes with multiple pages of information, and dialog boxes with controls that respond to actions the user takes.
Part 5, "Creating Effective Code," contains the following chapters:
Chapter 16 illustrates the benefits of building reusable modular code rather than monolithic procedures and then shows you how to create reusable code.
Chapter 17 explains the principles of debugging VBA code, examining the different kinds of errors that occur and discussing how to deal with them.
Chapter 18 explores how to build well-behaved code that's stable enough to withstand being run under the wrong circumstances and civilized enough to leave the user in the best possible state to continue their work after it finishes running.
Chapter 19 discusses the security mechanisms that Windows and VBA provide for safeguarding VBA code and ensuring that you or your users do not run malevolent code unintentionally. The chapter discusses digital certificates and digital signatures, how to choose an appropriate security setting for the application you're using, and how to manage passwords.
Part 6, "Programming the Office Applications," consists of these 12 chapters:
Chapter 20 explains the Word object model and shows you how to work with key objects in Word, including the
Documentobject, theSelectionobject, andRangeobjects. You also learn how to set options in Word.Chapter 21 discusses how to work with widely used objects in Word, including the objects for Find and Replace; headers, footers, and page numbers; sections, page setup, windows, and views; and tables.
Chapter 22 introduces you to the Excel object model and shows you how to work with key objects in Excel, including the
Workbookobject, theWorksheetobject, theActiveCellobject, andRangeobjects. You also learn how to set options in Excel.Chapter 23 shows you how to work with charts, windows, and Find and Replace in Excel via VBA.
Chapter 24 gets you started working with the PowerPoint object model and the key objects that it contains. You work with
Presentationobjects,Windowobjects,Slideobjects, andMasterobjects.Chapter 25 teaches you how to go further with VBA in PowerPoint by working with shapes, headers and footers, and the VBA objects that enable you to set up and run a slide show automatically.
Chapter 26 introduces you to the Outlook object model and the key objects that it contains. You meet Outlook's creatable objects and main interface items; learn general methods for working with Outlook objects; and work with messages, calendar items, tasks and task requests, and searches.
Chapter 27 shows you how to work with events in Outlook. There are two types of events, application-level events and item-level events, which you can program to respond to both Outlook actions (such as new mail arriving) and user actions (such as creating a new contact).
Chapter 28 familiarizes you with the Access object model and demonstrates how to perform certain key actions with some of its main objects.
Chapter 29 shows you how to manipulate the data in an Access database via VBA.
Chapter 30 shows you how to communicate between applications via VBA. You learn which tools are available, how to use Automation, how to use the
Shellfunction, and how to use data objects, DDE, andSendKeys.Chapter 31 explores the various ways you can customize the Ribbon programmatically. It's not possible to customize it by VBA code alone. Instead, you must write XML code to modify what the user sees on the Ribbon and write callbacks (event handler procedures in VBA) to respond when the user clicks one of the buttons or other controls you've added to the Ribbon. You see how to modify tabs, groups, and individual controls—in Word, PowerPoint, Excel, and, using different techniques, in Access.
This book tries to present material in a sensible and logical way. To avoid repeating information unnecessarily, the chapters build on each other, so when you get to the later chapters, it's generally assumed that you've read the earlier ones.
The first five parts of this book offer a variety of code samples using Word, Excel, PowerPoint, and to a lesser extent, Access. If you have these applications (or some of them), work through these examples as far as possible to get the most benefit from them. While you may be able to apply some of the examples directly to your work, mostly you'll find them illustrative of the techniques and principles discussed, and you'll need to create code of your own that follows those techniques and principles.
The sixth and last part of this book shows you some more advanced techniques that are useful when using VBA to program Word, Excel, PowerPoint, Outlook, and Access. Work through the chapters that cover the application or applications that you want to program with VBA.
Chapter 30 and Chapter 31 are specialized, but quite useful. Chapter 30 shows you how to use one application to control another application; for example, you might use Word to control Excel. And Chapter 31 shows you many different ways to program the Ribbon—the primary user interface in Office 2010 applications.
Yes. This book is for anyone who wants to learn to use VBA to automate their work in a VBA-enabled application. Automating your work could involve anything from creating a few simple procedures that would enable you to perform some complex and tedious operations via a single keystroke to building a custom application with a complete interface that looks nothing like the host application's regular interface.
This book attempts to present theoretical material in as practical a context as possible by including lots of examples of the theory in action. For example, when you learn about loops, you execute short procedures that illustrate the use of each kind of loop so that you can see them at work right away. And you'll also find many step-throughs—numbered lists that take you through a task, one step at a time. And, above all, I've tried to make this book clear and understandable, even to readers who've never written a program in their life.
This book uses several conventions to convey information succinctly:
+ signs indicate key combinations. For example, "press Ctrl+Shift+F9" means that you should simultaneously hold down the Ctrl, Shift, and F9 keys. Also, you'll often see this: Press Ctrl+F, I. That means, simultaneously press Ctrl and F, then release them and press I.
Some of these key combinations can be confusing at first (for example, "Ctrl++" means that you hold down Ctrl and press the + key—in other words, hold down Ctrl and Shift together and press the = key), so you may need to read them carefully.
Likewise, "Shift+click" means that you should hold down the Shift key as you click with the mouse, and "Ctrl+click" means that you should hold down the Ctrl key as you click.
↑→↓← represent the arrow keys on your keyboard. The important thing to note is that ← does not mean the Backspace key (which on many keyboards bears a similar arrow). The Backspace key is indicated simply by the words Backspace or the Backspace key.
Boldface indicates items that you are to type in.
Program fontindicates program items, or text derived from program lines. Complete program lines appear offset in separate paragraphs like the following example, while shorter expressions appear as part of the main text.Sub Sample_Listing() 'lines of program code look like this. End SubItalic text usually indicates either new terms being introduced or variable information (such as a drive letter that will vary from computer to computer and that you'll need to substitute for your own).
_ (a continuation underline character) indicates that a single line of code has been broken onto a second or subsequent line in the book (because of the limitations of page size). In the VBA Editor, you should enter these "broken" lines of code as a single line. For example, in this code sample, a single line of VBA Editor code has been broken into three lines when printed in this book:
MsgBox System.PrivateProfileString("", _ "HKEY_CURRENT_USERSoftwareMicrosoft _ Office11.0CommonAutoCorrect", "Path")You'll also see sidebars throughout the book. These include asides, notes, tips, and warnings. They're a bit like footnotes, though less tedious. Each sidebar, no matter how small, has a headline—so you can quickly see if you want to read the sidebar, or if you already know that information.
Finally, each chapter includes one, longer, real-world sidebar: a case study, an important practical technique, or some other useful advice.