Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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.

 Source Control
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

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:

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:
AddIn and button
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.

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.

 Now we can Diff!
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

Creating an Excel Add-In
Exporting VBA Modules
Tortoise SVN

Comments (6)


Hey Jonah,

For the part where you actually create an Add-In (and add the button), that's not my specialty. I pulled it off by following the steps in the article I linked to. You should follow it closely too:
Thanks Alain.

Yea, that's the part where I'll have to do some research. The page you've referenced isn't very explicit about this. Near the bottom of the page, there's a section titled:
Assigning Add-In Macros to Custom Toolbar Buttons and Menu Items
...but I don't quite understand it. Ideally, I'd like to create a button in the Custom Toolbar of the "Add-In" ribbon, and attach your function to it.



Ah, yeah I kind of wanted to do that too, but knowing nothing about modifying the ribbon, I settled for the simple "Quick Access Toolbar" which you can add to just by right clicking it.

It's not just us either. According to this xtremevbtalk thread, the existing methods of manipulating the ribbon involve programatically changing it from a COM or .NET add-in,  or hacking the XML of an XLA add-in. It does give advice on how to do the latter in the simplest way possible, but it's still not very simple.

Perhaps this 'ribbon customizer' add-on would be of use?

Rory ArchibaldGrand Poobah
Most Valuable Expert 2011
Top Expert 2011

One of the best sites for Ribbon customisation is Ron de Bruin's here:
That was the part which I didn't clue-in; when you say:
"Here I show how I've added the macro to the toolbar as a small button next to save"
I didn't know how to do that.

"Quick Access Toolbar" was the missing key....

1. Click the Office button, top left.
2. Press "Excel Options" button
3. Choose "Customize"
4. From the "Popular Commands" select list, choose: Macros
5. Select "ExportAllComponents" from the list, and click "Add".
6. Click Ok.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.