Excel VBA - industrialisation...Part One "Code to write code"

on
In previous posts we have covered the joys of Excel VBA.  Although I am a big fan and have written many tens of thousands of lines of VBA over the years, the language is in the doldrums - the cool kids using MEAN, Scala, OCaml, Haskell or something equally cutting-edge would never want to use VBA.   

One of the criticisms made of VBA is that it's not possible to use quality tooling and source code control such as subversion or git.


This is a common perception but it's also wrong.  

What is true is that 'out of the box' there is no integration with source code control.  However, with some judicious use of the VBE library it's possible to integrate Excel workbooks with VBA into source code control.
On the development machine I am using (Win64 with Excel 64bit) the library to reference is Microsoft Visual Basic for Applications Extensibility 5.3 (hereafter VBE).  This library opens up a world of possibilities for the developer, many of which are non-obvious and are of great value...
This will be the first post in a series which I will write over time and will publish in part to github.  The code in this post can be found here.
So, let's start with a pretty simple piece of code.  The beauty of VBE is that you can write VBA code that can interact with other VBA code.
Let's take an example.  To run this example please follow these steps:
  1. Download the two BAS files from github
  2. Create a new Excel 2013 workbook with one sheet
  3. Save the new workbook as an xlsm macro-enabled workbook
  4. Import the two BAS files into the VB IDE (Alt+F11).
  5. Have a look at look at "ReformatMePlease" and the subroutine "TestThisCode".  Clearly a dummy subroutine with no real purpose...
  6. Now, have a look at CustomVBE2 and the method EntryPointAddFunctionName(). 
  7. Execute EntryPointAddFunctionName().
  8. Now have a look at "TestThisCode". 
  9. You will see a constant has been added to the method immediately below the method name Const cstrMethodName As String = "ReformatMePlease.TestThisCode  "
So we have used VBA code to write more VBA code
This is the power of the VBE.  From within VBA code you can create and manipulate VBA .  In this example we have added a constant that is created dynamically from the module name and method name. But we can do a lot more within VBA - we can clean up code, remove unused variables and constants, add line numbers, add powerful error handling capabilities and a considerable range of other useful task.
The most powerful task is to industrialise Excel code.  By this, I mean to use the VBE to allow for dynamic integration with source code control. This then allows all business logic within VBA to enter a formal development cycle, rather than VBA development as a second class citizen in the development world.
     

Comments

Post a Comment