运行时错误“9”:下标超出范围 - 仅当 Excel VBE 关闭时
所有,
我在 Excel 宏中遇到一些 VBA 代码错误。这是我正在尝试的工作流程:
- 我有一个模块,它运行代码来创建一个新工作表,对其进行格式化并在同一模块中添加一堆值
- ,我根据填充的最后一行确定单元格范围(这将始终根据之前的步骤而有所不同)
一旦我知道了这个范围,我就使用下面的代码写入新创建的工作表代码模块,以便我可以设置“change_event”。我只希望当我刚刚确定的范围内的值发生更改时触发change_event:`
变暗起始线只要 起始线 = 1 Dim x 作为整数 x = 错误数 - 1 调光范围 设置 rng = Range("D" & LastRow - x & ":" & "D" & LastRow) 使用 ThisWorkbook.VBProject.VBComponents(VRS.CodeName).CodeModule Startline = .CreateEventProc("更改", "工作表") + 1 .InsertLines Startline,“Dim rng As Range” 起始线 = 起始线 + 1 .InsertLines Startline, "Set rng = Range(" & """" & CStr(rng.Address) & """" & ")" 起始线 = 起始线 + 1 .InsertLines Startline,“如果 Target.Count > 1 则退出 Sub” 起始线 = 起始线 + 1 .InsertLines Startline,“如果 Intersect(Target, rng) 没有任何内容,则退出 Sub” 起始线 = 起始线 + 1 .InsertLines Startline, "MsgBox (""值已更改!..."") " 结束于
代码有效,并将以下内容写入指定工作表的代码模块中:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D58:D62")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox ("Value Changed!...")
End Sub`
此代码也有效,并且当单元格中的单元格出现时,会出现消息框范围已更改。但是,随着 VBE 关闭,它将产生错误:
Run-time error '9': Subscript out of range
点击调试将我带到该行:
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
但它实际上在以下行中抛出错误:
Startline = .CreateEventProc("Change", "Worksheet") + 1
All,
I am facing an error with a some VBA code in an Excel macro. Here’s the workflow I am attempting:
- I have a module that runs code to create a new worksheet, format it and add in a bunch of values
- within this same module, I determine a range of cells based on the last row populated (which will always be different depending upon previous steps)
Once I know this range, I use the code below to write to the newly created worksheets codemodule so I can set up a ‘change_event’. I only want the change_event to trigger when the values in the range I just determined are changed:`
Dim Startline As Long Startline = 1 Dim x As Integer x = Errors.Count - 1 Dim rng As Range Set rng = Range("D" & LastRow - x & ":" & "D" & LastRow) With ThisWorkbook.VBProject.VBComponents(VRS.CodeName).CodeModule Startline = .CreateEventProc("Change", "Worksheet") + 1 .InsertLines Startline, "Dim rng As Range " Startline = Startline + 1 .InsertLines Startline, "Set rng = Range(" & """" & CStr(rng.Address) & """" & ")" Startline = Startline + 1 .InsertLines Startline, "If Target.Count > 1 Then Exit Sub" Startline = Startline + 1 .InsertLines Startline, "If Intersect(Target, rng) Is Nothing Then Exit Sub" Startline = Startline + 1 .InsertLines Startline, "MsgBox (""Value Changed!..."") " End With
The code works, and writes the following into the codemodule of the specified worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D58:D62")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox ("Value Changed!...")
End Sub`
This code works too, and the message box appears when the cells in the range are changed. However, with the VBE closed it will produce the the error:
Run-time error '9': Subscript out of range
Hitting debug takes me to the the line:
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
But it actually throws the error on the following line:
Startline = .CreateEventProc("Change", "Worksheet") + 1
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我不确定您为什么会收到该错误,但这是另一种可以避免该错误的方法
I'm not sure why you're getting that error, but here's another approach that will avoid it