Is there a tactical (read 'hack') solution to avoid having the VBA code and Excel sheet as one binary file?

Question

As I understand it when I create an Excel sheet with VBA code the VBA code is saved as binary with the sheet. I therefore can't put the code into source control in a useful way, have multiple devs working on the problems, diffing is difficult, etc.

Is there a way round this without switching to VSTO, COM addins etc? E.g. for the sheet to load all is VBA in at runtime from a web service, shared drive, etc? Any ideas appreciated.

Thanks.

Answer 1

Interesting question ... we also have a problem with VBA source control but have never actually got around to address it.

Does this Microsoft KB article match your criteria ? The code is put in a .BAS file which can be in an arbitary location (and separate from the .xls).

As I said I've never actually tried to do this but it looks as if this might be one approach.

Answer 2

I would strongly recommend against trying to load the VBA at runtime. The VBIDE automation is flaky at best and I think you'll run into quite a lot of maintenance and support headaches.

My solution to this was to export the code peridocally for source control as text files. I would also remove all code from the xls (complete removal of Forms, Modules and Class Modules and deletion of code in Worksheet and Workbook modules) and put only this 'stub' xls into source control.

A rebuild from source control would thereforce consist of taking the 'stub' xls, importing all the Forms, Class Modules and Modules and copy and pasting in all the code into the Worksheet and Workbook modules.

Whilst this was a painful process, it did allow for proper source control of code with all the usual capabilities of diffing, branching etc. Unfortunately most of it was manual. I did write an add-in which automated some of this but it was quickly hacked together solution, i.e. fairly buggy and requiring manual intervention. If I ever get round to revisiting it and getting it up to scratch then I'll let you know ;-)

Answer 3

I wrote a kind of build system for Excel which imports the VBA code from source files (which can then be imported into source control, diffed, etc.). It works by creating a new Excel file which contains the imported code, so it might not work in your case.

The build macro looks like this, I save it in a file called Build.xls:

Sub Build()
    Dim path As String
    path = "excelfiles"

    Dim vbaProject As VBIDE.VBProject
    Set vbaProject = ThisWorkbook.VBProject

    ChDir "C:\Excel"
    ' Below are the files that are imported
    vbaProject.VBComponents.Import (path & "\something.frm")
    vbaProject.VBComponents.Import (path & "\somethingelse.frm")

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "Output.xls"
    Application.DisplayAlerts = True

    Application.Quit
End Sub

Now, the VBIDE stuff means you have to import a reference called “Microsoft Visual Basic for Applications Extensibility 5.3", I think.

Of course you still have the problem with having to start Excel to build. This can be fixed with a small VB script:

currentPath = CreateObject("Scripting.FileSystemObject") _
    .GetAbsolutePathName(".")
filePath = currentPath & "\" & "Build.xls"

Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
    .Workbooks.Open(filePath)
    .Application.Run "Build.Build"
End With
Set objXL = Nothing

Running the above script should start the build Excel file which outputs the resulting sheet. You problably have to change some things to make it movable in the file system. Hope this helps!

Answer 4

I'd also advise against loading at runtime and look for a make-style utility. After all, if your code is under source control then you should only need to update your workbooks after a commit.

Unfortunately, there isn't such a utility, or at least not one that I could find.

Answer 5

I think compared with the alternatives (ie constantly rebuilding Excel workbook files) it'd be much less hassle to move over to VSTO or COM Addins, using VBA for light prototyping work. You also get the added benefit of not having easily "hackable" source code (ie you need more than to guess the VBA project password)