如何批量删除Excel工作簿中vba代码

如果想要用vba代码删除工作表中的vba代码，先要在excel中引入一个控件，步骤如下：

1.对于 EXCEL 2003 版本，在VBE中，工具--引用-- Microsoft Visual Basic For Application Extensibility 5.3

(不同的版本，Microsoft Visual Basic For Application Extensibility 5.3 的写法可能不同)

2.选中工具栏-->宏-->安全性...-->可靠发行商-->信任对于"Visual Basic 项目"的访问

3.接下来，如果只是想删除所有工作表中的代码，可以在vba窗口输入一下代码：

Sub Remove_some_vba_code()

    Dim activeIDE As Object 'VBProject
    Set activeIDE = ActiveWorkbook.VBProject

    Dim Element As VBComponent

    Dim LineCount As Integer
    For Each Element In activeIDE.VBComponents
        If Left(Element.Name, 5) = "Sheet" Then    
            LineCount = Element.CodeModule.CountOfLines
            Element.CodeModule.DeleteLines 1, LineCount
        End If
    Next

End Sub

如果是是要删除所有代码，就删掉中间的那个判断语句

Sub Remove_all_vba_code()

    Dim activeIDE As Object 'VBProject
    Set activeIDE = ActiveWorkbook.VBProject

    Dim Element As VBComponent

    Dim LineCount As Integer
    For Each Element In activeIDE.VBComponents
            LineCount = Element.CodeModule.CountOfLines
            Element.CodeModule.DeleteLines 1, LineCount
    Next

End Sub