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.

Page Content hide

1 The bootstrap

2 Allow access to the IDE

3 Set access to the IDE back off

4 What happened

5 Keeping updated

6 A more complicated example

7 Your own code distribution

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.

<gistThat info="this is a manifest for gistThat VBA code distribution  - see ramblings.mcpher.com for details">
<manifest description="cStringChunker" contact="bruce@mcpher.com">
<gists>
  <item1 gistid="5102369" version="" filename="cStringChunker.cls" module="cStringChunker" type="class"/>
</gists>
</manifest>
</gistThat>

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.

<gistThat info="this is a manifest for gistThat VBA code distribution  - see ramblings.mcpher.com for details">
    <manifest description="cDataset and associated classes and modules" contact="bruce@mcpher.com">
        <gists>
            <item1 gistid="3414216" version="" filename="cCell.cls" module="cCell" type="class" />
            <item2 gistid="3414216" version="" filename="cDataSet.cls" module="cDataSet" type="class" />
            <item3 gistid="3414216" version="" filename="cDataSets.cls" module="cDataSets" type="class" />
            <item4 gistid="3414216" version="" filename="cDataColumn.cls" module="cDataColumn" type="class" />
            <item5 gistid="3414216" version="" filename="cDataRow.cls" module="cDataRow" type="class" />
            <item6 gistid="3414216" version="" filename="cHeadingRow.cls" module="cHeadingRow" type="class" />
            <item7 gistid="3414346" version="" filename="" module="usefulStuff" type="module" />
            <item8 gistid="3414365" version="" filename="" module="cJobject" type="class" />
            <item9 gistid="3414365" version="" filename="usefulcJobject.vba" module="usefulcJobject" type="module" />
            <item10 gistid="3414615" version="" filename="" module="usefulColorStuff" type="module" />
            <item11 gistid="3414836" version="" filename="regXLib.vba" module="regXLib" type="module" />
            <item12 gistid="3414836" version="" filename="cregXLib.cls" module="cregXLib" type="class" />
        
        
            <item13 guid="{000204EF-0000-0000-C000-000000000046}" name="VBA" major="4" minor="0" description="Visual Basic For Applications" type="reference" />
            <item14 guid="{00020813-0000-0000-C000-000000000046}" name="Excel" major="1" minor="6" description="Microsoft Excel 12.0 Object Library" type="reference" />
            <item15 guid="{00020430-0000-0000-C000-000000000046}" name="stdole" major="2" minor="0" description="OLE Automation" type="reference" />
            <item16 guid="{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}" name="Office" major="2" minor="4" description="Microsoft Office 12.0 Object Library" type="reference" />
            <item17 guid="{F5078F18-C551-11D3-89B9-0000F81FE221}" name="MSXML2" major="6" minor="0" description="Microsoft XML, v6.0" type="reference" />
            <item18 guid="{0002E157-0000-0000-C000-000000000046}" name="VBIDE" major="5" minor="3" description="Microsoft Visual Basic for Applications Extensibility 5.3" type="reference" />
            <item19 guid="{3F4DACA7-160D-11D2-A8E9-00104B365C9F}" name="VBScript_RegExp_55" major="5" minor="5" description="Microsoft VBScript Regular Expressions 5.5" type="reference" />
            <item20 gistid="5102369" version="" filename="cStringChunker.cls" module="cStringChunker" type="class"/>
            <item21 guid="{0D452EE1-E08F-101A-852E-02608C4D0BB4}" name="MSForms" major="2" minor="0" description="Microsoft Forms 2.0 Object Library" type="reference"/>
        </gists>
    </manifest>
</gistThat>

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.