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.
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.
I'm building up a list of how to get various combinations of modules into your workbook here.
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.
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
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
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.
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
You'll now have all the modules and classes required for cJobject and cDataSet.
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
You'll notice that a comment is inserted in every module that is manageable by gistThat. It looks like this
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
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).
Finally create another gist and paste in the generated xml code, and note the gistID. This will be your manifest used as below
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.
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >