VBA programming of an Excel solution for stocktaking
Development of an inventory tool with intelligent CSV import with Microsoft Excel 2007 & VBA
Helmut von den Hoff GmbH & Co KG (Düren, Aachen, Bonn and St. Augustin) provides roofing companies with an extensive range of roofing products at first hand and has commissioned eWorks to program customized individual software to support the annual inventory process.
The objective was to develop a convenient software solution for the simple and error-free import of article master data from the merchandise management system, to generate clear routing slips and to export the recorded inventory list to the further processing system.
The software was developed using Microsoft Excel 2007 and VBA ("VisualBasic for Applications"). The user interface was developed as a convenient Excel solution with its own menu navigation ("ribbon"), while functions such as the CSV import were developed and integrated in the form of VBA macros. A particular challenge during the VBA programming was the "intelligent" CSV import, which had to recognize independently whether data records were to be added, deleted or merged. A progress bar with status display and cancel option round off the CSV import function and make it easy to use, even with a five-digit number of data records.
The software development process took place in two phases and did not require an explicit specification sheet: in phase 1, a prototype of the inventory tool was developed that had all the operating elements and masks - but no functionality at all ("click dummy"). Based on this prototype, the requirements were refined again in discussions with the client and ultimately the contract for the completion of the inventory tool was awarded. In phase 2, eWorks "only" finished programming the functionality outlined in the prototype and was able to complete the Excel application (almost) without any further clarification of requirements.
eWorks designed, developed and quality-assured the Excel tool and handed it over to the client. Subsequently, eWorks supported the client with the installation and commissioning of the Excel application. Since then, the client has been using the developed inventory tool to import inventory data, to create and merge routing slips and to export data to the downstream system, thereby saving both working time and process error costs.