Extracting VBA from a Excel spreadsheet

Is there a clean way to extract the VBA from a spreadsheet and store it in a repository.

The spreadsheets hardly ever change but the VBA does. Storing the whole spreadsheet in the repository makes it difficult to do diffs across the different VBA revisions to see what code has changed and who changed it.

We use VBA 6.5 / Excel 2003.

From stackoverflow

• Previous versions of Excel and Access (prior to 2003) supported VBA Source Code Version Control via an add-in. I have used this very effectively in Office 2000.

  Visual SourceSafe (VSS) support for VBA was dropped with the release of Office 2003, but the add-in that was shipped with Office XP Developer apparently works with Office 2003.

  Microsoft Knowledge Base articles:

  • Using Visual SourceSafe with Documents and VBA Code

  • Using the Visual SourceSafe Add-In with the Visual Basic Environment

  Failing that you can use this code to extract the VBA code (from here but it was missing the final object cleanups). Read the webpage for caveats:

  option explicit
  
  Const vbext_ct_ClassModule = 2
  Const vbext_ct_Document = 100
  Const vbext_ct_MSForm = 3
  Const vbext_ct_StdModule = 1
  
  Main
  
  Sub Main
      Dim xl
      Dim fs
      Dim WBook
      Dim VBComp
      Dim Sfx
      Dim ExportFolder
  
      If Wscript.Arguments.Count <> 1 Then
          MsgBox "As the only argument, give the FULL path to an XLS file to extract all the VBA from it."
      Else
  
          Set xl = CreateObject("Excel.Application")
          Set fs = CreateObject("Scripting.FileSystemObject")
  
          xl.Visible = true
  
          Set WBook = xl.Workbooks.Open(Trim(wScript.Arguments(0)))
  
          ExportFolder = WBook.Path & "\" & fs.GetBaseName(WBook.Name)
  
          fs.CreateFolder(ExportFolder)
  
          For Each VBComp In WBook.VBProject.VBComponents
              Select Case VBComp.Type
                  Case vbext_ct_ClassModule, vbext_ct_Document
                      Sfx = ".cls"
                  Case vbext_ct_MSForm
                      Sfx = ".frm"
                  Case vbext_ct_StdModule
                      Sfx = ".bas"
                  Case Else
                      Sfx = ""
              End Select
              If Sfx <> "" Then
                  On Error Resume Next
                  Err.Clear
                  VBComp.Export ExportFolder & "\" & VBComp.Name & Sfx
                  If Err.Number <> 0 Then
                      MsgBox "Failed to export " & ExportFolder & "\" & VBComp.Name & Sfx
                  End If
                  On Error Goto 0
              End If
          Next
  
          xl.Quit
  
          Set fs = Nothing
          Set xl = Nothing
  
      End If
  End Sub
  

• Couple of similar questions:

  • Multiple developers in a VBA project

  • Exporting VBA code from Multiple Excel documents to put into version control

  Mitch Wheat : I think that first link only applies to pre-2003 versions of Office

  Cannonade : Actually, both questions are fairly generic, but I think the various answers cover all the options.

  Anonymous Type : thanks for links

  Cannonade : @anonymous-type np :)

• The other answers to this question address it pretty well, but if you're just starting to write the code, look into VSTO. It provides managed code for Office, and best of all, you can even do it in C# or whatever .NET language strikes your fancy. It also works with Excel 2003, which is a bonus, in your case.

• You already have a great answer, but allow me to add an aside.

  It is quite possible to keep all of your VBA code in a spreadsheet seperate from the spreadsheet(s) that contains the data. If you do this it might make your comparisons easier.

  nekomatic : Can anyone point to an example of this please?

  JonnyBoats : Take a look at http://msdn.microsoft.com/en-us/library/aa221273%28office.11%29.aspx which shows how to create a new workbook seperate from the one that contains the VBA.

  JonnyBoats : See also http://msdn.microsoft.com/en-us/library/aa221367%28office.11%29.aspx for how to open an existing workbook from VBA.

• Answered the same thing in another topic. Another alternative for you to try.

  http://stackoverflow.com/questions/608872/exporting-vba-code-from-multiple-excel-documents-to-put-into-version-control/1453989#1453989

  (is there a way to cross-link these topics and/or answers?)