运行时错误\'9\'：下标超出范围-仅在关闭Excel VBE时

| 所有， 我在Excel宏中遇到一些VBA代码错误。这是我正在尝试的工作流程： 我有一个模块，该模块运行代码来创建新的工作表，对其进行格式化并添加一堆值 在同一模块中，我根据填充的最后一行来确定单元格的范围（根据之前的步骤，该行将始终是不同的） 知道此范围后，我将使用下面的代码写入新创建的工作表代码模块，以便设置“ 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
该代码有效，并将以下内容写入指定工作表的代码模块中： 
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
    
2020-01-09 2 条评论
分享

没有找到相关结果

    已邀请:

    骨乏唯瓜

    我不确定为什么会收到该错误，但是这是另一种避免此错误的方法 
    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
        
    2020-01-09 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.038419961929321