How to update modules automatically in VBA

Introduction


if you are just looking for the gistthat bootstrap code for this, it's here.

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.
  1. Make one or more gists of the code you want to share. You'll find an example gist here
  2. Create a manifest describing the modules and classes needed for a particular capability, and create a gist of that too. Here's an example
  3. 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.
Here's a Step by step module updating example using the Automatic documentation module, and here's a post from the Excel Liberation blog with an even simpler example.

Include modules from this site automagically

I'm building up a list of how to get various combinations of modules into your workbook here.


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. 

An example

Once you have created a module containing the gistThat bootStrap code, your workbook is ready to incorporate any code that is stored as a Gist on github and has been described in a manifest, itself an XML Gist. An example work book , gistThat.xlsm is included in the downloads section.

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
  • be sure that you trust the source and the purpose of the code.
This is no different than downloading any workbook from a public place, but in this case your project code is going to be updated in situ. Chip Pearson has the definitive writeup on updating code programatically, and this is exactly what the gistThat bootstrap does. To allow access to your project, you need to set this option in the Excel trust center.



Try it

Either download an empty workbook gistThat.xlsm which already has the bootstrap code in it, or create a module - called gistThat_ by convention in some empty workbook,  and paste in this code. Now run gtExampleLoad


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

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
  • remove the gistthat module. You can always get it again here
  • Turn back off trust to the VBA object model
  • Run a virus scan on your workbook to reassure yourself you have followed these steps and not picked up any malware.

Keeping them up to date

You'll notice that a comment is inserted in every module that is manageable by gistThat. It looks like this
'gistThat@mcpher.com :do not modify this line - see ramblings.mcpher.com for details: 
'updated on 21/08/2012 13:06:04 : from manifest:3414394 gist https://raw.github.com/gist/3414216/cCell.cls

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")
    '
    ' call this for each required gist of the manifest
    '---cDataSet
    gtAddToManifest dom, "3414216", "class", "cCell", "cCell.cls"
    gtAddToManifest dom, "3414216", "class", "cDataSet", "cDataSet.cls"
    gtAddToManifest dom, "3414216", "class", "cDataSets", "cDataSets.cls"
    gtAddToManifest dom, "3414216", "class", "cDataColumn", "cDataColumn.cls"
    gtAddToManifest dom, "3414216", "class", "cDataRow", "cDataRow.cls"
    gtAddToManifest dom, "3414216", "class", "cHeadingRow", "cHeadingRow.cls"
    '--Useful modules
    gtAddToManifest dom, "3414346", "module", "usefulStuff"
    '--cJobject
    gtAddToManifest dom, "3414365", "class", "cJobject"
    '--usefulcolorstuff
    gtAddToManifest dom, "3414615", "module", "usefulColorStuff"
    '--regex
    gtAddToManifest dom, "3414836", "module", "regXLib", "regXLib.vba"
    gtAddToManifest dom, "3414836", "class", "cregXLib", "cregXLib.cls"
    
    ' cut and paste the result of this into a gist - this will be your manifest
    Debug.Print dom.xml
    
End Function

Finally create another gist and paste in the generated xml code, and note the gistID. This will be your manifest used as below

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

You can also add required references to the manifest. The code below will add any references in the current project to the manifest being built. 
    ' -- references
    gtCreateReferences dom


How it fits together


Please contact me at the ramblings forum if you have any comments on this approach. Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.





Subpages (1): gistThat Idea
Comments