A simple and clean workflow for developing VBA for Excel (and perhaps other office applications). This article details an approach that uses practices such as code versioning and build steps to generate excel sheets with embedded macros.
Production of CSV/TSV files that contain tabular data is an easy task in most languages. However, producing excel documents with formattting programmatically has been tricky. Moreover, a clean workflow for VBA for Excel development is something I have not come across. This document details an approach that I followed during the implementation of a solution that involved non-trivial macro development. I have attempted to use source control and a build system in order to create Excel documents at runtime instead of embedding scripts within workbooks.
A recent client of mine required mass production of excel sheets with data tables, pivots and charts. There was also an area for users to fill in data bound to validation functions and formulae. Also, the content/presentation had to be adjusted based on the targetted groups.
- Formulae, VBA to be version controlled (and therefore available for review)
- Excel documents to be built using
- a declarative approach i.e a simple set of configurations
- a build agent
The build script consumes configuration and VBA from source control and combines this with data to generate Excel documents.
- PowerShell (configuration and build scripts)
- ImportExcel - PowerShell module to import/export Excel spreadsheets, without Excel
The first step in employing this process is to extract and store all the VBA scripts in source control as individual files (or a monolith if you choose). One could go further (like I did) and create a configuration document that contains formulae, colours and other resources that are to be added to the workbook.
Here's a snippet from the build script that creates an excel document with data provided and embeds a VBA module before saving it.
$excelPackage = $data | Export-Excel -PassThru
$module = $excelPackage.Workbook.VbaProject.Modules.AddModule($moduleName)
$module.Code = (Get-Content <VBA file path>) -join "`n"
$excelPackage.SaveAs(<output file path>)
Source code : https://github.com/shristi-dev/excel-macro-demo
Points of Interest
Although this approach works great for any macros, I still have to come across a similar approach when it comes to form design. Also, it would be ideal to add tests to the workflow, which is something I have not touched upon.
ImportExcel is an excellent PowerShell module and is well documented. I found it easy to use and recommend visiting the project for more details on formatting and formulae usage.