Is there a tactical (reading “hack”) solution to avoid using VBA code and an Excel worksheet as a single binary?

Question

As I understand it, when I create an Excel worksheet with VBA code, the VBA code is saved as binary with the worksheet. Therefore, I cannot put the code in the source control in a useful way, have several developers working on problems, it is difficult to execute, etc.

Is there any way around this without switching to VSTO, COM add-ons, etc.? For example. to load a sheet all this VBA at runtime from a web service, shared drive, etc.? Any ideas appreciated.

Thanks.

Answer 1

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

The assembly macro is as follows: I save it in the Build.xls file:

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, VBIDE stuff means you need to import a link called "Microsoft Visual Basic for Application Extensibility 5.3", I think.

Of course, you still have a problem with having to run 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 run the assembly Excel file, which displays the final sheet. You probably need to change some things to make them movable on the file system. Hope this helps!

Answer 2

I would highly recommend not trying to load VBA at runtime. VBIDE automation is flaky at best, and I think you'll run into quite a few supportive and supportive headaches.

My solution was to export peridocally code for version control as text files. I also remove all the code from xls (completely remove forms, modules, and class modules and remove code in Worksheet and Workbook modules) and put only this xls stub in the source control.

Rebuilding from the original control will consist of taking the xls stub, importing all Forms, Module Modules and Modules, as well as copying and pasting all the code into the Worksheet and Workbook Modules.

Despite the fact that it was a painful process, it did provide proper code management with all the usual features of distinguishing, branching, etc. Unfortunately, most of them were manual. I wrote an add-in that automated some of them, but it was a quickly hacked solution, i.e. Pretty rude and required manual intervention. If I ever get around to reviewing it and getting it to zero, then I'll let you know; -)

Answer 3

An interesting question ... we also have a problem with controlling the VBA source, but actually it never bypassed it.

Does this Microsoft KB article meet your criteria? The code is placed in a .BAS file, which can be located in a harsh location (and separately from .xls).

As I said, I never tried to do this, but it looks like this could be one approach.

Answer 4

I would also advise against loading at runtime and looking for a make install utility. In the end, if your code is under source control, you only need to update your books after committing.

Unfortunately, there is no such utility, or at least not what I could find.

Answer 5

I think that compared to alternatives (that is, constantly rebuilding Excel workbook files) it would be much harder to switch to VSTO or COM Addins using VBA to work with the light prototype. You also get the added benefit of not having “hacked” source code (i.e. you need more than guessing the password for the VBA project)

Answer 6

The shortest way forward is using SourceTools.xla addin from http://www.codeproject.com/KB/office/SourceTools.aspx

Another approach is to write a similar tool using COM bindings to your favorite programming language. One of my colleagues created one in Python that could export everything, including VBA code, contents and formulas of the worksheet, as well as links to VBA into text files, and could assemble a workbook from them. This was also before the XLSX format.