I was somewhat surprised to discover that the standard Microsoft Project installation does not have a variable or field for displaying the resource material cost of tasks. Apparently, displaying this project data requires the insertion of a Visual Basic Application (VBA) subroutine for Microsoft Project. Really!
My question is why would project data as simple as listing the material cost require a VBA subroutine extension? My brother-in-law, a computer science professor, surmises that Microsoft, most likely, intentionally left out the material cost field to nudge all us novice Microsoft Project users towards programming Project VBAs. I don’t intend to validate the accuracy of my brother-in-law’s theory. But knowing how to program Project VBAs, apparently, opens the door to a myriad of project data in Microsoft Project.
Well, by consulting a Microsoft Project discussion forum I quickly found the required VBA subroutine for listing task material cost. I was even recommended the following VBA Project programming book “VBA Programming for Microsoft Project ’98 through 2010 with an introduction to VSTO” by Rod Gill. In addition to the Project VBA basics creating a material cost field also requires a rudimentary understanding of Microsoft Project custom fields. Refer to the blog Using Microsoft Project Custom Fields for an introduction to custom fields.
Great! So applying a basic knowledge of custom fields and Project VBA programming will enable us to create a custom field for displaying the material costs of tasks. Microsoft Project comes standard with VBA program development tools.
This article builds on your understanding of Microsoft Project custom fields to explain how to get started with Microsoft Project VBA programming.
A word of warning before we get started. The VBA material cost subroutine demonstrated in this blog comes from my inquiry on a Project VBA forum. I did not write the subroutine nor have I fully tested all applications of the subroutine with Microsoft Project. The intent is to get you started in applying Project VBA subroutines to solve a real life limitation in Microsoft Project. Thanks to the VBA Project community I was able to obtain the code for this demonstration.
We begin with a demonstration project. In Figure 1 we have a Pipe Repair & Improve project.
This schedule already has been updated with one week’s progress. Note in the ‘Resource Names’ column that both labor and material resources have been applied to respective tasks. The Cost column displays the cost of both labor and material for each task and the entire project. Wouldn’t it be nice to have a column that separates out the material cost of each task and the entire project? Yes, it would, and to do this in Microsoft Project we’ll need the VBA Project developer.
To start we first add the developer tab, so that we have access to Microsoft Project VBA tools. Proceed by right clicking and selecting ‘Customize the Ribbon…” in the resulting popup menu, Figure 2.
Toggle Developer in the Main Tabs customize the ribbon options, Figure 3.
Next select the newly appearing Developer tab and click Visual Basic, Figure 4.
Up pops the Microsoft Project VBA developer tools.
In the top drop down Developer menu select insert and Procedure, Figure 5; we want to add a new subroutine for a material cost field.
The name of our subroutine is ‘MaterialsCost’, Figure 6.
Toggle Sub for Type and Public for Scope. Click OK and Project generates the first and last lines of our subroutine. What you will want to do now is to download and open the MaterialsCost.txt file and copy and paste the VBA code into the General Procedures editor, Figure 7.
Again, the intent is not to analyze the strength of this code, but to demonstrate how to utilize a simple VBA Project subroutine.
Once the code is entered, as displayed in Figure 7, select Run from the top drop down menu and ‘Run Sub/UserForm’, Figure 8.
Our code assigns Material Cost to the Cost1 custom field, which we display in Figure 9.
You’ll have to trust me that the costs listed in the Cost1 column are in fact the separated material costs of each task. Note that the subroutine does not sum the material costs for the summary tasks, which is a problem.
Custom Field Settings
We can solve this problem and provide a better label with a few adjustments to the Cost1 custom field. Select the Project tab and Custom Fields, Figure 10.
In Figure 11 toggle Task select the Cost Type.
Highlight Cost1 and click the Rename button. Type in the more appropriate ‘Material Cost’ label. That settles our labeling issue. Next to provide a summary Material Cost for our summary tasks, in Figure 12, we toggle Rollup Calculation for task and group summary rows.
One day, you will need to come back to your code to do a change and, when you look at it, you may think why did I do that, or even what does that do? You should get into the habit of:
- Adding comments that explain what is happening in the code – you will need them in 6 months time.
- Indenting the code, so that loops and control structures are easy to follow.
- The VBA development environment lets you use tabs, so its just one key press per line.
- Use Option Explicit in every module. That forces the declaration of variables before they are used – the Dim statements. That catches typos in the code and makes debugging easier.
Even though this introduction is brief, you should introduce good habits from the outset. Its all to easy to end up with spaghetti code and can be avoided.
Other than understanding our VBA Project code the process for inserting and running a VBA Project subroutine it fairly straightforward. And it just requires some minor adjustments to the Custom Fields dialog. So with these “get you started” instructions and the recommended book you should be on your way.
Hopefully, Project VBA will extend the limits of Microsoft Project for you. It certainly should help you to separate and display the material and/or equipment costs. You could, of course, use my approach and consult VBA Project forums to acquire existing VBA Project code to suite your purposes. Perhaps, though, understanding a little VBA Project coding will help you fully implement the power of Microsoft Project for your organization.