使用 VBA 将模块插入新工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24807623/
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
Inserting a module into a new workbook using VBA
提问by LuckySevens
I've had a trawl around the net and can't seem to find a simple solution to this one so hopeing you can help.
我已经在网上进行了拖网，似乎无法找到解决此问题的简单解决方案，因此希望您能提供帮助。
I have a Macro at the moment which takes data from a .txt file and overwrites an excel file with the latest data every 3 minutes. It names the new file using the date and time of the overwrite.
我现在有一个宏，它从 .txt 文件中获取数据，并每 3 分钟用最新数据覆盖一个 excel 文件。它使用覆盖的日期和时间命名新文件。
What i would like is for the workbook with the 'create new workbook' macro to also add a new module into each new workbook as it is created, including a pre-written macro inside the module (The inserted module will include code to record who and when the workbook is opened and closed by so will be writing into a third workbook, but i can do that bit)
我想要的是带有“创建新工作簿”宏的工作簿，以便在创建新工作簿时向每个新工作簿添加一个新模块，包括模块内预先编写的宏（插入的模块将包括记录谁的代码当工作簿被打开和关闭时，因此将写入第三个工作簿，但我可以做到这一点）
I'm hopeing that all of this can be done with a second Sub!
我希望所有这些都可以用第二个 Sub 来完成！
Thanks! 7's
谢谢！7个
回答by djikay
I'll get you started with adding a new module to a workbook, but can't possibly write everything for you.
我将帮助您开始向工作簿添加新模块，但不可能为您编写所有内容。
We'll be using the VBA Extensibility library (VBE). It contains the definitions of the objects that make up the VBProject. So, before you start, make sure to add this reference:
我们将使用 VBA 扩展库 (VBE)。它包含组成VBProject. 因此，在开始之前，请确保添加此引用：
Microsoft Visual Basic For Applications Extensibility 5.3
Microsoft Visual Basic 应用程序可扩展性 5.3
to your workbook. To do that, in the VBA editor go the the Toolsmenu and choose References. In that dialog, scroll down to and checkthe entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you don't set this reference, you will receive a compiler error.
到您的工作簿。为此，在 VBA 编辑器中，转到Tools菜单并选择References。在该对话框中，向下滚动并检查Microsoft Visual Basic For Applications Extensibility 5.3条目。如果不设置此引用，则会收到编译器错误。
You also need to enable programmatic access to the VBA Project. To do that (in Excel 2010 -- similar steps for other versions), go to File > Options > Trust Center > Trust Center Settings... > Macro Settings and tick Trust access to the VBA project object model.
您还需要启用对 VBA 项目的编程访问。为此（在 Excel 2010 中 - 其他版本的类似步骤），请转到文件 > 选项 > 信任中心 > 信任中心设置... > 宏设置并勾选对 VBA 项目对象模型的信任访问。
And now you're ready for the code:
现在您已准备好使用代码：
Public Sub AddNewModule()
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Set proj = ActiveWorkbook.VBProject
Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
comp.Name = "MyNewModule"
Set codeMod = comp.CodeModule
With codeMod
lineNum = .CountOfLines + 1
.InsertLines lineNum, "Public Sub ANewSub()"
lineNum = lineNum + 1
.InsertLines lineNum, " MsgBox " & """" & "I added a module!" & """"
lineNum = lineNum + 1
.InsertLines lineNum, "End Sub"
End With
End Sub
This will add a new standard module, called "MyNewModule" to the active workbook and hardcode a little sub called "ANewSub" that, when run, simply shows a message box.
这将向活动工作簿添加一个名为“MyNewModule”的新标准模块，并对一个名为“ANewSub”的小子进行硬编码，该子在运行时仅显示一个消息框。
I trust and hope you can take this and build on it.
我相信并希望你能接受并以此为基础。
