Click here to Skip to main content
14,580,263 members

A simple Excel VBA development workflow

Rate this:
5.00 (2 votes)
Please Sign up or sign in to vote.
5.00 (2 votes)
29 Jun 2020CPOL
Walk-through and source code for a excel VBA development workflow
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 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. 

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

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

License

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

Share

About the Author

Member 14874449
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 123643902-Jul-20 0:35
MemberMember 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.

Tip/Trick
Posted 29 Jun 2020

Tagged as

Stats

10.3K views
4 bookmarked