Nrthugu

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).

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

There is an excellent solution to the vba version control problem here: https://github.com/hilkoc/vbaDeveloper

The nice part about this is that it exports your code automatically, as soon as you save your workbook. Also, when you open a workbook, it imports the code.

You don't need to run any build scripts or maven commands and you don't need to make any changes to your workbooks. It works for all.

It has also solved the import problem where modules such as ModName are being imported as ModName1 into a duplicate module. The importing works as it should, even when doing it multiple times.

As a bonus, it comes with a simple code formatter, that allows you to format your vba code as you write it within the VBA Editor.

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

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.