A Simple Excel VBA Development Workflow

Introduction

Production of CSV/TSV files that contain tabular data is an easy task in most languages. However, producing Excel documents with formatting 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.

Background

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 targeted groups.

Development Goals

• 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

Solution

Overview

The build script consumes configuration and VBA from source control and combines this with data to generate Excel documents.

Technology Used

• 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.

# create an Excel package
$excelPackage = $data | Export-Excel -PassThru
# create a VB project
$excelPackage.Workbook.CreateVBAProject()
# embed macros
$module = $excelPackage.Workbook.VbaProject.Modules.AddModule($moduleName)
$module.Code = (Get-Content <VBA file path>) -join "`n"
# save the Excel file
$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.

History

• 29^th June, 2020: Initial version