vba VbComponents.Remove 并不总是删除模块

VbComponents.Remove doesn't always remove module

提问by sigil

I'm trying to use Chip Pearson's code for overwriting an existing VBA code module with an import from another project. Original code here.

我正在尝试使用 Chip Pearson 的代码通过从另一个项目导入来覆盖现有的 VBA 代码模块。原始代码在这里。

The particular section I'm looking at is:

我正在查看的特定部分是：

With ToVBProject.VBComponents
    .Remove .Item(ModuleName)
End With

But this VBComponents.Removecall will sometimes only actually take effect once VBA execution has stopped--that is, the delete operation doesn't take effect until all statements are finished, or if the code hits a breakpoint and then I stop debugging. This is a problem because of the following code for either importing a new module or replacing the existing module's code with the new module:

但是这个VBComponents.Remove调用有时只有在 VBA 执行停止后才会真正生效——也就是说，删除操作直到所有语句都完成后才会生效，或者如果代码遇到断点然后我停止调试。这是一个问题，因为以下代码用于导入新模块或用新模块替换现有模块的代码：

    Set VBComp = Nothing
    Set VBComp = ToVBProject.VBComponents(CompName)

    If VBComp Is Nothing Then
        ToVBProject.VBComponents.import filename:=FName
    Else
        If VBComp.Type = vbext_ct_Document Then
            'delete the module's code,
            'import a temp module,
            'copy over the temp module code,
            'delete the temp module
        End If
    End If

The module deletion hasn't taken effect yet, so VBCompis not Nothing, as far as the debugger knows. So the .importwon't be called.

模块删除尚未生效，所以VBComp没有Nothing，据调试器所知。所以.import不会被调用。

Even if I comment out the if VBComp.Type = vbext_ct_document thenand end ifso that the new module's code will overwrite the existing one no matter what VBComp.Typeit is, the module will still end up getting deleted once the code finishes executing, and no import will happen to replace it.

即使我注释掉if VBComp.Type = vbext_ct_document thenandend if以便新模块的代码不管VBComp.Type它是什么都会覆盖现有的模块，一旦代码完成执行，该模块仍然最终会被删除，并且不会发生导入来替换它。

What's odd is that this doesn't happen with all modules; some actually get deleted in real time after the VBComponents.Removecall.

奇怪的是，并非所有模块都会发生这种情况。有些实际上在VBComponents.Remove通话后被实时删除。

I've seen several different posts about this on various forums, and no satisfactory solution. For now I'm using a workaround of changing the .Removecall to:

我在各种论坛上看到了几个不同的帖子，但没有令人满意的解决方案。现在我正在使用一种将.Remove调用更改为：

    With ToVBProject.VBComponents
        .Item(ModuleName).name = ModuleName & "_remove"
        .Remove .Item(ModuleName & "_remove")
    End With

so that by changing the name, ModuleNameappears to no longer exist and therefore the .importcall will occur. This assumes, of course, that no module named ModuleName & "_remove"actually exists.

这样通过更改名称，ModuleName似乎不再存在，因此.import会发生调用。当然，这假设ModuleName & "_remove"实际上不存在命名的模块。

Is there a better solution?

有更好的解决方案吗？

回答by Bill

I tried the renaming and found that it caused problems with the sheet modules and ThisWorkbook. So I modified it slightly to rename only the non document modules. This seems to work cleanly.

我尝试了重命名，发现它会导致工作表模块和 ThisWorkbook 出现问题。所以我稍微修改了一下，只重命名非文档模块。这似乎很干净。

        If .Item(ModuleName).Type <> vbext_ct_Document Then
            .Item(ModuleName).Name = ModuleName & "_OLD"
            .Remove .Item(ModuleName & "_OLD")
        Else
            .Remove .Item(ModuleName)
        End If

回答by Walter Rauschenberger

I've spent endless time on removing/replacing Code Modules in order to find out what triggers works versus doesn't work.

我花了无数时间来移除/替换代码模块，以找出哪些触发器有效，哪些无效。

Removing/replacing Code Modules in another Workbook

删除/替换另一个工作簿中的代码模块

This is by far the least troubling approach and that's why I've implemented it with Code Module Management. Therein, for any yet not open Workbook, enabled to be selected in a file dialog, the Workbook is opend with:

这是迄今为止最不麻烦的方法，这就是我使用Code Module Management实现它的原因。其中，对于任何尚未打开的工作簿，可以在文件对话框中选择，工作簿的打开方式为：

Application.EnableEvents = False
....Open "workbook-fullname" ' selected in a file dialog
Application.EnableEvents = False

Code Module Managementwill not work when the target Workbook had been opened manually before (and thus is presented for selection as list of open Workbooks) by not having prevented any VBA-Code from being executed (see herefor ways this can be achieved). Of course this is only an issue when Workbook_Open executes code.

如果之前已手动打开目标工作簿（因此作为打开的工作簿列表供选择），代码模块管理将无法工作，因为没有阻止任何 VBA 代码的执行（请参阅此处了解实现此目的的方法）。当然，这只是 Workbook_Open 执行代码时的问题。

My conclusion: Once Macros(Subprocedures, Functions, etc.) had been executed, a copy of the VBA-Code resides in memory. Thus, any Removewill not happen before the procedure which removed the Code Module has finished. Consequently an Importconsiders the Code Module still existing and imports a Code Module with the same name with a numeric suffix to make its name unique. And this of cource is always the case when ...

我的结论：一旦执行了宏（子过程、函数等），VBA 代码的副本就会驻留在内存中。因此，在移除代码模块的过程完成之前，不会发生任何移除。因此，导入会认为代码模块仍然存在，并导入具有相同名称的代码模块并带有数字后缀以使其名称唯一。这当然总是如此，当......

Removing/replacing Code Modules in 'ThisWorkbook'*)

删除/替换“本工作簿”中的代码模块*)

Up to now I have not found any way to achieve this - except for a Class Module not declared in any of the other Code Modules, which I've realized accidentially! . Consequently I tried to temporarily comment out all declarations before Remove. Full success! Removeand Importworked fine. But when I finally tried to un-comment the previously out-commented declaration code lines, Excel allways crashed (in both cases, .DeleteLinesand .InsertLinesand .ReplaceLine). Since I have not found a solution for this I've given up trying - at least for the time being.

到目前为止，我还没有找到任何方法来实现这一点——除了一个没有在任何其他代码模块中声明的类模块，这是我偶然意识到的！. 因此，我尝试在Remove之前暂时注释掉所有声明。圆满成功！删除和导入工作正常。但是，当我最终尝试取消注释以前注释掉的声明代码行时，Excel 总是崩溃（在这两种情况下，.DeleteLines和.InsertLines和.ReplaceLine）。由于我还没有找到解决方案，因此我放弃了尝试 - 至少目前是这样。

See the Code Module Managementfor making Cleanup, Remove, Transfer, Export, Import, and even Synchronize much less cumbersome, save and reliable. Even selecting the wrong 'target' Workbook for a transfer or a remove is no drama because of an Undo feature.

请参阅代码模块管理，使清理、删除、传输、导出、导入甚至同步变得更简单、更省时且更可靠。由于撤消功能，即使选择错误的“目标”工作簿进行传输或删除也没什么大不了的。

*) The difference between ActiveWorkbookand ThisWorkbookmatters here. ThisWorkbookis the Workbook in which the VBA code is executed while the ActiveWorkbookmay be another one. Because normally both are identicall, not paying attention on the difference doesn't matter.

*) ActiveWorkbook和ThisWorkbook之间的区别在这里很重要。ThisWorkbook是在其中执行 VBA 代码的工作簿，而ActiveWorkbook可能是另一个工作簿。因为通常两者都是相同的，所以不注意差异并不重要。

回答by Tony K

I have struggled with this problem for many months and have finally found a solution thanks to Rob Bovey (http://www.apps-pro.com/).

我已经为这个问题苦苦挣扎了好几个月，最终在 Rob Bovey ( http://www.apps-pro.com/) 的帮助下找到了解决方案。

The trick is to use the Application.OnTimefunction to call a second procedure with the code you want to run after deleting the code modules.

诀窍是使用该Application.OnTime函数在删除代码模块后使用要运行的代码调用第二个过程。

Application.OnTime Now(), "Your_Procedure_Part2"

This seems to mimic the VBA code stopping and restarting. Any code after this line in the calling procedure will not be run.

这似乎模仿了停止和重新启动的 VBA 代码。调用过程中此行之后的任何代码都不会运行。

回答by karl skinner

If you have code and method references in the ThisWorkbooksheet then Excel might hang on to modules and not delete them. The trick is to hide calls from Excel using Application.run("foo")instead of direct calls like foo().
This worked for me in Excel 2010

如果工作表中有代码和方法引用，ThisWorkbook则 Excel 可能会保留模块而不是删除它们。诀窍是使用Application.run("foo")而不是像foo().
这在 Excel 2010 中对我有用

回答by Steve B

I have ran into things like this before. The 'DoEvents' command often helps. Have you given that a shot?

我以前遇到过这样的事情。“DoEvents”命令通常会有所帮助。你试过吗？

Other times, I have placed the command in a do while loop and used a boolean to continually check if the command in question has succeeded. Including the DoEvents command in the loop is sometimes needed.

其他时候，我将命令放在 do while 循环中，并使用布尔值不断检查有问题的命令是否成功。有时需要在循环中包含 DoEvents 命令。

Just remember to put something in the loop so that after so many cycles it will give up. Getting stuck in an infinite loop can be pesky.

只要记住在循环中放一些东西，这样经过这么多次循环它就会放弃。陷入无限循环可能会令人讨厌。

回答by Walter Rauschenberger

As Chip Pearson stated, the only way to manage components in a Workbook without conflict is to use code from within another Workbook. I have put everything I need into a CompMan.xlam Workbook which I use as Add-In. It allows me to export all modules from a Workbook whenever it is saved by the simple code lines:

正如 Chip Pearson 所说，在没有冲突的情况下管理 Workbook 中的组件的唯一方法是使用另一个 Workbook 中的代码。我已将我需要的所有内容放入我用作插件的 CompMan.xlam 工作簿中。它允许我从工作簿中导出所有模块，只要它通过简单的代码行保存：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
#If DevTest Then
   mCompMan.ExportAll ThisWorkbook
#End If
End Sub

By this I never again loose any VBA work and moreover any module is available for import into any other Workbook. I should add that I keep Workbooks with Macros in dedicated folders - at least for code work.

这样我再也不会丢失任何 VBA 工作，而且任何模块都可以导入到任何其他工作簿中。我应该补充一点，我将带有宏的工作簿保存在专用文件夹中 - 至少对于代码工作。

回答by John Doe

It's been quite a while since this question has been asked but I think I came up with something which has not been mentioned before.

自从提出这个问题以来已经有一段时间了，但我想我想出了一些以前没有提到的东西。

Since the modules will not be deleted until the procedure ends, a possible solution is to actually let the procedure end and then call the import procedure via Application.onTime.

由于直到过程结束才会删除模块，因此可能的解决方案是实际让过程结束，然后通过 调用导入过程Application.onTime。

So, for example

所以，例如

Private Sub ImportModules()
  With ThisWorkbook.VBProject.VBComponents
    .Remove .Item(ModuleName)
    Application.onTime Now + TimeValue("00:00:01"), tmpImportModules
  End With
End Sub

Public Sub tmpImportModules()
  With ThisWorkbook.VBProject.VBComponents
    .Import filename:=FName
  End With
End Sub