VBAGit

After Getting your apps scripts to Github I thought I’d have a go at doing something similar for VBA. If you are reading this, you’ll know that it’s very difficult to manage shared code, since VBA is really a container bound thing. You can do things with add-ins and by referencing other sheets, but the VBA environment is very specific to your machine and your folder structure. Sharing common code across Spreadsheet containers or between machines is just a mess. I have tried a few things in the past (actually they will still work but I wont be loading the latest version of code there any longer), such as How to update modules automatically in VBA which uses GIST to publish code from the web, but getting it there and figuring out dependencies was fairly manual. VbaGit is about managing your VBA code and getting it in and out of workbooks. If you distribute or share code, or need version control then this is probably of interest to you.

There’s a lot of words here, so if you want to skip it and get started, go here – Getting started with VbaGit or VBA and jsdoc

What VbaGit does

In the end, quite a lot actually – it kind of grew as I was playing around with it and realized what could be done. I find the automatic documentation especially useful

    1. Given a module or set of modules or classes, figures out the dependencies amongst the other modules or classes in your workbook. In other words – which classes and modules are used by a module. This allows you to split up a workbook – let’s say to extract and share libraries from a bigger project, and creates an info.JSON describing your repo. Using this method you can create multiple repos from one workbook project, and each repo will only contain the code it needs to make its main modules/classes compile.
    2. Automatically creates documentation in markdown about the dependency cross references, and also creates documentation about each procedure in each module describing the arguments and their types. I did something like this before in Automatic documentation but this is much more detailed and useful.
    3. Figures out the Excel references used by the source workbook and includes it in the dependency documentation and info.JSON
    4. Puts all that in a staging area by project. One project = one GitHub repo. This repo contains all the documentation mentioned above, and all the code and libraries needed for the selected modules. This staging area can used by a normal git client, or by the one written in VBA I include with the package.
    5. Commits everything in the staging area to GitHub (creating or updating a GitHub repo as necessary), taking care of version control and only updating changes so on. Once committed to GitHub, you have a full revision history and copy of each commit stored.
    6. Pulls a repo from GitHub directly into a workbook, setting it up with all the code it needs to support the project.

What VbaGit doesn’t do

  1. No sheets, data or forms are managed. Neither are local scripts associated with particular sheets or in the ThisWorkBook Module. This is because different Workbooks are likely to have different worksheet structures or updated data. VbaGit is just for code in a) ClassModules and b) StandardModules. If you have other stuff, you can put it in the repository yourself
  2. Dependencies on modules containing only public constants will be missed, since it only knows how to find functions, subs and classes. If you put them in a module with procedure dependencies then they will be carried forward okay.
  3. The VBA git client only works on the master branch for now. If you want to fork and create other branches, you can still use VbaGit to manage the local repo, and a normal windows git client to do all the other stuff.
  4. No doubt there will be other stuff. If you find anything, please join our community and let me know.

What’s in the repo

For the discussion, I’ll use the repo for the VbaGit project itself, which of course I used VbaGit to create.

Here’s what this repo looks like when automatically created and committed. You can find the repo for VbaGit here.


Here’s what the project looks like in the VBA IDE


The readme file

This is committed if there isn’t one in the repo, otherwise it leaves it alone. The idea is that you use the initial skeleton to build on. If you want the skeleton re-instated, just delete your readme from the repo. It doesn’t say much and looks like this. This is created  and committed to guthub  stage if there’s not already a README in the repository.

The dependency report

This describes what’s in the repo, a sample snip is shown below


Also in the dependency documentation you’ll see which Excel references were detected in the workbook being processed. VbaGit is unable to associate excel references with procedures and therefore know exactly which if these would be needed in a subset of the original workbook, so you can either choose to apply them manually in any workbook you create when importing from git, or have doImportGit do it automatically for you (in which case it will apply them all)

 

The cross reference document

This describes references between procedures, a sample snip is shown below 

The info.json

This is used to create all the documentation above, and to control execution of VbaGit.  Note that the info.json file will always be slightly newer than the one on github, since it records when it was committed after the commit completed.  Take a look directly on github, it’s a little big to reproduce below. 

The scripts folder

This is the source for all your scripts in your project and looks like this 

 

It contains one file for each module, plus one document for each module. Here’s a snip of the documentation that was created automatically. 

The libraries folder

If there are any, then all the libraries sources for each dependency needed by this project. Here’s a snip below. Each module or class has a code file as well as a documentation file the same as was shown in the scripts folder above 

Each folder name matches a library, and contains the source for that library. Note that every library that is referenced for which sources are available are committed here – including libraries that are referenced by other libraries and so on. This list matched the dependency list in the dependencies.md report.

Technical writeup

You can find the repository for VbaGit here. Now to get started, go here – Getting started with VbaGit
Now take a look below for more on this subject

For help and more information join our community, follow the blog or follow me on Twitter . For how to do this for Google Apps Script, see Getting your apps scripts to Github