使用 VBA 代码将 VBA 代码从一个工作表复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24701517/
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
Copy VBA code from one Worksheet to another using VBA code
提问by goodfella
Ok here is what I want to accomplish: I am trying to copy all the VBA code from "Sheet2" to "Sheet 3" code pane. I'm NOT referring to copying a Module from one to another but the excel sheet object code.
好的，这就是我想要完成的：我试图将所有 VBA 代码从“Sheet2”复制到“Sheet 3”代码窗格。我不是指从一个模块复制到另一个模块，而是 Excel 工作表对象代码。
I already added a Reference to MS VB for Applications Extensibility 5.3
我已经为应用程序扩展性 5.3 添加了对 MS VB 的引用
I'm not sure where to start but this is what I have started with and its not going anywhere and probably all wrong. Please Help - Simply want to programmatically copy sheet vba code to another sheet vba pane.
我不知道从哪里开始，但这是我的开始，它不会去任何地方，而且可能都是错误的。请帮助 - 只想以编程方式将工作表 vba 代码复制到另一个工作表 vba 窗格。
Dim CodeCopy As VBIDE.CodePane
Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").VBE
ActiveWorkbook.VBProject.VBComponenets("Sheet3").CodeModule = CodeCopy
回答by David Zemens
Use the CodeModuleobject instead of the CodePane, then you can create a second variable to represent the destination module (where you will "paste" the code).
使用CodeModule对象而不是CodePane，然后您可以创建第二个变量来表示目标模块（您将在其中“粘贴”代码）。
Sub test()
Dim CodeCopy As VBIDE.CodeModule
Dim CodePaste As VBIDE.CodeModule
Dim numLines As Integer
Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
Set CodePaste = ActiveWorkbook.VBProject.VBComponents("Sheet3").CodeModule
numLines = CodeCopy.CountOfLines
'Use this line to erase all code that might already be in sheet3:
'If CodePaste.CountOfLines > 1 Then CodePaste.DeleteLines 1, CodePaste.CountOfLines
CodePaste.AddFromString CodeCopy.Lines(1, numLines)
End Sub
In addition to adding a reference to "Reference to MS VB for Applications Extensibility 5.3"
除了添加对“ Reference to MS VB for Applications Extensibility 5.3”的引用外
You'll also need to enable programmatic accessto the VBA Project.
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.
在 Excel 2007+ 中，单击主功能区上的开发人员项，然后单击代码面板中的宏安全项。在该对话框中，选择宏设置并检查对 VBA 项目对象模型的信任访问。
回答by Wayne K
Thank you all! After testing multiple suggestions above, where "b" is the Worksheet name, you must use .CodeName, NOT .Name
谢谢你们！在测试了上述多个建议后，其中“b”是工作表名称，您必须使用.CodeName, NOT.Name
Set CodePaste = ActiveWorkbook.VBProject.VBComponents(WorkShe?ets(b).CodeName).Cod??eModule
If you have set your target worksheet as an object:
如果您已将目标工作表设置为对象：
Dim T As Worksheet
Set T = Worksheets("Test")
Then you simply need:
那么你只需要：
Set CodePaste = ActiveWorkbook.VBProject.VBComponents(Worksheets(T.Name).CodeName).Cod??eModule
