为MS Excel执行版本控制的最佳方法

UserForms可以像VBA代码一样导出和导入.唯一的区别是导出表单时将创建两个文件(您将获得每个UserForm的a .frm和一个.frx文件).其中一个包含你编写的软件,另一个是二进制文件(我很确定)定义了表单的布局.

微软Excel对象(MEOS)(意思是Sheet1,Sheet2,ThisWorkbook等),可以导出为一个.cls文件.但是,如果要将此代码恢复到工作簿中,如果尝试以与VBA模块相同的方式导入它,则如果工作簿中已存在该工作表,则会出现错误.

为了解决这个问题,我决定不尝试将.cls文件导入Excel,而是将.cls文件作为字符串读入excel,然后将此字符串粘贴到空MEO中.这是我的ImportCodeModules:

Sub ImportCodeModules(dir As String)

Dim modList(0 To 0) As String
Dim vbaType As Integer

' delete all forms, modules, and code in MEOs
With ThisWorkbook.VBProject
    For Each comp In .VBComponents

        moduleName = comp.CodeModule.Name

        vbaType = .VBComponents(moduleName).Type

        If moduleName <> "DevTools" Then
            If vbaType = 1 Or _
                vbaType = 3 Then

                .VBComponents.Remove .VBComponents(moduleName)

            ElseIf vbaType = 100 Then

                ' we can't simply delete these objects, so instead we empty them
                .VBComponents(moduleName).CodeModule.DeleteLines 1, .VBComponents(moduleName).CodeModule.CountOfLines

            End If
        End If
    Next comp
End With

' make a list of files in the target directory
Set FSO = CreateObject("Scripting.FileSystemObject")
Set dirContents = FSO.getfolder(dir) ' figure out what is in the directory we're importing

' import modules, forms, and MEO code back into workbook
With ThisWorkbook.VBProject
    For Each moduleName In dirContents.Files

        ' I don't want to import the module this script is in
        If moduleName.Name <> "DevTools.vba" Then

            ' if the current code is a module or form
            If Right(moduleName.Name, 4) = ".vba" Or _
                Right(moduleName.Name, 4) = ".frm" Then

                ' just import it normally
                .VBComponents.Import dir & moduleName.Name

            ' if the current code is a microsoft excel object
            ElseIf Right(moduleName.Name, 4) = ".cls" Then
                Dim count As Integer
                Dim fullmoduleString As String
                Open moduleName.Path For Input As #1

                count = 0              ' count which line we're on
                fullmoduleString = ""  ' build the string we want to put into the MEO
                Do Until EOF(1)        ' loop through all the lines in the file

                    Line Input #1, moduleString  ' the current line is moduleString
                    If count > 8 Then            ' skip the junk at the top of the file

                        ' append the current line `to the string we'll insert into the MEO
                        fullmoduleString = fullmoduleString & moduleString & vbNewLine

                    End If
                    count = count + 1
                Loop

                ' insert the lines into the MEO
                .VBComponents(Replace(moduleName.Name, ".cls", "")).CodeModule.InsertLines .VBComponents(Replace(moduleName.Name, ".cls", "")).CodeModule.CountOfLines + 1, fullmoduleString

                Close #1

            End If
        End If

    Next moduleName
End With

End Sub

SaveCodeModules "C:\...\YourDirectory\Project\source\"
ImportCodeModules "C:\...\YourDirectory\Project\source\"