Excel VBA - industrialisation...Part Two "Export VBA code to file system"

on
In this post we will follow on from Part One and look at a further piece of the toolkit needed to utilise Excel in an industrialised fashion.

This post covers the case of exporting VBA modules from Excel.  This is needed for industrialisation - the way to create stable, robust, version controlled Excel code that is integrated with source-code control.

Within the Excel VBIDE we see the following:


We need to get these VBA modules out of the Excel file (.xlsm) format and into the Windows file system.  At this point we now have files that can be loaded into svn, guthub or any other source code control system.
In a real implementation, rather than a cut down demonstration/proof-of-concept as shown here, the source code files (.bas, .cls, .frm) are also modified at run-time by the export module to include further meta-data to assist with versioning.
The key point is that the source code is controlled at the module level, rather than by versioning a specific spreadsheet.  This more granular control means that code can be managed and supported in a much more efficient manner.  Code can be created within modules and a check-in process can ensure that each module is updated to the latest version.  No more manual search and replace for a vast accumulation of spreadsheets on a series of network shares.


To replicate:
  1. Download the code from github
  2. Create a new Excel workbook
  3. Save the new workbook as an xlsm format workbook
  4. Add reference to VBE (see below screenshot)
  5. Import the two modules downloaded from github
  6. Open the VBA editor (Alt+F11)
  7. Execute the method EntryPointModulesExport()
  8. You will now see two files created in the folder where the new workbook is saved - "exim.bas" and "ReformatMePlease.bas"
[Note that this is a cut down implementation and does not include error checking and meta-data injection]

See also:

Comments

Post a Comment