源代码管理Excel VBA代码模块 [英] Source control of Excel VBA code modules

问题描述

我想能够控制我的Excel电子表格的VBA模块（目前使用Excel 2003 SP3），以便我可以共享和管理一堆不同的电子表格使用的代码 - 因此我想重新在打开电子表格时，从文件中加载它们。

我有一个名为Loader.bas的模块，我用来做大部分的驴工作（加载和卸载所需的任何其他模块） - 我想在电子表格打开后立即从文件加载。

我有将以下代码附加到Workbook_Open事件（在ThisWorkbook类中）。

  Private Sub Workbook_Open（）
调用RemoveLoader 
调用LoadLoader 
 End Sub 
  

其中RemoveLoader（也在ThisWorkbook类内）包含以下代码：

  Private Sub RemoveLoader（）
 Dim y As Integer 
 Dim OldModules，NumModules像在teger 
 Dim CompName As String 
 
 With ThisWorkbook.VBProject 
 NumModules = ThisWorkbook.VBProject.VBComponents.Count 
y = 1 
当y <= NumModules 
如果.VBComponents.Item（y）.Type = 1然后
 CompName = .VBComponents.Item（y）.Name 
如果VBA.Strings.InStr（CompName，Loader）> ; 0然后
 OldModules = ThisWorkbook.VBProject.VBComponents.Count 
 .VBComponents.Remove .VBComponents（CompName）
 NumModules = ThisWorkbook.VBProject.VBComponents.Count 
如果OldModules  -  NumModules = 1然后
y = 1 
 Else 
 MsgBox（无法从VBA项目中删除& CompName&模块）
 End If 
 End If 
结束如果
y = y + 1 
 Wend 
结束
 End Sub 
  

这可能有点过于复杂，稍微粗暴 - 但我正在尝试所有可以找到的东西来加载外部模块！

通常，当我打开电子表格时，RemoveLoader函数会发现VBA项目中已经包含一个Loader1模块，它无法删除，而且f从文件中加载新的Loader模块。

任何想法，如果我想要做的是可能的？ Excel似乎非常喜欢在这些模块名称中添加1，加载或删除（我不知道哪个）。

解决方案

p>查看VBAMaven页面。我有一个本土的解决方案，使用相同的概念。我有一个共同的库，一堆源代码，一个ant构建和一个'import'VB脚本。 Ant控制构建，它需要一个空白的excel文件，并将所需的代码推入它中。 @Mike绝对正确 - 任何重复的模块定义都会自动将一个数字附加到模块名称。此外，类模块（如Sheet和ThisWorkbook）类需要特殊处理。您不能创建这些模块，您必须读取输入文件并将缓冲区写入相应的模块。这是我目前使用的VB脚本。包含@分隔文本的部分（即@build文件@）是占位符 - ant构建使用有意义的内容替换这些标记。这不是完美的，但对我来说很有用。

 ''
'从src导入VB Basic模块和类文件文件夹
'存入bin文件夹中的excel文件。 
'
 
选项显式
 
 Dim pFileSystem，pFolder，pPath 
 Dim pShell 
 Dim pApp，book 
 
 Dim pFileName 
 
 pFileName =@build file @
 
设置pFileSystem = CreateObject（Scripting.FileSystemObject）
 
设置pShell = CreateObject （WScript.Shell）
 pPath = pShell.CurrentDirectory 
 
如果IsExcelFile（pFileName）然后
设置pApp = WScript.CreateObject（Excel.Application）
 pApp.Visible = False 
设置书= pApp.Workbooks.Open（pPath&\build\& pFileName）
 Else 
设置pApp = WScript.CreateObject（ Word.Application）
 pApp.Visible = False 
设置书= pApp.Documents.Open（pPath&\build\& pFileName）
 End If 
 
 
'如果没有设置参数，则包含根源文件夹代码
如果Wscript.Arguments.Count = 0然后
设置pFolder = pFileSystem.GetFolder（pPath&\src ）
 ImportFiles pFolder，book 
'
 '从公共库获取选定的模块，如果有
 @common path @@ common file @ 
 Else 
'从src的子目录添加代码。 。 。 
如果Wscript.Arguments（0）<> 然后
设置pFolder = pFileSystem.GetFolder（pPath&\src\&Wscript.Arguments（0））
 ImportFiles pFolder，book 
 End If 
结束如果
 
 
 
 
 
设置pFolder = Nothing 
设置pFileSystem = Nothing 
设置pShell = Nothing 
 
 
如果IsExcelFile（pFileName）然后
 pApp.ActiveWorkbook.Save 
 Else 
 pApp.ActiveDocument.Save 
 End If 
 
 pApp.Quit 
设置书=没有
设置pApp =没有
 
 
'通过srcFolder $ b $中的所有.bas或.cls文件循环b'并调用InsertVBComponent将其插入到工作簿wb中。 
'
 Sub ImportFiles（ByVal srcFolder，ByVal obj）
 Dim fileCollection，pFile 
设置fileCollection = srcFolder.Files 
对于fileCollection中的每个pFile 
如果右（pFile，3）=bas _ 
或右（pFile，3）=cls _ 
或右（pFile，3）=frm然后
 InsertVBComponent obj，pFile 
结束If 
下一个
设置fileCollection = Nothing 
 End Sub 
 
 
'将$ F $中的CompFileName的内容作为新组件插入b'一个工作簿或文档对象
'
'如果类文件以Sheet开头，那么代码是
'复制到相应的代码模块1一次痛苦的一行。 
'
'CompFileName必须是有效的VBA组件（类或模块）
 Sub InsertVBComponent（ByVal obj，ByVal CompFileName）
 Dim t，mName 
t = Split CompFileName，\）
 mName = Split（t（UBound（t）），。）
如果IsSheetCodeModule（mName（0） ，CompFileName）= True Then 
 ImportCodeModule obj.VBProject.VBComponents（mName（0））。CodeModule，_ 
 CompFileName 
 Else 
如果不是obj，则为
 obj.VBProject.VBComponents.Import CompFileName 
 Else 
 WScript.Echo无法导入& CompFileName 
 End If 
 End If 
 End Sub 
 
''
'将文件fName中的代码导入工作簿对象
'由mName引用。 
'@param目标目标excel文件中的CodeModule对象
'@param fName包含要导入代码的文件系统文件
 Sub ImportCodeModule（ByVal target，ByVal fName）
 Dim shtModule ，代码，buf 
 Dim fso 
设置fso = CreateObject（Scripting.FileSystemObject）
 Const ForReading = 1，ForWriting = 2，ForAppending = 3 
 Const TristateUseDefault = -2 ，TristateTrue = -1，TristateFalse = 0 
 
设置buf = fso.OpenTextFile（fName，ForReading，False，TristateUseDefault）
 buf.SkipLine 
 code = buf.ReadAll 
 
 target.InsertLines 1，代码
设置fso = Nothing 
 End Sub 
 
 
''
'如果代码返回true文件fName 
'中的模块似乎是工作表的代码模块。 
函数IsSheetCodeModule（ByVal mName，ByVal fName）
 IsSheetCodeModule = False 
如果mName =ThisWorkbook然后
 IsSheetCodeModule = False 
 ElseIf Left（mName，5）= SheetAnd _ 
 IsNumeric（Mid（mName，6，1））和_ 
 Right（fName，3）=cls Then 
 IsSheetCodeModule = True 
 End If 
结束函数
 
''
'如果fName有一个xls文件扩展名，则返回true 
函数IsExcelFile（ByVal fName）
如果右（fName，3） =xls然后
 IsExcelFile = True 
 Else 
 IsExcelFile = False 
如果
结束函数
  

I'd like to be able to source control my Excel spreadsheet's VBA modules (currently using Excel 2003 SP3) so that I can share and manage the code used by a bunch of different spreadsheets - and therefore I'd like to re-load them from files when the spreadsheet is opened.

I've got a module called Loader.bas, that I use to do most of the donkey work (loading and unloading any other modules that are required) - and I'd like to be able to load it up from a file as soon as the spreadsheet is opened.

I've attached the following code to the Workbook_Open event (in the ThisWorkbook class).

Private Sub Workbook_Open()
    Call RemoveLoader
    Call LoadLoader
End Sub

Where RemoveLoader (also within the ThisWorkbook class) contains the following code:

Private Sub RemoveLoader()
    Dim y As Integer
    Dim OldModules, NumModules As Integer
    Dim CompName As String

    With ThisWorkbook.VBProject
        NumModules = ThisWorkbook.VBProject.VBComponents.Count
        y = 1
        While y <= NumModules
            If .VBComponents.Item(y).Type = 1 Then
                CompName = .VBComponents.Item(y).Name
                If VBA.Strings.InStr(CompName, "Loader") > 0 Then
                    OldModules = ThisWorkbook.VBProject.VBComponents.Count
                    .VBComponents.Remove .VBComponents(CompName)
                    NumModules = ThisWorkbook.VBProject.VBComponents.Count
                    If OldModules - NumModules = 1 Then
                        y = 1
                    Else
                        MsgBox ("Failed to remove " & CompName & " module from VBA project")
                    End If
                End If
            End If
            y = y + 1
        Wend
    End With
End Sub

Which is probably a bit overcomplicated and slightly crude - but I'm trying everything I can find to get it to load the external module!

Often, when I open the spreadsheet, the RemoveLoader function finds that there's a "Loader1" module already included in the VBA project that it is unable to remove, and it also fails to load the new Loader module from the file.

Any ideas if what I'm trying to do is possible? Excel seems very fond of appending a 1 to these module names - either when loading or removing (I'm not sure which).

解决方案

Look at the VBAMaven page. I have a homegrown solution that uses the same concepts. I have a common library with a bunch of source code, an ant build and an 'import' VB script. Ant controls the build, which takes a blank excel file and pushes the needed code into it. @Mike is absolutely correct - any duplicate module definitions will automatically have a number appended to the module name. Also, class modules (as in Sheet and ThisWorkbook) classes require special treatment. You can't create those modules, you have to read the input file and write the buffer into the appropriate module. This is the VB script I currently use to do this. The section containing @ delimited text (i.e. @build file@) are placeholders - the ant build replaces these tags with meaningful content. It's not perfect, but works for me.

''
' Imports VB Basic module and class files from the src folder
' into the excel file stored in the bin folder. 
'

Option Explicit

Dim pFileSystem, pFolder,  pPath
Dim pShell
Dim pApp, book

Dim pFileName

pFileName = "@build file@"

Set pFileSystem = CreateObject("Scripting.FileSystemObject")

Set pShell = CreateObject("WScript.Shell")
pPath = pShell.CurrentDirectory

If IsExcelFile (pFileName) Then
    Set pApp = WScript.CreateObject ("Excel.Application")
    pApp.Visible = False
    Set book = pApp.Workbooks.Open(pPath & "\build\" & pFileName)
Else
    Set pApp = WScript.CreateObject ("Word.Application")
    pApp.Visible = False
    Set book = pApp.Documents.Open(pPath & "\build\" & pFileName)
End If


'Include root source folder code if no args set
If Wscript.Arguments.Count = 0 Then
    Set pFolder = pFileSystem.GetFolder(pPath & "\src")
    ImportFiles pFolder, book
    '
    ' Get selected modules from the Common Library, if any
    @common path@@common file@
Else
    'Add code from subdirectories of src . . .
    If Wscript.Arguments(0) <> "" Then
        Set pFolder = pFileSystem.GetFolder(pPath & "\src\" & Wscript.Arguments(0))
        ImportFiles pFolder, book
    End If
End If





Set pFolder = Nothing
Set pFileSystem = Nothing
Set pShell = Nothing


If IsExcelFile (pFileName) Then
    pApp.ActiveWorkbook.Save
Else
    pApp.ActiveDocument.Save
End If

pApp.Quit
Set book = Nothing
Set pApp = Nothing


'' Loops through all the .bas or .cls files in srcFolder
' and calls InsertVBComponent to insert it into the workbook wb.
'
Sub ImportFiles(ByVal srcFolder, ByVal obj)
    Dim fileCollection, pFile
    Set fileCollection = srcFolder.Files
    For Each pFile in fileCollection
        If Right(pFile, 3) = "bas _
          Or Right(pFile, 3) = "cls _
          Or Right(pFile, 3) = "frm Then
            InsertVBComponent obj, pFile
        End If
    Next
    Set fileCollection = Nothing
End Sub


'' Inserts the contents of CompFileName as a new component in 
'  a Workbook or Document object.
'
'  If a class file begins with "Sheet", then the code is
'  copied into the appropriate code module 1 painful line at a time.
'
'  CompFileName must be a valid VBA component (class or module) 
Sub InsertVBComponent(ByVal obj, ByVal CompFileName)
    Dim t, mName
    t = Split(CompFileName, "\")
    mName = Split(t(UBound(t)), ".")
    If IsSheetCodeModule(mName(0), CompFileName) = True Then
        ImportCodeModule obj.VBProject.VBComponents(mName(0)).CodeModule, _
                         CompFileName
    Else
        If Not obj Is Nothing Then
            obj.VBProject.VBComponents.Import CompFileName
        Else
            WScript.Echo  "Failed to import " & CompFileName
        End If
    End If 
End Sub

''
' Imports the code in the file fName into the workbook object
' referenced by mName.
' @param target destination CodeModule object in the excel file
' @param fName file system file containing code to be imported
Sub ImportCodeModule (ByVal target, ByVal fName)
    Dim shtModule, code, buf    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set buf = fso.OpenTextFile(fName, ForReading, False, TristateUseDefault)
    buf.SkipLine
    code = buf.ReadAll

    target.InsertLines 1, code
    Set fso = Nothing
End Sub


''
' Returns true if the code module in the file fName
' appears to be a code module for a worksheet.
Function IsSheetCodeModule (ByVal mName, ByVal fName)
    IsSheetCodeModule = False
    If mName = "ThisWorkbook" Then
       IsSheetCodeModule = False
    ElseIf Left(mName, 5) = "Sheet" And _
       IsNumeric(Mid (mName, 6, 1)) And _
       Right(fName, 3) = "cls Then
       IsSheetCodeModule = True
    End If
End Function

''
' Returns true if fName has a xls file extension
Function IsExcelFile (ByVal fName)
    If Right(fName, 3) = "xls" Then
        IsExcelFile = True
    Else
        IsExcelFile = False
    End If 
End Function

这篇关于源代码管理Excel VBA代码模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！