Automate Exporting all Components in an Excel Project

Source Control is Important
This is just one reason why you might be interested in exporting your Excel project code. Excel files are in a format such that revision control software can't do much other than save a copy of every version of your file. Especially if you're most concerned about the code you develop in a VBA project, you might be interested in rolling back changes, differences, and comparing code in other ways. The only way to do that is to export all sheet code, modules, and forms to text based files and add those to a repository.

 
You might also want to export your modules simply to create personal backups of your code, add it to a visual studio project, send it to a friend, etc. Whatever your reason, it's a tedious task. Each time you want to export code, you have to do it one module at a time, browse for the save location, save it, and then move on to the next file. Often times it's done infrequently enough that you don't need a script, but if it becomes a regular part of your day, or you decide to back up years worth of excel projects, it might be time to automate the process.

Automating Module Exporting
I've created a VB script that does an all-round decent job of exporting any of the 4 component types in an automated way. As a bonus, it checks the components to see if there is actually any code in them - if not, there's no point in exporting an empty file, so I skip it.

Option Explicit
                      'Remember to add a reference to Microsoft Visual Basic for Applications Extensibility 
                      'Exports all VBA project components containing code to a folder in the same directory as this spreadsheet.
                      Public Sub ExportAllComponents()
                          Dim VBComp As VBIDE.VBComponent
                          Dim destDir As String, fName As String, ext As String 
                          'Create the directory where code will be created.
                          'Alternatively, you could change this so that the user is prompted
                          If ActiveWorkbook.Path = "" Then
                              MsgBox "You must first save this workbook somewhere so that it has a path.", , "Error"
                              Exit Sub
                          End If
                          destDir = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Modules"
                          If Dir(destDir, vbDirectory) = vbNullString Then MkDir destDir
                          
                          'Export all non-blank components to the directory
                          For Each VBComp In ActiveWorkbook.VBProject.VBComponents
                              If VBComp.CodeModule.CountOfLines > 0 Then
                                  'Determine the standard extention of the exported file.
                                  'These can be anything, but for re-importing, should be the following:
                                  Select Case VBComp.Type
                                      Case vbext_ct_ClassModule: ext = ".cls"
                                      Case vbext_ct_Document: ext = ".cls"
                                      Case vbext_ct_StdModule: ext = ".bas"
                                      Case vbext_ct_MSForm: ext = ".frm"
                                      Case Else: ext = vbNullString
                                  End Select
                                  If ext <> vbNullString Then
                                      fName = destDir & "\" & VBComp.Name & ext
                                      'Overwrite the existing file
                                      'Alternatively, you can prompt the user before killing the file.
                                      If Dir(fName, vbNormal) <> vbNullString Then Kill (fName)
                                      VBComp.Export (fName)
                                  End If
                              End If
                          Next VBComp
                      End Sub
                      

Select all Open in new window

There's room to make some changes. The behaviour that suits me best is making a new directory alongside the workbook called "(Workbook name) Modules" and filling it with all the code. This is coded in the script, but could easily be changed to some other path, or to prompt the user for the path. You can also customize the exporting extension, but I use the standard extensions excel uses so that files can be re-imported easily. Finally, I automatically overwrite any existing versions of the exported files, but you could easily arrange to prompt the user first. If using the code directly, remember to use the Reference mentioned at the top of the code.

But adding this code to every workbook is also tedious...
And so we must go to the next level - converting it into an Excel Add-In - this way we can use the export feature easily on any open workbook. Following these handy tips on how to build an Add-In, I created one. I've attached it to this article so that others can benefit from it too:
Export-Components.xls

It's attached it as an xls file, but by just renaming an xla file, it will be an Add-In. To make it easily findable, drag it to your (username)/Application Data/Microsoft/AddIns/ folder and it will be easily findable under options AddIns. Will work in Excel 2003 or 2007. Here I show how I've added the macro to the toolbar as a small button next to save:

Note that to use the AddIn, you'll of course need to give your VBProject trusted access in Excel. If you haven't already configured excel to trust the VBProject, you must do so. In Excel 2007, you go to Options > Trust Center > Add-ins and deselect "Require Application Add-ins to be signed by Trusted Publisher". You should also go to "Macro Settings" in this same location and Enable macros and check "Trust access to the VBA project object model. I'm sure this isn't a problem for you though, because if you're interested in this automated method to export macros and modules you're written in Excel VBA, you're probably already all over the Excel security stuff.

Summary
Now that there's an easy way to export all of your Excel VBA project into a human-readable format, maintaining a code repository or doing proper versioning isn't so bad. Look - I know Excel VBA isn't widely considered a proper development environment, but sometimes you're forced to adapt to what you have to work with. Relieving that bottleneck so that you can work with code in tools like Diff can really take the edge off of stressful Excel programming.

 
By the way, all source control screen-shots are of tools provided by Tortoise SVN.
Here's to better coding practice, in spite of Microsoft's outdated constructs!

--
Alain Bryden

References
Creating an Excel Add-In
Exporting VBA Modules
VBComponents
Tortoise SVN