1.2 Macros and Visual Basic for Applications
Before you start writing your own macros, you should have a good understanding of what a macro is and how a Visual Basic for Applications (VBA) programmed macro differs from a recorded macro.
What Is a Macro?
Eventually, you'll notice that you execute the same sequences of actions in Excel over and over in order to accomplish routine tasks. Rather than repeat the sequence of actions every time you want to perform a task, you can create a macro to make Excel execute the sequence of actions on its own. Macros enable you to perform a single action that accomplishes the same task that would otherwise require you to perform several actions manually.
Recorded command sequences were originally called macro-commands; modern usage has shortened the term to the simpler macro. (The term macro is found as a prefix for several words in the English language; it is derived from the Greek word meaning enlarged or elongated.) In the context of computers and application software, macro is always understood to mean a macro-command.
Macros provide benefits other than convenience. Because computers are much better suited to performing repetitive tasks than human beings, recording repeated sequences of commands in a macro increases the accuracy of the work you perform, as well as the speed. After you record the correct series of commands, you can count on the computer to repeat the sequence flawlessly each time it executes the macro. Another benefit from using macros is that a human operator is not usually needed while a macro executes. If the macro is particularly long, or performs operations that require substantial computer processing time (such as database queries and sorts), you are free to leave the computer and do something else, or to switch to a different application and continue working on some other task.
Excel's Macro Recorder records all of your actions – including mistakes and false starts. When Excel plays back your macro, it performs each recorded action in the exact sequence you originally performed it.
Without the capability to edit or alter your macros, you're stuck with a couple of serious drawbacks: If you record a lengthy series of actions that contain a minor mistake, the only way to remove the mistake is to record the macro all over again. Also, if you need to make a minor change in a long macro, you have to re-record the entire macro. Often, re-recording a long macro simply leads to additional mistakes in the new recording. Fortunately, Excel provides a relatively easy means of editing, altering, or enhancing the macros you record.
What is Visual Basic for Applications?
Visual Basic for Applications (VBA) is a complete programming language that comes from a background with a history almost as long as that of the entire computer industry. Visual Basic for Applications is a modern dialect of the BASIC programming language that was first developed in the early 1960s. (BASIC is an acronym for Beginner's All-Purpose Symbolic Instruction Code.) Although, by today's standards, the original BASIC programming language was severely limited, it was easy to learn and understand, and rapidly became very widespread.
VBA in Excel is the means by which Excel records macros – your actions are recorded using the VBA programming language. Using VBA in Excel makes it possible for you to edit or alter the macros that you record. A thorough knowledge of VBA enables you to create powerful and sophisticated applications for working in Excel.
VBA is also found in all of the applications in Microsoft Office – Access, Word, Outlook, PowerPoint, and so on. By implementing VBA in all of its products, Microsoft has eliminated the need to learn more than one programming language to automate or enhance any of its products. Almost all of what you learn about VBA in Excel will apply to other Microsoft Office products.
Each application that includes Visual Basic for Applications is called a VBA host application. Because each host application (Excel, Word, Access, and so on) adds features to VBA that pertain only to that host application, there are several distinct "flavors" of VBA. When referring to VBA features specific to a particular host application, this book uses the terms Excel VBA, Word VBA, Access VBA, and so on to distinguish the particular implementation of Visual Basic for Applications under discussion. This book uses the single term VBA when describing features common to Visual Basic for Applications in all host applications.
VBA macro programs are stored in a file format used by the application you wrote or recorded the Visual Basic for Applications macro in. For example, VBA macro programs created in Excel are stored in an Excel workbook file, VBA programs created in Word are stored in a document or template file, and VBA programs in Access are stored in an Access database file.
To run a Visual Basic for Applications macro program, you must start it by using the application you wrote the macro in. For example, you cannot start an Excel VBA macro from any program other than Excel – although another application could use VBA Automation to cause Excel to execute a particular macro. Although the core features of VBA remain the same in each application, each different application adds special commands and objects (depending on the specific application) to Visual Basic for Applications.
For example, VBA in Excel contains many commands that pertain only to worksheets and the tasks that you can perform with a worksheet. Similarly, VBA in Word contains commands that pertain only to manipulating the text in a document, whereas VBA in Access contains commands that pertain only to database manipulation, and so on. You'll see some examples of VBA used with other applications in the advanced topics towards the end of this book.