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.
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
And this open approach is how I intend to implement it.
Please contact me at the ramblings forum if you have any comments on this approach. Watch this space as this evolves.
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 > How to update modules automatically in VBA >