Development of an Excel project management tool for VGF

Excel programming for Stadtwerke Verkehrsgesellschaft Frankfurt am Main mbH (VGF)

Stadtwerke Verkehrsgesellschaft Frankfurt am Main mbH (VGF) has commissioned eWorks as Frankfurt's Excel programmer to develop a project management tool based on Microsoft Excel and VBA. VGF's 2,500 employees operate over 400 rail vehicles on 10 streetcar and 9 subway lines and are responsible for the rolling and fixed infrastructure, traffic management, cleanliness, safety, service and customer service. As a service provider, VGF transports over 200 million passengers annually. VGF wanted to have a project management tool programmed in Microsoft Excel to support project management in SAP, RIBiTWO, myParm and ZEDAS in order to plan, manage and successfully complete hundreds of projects every year: Elevator installations, platform modernizations, other construction measures and much more.

Based on an existing VGF Excel prototype, on-site workshops, Microsoft Teams meetings, telephone calls and e-mails, eWorks first designed an advanced, customized project planning tool with numerous convenient functions and then implemented it using Excel programming. The developed Excel tool simplifies, accelerates and standardizes the creation and maintenance of the so-called "project management documentation" (PLD), which VGF employees create and maintain for each project. The appealing user interface in the familiar VGF corporate design with an attractive "timetable look" guides users playfully through all views and the VGF buttons in the familiar Excel ribbon allow quick access to all functions at any time with a click of the mouse.

The Excel template developed (or the "digital project management documentation") is to be maintained and kept up to date for each VGF project, with the sequential project phases and master data being prepared in a structured manner using different masks: Project description, assignment, team structure, work packages, milestones, contracts, cost plans, final report and much more. Depending on the project size, the scope of the documentation can be adjusted so that superfluous information is hidden in this project context - with automatic adjustment of the cockpit status traffic light and, of course, without data loss if the project size should subsequently change again. Unlike in the past, it is no longer necessary to develop and maintain different Excel templates for different project types, but everything is in one place - with corresponding savings in terms of time, costs, processing errors and inconsistencies.

Unlike in the past, the underlying Microsoft Excel foundation validates all user entries for mandatory fields, data types and value ranges immediately during input and checks their plausibility using the individually programmed specialist logic while they are still being entered. Drop-down fields, conditional formatting and operating instructions as context help round off the user guidance - eliminating the need for lengthy training courses and complex manuals. In order to keep this "living project documentation" up to date at all times, the VGF administration has also been given a password-protected "administrator mode", which enables the user to maintain all help texts independently in order to gradually incorporate new findings and procedural instructions - printed paper documentation "for the garbage can" is now a thing of the past, as are paper andCO2 emissions. If paper is still required, all forms can be printed out in A4 format (or exported as a PDF) at the touch of a button.

To digitize the follow-up processes, VGF has also had a Microsoft Word template programmed, which is automatically filled with all project data at the touch of a button, lightning-fast and error-free - for example as a basis for decision-making at management meetings. As well as a project request form, which imports all data fully automatically using an Excel VBA macro and makes manual data entry superfluous - hundreds of project master data at the touch of a button, in seconds and error-free on the spot.

As a special bonus, a benefit analysis for project classification into benefit and complexity categories was integrated through VBA programming. Projects can be compared and evaluated using a decision matrix as a basis for further planning steps, including graphic visualization of the benefit and complexity categories. There is also a graphical dashboard for a schematic representation of the planning process, including a completeness check and status traffic light.

eWorks has supported VGF as a Frankfurt VBA programmer from the very beginning: starting with the initial requirements analysis workshops and intensive project planning through technical implementation to completion and handover of the developed Excel solution. Following the handover, the eWorks VBA developers supported VGF both in questions of operation and in the event of any program errors (warranty), as well as in the planning and implementation of future further developments.

Used technologies

Microsoft Office
VBA

Related references

Would you like to
commission a project?

Enquire now!

Are you looking
for a job?

Apply now!