title: VBA throwing "The object invoked has disconnected from its clients" error
body: <p>I wrote a VBA macro in Excel quite a while back that was working flawlessly for months. Suddenly, the other day it started throwing an error, though I had not made any changes to the workbook:</p>
  <blockquote>
  <p>Run-time error '-2147417848 (80010108)':</p>
  <p>Automation error</p>
  <p>The object invoked has disconnected from its clients.</p>
  </blockquote>
  <p>I traced the error to the following lines of code.</p>
  <pre><code>wkbTool.Worksheets(&quot;Excl Spine&quot;).Range(&quot;A2:D&quot; &amp; exclRow).ClearContents '&lt;--- RUNS GREAT
  wkbTool.Worksheets(&quot;Delq Spine&quot;).Range(&quot;C2:D13&quot;).ClearContents '&lt;--- ERROR OCCURS HERE
  </code></pre>
  <p>Note that the <strong>first</strong> line runs fine every time and the <strong>second</strong> line is where the error occurs. I point this out because they are doing roughly the same types of work, but one throws the error while the other doesn't. This isn't my first reference to <code>wkbTool.Worksheets(&quot;Delq Spine&quot;)</code> in the script.</p>
  <hr />
  <p>I'm <a href="https://stackoverflow.com/questions/34065262/excel-vba-error-the-object-invoked-has-disconnected-from-its-clients">aware</a> that <a href="https://stackoverflow.com/questions/17302918/excel-vba-automation-error-the-object-invoked-has-disconnected-from-its-clients">solutions</a> for <a href="https://superuser.com/questions/1062608/the-object-invoked-has-disconnected-from-its-clients">this</a> error <a href="http://www.ozgrid.com/forum/showthread.php?t=83218" rel="nofollow noreferrer">abound</a>. I've tried most of them and a few ideas of my own. For example, the following throws the same error:</p>
  <p><strong>Attempt 1 - Everything is a variable</strong></p>
  <pre><code>Dim wkbTool as Workbook
  Dim wksht as Worksheet
  Dim myRange as Range
  
  Set wkbTool = Workbooks(&quot;Name.xlsm&quot;)
  Set wksht = wkbTool.Worksheets(&quot;Delq Spine&quot;)
  Set myRange = wksht.Range(&quot;C2:D13&quot;)
  
  myRange.ClearContents '&lt;--- ERROR
  </code></pre>
  <p><strong>Attempt 2 - The opposite</strong></p>
  <pre><code>Workbooks(&quot;Name.xlsm&quot;).Worksheets(&quot;Delq Spine&quot;).Range(&quot;C2:D13&quot;).ClearContents
  </code></pre>
  <p><strong>Attempt 3 - Perhaps <code>ClearContents</code> doing something crazy, skip it</strong></p>
  <pre><code>For i = 3 to 4
      For j = 2 to 13
          wkbTool.Worksheets(&quot;Delq Spine&quot;).cells(j,i) = &quot;&quot;
      Next j
  Next i
  </code></pre>
  <p>This results in Excel crashing, though it does <strong>not</strong> throw the error first. It clearly performs the operation in the first iteration before crashing because, when Excel recovers the workbook, cell(2,3) is empty. Also, I am not using <code>ClearContents</code> for the first line in this attempt, just in case <code>ClearContents</code> is causing something in itself.</p>
  <p><strong>Attempt 4 - Fine, I don't need that line anyway</strong></p>
  <p>I comment out the line that is causing the error. Upon looping back the next time, there is a reference to <code>Worksheets(&quot;Delq Spine&quot;)</code> that works in the first iteration but not the second:</p>
  <pre><code>With wkbTool.Worksheets(&quot;Delq Spine&quot;)
          .Activate
          .Range(&quot;C2&quot;).FormulaR1C1 = &quot;=IFERROR(VLOOKUP(RC[-1],'[&quot; &amp; wkbNew.Name &amp; &quot;]&quot; &amp; wksht.Name &amp; &quot;'!R3C2:R14C3,2,FALSE),0)&quot;
  End With
  </code></pre>
  <p>In the above, the worksheet is activated, but Excel crashes similar to Attempt 3 when assigning the formula to C2. Again, once Excel recovers, it's clear that the operation was successful, as the formula is updated in that cell.</p>
  <p><strong>Attempt 5 - Surely the workbook has been corrupted</strong></p>
  <p>I went back through my company's backup files to find a version of the workbook that was definitely working at the time. It ran in to the same error at the same place.</p>
  <p><strong>Attempt 6 - Let's just recreate the workbook then</strong></p>
  <p>I recreated the workbook from scratch, including writing in all formulas and formatting the cells. I did copy/paste the VBA script to the new version because I don't have time to rewrite it all only for it to fail again.</p>
  <hr />
  <p>I will continue to add more information about attempts I made as I think of them, I've been working on this problem on and off for the last week.</p>