如何通过宏清除工作表的 VBA 代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15630035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to clear the VBA code of a worksheet via a macro?
提问by Nelo Mitranim
I have a file where there's a template sheet that needs to run some code when it's activated. This sheet is being duplicated to create sheets that don'tneed to run this code. Currently, I have the code to check for worksheet's codename when run so that it does nothing on extra sheets, but it still slows usage down when you switch between sheets.
我有一个文件，其中有一个模板表，需要在激活时运行一些代码。这片被复制到创建表并不需要运行该代码。目前，我有代码在运行时检查工作表的代号，这样它就不会在额外的工作表上执行任何操作，但是当您在工作表之间切换时它仍然会减慢使用速度。
Is there any way to make the macro that makes duplicates also clear their VBA code contents?
有什么方法可以使生成重复项的宏也清除其 VBA 代码内容？
(Edit) Please note that the code I need to clear is not in a module. After some research, it seems I found a way to remove modules (by accessing VBProject.VBComponents), but I'm not sure how to access the VBA codeof a worksheet.
（编辑）请注意，我需要清除的代码不在模块中。经过一番研究，我似乎找到了一种删除模块的方法（通过访问VBProject.VBComponents），但我不确定如何访问工作表的 VBA代码。
采纳答案by Kazimierz Jawor
To remove complete code in all Sheetmodules you could try something like this:
要删除所有Sheet模块中的完整代码，您可以尝试以下操作：
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 'change name if necessary
LineCount = Element.CodeModule.CountOfLines
Element.CodeModule.DeleteLines 1, LineCount
End If
Next
End Sub
回答by Dick Kusleika
Another way you could approach this is to keep all of your code out of the worksheet. Then you don't have to delete anything. The worksheet's code module is a handy place to code events, but you can create your own class module to handle events too. Put this in a standard module:
解决此问题的另一种方法是将所有代码保留在工作表之外。那么您不必删除任何内容。工作表的代码模块是对事件进行编码的一个方便的地方，但您也可以创建自己的类模块来处理事件。把它放在一个标准模块中：
Public gclsEvent As CEvent
Sub Auto_Open()
Set gclsEvent = New CEvent
Set gclsEvent.This = Sheet1
End Sub
This will create an instance of CEvent that's global, so it won't lose scope as long as your workbook is open. It assigns the worksheet codenamed Sheet1 to the This property of the class. Create a class module named CEvent with this code
这将创建一个全局的 CEvent 实例，因此只要您的工作簿处于打开状态，它就不会丢失范围。它将代号为 Sheet1 的工作表分配给类的 This 属性。使用此代码创建一个名为 CEvent 的类模块
Private WithEvents mwsThis As Worksheet
Public Property Set This(ByVal wsThis As Worksheet): Set mwsThis = wsThis: End Property
Public Property Get This() As Worksheet: Set This = mwsThis: End Property
Private Sub mwsThis_Activate()
Me.This.Copy , Me.This.Parent.Sheets(Me.This.Parent.Sheets.Count)
End Sub
The WithEvents keyword exposes events for that object. Since we're only hooking up the events for Sheet1, activating another sheet won't trigger the code.
WithEvents 关键字公开该对象的事件。由于我们只是连接 Sheet1 的事件，因此激活另一个工作表不会触发代码。
