excel - 如何使用 VBA 删除 Module1？

Question

我想在 Excel 的 VBA 编辑器中使用 VBA 命令删除 Module1。这可能吗？

Answer 1

情况很复杂。看到这个link了解更多关于为什么。

简而言之，这是您需要做的:

首先你需要引用VBIDE

VBIDE The VBIDE is the object library that defines all the objects and values that make up VBProject and the Visual Basic Editor. You must reference this library to use the VBA Extensibility objects. To add this reference, open the VBA editor, open your VBProject in the editor, and go to the Tools menu. There, choose References . In the References dialog, scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 and check that item in the list. You can add the reference programmatically with code like:

ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:="{0002E157-0000-0000-C000-000000000046}", _
    Major:=5, Minor:=3

接下来，您需要授予“对 Visual Basic 项目的信任访问权限”

Next, you need to enable programmatic access to the VBA Project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

现在您可以添加代码以删除模块:

Deleting A Module From A Project

This code will delete Module1 from the VBProject. Note that you cannot remove any of the Sheet modules or the ThisWorkbook module. In general, you cannot delete a module whose Type is vbext_ct_Document.

Sub DeleteModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    VBProj.VBComponents.Remove VBComp
End Sub