Managing VBA Projects
The preceding three VBA chapters gave you an eagle's-eye view of how to put macros to work in your office. After you move beyond the fundamentals of VBA, becoming truly productive is mostly a question of mastering the vocabulary. In the case of Office XP, that means learning the underlying object models of each of the Office applications. Like any language, VBA gets easier with practice. As you work with VBA and build up a library of macros and code that you want to reuse, managing the collection becomes a challenge.
Storing Projects
When you begin writing macros in Word and Excel, it's easiest to store them in the projects provided by the macro recorder. In Word, that's the Normal project (by default), stored in Normal.dot, or in the current document. Excel reverses the defaults: A recorded macro goes into either the currently open workbook (by default), or the Personal Workbook known as Personal.xls.
Although those locations might be convenient for novices, they're far from optimal. If you want to move your macros from one machine to another, for example, copying Normal.dot or Personal.xls to the new machine will change settings, wipe out existing recorded macros, and generally cause all sorts of headaches.
Caution
Here's another good reason not to use the default macro storage locations. Some viruses actually lock Normal.dot and Personal.xls with a password. (In fact, an old Microsoft antivirus product, called Scanprot, password protects Normal.dot.) If you have important macros stored in either of these files and you can't open the file because a virus encrypted them with a password, your macros are lost—and there's nothing you can do to retrieve them.
It's strongly recommended that you store your VBA projects outside of Normal.dot and Personal.xls.
To create a new Word template for macros and make it available for all documents, follow these steps:
Choose File, New. Click the Blank Document icon, and choose the Template option under Create New. Click OK. Word creates a new template.
Choose File, Save. Navigate to Word's Startup folder (look in Tools, Options, File Locations for the folder's location), give the template a name, and save the file (see Figure 41.1).
Figure 41.1. Create a new template in the Startup directory to hold all your macros. Give it a descriptive name such as Macros.dot.
Remember that you must open this new template in Word before the VBA Editor will enable you to make changes to it.
Follow these steps to create a default workbook for storing Excel macros:
Start with a clean, empty workbook.
Choose File, Save. Navigate to the XlStart folder (typically C:Program FilesMicrosoft OfficeOffice10Xlstart), give the workbook a name such as Macros.xls, and save the file (see Figure 41.2).
Figure 41.2. After creating a workbook to store Excel macros, file it in the XlStart folder and mark it as hidden.
Excel automatically opens any workbook in XlStart. To make sure the new workbook doesn't get in the way when you're working with Excel, choose Window, Hide.
Tip from 
Before you can record or edit macros in this new location, you have to explicitly unhide and select Macros.xls. After you've finished working with your macros, you have to hide the sheet again. Add the following macro code to Macros.xls to make it easier to manage this sheet:
Sub SaveAndHideMacrosWorkbook()
'Hides the workbook.
ThisWorkbook.Windows(1).Visible = False
'Saves the workbook after it is hidden.
ThisWorkbook.Save
End Sub
Next, choose Tools, Customize. Click the Commands tab, scroll through the Categories list, and choose Macros. Under Commands, click Custom Menu Item. Drag this item onto the Window menu and place it just below the Unhide choice. Right-click the new menu item and change its Name to Save/Hide Macros Workbook. Right-click again and choose Assign Macro from the bottom of the shortcut menu. Assign the macro you just created to the new menu item and click OK. Now, to make macros available for editing, choose Window, Unhide; after you've finished working with the code in this worksheet, choose Window, Save/Hide Macros Worksheet to save your changes and hide the sheet again.
Neither PowerPoint, Outlook, FrontPage, Publisher, nor Access has analogous startup capabilities.
Locking VBA Projects
Word, Excel, PowerPoint, Outlook, Publisher, FrontPage and Access all have provisions for password-protecting VBA code. The Lock Project for Viewing option prevents users from seeing, modifying, or even copying any of the VBA source code for procedures, modules, and custom dialog boxes in a given project.
Caution
Before locking VBA source code, it's always a good idea to save a copy of the original project without password protection, in a safe location. That way, if you forget the password, you can reconstruct your work.
To lock a project in Word, Excel, PowerPoint, Outlook, FrontPage, or Access, follow these steps:
In Word, Excel, Publisher, or PowerPoint, open the project (template, document, workbook, or presentation). In Access, open the database. In Outlook and FrontPage, you need only start the application.
Press Alt+F11 to start the VBA Editor.
In Project Explorer, right-click the project and select Project Properties. Click the Protection tab.
Check the Lock Project for Viewing box (see Figure 41.3), and provide a password. Click OK, and close the project in the application.
Figure 41.3.
Tip from 
When you lock a project for viewing, the macros still run, but you'll need to supply the password to view, edit, or copy the macros or custom dialog boxes.
Caution
The Lock Project for Viewing password protection scheme is relatively easy to crack. If you really need to keep your source code out of competitors'hands, consider writing your sensitive programs (or parts of programs) in Visual Basic. A compiled VB program has no remnants of source code, and only the most determined and technically proficient hacker can reverse-engineer it.
Access includes the capability to save a database as an MDE file that doesn't include VBA source code. This type of file (which uses the *.mde extension) runs like the original database, but users can't change the VBA modules (or forms or reports, for that matter).
Caution
After converting a database to an MDE file, you can't change it back to a regular database.
To convert a database to an MDE file, first make a copy of the original database, and then choose Tools, Database Utilities, Make MDE File.
Code Signing
Digital signatures form the backbone of Office XP's macro security—in Word, Excel, and PowerPoint, anyway. Digital signatures are also available in Outlook and FrontPage, although they're of limited benefit in those applications because Outlook and FrontPage VBA projects are rarely moved from machine to machine.
A digital signature verifies the identity of the person or organization that created a macro; Office includes features that enable you to preapprove signatures from a particular source.
→ For more information about using digital signatures, see "Using Digital Signatures to Verify a Macro's Source".
If you are writing VBA programs to distribute within your organization, or for general distribution, you must
Acquire a digital certificate
"Sign" your VBA project
Tell the people who will be using your macros what your signature looks like, and what they need to do to get your macros working
Note
If your users have their security setting on High—the default for all Office applications—they won't be able to use your macros, and they won't be told why unless you sign your macros.
→ For more details on setting up security levels to manage macros, see "Configuring Office Security Levels".
You have three options for obtaining a digital certificate:
You can create an unauthenticated certificate by running Selfcert.exe (see Figure 41.4). If you can't find SelfCert.exe on your hard drive, rerun Office XP Setup from the Office CD and click Add or Remove Features. Click the plus sign (+) next to Office Shared Features; click Digital Signature for VBA Projects and then click Run from My Computer. Click Update. This certificate is Stored in the Registry and is required to get past Office Applications'Medium Security Setting. An unauthenticated certificate represents no security at all: Anybody can create an unauthenticated certificate claiming to be Bill Gates, for example. When a user opens a VBA project that's signed with an unauthen ticated certificate, he will always be asked to verify that he trusts the source of the project.
Figure 41.4.
If you work for a large organization, you might be able to get a certificate from your group's certification authority (your network administrator will use Microsoft Certificate Server to generate the file for you).
You can buy an authenticated certificate from VeriSign (http://www.verisign.com) or Thawte (http://www.thawte.com)—look for "developer certs" or "software signing Ids." Avoid Class 2 IDs, which certify the existence of a particular e-mail address—Selfcert.exe works just as well. Class 3 IDs, for organizations, costs $200–$400 per year for the first year (subsequent years are half that price). Microsoft maintains links to other certification authorities at http://officeupdate.microsoft.com/office/redirect/fromOffice9/cert.htm.
To sign a VBA project, follow these steps:
Open the project (template, document, workbook, publication, or presentation) using the associated application.
Press Alt+F11 to start the VBA Editor.
In Project Explorer, highlight the project you want to sign.
Choose Tools, Digital Signatures, Choose (see Figure 41.5). Select either a new signature or change an existing one; click OK.
Figure 41.5.
Note
You can't sign Excel workbooks (that is, projects) that contain old-fashioned Excel "macro" (XLM) code. Digital signatures are valid only with VBA projects. Because the old XLM macros exist only in spreadsheets (and not in VBA), they can't be signed.
Access does not support the use of digital signatures.
Variable Naming Conventions
If you're recording a macro, or making adjustments to a short macro, you probably won't have much trouble following the logic of the code. As macros grow longer, they become increasingly inscrutable, even with extensive inline comments.
Years ago, Microsoft adopted a variable naming convention which, if applied religiously, can help you keep track of a program's logic. Often called Hungarian notation (in a bit of homage to its inventor, Charles Simonyi), it simplifies the process of scanning through programs by applying consistency to the assignment of variable names.
The part of Hungarian notation that's most useful—and the style you'll see in this book—assigns prefixes to variable names that describe the data type. For example, a variable called intNumberOfPlaces is an integer, strResponse is a string, and objExcelWorksheet is an object. Some of the more common Hungarian notation prefixes are listed in Table 41.1.
Thus, you're likely to see variable declarations at the beginning of well-written programs that look like this:
Dim intPointSize as Integer Dim objExcelWorkbook as Object Dim strLastName as String
Where the Dim (or "Dimension") statement tells VBA to set aside storage locations for variables with the given names and attributes.
Arrays of variables follow similar naming conventions. For example, you could set up an array of three different store names like this:
Dim strStoreName(3) strStoreName(1) = "Downtown" strStoreName(2) = "Woodland Park" strStoreName(3) = "Westminster"
Calling Procedures in Other Projects
Many developers create libraries of little programs that they like to use over and over again. You can set up program libraries with reusable routines, but there are two tricks:
The routines in the libraries have to be declared Public—that is, accessible to programs outside the immediate module—so they have Public Sub or Public Function as their first lines.
You must explicitly link the calling project to the library project.
Say you've created a Word template called Library.dot, and you've stored it in the Startup folder so it loads automatically every time Word starts. Library.dot contains a few programs that are declared Public—perhaps a
Public Sub Register(strCourseName, strStudent)
or a
Public Function Calculate(intCourseHours)
Further, let's say you have a Word template called Signup.dot, and you want it to be able to use commands from the library, such as
Register("ECON 101", "John Adams")
curFee = Calculate(4)
Here's how you link the Signup project (that is, template) to the Library project, so those commands will work:
In Word, open Signup.dot. Press Alt+F11 to bring up the VBA Editor.
In the Project Explorer, double-click the project in which you want to use the external commands—in this example, double-click Signup.
Choose Tools, References. Check the box next to the name of the project that contains the code you want to use—in this example, that's Library. (If you can't find the file, click Browse to search through other folders.)
With this link manually established between Signup and Library, the commands that refer to the Library will work.
Rejuvenating VBA
In previous Office versions, VBA would occasionally go out to lunch for no apparent reason—mysterious General Protection Faults occurred, projects wouldn't save correctly, and sometimes entire modules became irretrievably scrambled. Although VBA 6 is not so badly afflicted, three precautions are in order:
If you're using Windows 95, 98, or Me, restart Windows frequently. With hard VBA work, two or three restarts a day is not out of the question. Constant use of VBA seems to make Windows 95 and 98 unstable, and more prone to General Protection Faults. (If you do a lot of program development, consider switching to Windows 2000, which is far more stable.)
If VBA starts producing random, inexplicable errors, uninstall and then reinstall the entire Office suite from the original CDs.
From time to time, create text backups of all your modules and UserForms and save them in a safe place. To do so, select the module or UserForm and choose File, Export File. Choose Basic Files (*.bas) format for modules or Form Files (*.frm) format for UserForms. Both of these formats produce standard text files that you can import into VBA 6 by choosing Files, Import.