Click here to Skip to main content
15,867,835 members
Articles / DevOps / Git
Tip/Trick

A Simple Excel VBA Development Workflow

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
29 Jun 2020CPOL2 min read 17.1K   5   1
Walk-through and source code for an Excel VBA development workflow
This is 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.

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

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.

PowerShell
# 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

  • 29th June, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 123643902-Jul-20 0:35
Member 123643902-Jul-20 0:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.