Example of automatic code updating for VBA

In a previous post I covered a way of automatically including and inserting VBA code in Excel from gists. You would normally use this if you wanted to include a bunch of functions and classes and excel references with dependencies on each other in some workbook. However, you can easily use it to just pull in a simple class or function, or even to set up references automatically. The main thing though, is that you can get updates directly from gist as and when required.

Using the cStringChunker class as an example, lets go through the steps needed to make this happen. First create an empty workbook (.xlsm) to play around with. You can repeat later in a real workbook if you want.

The bootstrap

You need some code that will manage the initial load and future updates. You only need to do this once per workbook. You can get this here. Create a module and paste in the code – I usually name the module gistThat_, but its not important what you call it. Once you have this, you can just leave it in the workbook to use again later.

Allow access to the IDE

The bootstrap works by retrieving code from a Gist, then writing that code in the workbook. You need to allow it to be able to do that in the macro settings Excel Trust Center

Run the update

Either in the immediate window, or in a procedure you create, run this
gtDoit “5152181”, True

Set  access to the IDE back off

You can set the trust access to the object model back off again now if you wish. 

What happened

Your workbook has been updated to included whatever modules, classes and references were described in gist 5152181. Here’s the contents below – in this example there is only one class, but it can contain any number of functions, classes and even Excel references.

Keeping updated

At any time in the future, you can get the latest version I have posted by re-enabling IDE access and running 

gtDoit “5152181”

A more complicated example

Let’s take something more complex, which has many dependent classes, functions and Excel references. The cDataSet data abstraction classes. One way to get these would be to download the cDataSet.xslm workbook from Excel Liberation. However this has a whole bunch of other stuff in that workbook too. If you just want the raw code for cDataSet you can bring in (and keep updated), all of this like this.
gtDoit “3414394”, true

Here’s the description of the required artefacts for cDataSet.

Your own code distribution

You can use this for your own code distribution. Its just a matter of creating an xml file (gistThat_ contains and XML maker too), and loading your sources on github. See Excel liberation for how.

For more stuff like this, visit the Excel liberation site.

http://xliberation.com/b/but.html

Author: bm082975

Leave a Reply

Your email address will not be published. Required fields are marked *