Doing the Impossible
In 2010, I attended a Baltimore MPUG meeting that changed my life. I learned about Visual Basic for Applications (VBA) for Microsoft Project, and I was stunned to discover easily-performed operations that I had previously thought were impossible.
What is VBA?
Visual Basic for Applications, or VBA, is behind the scenes in all of the Microsoft Office applications. When you expose the “Developer” tab you will see it (shown in Figure 1 below).
Figure 1: Expose the Developer Tab to See VBA
VBA is an object-oriented language, which means that an “object” has “properties” and “methods.” An example object is “Task,” and for that an example property is “Name” and an example method is “Delete.” A very respectable way to discover the VBA instruction for any action is to record a macro. Here is the macro recording of adding one holiday to a holiday calendar (Figure 2).
Figure 2: Macro Recording of Adding a Holiday
The space-underscore at the end of one line allows the instruction to continue on the next line. To learn about available objects and their associated properties and methods, see the Object Model via the Help tab (Figure 3).
Figure 3: See the Object Model via Help Tab
Here’s an example of doing the impossible! Suppose that you need to create a text file containing a task’s Notes and Successors (Figure 4). You could open the Gantt Chart, copy each field, and paste the data into the text file, but you’ll be limited because only the first 255 characters can be copied (Figure 5). In contrast, the VBA instructions in Figure 6 will produce the full strings as shown in Figure 7.
Figure 4: Task #2 Has Long Notes and Successors
Figure 5: Copy & Paste Yields Field Contents Truncated to 255 Characters
Figure 6: Simple VBA Procedure to Print Notes & Successors
Figure 7: VBA Procedure Yields Complete Field Contents
As you can see using VBA has its advantages. Here are some personal examples from the last decade:
- Searching for redundant logic in MS Project, and then deleting each redundancy.
- Exporting monthly resource-loading into an Excel spreadsheet in the exact format required to upload that into an enterprise Resource Planning System.
- Building a summary schedule for populating a spacecraft circuit board, based upon a detailed Excel work-flow report from an enterprise Manufacturing Planning System.
- Finding the months spanned by tasks in each WBS element.
- Tracing the driving predecessors path to any selected task.
- Tracing the driven successors path from any selected task.
I recently developed US and Canadian holiday calendars for the years 2020-2050, which yield much more realistic schedules than assuming every weekday is a workday. I’ll demonstrate below how I used VBA to automate this production.
Automating the Process to Generate a Holiday Calendar
Using VBA, I determined the actual dates for all of the fixed-day holidays, such as the Canadian “Family Day” on the third Monday in February. Here is the VBA code to find the third Monday in February (Figure 8). The results were written into comma-separated-values file for this one and all the other fixed-day holidays.
Figure 8: VBA Code to Find Third Monday in February
The fixed-date holidays (for example, the US’ Independence Day on July 4) were handled manually in an Excel spreadsheet, making adjustments for those instances where the dates fell over a weekend.
Once I had determined all the holidays, I populated the list within MS Project. Doing this manually is error-prone and time-consuming, so I used the VBA instruction discovered in Figure 2 along with Excel formulas to construct a sequence of 350 VBA instructions. See Figure 9.
Figure 9: VBA Instructions Created in Excel
At this point, I simply copied the VBA instructions from the Excel spreadsheet, pasted them into an empty VBA module, and executed them. You can see the results here.
Going Forward with Your Own Use of VBA
Getting started is hard. In the below resources list, I have posted a VBA structural template with all of the necessary sections, along with some hard-to-discover instructions and a few consultants I recommend. I’ve also included a collection of VBA code snippets for the most-likely requirements.
Resources
#1. VBA template and code snippets available via my Google Drive.
#2. Book: VBA for MS Project by Rod Gill. Out of print, but a PDF is available via MSProjectHolidayCalendars.com. A few used physical books remain at Amazon.com.
#3. Consultant: Rod Gill via rodg@project-systems.co.nz and at Microsoft Project VBA development services (project-systems.co.nz) and also at Introduction to Project VBA – how to boost your productivity – YouTube
#4. Consultant: Minerva Goree | Microsoft Project Consultant and Teacher | Udemy | info.madschedules@gmail.com
#5. Consulting Company: Sarah Howard via sarah.howard@projility.com
#6 Microsoft official reference at Project Visual Basic for Applications (VBA) reference | Microsoft Docs
What have you done in MS Project with VBA? I’d love to hear from you in the comments.
