如何通过宏清除工作表的VBA代码？ [英] How to clear the VBA code of a worksheet via a macro?

问题描述

我有一个文件，其中有一个模板表，需要在激活时运行一些代码。正在复制此工作表以创建不需要运行此代码的工作表。目前，我有代码在运行时检查工作表的代号，以便在额外的工作表上不执行任何操作，但是当您在工作表之间切换时，仍然会减慢使用率。

是有什么办法让复制的宏也清除他们的VBA代码内容？

（编辑）请注意，我需要清除的代码不在一个模块中。经过一些研究，似乎我找到了一种删除模块的方法（通过访问 VBProject.VBComponents ），但我不知道如何访问VBA 代码

解决方案

要删除所有工作表中的完整代码你可以尝试这样的模块：

  Sub Remove_some_vba_code（）
 
 Dim activeIDE As Object 'VBProject 
设置activeIDE = ActiveWorkbook.VBProject 
 
 Dim元素作为VBComponent 
 
 Dim LineCount As Integer 
每个元素在activeIDE.VBComponents 
如果Left（Element.Name，5）=Sheet然后'如果需要更改名称
 LineCount = Element.CodeModule.CountOfLines 
 
 Element.CodeModule.DeleteLines 1，LineCount 
结束如果
下一个
 
 End Sub 
  

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't need 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?

(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 code of a worksheet.

解决方案

To remove complete code in all Sheet modules you could try something like this:

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

这篇关于如何通过宏清除工作表的VBA代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！