Excel & VBA development with GitHub

in #vba3 years ago

A 5 step how-to for VBA/Excel developers who want to share their code and want to have structure in their excel-tool development.

What I have set up for my project:

  1. setup a basic excel to work on 
  2. and an online place to store the project
  3. start coding and be able to test the code
  4. have a macro to export all relevant VBA code
  5. get the new code in a Github repository online

1. Basic Excel setup

I've been building quite a lot of excel tools in my life. Normally I save the excel file I am working on several times during development, using Save As. I use names like: CryptoTool_20171118a.xlsm, CryptoTool_20171118b.xlsm, etc. Some years ago I've worked on a start-up and discovered version control. So when I recently decided to build a VBA project to connect Excel to various bitcoin/cryptocurrency exchanges, I decided to go for a more professional approach: including version control and testing in my development process.

One side note: version control only works well for VBA code, not so much for logging changes in sheets, charts etc. Anyhow, since my project is mainly about code, I decided to give version control a try. I do save my master file ocasionally with the naming convention above.

2. Store the project online

So, version control... According to Wikipedia: Git is a version control system for tracking changes in computer files and coordinating work on those files among multiple people.

So Git basically 1) helps me tracking changes in your code and 2) gives me the ability to go back to earlier versions of that code. So I got myself an account at GitHub, and created a new project called crypto_vba. Step one was to create a readme.md file and make a small description of the project.

What I found hard about github is that git is basically command-line based. Although I grew up with MS DOS on the command line, I fancy a graphical interface. Luckily github has a desktop version: https://desktop.github.com/ . After downloading and installing that it's pretty easy to connect it to my online project and to add a local directory where I will be storing the files I want to have online.

3. Code testing

After version control, a second building block for profesional development is testing. An extensive variety of this is Test driven development. That means: first write the test for a function, after which you can start building the function. I don't go that far for this project, but do include a test Sub at the top of every module.

I found a complication while building this, as I need to test my code with some private settings. In my case that's the exchange API keys. I don't want those to end up in my Github repository online. So what I do:

  • Create a module that won't be exported to my Github project
  • In that module, create a public Constant:
Public Const apikey_btce = "MP3YF9J3"

And in the modules for the people who want to use it:

apikey = "your api key here"
'Remove this line, unless you define 2 constants somewhere ( Public Const apikey_btce = "the key to use everywhere" etc )
apikey = apikey_btce

In this way, tests pass and as long as you don't export your secret module, nobody will have the private settings.

4. Export VBA code and import it into a test file

So now you have a setup where you can work in the master file, test the code and want to export that code. For this I found various snippets online, the version I use is here: https://pastebin.com/Lhb6yupj. What this macro does:

  • export all VBA modules that are listed and save them as .bas files in a specified directory
  • import those .bas files into the example file in that same directory

5. Push the updated files online

So from the master you can export code, but still need to  put it online. At that point, the Github desktop comes in. What the program does is basically show you the differences in the .bas files between the last version and the current version. Note: the program is looking at the exported files, not at the code inside your Excel file. After adding a short description of the update, it's simply pushing the "Commit to master" button and after that at the top "push". 

So that's how it works for me, hope that this how-to inspires you to share your code. Feedback is welcome in the comments. If you want to go a step further, like importing & exporting VBA code, do check out this project: http://ramblings.mcpher.com/Home/excelquirks/vbagit/gettingstarted


Hi Koen,

thanks for the article (funnily, I've actually come across your crypto currency workbook on GitHub in a completely different context).

I've recently written about using the git pre-commit hook to take care of auto-exporting VBA code from a workbook. In other words, every time you do git commit, git executes a little Python script that grabs the VBA modules from the workbook, writes it to the file system and adds it to the commit: https://www.xltrail.com/blog/auto-export-vba-commit-hook

What are your thoughts on that?

Hi Björn,
thanks for your feedback! I do like your example, it does make life more convenient for some people. It does however have two drawbacks for me:

  • I would have to install & learn python (I do speak it a little). I do like to stick to my Excel/VBA only setup (with the GitHub GUI). It's basically the "lowest/least tech option".
  • In my setup there are some modules I don't want to export, have a test module and one with my API keys. I prefer those not to end up on the web, so still have to white list my modules somewhere.

I'm trying to come up with an answer to your statement that "You are dependent on Excel, so if you copy your workbook from an email or another folder into your Git repository folder, your VBA export function will not run. "
My macro runs just fine, it's only that my export directory settings are fixed in my Excel-dev file. What I've once built is an extra settings file in my work directory. On opening my workbook, the workbook looks for that settings file in the same directory it is located. It than grabs those settings and runs on whatever machine or give an error if it can't find the settings nor the normal GitHub directory.

Congratulations @beeheap! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!