| It doesn't matter which Office program you're working in ”it could be Word, it could be Excel, it could be PowerPoint ”a few times a day, you probably find yourself performing some chore that either you've done dozens of times in the past or that you have to repeat a bunch of times in a row. It could be typing and formatting a section of text, running a series of menu commands, or editing a document in a particular way. If you're like me, when faced with these repetitive chores, you probably find yourself wishing there was some way to ease the drudgery and reduce the time taken by this mindless but necessary work. Sure, most of the Office applications have a Repeat command on the Edit menu that lets you repeat your most recent action. That's handy, but it only repeats a single action. If you need to repeat two or more actions, this solution won't work. What's a person to do about this? Well, what if I told you that it was possible to automate just about any routine and repetitive task? What if I told you that it was possible to take this automated task and run it immediately simply by selecting a command or even by just pressing a key or clicking a toolbar button? It sounds too good to be true, I know, but that's just what Visual Basic for Applications (VBA) can do for you. You use VBA to create something called a macro , which is really just a list of tasks that you want a program to perform. Therfore, a macro is not unlike a recipe, which is a set of instructions that tells you what tasks to perform to cook or bake something. A macro is a set of instructions that tells a program (such Word or Excel) what tasks to perform to accomplish some goal. The big difference, however, is that a macro combines all these instructions into a single script that you can invoke using a menu command, a toolbar button, or a keystroke. In this sense, then, a macro isn't so much like a recipe for, say, how to bake bread, but is more akin to a bread machine which, once it has been loaded with ingredients , bakes a loaf with the push of a button. This list of instructions is composed mostly of macro statements . Some of these statements perform specific macro- related tasks, but most correspond to the underlying application's menu commands and dialog box options. For example, in any application, you can close the current (active) window by selecting the File menu's Close command. In a VBA macro, the following statement does the same thing: ActiveWindow.Close What Does VBA Have to Do with Macros? VBA is a programming environment designed specifically for application macros. That sounds intimidating, I'm sure, but VBA's biggest advantage is that it's just plain easier to use than most programming languages. If you don't want to do any programming, VBA enables you to record macros and attach them to buttons , either inside a document or on a menu or toolbar. You can also create dialog boxes by simply drawing the appropriate controls onto a document. Other visual tools enable you to customize menus and toolbars as well, so you have everything you need to create simple scripts without writing a line of code. Of course, if you want to truly unleash VBA's capabilities, you'll need to augment your interface with programming code. That sounds pretty fancy, but the VBA language is constructed in such a way that it's fairly easy to get started and to figure things out as you go along. More than any other programming language, VBA enables you to do productive things without a huge learning curve. Understanding VBA Procedures Before you get to the nitty-gritty of recording your first macro, let's take a second to understand what exactly you'll be recording. In a nutshell , when you record a macro (or, as you'll see in Chapter 2, "Writing Your Own Macros," when you create VBA code by hand), what you're creating is something called a procedure . In VBA, a procedure is, broadly speaking, a collection of related statements that forms a unit and performs some kind of task. For our purposes in this book, VBA procedures come in two flavors: command macros and user -defined functions. Here's a summary of the differences: -
Command macros (which are also known as Sub procedures , for reasons that will become clear in Chapter 2) are the most common types of procedures; they usually contain statements that are the equivalent of menu options and other program commands. The distinguishing feature of command macros is that, like regular application commands, they have an effect on their surroundings. (In Word, for example, this means the macro affects the current document, a section of text, and so on.) Whether it's formatting some text, printing a document, or creating custom menus, command macros change things. I show you how to create command macros in the section of Chapter 2 entitled "Writing Your Own Command Macro." -
User-defined functions (also called Function procedures ) work just like a program's built-in functions. Their distinguishing characteristic is that they accept input values ”called arguments ”and then manipulate those values and return a result. A properly designed function has no effect on the current environment. I show you how to create these functions in the section of Chapter 2 entitled "Creating User-Defined Functions with VBA." |
