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.

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.

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.

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.

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.

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