Read an Excerpt
Office VBA Macros You Can Use Today
Over 100 Amazing Ways to Automate Word, Excel, Power Point, Outlook and Access
By Juan Pablo González, Cindy Meister, Suat Ozgur, Bill Dilworth, Nico Altink
Holy Macro! BooksCopyright © 2006 Holy Macro! Books
All rights reserved.
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:
This term is generally used to refer to the text of the program language.
Visual Basic for
The programming language used to write programs in Microsoft Office Applications (VBA) applications.
Visual Basic Editor (VBE) The environment (or window) in which VBA code is written.
Entities that perform a task and in which the code is written; also called macros. Think of them as containers for code.
A container for Procedures. A Module can hold many Procedures or just one.
A container for modules. All of the VBA code in any single workbook, document, database, presentation, or Outlook item is called a project.
This is a part of the code that is preceded by special characters so that it is seen as text and will not run. Placing an apostrophe as the first character of the line "tells" VBA that this line is a comment.
Good coders heavily comment their code so that it can be more easily edited when necessary.
When a Microsoft Office program opens, any installed add-ins also open.
Add-ins are special programs that extend the built-in features of the program in some way.
Thus, there are sometimes more objects open within the application environment than you might think. Not all of these objects have a visible presence in the standard interface, but they are all at least partially exposed in the programming environment.
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.
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.
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.
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.
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.
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:
1. Select View | Project Explorer from the VBE Toolbar.
2. Click the Project Explorer icon [??] on the VBE Toolbar.
3. 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:
Lists every worksheet that is in the workbook
within the Microsoft Excel Objects group.
ThisWorkbook is listed as well. In the macro
examples, the specialized code that can be placed
in these sections is illustrated.
Lists Microsoft Word Objects where ThisDocument
is listed. It operates in a similar fashion
Microsoft Lists Microsoft Outlook Objects where
ThisOutlookSession is listed. It operates
in a similar fashion to ThisWorkbook, also.
Microsoft Lists no Microsoft Access Objects group.
There is no ThisDatabase object.
Microsoft Does not list an Objects group.
PowerPoint There is no ThisPresentation object, which has long
frustrated those trying to learn how to use
VBA with PowerPoint.
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 Figure 5. Alternatively, press the down arrow on the Insert button [??] and select 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.
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 easier.
To rename a module, first make sure that the Properties window is visible. By default, it is located at the lower-left of the VBE, and is shown in Figure 7.
The Properties window should be visible by default. If it is not, it can be shown by any of following methods:
1. Select View | Properties Window from the VBE Toolbar.
2. Click the Properties Window button [??] on the VBE Toolbar.
3. Use the shortcut key, F4.
Change the name of the module by typing the desired name into the Name field. The name must start with a letter, cannot be more than 31 characters long, and cannot contain any spaces. Also, most symbols cannot be used. For the illustration in the next section, we renamed the module from Module1 to MyCodeModule. When the name in the Properties window is changed, the Project Explorer is automatically updated with the new name.
Using the Main Code Window
Now that we have gone through the objects that can appear in the Project Explorer, it is time to see the actual window where code for modules is displayed, edited, and debugged.
To work with an existing module, first select it by clicking on its Name in the Project Explorer. You can then open it using any of the following methods:
* Double-click it.
* Click the View Code button [??] from the Project Explorer.
* Select View | Code from the VBE Toolbar.
* Use the shortcut key, F7.
At the top of the Main Code window, you will see two drop-downs. The drop-down on the left lists various objects within the module. The drop-down on the right lists the different procedures within the module. Refer to Figure 9 and Figure 10.
When you write multiple procedures, you can use these drop-downs to navigate directly to the start of any procedure. Think of them as menus for the module.
There are three windows in the VBE that we will not discuss: the Immediate window, the Watch window, and the Locals window. These windows are used in specific instances, and are not necessary to operate the macros in this book.
Protecting Your Projects
You can protect a VBA project so the user cannot show the worksheets directly from the Visual Basic Editor. To do so, go to Tools | VBA Project Properties. ... The dialog that launches has a Protect tab. On this tab, check the box to Lock project for viewing; then press Enter and confirm the password to be used.CHAPTER 2
By Juan Pablo González
Printing All Files
Use this procedure to print all the available workbooks within a directory without having to open and print each one separately.
Scenario: This macro is useful when there is a list of files (for instance, sales reports from different regions or statistics from all the departments in the company) within a folder and/or its subfolders, and printing them out quickly is desired.
Example file: E001.xls
View the Appendix to learn about storing this procedure in a Standard module.
You can change the information in the 'Change the following variables' section by changing the path in which to look for the files and setting the
LookInSubFolders to True or False, depending on whether the subfolders should also be searched.
Saving a Workbook as Today's Date
This macro saves the workbook using today's date as the base name.
Scenario: This macro automates the creation of period reports that can be easily identified by the file name.
Example file: E002.xls
View the Appendix to learn about storing this procedure in a Standard module or in ThisWorkbook.
In the 'Change the following variables' section, there are three variables that you can change:
* Path: where the file will be saved
* DateFormat: the format to use for the file name
* Append: in case adding something to the file name of the workbook (such as 'Sales for') is desired
See Excel's Help topic, 'About number formats', to see examples on formatting dates.
As the code is written, these three variables are entered into cells B1, B2, and B3. The location of the input cells can be changed as long as you reflect that change in the code. The user then can change the values of those input cells but doesn't need to change any of the VBA code. So, you could write this code in your workbook and let others use it without ever entering the VBE.
Highlighting Duplicates Within a Range
Use this procedure to highlight any duplicate entries that appear within a contiguous range (not only a column).
Scenario: This macro enables the user to visually highlight any duplicates entries within a range of cells. It is particularly useful when consolidating information from accounts or when trying to pinpoint items that appear more than once in a long list.
Example file: E003.xls
View the Appendix to learn how to store this procedure in a Standard module.
This macro has only two variables. As it is written, it works only on the range A1:A13. Change the range A1:A13 to your desired range. Or, you can change it to work on a range selected by the user. The Interior.ColorIndex = 38 can be set to a different color by changing the 38 to a different number.
This macro erases any conditional formats on the range that gets checked.
With this procedure, you can sort the worksheets of the current workbook — all or just the selected ones — in ascending or descending order, according to their names.
Scenario: There are situations in which a workbook might contain quite a few worksheets, making it very hard to find a specific one. Sorting them alphabetically might alleviate the problem. Another example might be a workbook that contains one sheet for each employee in the company. This workbook would be much easier to use if its sheets were sorted alphabetically.
Example file: E004.xls
View the Appendix to learn how to store this procedure in a Standard module.
There is one variable that can be changed in this workbook: change the sort order from ascending to descending depending on your needs. To do this, change the Ascending variable from True to False. This can be done via cell B1 of the example file.
The macro will still work if the user selects only some of the sheets to sort, as long as they are next to one another (contiguous).
Generating a Unique List
Using a given range, this macro creates a subset containing only the unique records within it.
Scenario: Whenever there is a database or a list of records, such as inventory parts, customer information, employee information, or account numbers, obtaining a unique list to perform analysis of the data is often required.
Example file: E005.xls
View the Appendix to learn how to store this procedure in a Standard module.
There is one variable that can be changed in the 'Change the following variables' section: the range. Inside the code there are three examples of how to set this variable:
1. Use a predefined range.
2. Use the currently selected range.
3. Ask the user to input a range (this is the option enabled in the code as written).
To enable one of the other two, place an apostrophe in front of the code lines for the one currently in use, and then remove the apostrophe in front of the code line for the option desired.
Excerpted from Office VBA Macros You Can Use Today by Juan Pablo González, Cindy Meister, Suat Ozgur, Bill Dilworth, Nico Altink. Copyright © 2006 Holy Macro! Books. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.