How to update modules automatically in VBA

Introduction

I first started to think about this problem when I found myself having to tailor workbooks for particular capabilities described on this site. I'm not great a fan of add-ins (for libraries), and I wanted to find a way of making the code openly available, and at the same create a general capability for people to share and update VBA code painlessly, either publicly, or between their own workbooks. In the end I decided to use Gists on GitHub. Here's how it works.

Make one or more gists of the code you want to share. You'll find an example gist here

Create a manifest describing the modules and classes needed for a particular capability, and create a gist of that too. Here's an example

Install some bootstrap code in your VBA project. This will automagically bring in the code mentioned in the manifest to your project, and make updates on demand. The code for that is here.

Include modules from this site automagically

The details

The cDataSet and cJobject family of classes are the most widely used on this site, but I often get questions about which modules are needed to do some function or other, and then when there are updates there is no way to push them out to people who have already incorporated them in their workbooks. This gistThat capability provides it.

Let's say you wanted to load any classes or modules associated with the cJobject and cdataset classes from this site. I have already created a manifest, so all you have to do is execute this code, where the first parameter is the gistID of the manifest describing the requirements for these two classes

Public Function gtExampleLoad()

' this is an example of how you would load your VBE with a particular manifest

' you would set the 2nd parameter to overrride conflict checking the first time used

gtDoit "3414394", True

End Function

References required by gistThat

gistThat uses late binding, so no specific additional references are required

Allowing access to your project's object model

As you can imagine, the bootstrap needs to write to your project - it is inserting/modifying modules, classes and references in your project . That means that you need to

always make a copy of your workbook before updating any code programatically like this

' this is an example of how you would load your VBE with a particular manifest

' you would set the 2nd parameter to overrride conflict checking the first time used

gtDoit "3414394", True

End Function

You'll now have all the modules and classes required for cJobject and cDataSet.

A note about virus false positives

Any code that has the capability of modifying the VBA excel codebase has the capability of introducing nasty code into your workbook. By its nature (it pulls code off the internet and inserts it into your workbook), along with the fact you've allowed it to do it, this bootstrap can look like a potential virus to some virus checkers. once you've updated your workbook with the latest modules, I recommend you

Aside from information on when and how the module was created, it also serves as a marker for the gistThat process. It will refuse to overwrite a module with the same name as one it is trying to update unless it has this marker. This avoids collisions of module names. Note that I do not yet check for collisions of procedure or function names. That'll be the be next enhancement. The other useful feature this enables is that you can automatically refresh all modulesto their latest versions from github managed by a manifest (you can have as many manifests active as you like in a project), by simply executing

gtUpdateAll()

Creating your own manifests

You can of course create your own manifests. This will allow you to share( and keep up to date) useful modules with others and between your own workbooks. Simply load your code to one or more gists, and follow the instructions for including the bootstrap code in your worksheet and modify the example given for cDataSet (cDataSet is very complex involving many classes and modules).

Private Function gtExampleMakeManifest()

' this is an example of how you would create a manifest to be loaded up as a Gist

'

Dim dom As DOMDocument

Set dom = gtInitManifest("cDataset and associated classes and modules", "bruce@mcpher.com")