Best way to source control VBA code.

Question

Hi,

Was wondering if people have encountered a situation where they want to source control Excel VBA.

We have a team working on complex Macros. I want to be able to track code changes of VBA code of Excel Macros. Unfortunately xla's files are binaries and its not recommend to put binaries in TFS. (We are using TFS as our source control tool.) Instead of writing code to extract each Excel objects that has code, put in a text file and save to TFS, is there a better way. I'm all ears. If my options is the only one, then is thre code laying around that I could use for this extraction! 

thanks.

Answer 1

hi thanks for that.  I thank the orginal coder.  I made a minor adjustment. works perfert for forms and class modules.

Sub SaveVBACode()

'This code Exports all VBA modules

Dim i As Integer

Dim mName As String

Dim Fname As String

    With ThisWorkbook

        For i = 1 To .VBProject.VBComponents.Count

            If .VBProject.VBComponents(i).CodeModule.CountOfLines > 0 Then

                mName = .VBProject.VBComponents(i%).CodeModule.name

                 Fname = .path & "" & mName & ".txt"

                .VBProject.VBComponents(mName).Export Fname

            End If

        Next i

    End With

End Sub

Answer 2

You have the right idea.

Here is a discussion and some sample code to do the exporting and importing:

http://stackoverflow.com/questions/131605/best-way-to-do-version-control-for-ms-excel

Kevin