Designed a custom construction project management application
Project Description
Hensel Phelps, a large construction giant across the US, hired Digital Workshop Center to design a project management application using Microsoft Excel. The project required a high degree of customized Visual Basic for Applications (VBA) code, as well as tapping into some of Excel’s advanced reporting, pivot table, and pivot report features.
The project’s goal was to allow project managers and management to more accurately assess construction costs , man hours, and production rate throughout the life-cycle of a large construction project. While there are software tools for project management like Microsoft Project, Hensel Phelps had experience with Excel and did not want to train their staff on a new application.
Project Details
In order to design the application to their specific workflow, we created a step-by-step process for the user to follow. A “settings” worksheet outlined the project scope including projected man hours, costs, start date, and end date. The user could select the specific “elements” of the project to be included, such as foundation, grade beams, framing, and more. If subcontractors were hired, there was an extra step to allow for the tracking of their expenses and time as well.
After the update of this information, each minor element would generate a worksheet and the appropriate “projected” data would carry over. From here, the user could enter the “actual” data as the project went into production.
The described steps involved some heavy programming in Visual Basic for Applications (VBA), with several procedures designed to mimic the logic of projected data. The company had never had an effective way to track the baseline versus the actual data, so this was a step towards better management.
Next, as the construction life-cycle moves forward on each minor element worksheet, the user would enter the actual man hours data. Based on the projected man hour rate and other factors, the costs of the project would stay up to date.
With projected versus actual data to work with, we could now compare the baseline numbers to the reality of the project. We designed a custom PivotTable and PivotReport method to plot and chart the data interactively. Options to view by element, as well as different date ranges (i.e. quarter, year, etc) were provided.
On the PivotCharts, to view the projected versus actual accurately, we formatted the plot lines in various, distinct ways, but also gave the user a way to control the formatting themselves. As much as possible throughout the project, we wanted to leave the original Excel features (especially concerning formatting), while locking other features that could mess with the custom design we provided.
Summary
This project was one of the most dynamic and involved Excel project we have ever been hired to design. The amount of VBA code needed was staggering, especially when compared to most custom Excel projects. However, the needs of the client demanded that we dive into extensive logic to get to the proper end result and we are so proud to be able to step up to the task. The use of several types of custom forms was also included when Excel worksheets and controls were not sufficient.
Rigorous testing was done to account for as many different scenarios we could imagine. With time, there may need to be more revisions to update the application to keep with the changing demands of their company, or to account for logic that we did not think of at the onset of the project.
In the end, the client expressed their gratitude and appreciation for the project as they have begun to implement it in the field. The biggest challenge for them will be to get their staff to use this new application properly. With new tools, there will take some time to adopt and then measure the effectiveness of the project.
In some ways, it may have been easier for Hensel Phelps to purchase an out-of-the-box project management solution and train their staff. But the ability to stay within their preferred technology, Microsoft Excel, was of more importance and we enjoyed creating this custom application for the company to use for years to come.
Need a custom Excel project?
Let’s talk! We have designed VBA Excel projects for companies of all sizes, across Colorado, and across the United States. Fill out the following form to schedule a free consultation.
"*" indicates required fields