Source control of Excel VBA code modules

excel, vba

Solution

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

Problem

Original source