Using VBA Macros
Microsoft Project 2003 gives you the ability to automate
Each instruction or command within a macro is executed via a programming language called Visual Basic for Applications (VBA). Although this language is basically a watered-down version of Visual Basic, there is almost no limit to the types of tasks that can be performed within Microsoft Project and even beyond.
In fact, VBA is so powerful that it could almost be used to create standalone applications, aside from executing from within Project. You have the ability to reference binaries written in other languages, as long as it has objects exposed over the Component Object Model (COM).
All macros are stored as snippets of text called modules . These modules can reside in the Global template for use across all projects, or in individual project files. They can also be imported from and exported to Visual Basic style files.
Of course you don't need to be a Visual Basic programmer to make simple macros. Project has the ability to translate all your keystrokes and mouse clicks into corresponding VBA code simply by using the macro recorder and performing the desired task manually.
Preparing to Record
Before recording your macro, it is best to have a plan of all the actions you need to perform. Because Project records all your keystrokes and clicks, any mistakes you make are recorded as well.
You also want to determine beforehand whether you want the macro available from a specific file and not globally. If this is the case,
From the menu within Project, choose Tools, Macro, and then Record New Macro. The Record Macro dialog box appears, as shown in Figure 28.26.
Figure 28.26. The Record Macro dialog prompts you for information about your new macro.
The Visual Basic toolbar provides all the recording flow
Give your new macro a name. Keep in mind that the
Try to be descriptive with your macro
If you want, specify a shortcut key combination that can be used to quickly execute your macro (such as Ctrl+R).
Specify where you want the macro code to be stored, either in the global file or in the current project file. Keep in mind that if you specify the current project, the macro can be executed only when that project file is open.
The reference boxes specify how the macro recorder interprets selection changes on the view sheets. The Absolute options interpret any cell selection literally (such as A5), whereas the Relative options interpret selection changes in relation to the currently selected
Record the Macro
After you click OK on the Record Macro dialog box, Project then
Testing the Macro
It's always good practice to test your macro immediately after recording to see whether any changes need to be made. Open the Macro dialog by clicking the Macro menu in Tools, Macro (or by clicking the Play button on the Visual Basic toolbar).
With your macro selected, click the Run button and watch as Project
Assigning the Macro to a Toolbar Button
For macros that you execute frequently, specifying a shortcut key can sometimes be difficult to remember,
To do so, open the Customize dialog box by selecting Tools, Customize, and select
Figure 28.27. Macros can be dragged from the Customize dialog box to toolbars to create macro buttons.
Drag the desired macro to a
If you frequently use many macros, it may be convenient to create a whole new toolbar for your macro buttons. This can be done in the Customize dialog box on the Toolbars tab.
Using the same technique, you can also add your macros to the menu bar. Just drag the macro from the Customize window to the desired location on the menu bar. The menu automatically pops up as your mouse moves over it.
Any changes to toolbars, buttons, and
When creating a button or menu item from a macro, try to reference only those that reside in the Global template files. If a macro in a specific file is referenced, that file must be open for it to work.
Viewing and Editing VBA Code
When Project records a macro, it actually stores VBA code in a subroutine. You can view and edit this code. In fact, you are not limited to just creating macros; you can also create complex functions, classes, and even
The Office suite provides the convenient and powerful Visual Basic Editor, which can be opened from Project. This editor provides an easy way to write and debug code, transfer objects among open files, and even design Windows forms for your code to interact with the
A specific macro can be opened by clicking the Edit button with the desired macro selected in the Macros dialog box. Open this dialog box by selecting Tools, Macros, and then Macros.
The Project Explorer and Basic Objects
By default, the Project Explorer is located on the
You can view any object in the Explorer simply by double-clicking it. Right-clicking an object brings up a menu with more options, as shown in Figure 28.28.
Figure 28.28. The Project Explorer behaves similarly to Windows Explorer.
The Explorer functions much like the left pane of the Windows Explorer. This makes dragging objects between different files easy and intuitive.
The Properties Window
Below the Project Explorer (by default) lives the Properties window. This window displays the properties of any selected object, whether it is an object in the Explorer window or a button on a user form.
Aside from clicking an object, its settings can also be displayed by selecting it in the top drop-down list of the Properties window, as shown in Figure 28.29.
Figure 28.29. Some properties can be easily changed with a drop-down style list.
Each type of object has its own corresponding properties list, which can contain anything from the object's name to an image for it to display. Generally the property fields will be a text field or a drop-down list.
The actual list itself has two sorting modes, Alphabetical and Categorized. The Categorized mode groups similar properties together for convenience. Both modes display the same list, so it's a matter of preference which one you use.
If ever in doubt of what a property is used for, simply select it and press F1 to bring up the Office help pane.
The Code Frame
The code frame contains all windows of code modules for any object that you have open, as well as the form designer for User Forms. This is where all your actual code and form editing takes place. Notice in Figure 28.30 the two drop-down boxes on the top of the Code windows. The drop-down on the left allows you to navigate through any objects located on the open module, like a form. The one on the right navigates through existing and potential subroutines and functions for the object selected on the right.
Figure 28.30. The Code window allows you to navigate through code, as well as drag controls to a user form.
Designing User Forms
Some VBA programs may require the user to input information, or may need to display pop-ups to the user. The Visual Basic Editor provides an intuitive Windows form editor to design Windows forms.
When you open a form by double-clicking it in the Project Explorer, it is displayed in the Form Designer. You can drag various controls from the controls toolbox (see Figure 28.30) onto your form to build a user interface. After a control is on the form, you can resize it and change its properties in the Properties window to get the desired look and feel.
You can change the properties of the child controls placed on a form by just selecting them and using the Object Properties window to change things such as caption and visibility.
After the control is placed on the form, you can set and view the code behind the control by double-clicking it. You'll notice when you do that the left drop-down box of the code window shows the name of the control, and the right one shows the subroutine you are in. This subroutine represents various events, such as when a button is clicked. The function is called by VB whenever the event is
To display the user form from code, use the
When the Show method of a user form is called, program execution for that containing code segment is halted until the user form exits or is closed by the user.
Using this powerful editor, you can create what seems like an entire professional application within a Project file, or any Office document for that matter.
Debugging VBA Code
With any type of development, a great deal of time is spent debugging the code. The Visual Basic Editor provides a powerful and easy-to-use debugger.
Stepping Through Code
Stepping through the code line by line can strengthen your confidence that the code you've written functions as expected. While doing so, you are given a variety of tools to assess your code, and even the ability to change it without
The following list provides the key combinations you can use to step through your code:
These keys give you the power to alter any code execution flow to help you assess your code's validity. These functions are also provided in the Debug menu, if you happen to forget which key combination you need.
The Immediate Window
The Immediate window is usually displayed as a long bar along the bottom of the Visual Basic Editor, which acts as a text entry box. If this is not displayed, you can enable it in the Views menu or by pressing Ctrl+G.
This window provides an easy way to find a value of a variable or the return value of a function. Just type a ? followed by the variable or function you want to test.
Always test a function that simply returns a value in the Immediate window to verify that the function returns the expected result.
The Project Object Model
Each object contains various properties that can be
The best way to browse an object's properties and methods is by typing the period character after an object's name. A list displays showing all properties and methods. This list is known as intelli-sense and is common in most programming environments. When more information about any object is desired, just search the online help by pressing the F1 key.
Pressing Ctrl+Space brings up the intelli-sense list, which can help you browse for an object you may not know the entire name of.
Included with the Project object model are various methods executed throughout various stages of normal editing of a document. These methods can be programmed by adding a subroutine labeled Auto XXX , where XXX is the name of the event to program (press Ctrl+Space to see a list of these). Add the code desired to accompany these events.
Security and Certificates
The closer you look at VBA's capabilities, the more it looks like a standalone development environment. However, the rich power and flexibility it provides is not
Luckily this is easily preventable by properly setting macro security settings within Project and all Office applications. This can be done in the Macro menu under Security, as shown in Figure 28.32. Four different security levels are available:
Figure 28.32. Restrict the code allowed to run by setting macro security.
This undermines the goal of macro security because it takes only one run of a virus to be detrimental. A safer way is to sign your code with a digital certificate.
Don't just ask your users to lower their macro security to run your code. Always digitally sign it.
Think of a digital certificate as an electronic ID or passport carried along with a file or document. When opening or executing a file, this passport can be
The certificate's authenticity is ensured using a cryptographic method called asymmetric encryption . Asymmetric encryption uses two keys in the encryption and decryption process. A private key is used to encrypt data, and a public key is used to decrypt it.
If the information contained within a certificate is not decrypted properly (using the public key), the certificate is
The certificate also contains hash codes created from the document it signs and the certificate itself. If these
As a developer, getting a certificate issued by an established certificate authority is probably the most efficient method of signing your code. It gives your customers an easier way to verify the authenticity of your code in addition to
Creating a Certificate
You can create a new digital certificate yourself to sign code that you have created or for distribution throughout your organization. The Office suite contains a program to easily create a certificate named SelfCert.exe and located in the root office folder, as shown in Figure 28.33.
Figure 28.33. Certificates can be created using the SelfCert program.
This program is not installed on a typical installation, so you need to add it through Add/Remove programs. Look for the Office Tool named Digital Signature for VBA Projects to Run from My Computer.
After you click OK in the SelfCert window, a certificate is automatically created with that name and saved into the computer's certificate store.
Notice in Figure 28.33 that a link is provided to a list of established certificate authorities. Follow this link if you are interested in applying for a certificate.
Exporting and Transferring Certificates
After a certificate is created (or installed from another location), it can be imported and exported through Internet Explorer. Simply go to IE's Properties page, click on the Content tab, and then click the Certificates button.
Clicking the Import and Export buttons brings up corresponding wizards that guide you through the process. Certificates are exported as a file that can subsequently be
To import a certificate into a specific store (such as Trusted Publishers), just select that tab on the certificate window before clicking the Import button.
Signing a VBA Macro
When certificates have been properly created or installed, it is easy to sign your VBA projects. From within the Visual Basic Editor, select the file that you want to digitally sign.
From the menu, select Tools, Digital Signature. A dialog box appears showing what certificate, if any, has signed the current document. Click the Choose button to display a list of certificates installed on the current computer, as shown in Figure 28.34.
Figure 28.34. Select a certificate installed on your computer to digitally sign VBA code.
Click OK, and the current VBA project is signed. If the code is changed after it has been signed (on a computer other than the one used for the original development), the signature is automatically removed.
Adding a Trusted Certificate
An alternative to requiring users to lower their macro security settings is signing your VBA code and providing users instructions on trusting your certificate. With a security level set to high, they will still receive a security warning telling them the code and certificate are untrusted, as shown in Figure 28.35.
Figure 28.35. A security warning is displayed when a macro is signed with an untrusted certificate.
From this security warning, guide your users to click the Details button to view the signature. The Digital Signature Details screen contains a View Certificate button. Clicking this button brings up the Certificate dialog. From here, users can install the certificate as trusted by clicking the Install Certificate button.
Installing a certificate created with SelfCert poses a security risk because anyone can sign a macro with this certificate. This method should be used only in small organizations where you are certain that the certificate will not become public. Even then, however, it is best to apply for a certificate from an established authority.
After the certificate is installed, the user must then reopen the file, and the user can check the Always Trust Macros from This Publisher box. Then when users click Enable Macros, the security warning does not display.
Even though it is possible to use SelfCert to sign VBA code, this is not entirely risk-free. If malicious developers get a copy of any document signed by this certificate, they will be able to sign their code, which will consequently be allowed to run automatically on your computer. It is strongly recommended to sign VBA code with a certificate issued from an established certificate authority.
The Tip of the Iceberg
This section has provided some of the basics when it comes to VBA macros but has just