title: Run-time error '9': Subscript out of range - only when Excel VBE is closed
body: <p>All,</p>
  
  <p>I am facing an error with a some VBA code in an Excel macro. Here’s the workflow I am attempting:</p>
  
  <ul>
  <li>I have a module that runs code to create a new worksheet, format it and add in a bunch of values</li>
  <li>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) </li>
  <li><p>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:`</p>
  
  <pre><code>Dim Startline As Long
  Startline = 1
  Dim x As Integer
  x = Errors.Count - 1
  
  Dim rng As Range
  Set rng = Range("D" &amp; LastRow - x &amp; ":" &amp; "D" &amp; 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(" &amp; """" &amp; CStr(rng.Address) &amp; """" &amp; ")"
          Startline = Startline + 1
          .InsertLines Startline, "If Target.Count &gt; 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
  </code></pre></li>
  </ul>
  
  <p>The code works, and writes the following into the codemodule of the specified worksheet:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Set rng = Range("D58:D62")
  If Target.Count &gt; 1 Then Exit Sub  
  If Intersect(Target, rng) Is Nothing Then Exit Sub
  MsgBox ("Value Changed!...") 
  End Sub`
  </code></pre>
  
  <p>This code works too, and the message box appears when the cells in the range are changed. However, with the VBE <strong>closed</strong> it will produce the the error:</p>
  
  <pre><code>Run-time error '9': Subscript out of range
  </code></pre>
  
  <p>Hitting debug takes me to the the line:</p>
  
  <pre><code>With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
  </code></pre>
  
  <p>But it actually throws the error on the following line:</p>
  
  <pre><code>Startline = .CreateEventProc("Change", "Worksheet") + 1
  </code></pre>