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

Run-time error '9': Subscript out of range - only when Excel VBE is closed

提问by gg7aph

All,

全部，

I am facing an error with a some VBA code in an Excel macro. Here's the workflow I am attempting:

我在 Excel 宏中遇到一些 VBA 代码错误。这是我正在尝试的工作流程：

• 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
  

• 我有一个模块运行代码来创建一个新的工作表，格式化它并添加一堆值
• 在同一个模块中，我根据填充的最后一行确定一系列单元格（根据前面的步骤，它总是不同的）

• 一旦我知道这个范围，我就使用下面的代码写入新创建的工作表代码模块，以便我可以设置一个“change_event”。我只希望 change_event 在我刚刚确定的范围内的值发生更改时触发：`

  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 closedit will produce the the error:

此代码也有效，并且在更改区域中的单元格时会出现消息框。但是，当 VBE关闭时，它会产生错误：

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

回答by Dick Kusleika

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