Source control of Excel VBA code modules

Question

I'd like to be able to source control my Excel spreadsheet's VBA modules (currently using Excel 2003 SP3) so that I can share and manage the code used by a bunch of different spreadsheets - and therefore I'd like to re-load them from files when the spreadsheet is opened.

I've got a module called Loader.bas, that I use to do most of the donkey work (loading and unloading any other modules that are required) - and I'd like to be able to load it up from a file as soon as the spreadsheet is opened.

I've attached the following code to the Workbook_Open event (in the ThisWorkbook class).

Private Sub Workbook_Open()
    Call RemoveLoader
    Call LoadLoader
End Sub

Where RemoveLoader (also within the ThisWorkbook class) contains the following code:

Private Sub RemoveLoader()
    Dim y As Integer
    Dim OldModules, NumModules As Integer
    Dim CompName As String

    With ThisWorkbook.VBProject
        NumModules = ThisWorkbook.VBProject.VBComponents.Count
        y = 1
        While y <= NumModules
            If .VBComponents.Item(y).Type = 1 Then
                CompName = .VBComponents.Item(y).Name
                If VBA.Strings.InStr(CompName, "Loader") > 0 Then
                    OldModules = ThisWorkbook.VBProject.VBComponents.Count
                    .VBComponents.Remove .VBComponents(CompName)
                    NumModules = ThisWorkbook.VBProject.VBComponents.Count
                    If OldModules - NumModules = 1 Then
                        y = 1
                    Else
                        MsgBox ("Failed to remove " & CompName & " module from VBA project")
                    End If
                End If
            End If
            y = y + 1
        Wend
    End With
End Sub

Which is probably a bit overcomplicated and slightly crude - but I'm trying everything I can find to get it to load the external module!

Often, when I open the spreadsheet, the RemoveLoader function finds that there's a "Loader1" module already included in the VBA project that it is unable to remove, and it also fails to load the new Loader module from the file.

Any ideas if what I'm trying to do is possible? Excel seems very fond of appending a 1 to these module names - either when loading or removing (I'm not sure which).

Answer 1

Usually the "Loader1" thing happens when Excel is asked to import a module and a module already exists with the same name. So if you import "Loader", then load it again and you'll get "Loader1". This would be because Excel doesn't know (or maybe just doesn't care) if it's really the same thing or a new chunk of functionality that just happens have the same module name, so it imports it anyway.

I can't think of a perfect solution, but I think I'd be inclined to try putting the load/unload logic in an add-in - that Workbook_Open thing looks a little vulnerable and having it in all workbooks is going to be a huge pain if the code ever needs to change (never say never). The XLA logic might be more complex (trickier to trap the necessary events, for one thing) but at least it'll only exist in one place.

Answer 2

Look at the VBAMaven page. I have a homegrown solution that uses the same concepts. I have a common library with a bunch of source code, an ant build and an 'import' VB script. Ant controls the build, which takes a blank excel file and pushes the needed code into it. @Mike is absolutely correct - any duplicate module definitions will automatically have a number appended to the module name. Also, class modules (as in Sheet and ThisWorkbook) classes require special treatment. You can't create those modules, you have to read the input file and write the buffer into the appropriate module. This is the VB script I currently use to do this. The section containing @ delimited text (i.e. @build file@) are placeholders - the ant build replaces these tags with meaningful content. It's not perfect, but works for me.

''
' Imports VB Basic module and class files from the src folder
' into the excel file stored in the bin folder. 
'

Option Explicit

Dim pFileSystem, pFolder,  pPath
Dim pShell
Dim pApp, book

Dim pFileName

pFileName = "@build file@"

Set pFileSystem = CreateObject("Scripting.FileSystemObject")

Set pShell = CreateObject("WScript.Shell")
pPath = pShell.CurrentDirectory

If IsExcelFile (pFileName) Then
    Set pApp = WScript.CreateObject ("Excel.Application")
    pApp.Visible = False
    Set book = pApp.Workbooks.Open(pPath & "\build\" & pFileName)
Else
    Set pApp = WScript.CreateObject ("Word.Application")
    pApp.Visible = False
    Set book = pApp.Documents.Open(pPath & "\build\" & pFileName)
End If


'Include root source folder code if no args set
If Wscript.Arguments.Count = 0 Then
    Set pFolder = pFileSystem.GetFolder(pPath & "\src")
    ImportFiles pFolder, book
    '
    ' Get selected modules from the Common Library, if any
    @common path@@common file@
Else
    'Add code from subdirectories of src . . .
    If Wscript.Arguments(0) <> "" Then
        Set pFolder = pFileSystem.GetFolder(pPath & "\src\" & Wscript.Arguments(0))
        ImportFiles pFolder, book
    End If
End If





Set pFolder = Nothing
Set pFileSystem = Nothing
Set pShell = Nothing


If IsExcelFile (pFileName) Then
    pApp.ActiveWorkbook.Save
Else
    pApp.ActiveDocument.Save
End If

pApp.Quit
Set book = Nothing
Set pApp = Nothing


'' Loops through all the .bas or .cls files in srcFolder
' and calls InsertVBComponent to insert it into the workbook wb.
'
Sub ImportFiles(ByVal srcFolder, ByVal obj)
    Dim fileCollection, pFile
    Set fileCollection = srcFolder.Files
    For Each pFile in fileCollection
     If Right(pFile, 3) = "bas _
       Or Right(pFile, 3) = "cls _
       Or Right(pFile, 3) = "frm Then
         InsertVBComponent obj, pFile
     End If
    Next
    Set fileCollection = Nothing
End Sub


'' Inserts the contents of CompFileName as a new component in 
'  a Workbook or Document object.
'
'  If a class file begins with "Sheet", then the code is
'  copied into the appropriate code module 1 painful line at a time.
'
'  CompFileName must be a valid VBA component (class or module) 
Sub InsertVBComponent(ByVal obj, ByVal CompFileName)
    Dim t, mName
    t = Split(CompFileName, "\")
    mName = Split(t(UBound(t)), ".")
    If IsSheetCodeModule(mName(0), CompFileName) = True Then
        ImportCodeModule obj.VBProject.VBComponents(mName(0)).CodeModule, _
                         CompFileName
    Else
     If Not obj Is Nothing Then
         obj.VBProject.VBComponents.Import CompFileName
        Else
         WScript.Echo  "Failed to import " & CompFileName
        End If
    End If 
End Sub

''
' Imports the code in the file fName into the workbook object
' referenced by mName.
' @param target destination CodeModule object in the excel file
' @param fName file system file containing code to be imported
Sub ImportCodeModule (ByVal target, ByVal fName)
    Dim shtModule, code, buf    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set buf = fso.OpenTextFile(fName, ForReading, False, TristateUseDefault)
    buf.SkipLine
    code = buf.ReadAll

    target.InsertLines 1, code
    Set fso = Nothing
End Sub


''
' Returns true if the code module in the file fName
' appears to be a code module for a worksheet.
Function IsSheetCodeModule (ByVal mName, ByVal fName)
    IsSheetCodeModule = False
    If mName = "ThisWorkbook" Then
       IsSheetCodeModule = False
    ElseIf Left(mName, 5) = "Sheet" And _
       IsNumeric(Mid (mName, 6, 1)) And _
       Right(fName, 3) = "cls Then
       IsSheetCodeModule = True
    End If
End Function

''
' Returns true if fName has a xls file extension
Function IsExcelFile (ByVal fName)
    If Right(fName, 3) = "xls" Then
        IsExcelFile = True
    Else
        IsExcelFile = False
    End If 
End Function

Answer 3

I've been working on exactly this for months. I think I figured it out.

If the VB Project is trying to remove a module containing something in the call stack, it delays the removal until the call stack pops the module being replaced.

To avoid a module being in the call stack, launch your code with Application.OnTime

Private Sub Workbook_Open()

    'WAS: module_library (1)

    Application.OnTime (Now + TimeValue("00:00:01")), "load_library_kicker_firstiter"

End Sub

If you are self-healing your code like I am, you'll also have to launch your code that overwrites the 'calling' code with that same strategy.

I did not perform extensive testing yet, I am in total celebration mode, but this gets me extremely close to straightforward 99.9% self-healing code within a standalone .xls file without any other tricks

-Mike G

mguydish at Y! dot com