Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access

By Juan Pablo González, Cindy Meister, Suat Ozgur and 3 more

For any Microsoft Office user familiar with Word, Excel, PowerPoint, Access, or Outlook, this book provides tutorials on the tools and tricks that automate these programs for increased productivity. Each lesson includes instructions and a downloadable file on which to test the VBA macros and procedures provided. Tasks covered include how to run several mail merges at once, removing duplicates from data, and saving email attachments.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Jan 1, 2006
• ISBN: 9781615473045

Book preview

versions.

Introducing the Visual Basic Editor

Understanding a concept begins with defining unfamiliar terms. The terminology required to understand Visual Basic for Applications (VBA) is fairly straightforward and is introduced throughout the book. In order to start learning, you need to become familiar with the following terms:

Accessing Visual Basic Editor (VBE)

The VBE (the programming environment) can be accessed from any of the Microsoft Office programs by opening the program and then either pressing Tools | Macro | Visual Basic Editor from the Menu or using the keyboard shortcut: Alt+F11. Clicking the right mouse button on some objects in the Microsoft Office programs gives the option to View Code; others have the Visual Basic Editor as an option on the drop-down menu. Any of the methods mentioned can be used to open the VBE.

For consistency, we use the VBE for Microsoft Excel to illustrate the functionality of the VBE in this book. When one of the other applications varies from the way Excel uses the VBE, we note this exception.

When the VBE opens, it pops up in a new window. An example for Excel is shown in Figure 1.

Figure 1 –Microsoft Excel Visual Basic Editor

The VBE window is just like any other window. It has a Title bar, a Menu bar, and a Tool bar, and likely contains some familiar options or icons, and some that you might not have seen before. The main body of the VBE window consists of one or more sub-windows.

Each individual Office program launches its own VBE. When running multiple programs, multiple VBE windows can be open at the same time without interfering with each other. The coding in any window is relevant only to the program that launched it.

Touring the VBE Toolbar

Figure 2 depicts the Excel VBE Menu bar and Standard toolbar, collectively called the Visual Basic Editor toolbar in this book. You can perform various tasks from this toolbar when using or writing code.

Figure 2 – The VBE Toolbar

Some of the buttons available on the VBE toolbar are described in the table below.

Setting VBE Options

The VBE has a set of options that allow for customizing the interface to individual user preferences. From the VBE Toolbar, select Tools | Options to display the Option dialog shown in Figure 3.

Figure 3 – The VBE Options Menu

The Options Dialog in the VBE affords users the opportunity to modify the way the VBE looks and behaves. From the Editor tab, make sure that all the checkboxes are checked. While the individual settings are not covered in detail in this book, we do recommend checking the box for Require Variable Declaration. It is not checked by default, but it is good programming practice to enable this option.

Tip:

For those interested, more detail and online VBA Training can be found online at www.VBAExpress.com, as well as other websites.

Using Project Explorer

To navigate within the various elements in the programming environment, there is one very useful window: the Project Explorer. By default, it is located at the top-left of the VBE; it is shown in Figure 4.

Figure 4 – The Project Explorer Window

The Project Explorer window should be visible by default. If it is not, or if you accidentally close it, it can be shown by any of the following methods:

Select View | Project Explorer from the VBE Toolbar.

on the VBE Toolbar.

Use the Ctrl+R keyboard shortcut.

The Project Explorer is much like any other Windows Explorer interface. It shows a hierarchy of objects from which to choose. Branches to expand and/or choose elements to view in detail in one of the other panes are shown with the familiar + or –.

Understanding VBA Project

The first item in the Project Explorer (see Figure 4) is called VBAProject(Book1). Book1 is simply the name of the Excel workbook being used in the screen capture. When the workbook is saved, the name changes to the name and file extension of the workbook. For example, if the workbook were saved as MyWorkbook.xls, then the Project Explorer window would also change to display VBAProject(MyWorkbook.xls). If multiple workbooks are open, each one is listed in the Project Explorer alphabetically, as well as any loaded Add-ins. For simplicity, we assume that just one workbook is open at this time.

Word displays the Normal project as open in the VBE if a document is open. It loads when Word is launched. The file name of the Normal project is normal.dot and it is Word’s default template; it is always in use when Word is in use, much like an Add-in.

The first item within the VBAProject is Microsoft Excel Objects. As with Windows Explorer, any of the items with a plus (+) or a minus (–) can be expanded or retracted by double-clicking on the name or by single-clicking the + or –.

These are some of the primary differences in the user interface between the five applications:

Working with Modules

VBA has several places in which to store code, but the vast majority of code is stored in Standard modules, regardless of the application. Before a module can be used, it must first be added to the project. From the VBE Toolbar, select Insert | Module as shown in and select Module.

Figure 5 – Inserting a Module

The only limitation to the number of modules an Office file can have is the limitation imposed by the amount of the computer’s memory. Each module can have one or many procedures. Writing a project using modules to group similar procedures together helps keep your project better organized.

In this book, the word module is used to indicate a Standard module, though the word Standard is rarely used to describe it; it’s just called a module. Class modules can also be used in VBA Projects; these are referred to as Class modules in this text.

In our next graphic, the project is expanded to display the contents. Double-click to hide the contents. Click on the Toggle Folders button to display the projects as folders, as shown in Figure 6.

Figure 6 – Toggle Folders View

Once the module is inserted, be sure to give it a meaningful name. If you leave the name as Module 1, when the project is revised later there will be no immediate clue as to the content of that module. Applying meaningful, descriptive names to modules makes troubleshooting or retooling much