运行时错误“9”：下标超出范围 - 仅当 Excel VBE 关闭时

发布于 2024-11-10 04:16:45 字数 1648 浏览 4 评论 0原文

所有，

我在 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

疯狂的代价 2024-11-17 04:16:45

我不确定您为什么会收到该错误，但这是另一种可以避免该错误的方法

Sub Main()

    Dim ws As Worksheet
    Dim rng As Range
    Dim sCode As String

    Set ws = ThisWorkbook.Worksheets.Add
    Set rng = ws.Range("D1:D10")

    sCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "Dim rng As Range" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "Set rng = Me.Range(" & """" & rng.Address & """" & ")" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "If Target.Count > 1 Then Exit Sub" & vbNewLine
    sCode = sCode & vbTab & "If Intersect(Target, rng) Is Nothing Then Exit Sub" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "MsgBox (""Value Changed!..."") " & vbNewLine
    sCode = sCode & "End Sub"

    ThisWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule.AddFromString sCode

End Sub

I'm not sure why you're getting that error, but here's another approach that will avoid it

Sub Main()

    Dim ws As Worksheet
    Dim rng As Range
    Dim sCode As String

    Set ws = ThisWorkbook.Worksheets.Add
    Set rng = ws.Range("D1:D10")

    sCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "Dim rng As Range" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "Set rng = Me.Range(" & """" & rng.Address & """" & ")" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "If Target.Count > 1 Then Exit Sub" & vbNewLine
    sCode = sCode & vbTab & "If Intersect(Target, rng) Is Nothing Then Exit Sub" & vbNewLine & vbNewLine
    sCode = sCode & vbTab & "MsgBox (""Value Changed!..."") " & vbNewLine
    sCode = sCode & "End Sub"

    ThisWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule.AddFromString sCode

End Sub

回复收藏 0 原文

~没有更多了~