Excel VBA programming for the Goethe University Frankfurt

Digitization and increased efficiency in procurement through Microsoft Excel macros

After an intensive search, Goethe University Frankfurt am Main commissioned eWorks as a certified Excel specialist and Microsoft partner in December 2018 to develop a tailor-made Excel VBA solution to optimize the tendering process for cleaning services. The company was looking for a qualified Excel service provider who could implement both the technical and visual requirements for the tender documents. The contract was awarded by the Real Estate Management division, which is responsible for facility management at all four university campuses: Westend, Riedberg, Bockenheim and Ginnheim. The regional proximity was not only practical for personal meetings, but also for the direct line to the university: eWorks employs numerous alumni of Goethe University - even the two founders and managing directors once studied computer science there. Home game! In order to get started right away, a flexible support contract was concluded so that eWorks could program, expand and optimize Excel macros on demand as an "extended workbench". Since then, more than a hundred tickets have been successfully processed and several person-months of development work have been invested.

The Excel solution was originally developed for tender documents in the cleaning services sector, but the appetite came with the food: It was gradually extended to other tendering procedures - from glass surface cleaning to winter services and security services. Particular emphasis was placed on a user-friendly design. All Excel forms are in the corporate design of Goethe University, with university colors, practical cell formats and intuitive navigation. Sheet protection prevents unwanted changes (for anyone who accidentally selects the entire table and presses "Delete"). To ensure maximum compatibility, VBA macros were deliberately omitted from the forms - so the solution not only runs in Excel 2021, Excel 2019 and Excel 2016, but also in Excel for Mac, on mobile devices and any other Excel version. For the analogue group, the forms have also been optimized for A4 printing - of course with university logo, headers and footers and print-optimized.

The Excel awarding software with VBA macros that has been developed integrates seamlessly into Microsoft Office 365 and automates the entire tendering process. Bidder evaluations? Now at the touch of a button. Criteria weighting? Dynamically adjustable. Award forms? Are created directly from the data entered. The evaluation matrix in accordance with VgV ensures that the evaluation criteria can be flexibly adapted to any tender - including submission opening, award file and audit-proof documentation. Smart plausibility checks prevent incorrect entries and guarantee consistently high data quality. This gives awarding authorities more time for the essentials - and less hassle with Excel error messages.

But that's not all: additional Excel VBA tools have been developed to further automate the tendering process. Tender data can now be recorded in a structured manner, automatically weighted and evaluated. The values determined serve as a transparent basis for the decision to award a contract. A specially developed Excel import ensures that submitted tenders are automatically recorded and processed, making manual data entry virtually superfluous. A statistical data analysis with pivot tables and VBA was also added - it analyses and compares previous award procedures so that tenders can be optimized on the basis of valid data.

In addition to Excel, a Word form was also developed for recording image proofs. It offers form controls and image placeholders for product specifications and images - so that the awarding authority can digitally record not only figures, but also clear product images. Integrated document protection ensures a uniform structure so that Goethe University can process the collected data without any problems.

As is so often the case, the demands on performance grew with use. At some point, it became apparent that the calculation logic was reaching its limits - a typical luxury problem of successful software. By using array formulas such as SUMPRODUCT and INDEX COMPARISON as well as targeted caching, the calculations could be massively accelerated. The result: all calculations now run in real time. Progress bars? That was yesterday. Hourglass animations? Superfluous. The speed not only contributes to increased efficiency, but also to user satisfaction - because nobody likes watching Excel do the maths.

The different number and data formats in the inventory lists posed a particular challenge. These initially prevented standardized processing, but could be normalized and standardized using customized VBA macros. This meant that even large volumes of data could be efficiently cleansed and evaluated. The similarity of the various tendering procedures also proved to be an advantage: once developed, macros could be reused for new tenders with only minimal adjustments. This meant that no wheels had to be reinvented - instead, thematic overlaps could be converted into technical synergies, which saved time and costs.

After the successful implementation, the Excel VBA solution was transferred to regular operations at Goethe University's real estate management department. Beta testers from the specialist department were involved for quality assurance purposes in order to validate the tender documents. Comprehensive documentation and training courses were created to ensure that the software remains future-proof in the long term. Continuous Excel support from eWorks ensures that the software not only remains operational, but is also adapted to current procurement guidelines. Regular maintenance, updates and change requests ensure that the Excel solution is always up to date.

eWorks is a Microsoft partner with over 25 years of experience in Excel programming and has a team of certified Microsoft Excel developers. Our experienced VBA developers implement customized Excel VBA solutions, Excel macros and Excel add-ins for companies, universities and public clients. In addition to VBA development, we also offer the creation of COM add-ins with C# and .NET as well as modern web add-ins for all platforms and mobile devices. Our team of Excel specialists supports you in the automation and optimization of your Excel VBA solutions - be it through the development of new functions or the professional adaptation of existing macros. If you need an Excel macro created or are specifically looking for a VBA programmer for your company, we are your reliable partner. Contact us now - our team of Excel VBA developers will be happy to help you with your next project.