Featured post

Fixed Income Trading: New venues

A simple question came up recently in a conversation – how many new Fixed Income trading venues are there?  I could not think of anywhere th...

Friday, 27 March 2015

Excel VBA - industrialisation...Part Five "Integration with Git"

Here we look at integrating Excel VBA with Git.  I will assume that the reader is familiar with Excel.  A great book on Git is downloadable from here.  I won't go into details on Git - it's covered in the pdf link.
So, one key deliverable for Excel Industrialisation is the use of source code control.  There is a slight challenge in the sense that this would normally be done using a user interface such as github rather than from within the VBA development environment.  Happily there is a simple way to integrate Git with VBA.

The key functionality needed is delivered in the Windows Script Host Object Model (typically at C:\Windows\System32\wshom.ocx).  This contains IWshRuntimeLibrary.WshShell which is where most of the functionality used to interface to git is located.
IWshRuntimeLibrary.WshShell  has a method Exec which essentially allows you to create an instance of a command line and then interact with that command line through the StdIn, StdOut and StdErr interfaces.  Git is very much a command line based application so that works well with IWshRuntimeLibrary.WshShell.

On github you can find the code to reproduce this. To reproduce:

  1. Download the code from github
  2. Create a new Excel workbook.
  3. Save the new workbook as an xlsm format workbook
  4. Enter the VBA editor
  5. Import the module you just downloaded from github
  6. Add a reference to Windows Script Host Object Model within the new workbook
  7. Modify the strSourceDirectory constant in the WriteToGit method
  8. Execute the "WriteToGit" method

As with all of the code published in this series, this is not production standard, it's proof-of-concept to illustrate what can be done and what has been done with other firms.  So there is no error handling and other features that are needed to make the user experience robust and a real value-add are missing.
See also
Excel industrialisation...Part One "Code to write code"    
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"  
Excel industrialisation...Part Ten "Enumerating COMAddins"

No comments:

Post a Comment