Excel vba 以编程方式将代码添加到工作表模块
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34837006/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 10:10:58 来源:igfitidea点击:
Excel vba add code to sheet module programmatically
提问by vims liu
How to put the programmatically generated workbook an event code similar to below:
如何将以编程方式生成的工作簿放入类似于以下的事件代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextTarget As Range
Set nextTarget = Range(Selection.Address) 'store the next range the user selects
Target.Columns.Select 'autofit requires columns to be selected
Target.Columns.AutoFit
nextTarget.Select
End Sub
回答by Davesexcel
Use this to add a workbook and place a worksheet change event into the Sheet1 module.
使用它来添加工作簿并将工作表更改事件放入 Sheet1 模块中。
Sub AddSht_AddCode()
Dim wb As Workbook
Dim xPro As VBIDE.VBProject
Dim xCom As VBIDE.VBComponent
Dim xMod As VBIDE.CodeModule
Dim xLine As Long
Set wb = Workbooks.Add
With wb
Set xPro = .VBProject
Set xCom = xPro.VBComponents("Sheet1")
Set xMod = xCom.CodeModule
With xMod
xLine = .CreateEventProc("Change", "Worksheet")
xLine = xLine + 1
.InsertLines xLine, " Cells.Columns.AutoFit"
End With
End With
End Sub
When you 1st run the code you may get an error.
当您第一次运行代码时，您可能会收到错误消息。
Hit the Stop Icon and select the tools menu and "References"
点击停止图标并选择工具菜单和“参考”
Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.
然后找到“Microsoft Visual Basic for Applications Extensibility 5.3 library”并检查它。
Run the code again and it should work.
再次运行代码，它应该可以工作。
