gistThat Idea


Usually when I write about something here, I've either finished it, or at least done a proof of concept. This is just an idea at the moment and if you would like to contribute any ideas then please contribute them on our forum or via google moderator.

The problem of the code being in the workbook.

Certainly you can use finished add-ins in Excel, or even distribute Dlls, but the whole business about moving latest versions of useful modules and classes around is just a pain, even during the development process.  For the non professional developer, who just wants to use their growing library of useful functions, this method does not encourage re-use.

Unlike in Google Apps Script, where you can create a library of useful stuff (see Creating a Google Apps Script Library) to be used anywhere you like, with VBA there is the whole unpleasant business of copying from one workbook to the other. There is also the matter of dependencies - what modules do I need for that ?  Do you create a massive workbook (or add-in) with almost everything in it (as I have done with cDataSet.xlsm) , or do you try to maintain seperate workbooks on particular topics (as in googleMaps.xlsm) and try to remember to update it each time you change the utility classes?  Imposing the need for Add-ins to be installed before someone can try out your code is really a non- starter for what I have in mind here, and in any case - add-ins themselves need to be updated. 

So let's look at getting everything in one workbook, but getting updates online on demand.

What is gistThat for VBA?

Right now-  nothing. What it is going to be is a way of automatically updating, from an online repository, all modules and classes (and forms and maybe even sheets) required for a particular capability. The intention is that anyone can use it to create their own repository and update their VBA modules with it. 

The repository

Initially I had considered calling this gitThat, and using gitHub repositories to store releases of particular collections of code that could be replaced in your VBA project through a simple function call. However, not everyone uses git, or wants to install it - so I decided on a simpler method - even though we would be not using the source code management capabilities of git. I also considered the Google Drive API, but decided it was too complex, needed registration and authentication. If this was to be something that anybody could quickly implement using their own code, then it needed to be free, simple, authenticationless and registration-lite.


Gists are generally used as a way to publicly distribute code samples (with version control built in) - for example - but the key point here is that they are public (no need for authentication), need no local software (browser only), and can be queried in raw format - here's a VBA example.

Open and Free

Having decided to use Gists as the repository mechanism, the implication (although you can create private gists), is that all code using this mechanism would be 
  • publicly available
  • automatically downloadable by anyone with the gistThat bootstrap mechanism.
And this open approach is how I intend to implement it.

How will this work

  • A developer will create (or update to a new version) a Gist of some code to be distributed
  • A manifest, itself a Gist, will contain details on which  gists are needed for a particular capability (essentially which modules and classes are needed to do 'function x'). Because all  gistThats are public, this could contain references to other gists not written by that developer (accepting that these might change or be deleted since they would be public). Since gists are version controlled, specific versions can be specified in the manifest.
  • A bootstrap module will be downloadable from somewhere. This is needed to 'get started', and will be able to 
    • Replace or insert all items mentioned in a manifest
    • Update all, or individually by manifest, gistThatAble modules in a VBA project, including itself
    • Check that there are no naming conflicts with existing procedures that are actually not part of the manifest but called the same thing
  • In order to minimize the amount of code in the bootStrap (which will need to be able to read manifests) it's likely that the manifest encoding will have to be XML (as opposed to jSon) to leverage as much Excel built in capability as possible
  • The bootstrap will need access to the VBE enabled.

How it fits together

Please contact me at the ramblings forum if you have any comments on this approach. Watch this space as this evolves.

For help and more information join our forumfollow the blogfollow me on twitter

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.