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

Question

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

Ok, so i modified the code to simply delete all code from the specified worksheet and re-write instead of deleting and writing fresh each time. This prevents the error from popping up, but automatically opens the VBE instead.... I use the line below to close the VBE...not very slick though. I'm open to a more graceful solution: Application.VBE.MainWindow.Visible = False — gg7aph, May 26 '11 at 16:58
Possible duplicate of [VBA changing sheet codename Run-Time Error 9: Subscript out of range](http://stackoverflow.com/questions/41022319/vba-changing-sheet-codename-run-time-error-9-subscript-out-of-range) — ThunderFrame, Dec 22 '16 at 23:30

score 3 · Answer 1 · answered May 27 '11 at 18:11

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

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

1 Answers1