使用 VBA 在 Excel 中刷新对 VBProject.VBComponents 所做的更改

Flushing changes made to VBProject.VBComponents in Excel using VBA

提问by CamilB

I've been experiencing some strange quirks in Excel while programatically removing modules then reimporting them from files. Basically, I have a module named VersionControl that is supposed to export my files to a predefined folder, and reimport them on demand. This is the code for reimporting (the problem with it is described below):

在以编程方式删除模块然后从文件中重新导入它们时，我在 Excel 中遇到了一些奇怪的怪癖。基本上，我有一个名为 VersionControl 的模块，它应该将我的文件导出到预定义的文件夹，并根据需要重新导入它们。这是重新导入的代码（下面描述了它的问题）：

Dim i As Integer
Dim ModuleName As String
Application.EnableEvents = False
With ThisWorkbook.VBProject
    For i = 1 To .VBComponents.Count
        If .VBComponents(i).CodeModule.CountOfLines > 0 Then
            ModuleName = .VBComponents(i).CodeModule.Name
            If ModuleName <> "VersionControl" Then
                If PathExists(VersionControlPath & "\" & ModuleName & ".bas") Then
                    Call .VBComponents.Remove(.VBComponents(ModuleName))
                    Call .VBComponents.Import(VersionControlPath & "\" & ModuleName & ".bas")
                Else
                    MsgBox VersionControlPath & "\" & ModuleName & ".bas" & " cannot be found. No operation will be attempted for that module."
                End If
            End If
        End If
    Next i
End With

After running this, I've noticed that some modules don't appear anymore, while some have duplicates (e.g. mymodule and mymodule1). While stepping through the code, it became obvious that some modules still linger after the Removecall, and they get to be reimported while still in the project. Sometimes, this only resulted having the module suffixed with 1, but sometimes I had both the original and the copy.

运行此程序后，我注意到有些模块不再出现，而有些模块有重复（例如 mymodule 和 mymodule1）。在逐步执行代码时，很明显有些模块在Remove调用后仍然存在，并且它们仍然在项目中时会被重新导入。有时，这只会导致模块后缀为1，但有时我同时拥有原件和副本。

Is there a way to flush the calls to Removeand Importso they apply themselves? I'm thinking to call a Savefunction after each, if there's one in the Application object, although this can cause losses if things go wrong during import.

有没有办法刷新调用Remove，Import以便它们应用自己？我想Save在每个函数之后调用一个函数，如果 Application 对象中有一个函数，尽管如果在导入过程中出现问题，这可能会导致损失。

Ideas?

想法？

Edit: changed tag synchronizationto version-control.

编辑：将标签更改synchronization为version-control.

回答by Karl Greenhalgh

This is a live array, you are adding and removing items during iteration thereby changing the index numbers. Try processing the array backwards. Here is my solution without any error handling:

这是一个实时数组，您在迭代期间添加和删除项目，从而更改索引号。尝试向后处理数组。这是我的解决方案，没有任何错误处理：

Private Const DIR_VERSIONING As String = "\VERSION_CONTROL"
Private Const PROJ_NAME As String = "PROJECT_NAME"

Sub EnsureProjectFolder()
    ' Does this project directory exist
    If Len(Dir(DIR_VERSIONING & PROJ_NAME, vbDirectory)) = 0 Then
        ' Create it
        MkDir DIR_VERSIONING & PROJ_NAME
    End If
End Sub

Function ProjectFolder() As String
    ' Ensure the folder exists whenever we try to access it (can be deleted mid execution)
    EnsureProjectFolder
    ' Create the required full path
    ProjectFolder = DIR_VERSIONING & PROJ_NAME & "\"
End Function

Sub SaveCodeModules()

    'This code Exports all VBA modules
    Dim i%, sName$

    With ThisWorkbook.VBProject
        ' Iterate all code files and export accordingly
        For i% = 1 To .VBComponents.count
            ' Extract this component name
            sName$ = .VBComponents(i%).CodeModule.Name
            If .VBComponents(i%).Type = 1 Then
                ' Standard Module
                .VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
            ElseIf .VBComponents(i%).Type = 2 Then
                ' Class
                .VBComponents(i%).Export ProjectFolder & sName$ & ".cls"
            ElseIf .VBComponents(i%).Type = 3 Then
                ' Form
                .VBComponents(i%).Export ProjectFolder & sName$ & ".frm"
            ElseIf .VBComponents(i%).Type = 100 Then
                ' Document
                .VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
            Else
                ' UNHANDLED/UNKNOWN COMPONENT TYPE
            End If
        Next i
    End With

End Sub

Sub ImportCodeModules()
    Dim i%, sName$

    With ThisWorkbook.VBProject
        ' Iterate all components and attempt to import their source from the network share
        ' Process backwords as we are working through a live array while removing/adding items
        For i% = .VBComponents.count To 1 Step -1
            ' Extract this component name
            sName$ = .VBComponents(i%).CodeModule.Name
            ' Do not change the source of this module which is currently running
            If sName$ <> "VersionControl" Then
                ' Import relevant source file if it exists
                If .VBComponents(i%).Type = 1 Then
                    ' Standard Module
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".bas"
                ElseIf .VBComponents(i%).Type = 2 Then
                    ' Class
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".cls"
                ElseIf .VBComponents(i%).Type = 3 Then
                    ' Form
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".frm"
                ElseIf .VBComponents(i%).Type = 100 Then
                    ' Document
                    Dim TempVbComponent, FileContents$
                    ' Import the document. This will come in as a class with an increment suffix (1)
                    Set TempVbComponent = .VBComponents.Import(ProjectFolder & sName$ & ".bas")

                    ' Delete any lines of data in the document
                    If .VBComponents(i%).CodeModule.CountOfLines > 0 Then .VBComponents(i%).CodeModule.DeleteLines 1, .VBComponents(i%).CodeModule.CountOfLines

                    ' Does this file contain any source data?
                    If TempVbComponent.CodeModule.CountOfLines > 0 Then
                        ' Pull the lines into a string
                        FileContents$ = TempVbComponent.CodeModule.Lines(1, TempVbComponent.CodeModule.CountOfLines)
                        ' And copy them to the correct document
                        .VBComponents(i%).CodeModule.InsertLines 1, FileContents$
                    End If

                    ' Remove the temporary document class
                    .VBComponents.Remove TempVbComponent
                    Set TempVbComponent = Nothing

                Else
                    ' UNHANDLED/UNKNOWN COMPONENT TYPE
                End If
            End If
            Next i
        End With

End Sub

回答by CamilB

OP here... I managed to work around this weird issue, but I haven't found a true solution. Here's what I did.

OP 在这里...我设法解决了这个奇怪的问题，但我还没有找到真正的解决方案。这就是我所做的。

1. My first attempt after posting the question was this (spoiler: it almostworked):

   Keep removing separate from importing, but in the same procedure. This means that I had 3 loops - one to store a list of the module names (as plain strings), another to remove the modules, and another to import the modules from files (based on the names that were stored in the aforementioned list).

   The problem: some modules were still in the project when the removal loop ended. Why? I cannot explain. I'll mark this as stupid problem no. 1. I then tried placing the Removecall for every module inside a loopthat kept trying to remove that single module until it couldn't find it in the project. This got stuck in an infinite loop for a certain module - I can't tell what's so special about that particular one.

   I eventually figured out that the modules were only truly removed after Excel finds some time to clear its thoughts. This didn't work with Application.Wait(). The currently running VBA code actually needed to end for this to happen. Weird.

2. Second work-around attempt (spoiler: again, it almostworked):

   To give Excel the required time to breathe after removals, I placed the removing loop inside a button click handler (without the "call Remove until it's gone" loop), and the importing loop in the click handler of another button. Of course, I needed the list of module names, so I made it a global array of strings. It was created in the click handler, before the removal loop, and it was supposed to be accessed by the importing loop. Should have worked, right?

   The problem: The aforementioned string array was empty when the importing loop started (inside the other click handler). It was definitely there when the removal loop ended - I printed it with Debug.Print. I guess it got de-allocated by the removals (??). This would be stupid problem no. 2. Without the string array containing the module names, the importing loop did nothing, so this work-around failed.

3. Final, functional workaround. This one works.

   I took Work-around number 2 and, instead of storing the module names in a string array, I stored them in a row of an auxiliary sheet (I called this sheet "Devel").

1. 发布问题后我的第一次尝试是这样的（剧透：它几乎有效）：

   继续删除与导入分开，但在相同的过程中。这意味着我有 3 个循环 - 一个用于存储模块名称列表（作为纯字符串），另一个用于删除模块，另一个用于从文件导入模块（基于存储在上述列表中的名称） .

   问题：当移除循环结束时，一些模块仍在项目中。为什么？我无法解释。我会将此标记为愚蠢的问题。1. 然后我尝试将Remove每个模块的调用放在一个循环中，该循环不断尝试删除该单个模块，直到在项目中找不到它为止。这陷入了某个模块的无限循环 - 我不知道那个特定模块有什么特别之处。

   我最终发现只有在 Excel 找到一些时间来清除它的想法后才真正删除了这些模块。这不适用于 Application.Wait()。当前运行的 VBA 代码实际上需要结束才能发生这种情况。奇怪的。

2. 第二次变通尝试（剧透：同样，它几乎奏效了）：

   为了让 Excel 在删除后有足够的时间喘口气，我将删除循环放在按钮单击处理程序中（没有“调用 Remove 直到它消失”循环），并将导入循环放在另一个按钮的单击处理程序中。当然，我需要模块名称列表，因此我将其设为全局字符串数组。它是在点击处理程序中创建的，在删除循环之前，它应该被导入循环访问。应该有效吧？

   问题：当导入循环开始时（在另一个点击处理程序内），上述字符串数组是空的。当删除循环结束时它肯定在那里 - 我用 Debug.Print 打印它。我猜它被移除（??）取消了分配。这将是愚蠢的问题不。2. 如果没有包含模块名称的字符串数组，导入循环什么都不做，所以这个变通方法失败了。

3. 最后，功能性解决方法。这个有效。

   我采用了解决方法 2，而不是将模块名称存储在字符串数组中，而是将它们存储在辅助工作表的一行中（我将此工作表称为“开发”）。

This was it. If anyone can explain stupid problem no. 1and stupid problem no. 2, I beg you, do so. They're probably not that stupid - I'm still at the beginning with VBA, but I have solid knowledge of programming in other (sane and modern) languages.

就是这样。如果有人可以解释愚蠢的问题没有。1和愚蠢的问题没有。2、求求你，这样做吧。他们可能没那么蠢——我还处于 VBA 的起步阶段，但我对其他（理智和现代）语言的编程有扎实的了解。

I could add the code to illustrate stupid problem no. 2, but this answer is already long. If what I did was not clear, I will place it here.

我可以添加代码来说明愚蠢的问题。2，但这个答案已经很长了。如果我做的不清楚，我会把它放在这里。

回答by David

To avoid duplicate when importing, I modified the script with following strategy :

为避免导入时重复，我使用以下策略修改了脚本：

• Rename existing module
• Import module
• Delete renamed module

• 重命名现有模块
• 导入模块
• 删除重命名的模块

I don't have anymore duplicate during import.

我在导入过程中不再有重复。

---

---

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i As Integer, name As String

With ThisWorkbook.VBProject
For i = .VBComponents.Count To 1 Step -1

    name = .VBComponents(i).CodeModule.name

    If .VBComponents(i).Type = 1 Then
        ' Standard Module
        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".module"
    ElseIf .VBComponents(i).Type = 2 Then
        ' Class
        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".classe"
    ElseIf .VBComponents(i).Type = 3 Then
        ' Form
        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".form"
    Else
        ' DO NOTHING
    End If
Next i
End With

End Sub

---

---

Sub ImportCodeModules()

Dim i As Integer
Dim delname As String
Dim modulename As String

With ThisWorkbook.VBProject
For i = .VBComponents.Count To 1 Step -1

    modulename = .VBComponents(i).CodeModule.name

    If modulename <> "VersionControl" Then

        delname = modulename & "_to_delete"

        If .VBComponents(i).Type = 1 Then
            ' Standard Module
            .VBComponents(modulename).name = delname
            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".module"
            .VBComponents.Remove .VBComponents(delname)

        ElseIf .VBComponents(i).Type = 2 Then
            ' Class
            .VBComponents(modulename).name = delname
            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".classe"
            .VBComponents.Remove .VBComponents(delname)

        ElseIf .VBComponents(i).Type = 3 Then
            ' Form
            .VBComponents.Remove .VBComponents(modulename)
            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".form"
        Else
            ' DO NOTHING
        End If

    End If
Next i

End With

End Sub

---

---

Code to be pasted in a new module "VersionControl"

要粘贴到新模块“VersionControl”中的代码

回答by Logan Smyth

I've been struggling with this issue for days now. I built a crude version control system similar to this, though not using arrays. The version control module is imported on Workbook_Open and then the startup procedure is called to import all of the modules listed in the version control module. Everything works great except Excel started creating duplicate version control modules because it would import the new module before the deletion of the existing one finished. I worked around that by appending Delete to the previous module. The problem then being that there were still two procedures with the same name. Chip Pearson has some code for deleting a procedure programmatically, so I deleted the startup code from the older version control module. Still, I ran into an issue where the procedure had not been deleted by the time the startup procedure was called. I finally found a solution on another stack overflow thread that is so simple it makes me want to put my head through a wall. All I had to do was change the way I call my startup procedure by using

我已经为这个问题苦苦挣扎了好几天了。我构建了一个与此类似的粗略版本控制系统，但没有使用数组。在 Workbook_Open 上导入版本控制模块，然后调用启动过程以导入版本控制模块中列出的所有模块。除了 Excel 开始创建重复的版本控制模块之外，一切都很好，因为它会在删除现有模块之前导入新模块。我通过将 Delete 附加到上一个模块来解决这个问题。问题是仍然有两个同名的过程。Chip Pearson 有一些用于以编程方式删除过程的代码，因此我从旧版本控制模块中删除了启动代码。仍然，我遇到了一个问题，即在调用启动过程时该过程尚未被删除。我终于在另一个堆栈溢出线程上找到了一个解决方案，它非常简单，让我想把头穿墙。我所要做的就是改变我调用启动程序的方式

Application.OnTime Now + TimeValue("00:00:01"), "StartUp"    

Everything works perfectly now. Though, I will probably go back and delete out the now redundant renaming of the module and deleting the second procedure and see if this alone solves my original problem. Here is the other thread with the solution...

现在一切正常。不过，我可能会回去删除现在多余的模块重命名并删除第二个程序，看看是否仅此一项就可以解决我原来的问题。这是解决方案的另一个线程......

Source control of Excel VBA code modules

Excel VBA 代码模块的源代码管理

回答by S1000RR

Not sure if it helps somebody ... In an xlsm container (if you open it as a zip-file) you can find a file called like vbaproject. You can overwrite this in order to "update" the whole content of the VBProject (all standardmodules, classmodules, userforms, table-modules and thisworkbook-module). With this aproach you can create the "new" code etc. in a copy of your workbook and finally overwrite the vbproject file in the destination workbook. This maybe avoid the need to export / import modules and suffering of duplicates. (Sorry for posting no code due to lack of time)

不确定它是否对某人有帮助...在 xlsm 容器中（如果您将其作为 zip 文件打开），您可以找到一个名为 vbaproject.txt 的文件。您可以覆盖它以“更新”VBProject 的全部内容（所有标准模块、类模块、用户表单、表模块和此工作簿模块）。使用这种方法，您可以在工作簿的副本中创建“新”代码等，并最终覆盖目标工作簿中的 vbproject 文件。这可能避免导出/导入模块的需要和重复的痛苦。（抱歉，由于时间不够，没有发布代码）

回答by Apteryx

The rename, import and delete workaround didn't work in my case. It seems (but this is pure speculation) that Excel might save the compiled objects in its .XLMS file, and when this file is re-opened these objects are reloaded in memory before the ThisWorkbook_open function occurs. And this leads the renaming (or removal) of certain modules to fail or be delayed (even when trying to force it with the DoEvents call). The only workaround I found is to use the .XLS binary format. For some obscure reason (I suspect the compiled objects are not bundled in the file), it works for me.

在我的情况下，重命名、导入和删除解决方法不起作用。似乎（但这纯粹是推测）Excel 可能会将编译的对象保存在其 .XLMS 文件中，并且当重新打开此文件时，这些对象会在 ThisWorkbook_open 函数发生之前重新加载到内存中。这会导致某些模块的重命名（或删除）失败或延迟（即使尝试使用 DoEvents 调用强制执行）。我发现的唯一解决方法是使用 .XLS 二进制格式。出于某种晦涩的原因（我怀疑编译的对象没有捆绑在文件中），它对我有用。

You have to know that you won't be able to re-import any module being/having been used or referenced at the time your import code runs (renaming will fail with error 32813/removal of the module will be delayed until after you will try to import, adding annoying '1's at the end of your module names). But for any other module, it should work.

您必须知道，您将无法重新导入在导入代码运行时正在/已经使用或引用的任何模块（重命名将失败并显示错误 32813/模块的删除将延迟到您将尝试导入，在模块名称的末尾添加烦人的“1”）。但是对于任何其他模块，它应该可以工作。

If all of your source code needs to be managed, a better solution would be to "build" your workbook from scratch using some script or tool, or switch to a better suited programming language (i.e. one that doesn't live inside an Office suite software ;) I haven't tried it but you could look here: Source control of Excel VBA code modules.

如果您的所有源代码都需要管理，更好的解决方案是使用某些脚本或工具从头开始“构建”您的工作簿，或者切换到更适合的编程语言（即不存在于 Office 套件中的编程语言）软件 ;) 我还没有尝试过，但您可以在这里查看：Excel VBA 代码模块的源代码控制。