1

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:

Run-time error '-2147417848 (80010108)':

Automation error

The object invoked has disconnected from its clients.

I traced the error to the following lines of code.

wkbTool.Worksheets("Excl Spine").Range("A2:D" & exclRow).ClearContents '<--- RUNS GREAT
wkbTool.Worksheets("Delq Spine").Range("C2:D13").ClearContents '<--- ERROR OCCURS HERE

Note that the first line runs fine every time and the second 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 wkbTool.Worksheets("Delq Spine") in the script.

I'm aware that solutions for this error abound. I've tried most of them and a few ideas of my own. For example, the following throws the same error:

Attempt 1 - Everything is a variable

Dim wkbTool as Workbook
Dim wksht as Worksheet
Dim myRange as Range

Set wkbTool = Workbooks("Name.xlsm")
Set wksht = wkbTool.Worksheets("Delq Spine")
Set myRange = wksht.Range("C2:D13")

myRange.ClearContents '<--- ERROR

Attempt 2 - The opposite

Workbooks("Name.xlsm").Worksheets("Delq Spine").Range("C2:D13").ClearContents

Attempt 3 - Perhaps ClearContents doing something crazy, skip it

For i = 3 to 4
    For j = 2 to 13
        wkbTool.Worksheets("Delq Spine").cells(j,i) = ""
    Next j
Next i

This results in Excel crashing, though it does not 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 ClearContents for the first line in this attempt, just in case ClearContents is causing something in itself.

Attempt 4 - Fine, I don't need that line anyway

I comment out the line that is causing the error. Upon looping back the next time, there is a reference to Worksheets("Delq Spine") that works in the first iteration but not the second:

With wkbTool.Worksheets("Delq Spine")
        .Activate
        .Range("C2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'[" & wkbNew.Name & "]" & wksht.Name & "'!R3C2:R14C3,2,FALSE),0)"
End With

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.

Attempt 5 - Surely the workbook has been corrupted

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.

Attempt 6 - Let's just recreate the workbook then

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.

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.

Community
  • 1
  • 1
Poisson Fish
  • 117
  • 8
  • 1
    Does the worksheet `"Delq Spine"` have event handlers? Do some of its cells have data validation? Protection? do you use `.Activate` and `.Select` frequently in the code? There are so many potential causes for this anomaly, you need to check them one by one. Difficult to diagnose by anyone else without some minimal reproducible example. – A.S.H Apr 13 '17 at 21:26
  • You can also try disabling events, screen updating, all possible stuff at the beginning of the macro. – A.S.H Apr 13 '17 at 21:36
  • @A.S.H, thanks for the tips. I do have events and screen updating disabled. There are no event handlers, data validation, or protection on the worksheet. Unfortunately, I do have a couple instances of `.Activate` and `.Select`, some of which would be easy to replace. I can't imagine that would be the cause though, since it was working fine until recently (with no changes being made). I'll just have to spend some time replacing those Activations and Selections. – Poisson Fish Apr 13 '17 at 21:48
  • @A.S.H, if that fails, I will see about stripping out as much as possible while still having the the script run and fail. It's too long to post here in raw form. Thanks again! – Poisson Fish Apr 13 '17 at 21:50
  • Yes, those lines where the code breaks are really too simple to be suspect. There's surely something going on elsewhere. – A.S.H Apr 13 '17 at 22:16
  • 2
    An error will occur if at least one cell in the range to be cleared is merged with a cell outside the range. – Variatus Apr 14 '17 at 01:58

0 Answers0