What is causing "Automation error Unspecified error" on Worksheet_Activate?

9
votes

I have a worksheet named "Dates" (object name is A_Dates) that needs to be calculated when it is activated (It may be worth noting that this is in my Personal macro workbook). I regularly have workbooks open that have too many calculations in the for me to have auto-calculation on. So I have auto-calc set to manual, and the following code in the worksheet:

Private Sub Worksheet_Activate()
   A_Dates.Calculate
End Sub

This has worked fine for the last 3 months, day-in and day-out. Yesterday, it stopped working. It now throws this error on the declaration line:

Microsoft Visual Basic
Automation error
Unspecified error
[OK] [Help]

I have tried changing how I reference the sheet, using:

Sheets("Dates").Calculate

and

ActiveSheet.Calculate

to no avail. I've also included error handling:

On Error Resume Next

which doesn't prevent it. I've even gone so far as:

Private Sub Worksheet_Activate()
   On Error GoTo headache
   Sheets("Dates").Calculate
Exit Sub

headache:
   Exit Sub
End Sub

and it still shows up. I am totally at a loss. Help?

Additional Information

I have the following references, and use all of them in various macros in this workbook:

Visual Basic for Applications

  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library
  • Microsoft Scripting Runtime
  • Microsoft Forms 2.0 Object Runtime
  • Microsoft HTML Object Library
  • Microsoft Internet Controls
  • Microsoft ActiveX Data Objects 2.8 Library
  • Microsoft ActiveX Data Objects Recordset 2.8 Library
9
vbaerror-handlingexcel-2007
If you comment that line out and manually force a Recalc (using [F9]) do you receive an error? – mwolfe02
No, I do not. And, everything seems to calculate correctly. – Farfromunique
Maybe the error has to do with running any VBA, not necessarily the Calculate method. What if you try replacing A_Dates.Calculate with Debug.Print A_Dates.Name? Does that generate an error? – mwolfe02
Yes, it does generate the same error. sigh I don't like where this is heading ... – Farfromunique
Next step is to make sure you are not missing any references. In VBA IDE, Tools --> References... – mwolfe02

9 Answers

14
votes

We ran into the same problem, but with a twist - we have several people all using the same macros successfully, but one is having the "Automation Error" "Unspecified Error" problem. The other answer helped me identify that the problem might be due to the "Microsoft ProgressBar Control". (Thank you very much)

But instead of removing the form, I unregistered and registered the MSCOMCTL.OCX on the user's PC that was having problems and he's back in business again. I wish I knew what caused the registration of the control to go south - this is not the first time I've had to track down problems with this control.

To unregister and register the control:

Use an “Elevated command prompt” (command prompt run as an administrator), issue the following commands:

 Regsvr32 /u c:\windows\SysWOW64\MSCOMCTL.OCX

 Regsvr32 c:\windows\SysWOW64\MSCOMCTL.OCX

NOTE: the /u unregisters the ocx

8
votes

Whenever I get strange errors like this, the first thing I do is clean the code with http://www.appspro.com/Utilities/CodeCleaner.htm. It's a free add-in, but you can do it manually too. Just copy your code out of the module and into a text file (or right click and Export). Then delete the code in the module, compile and save, and put the code back in.

When Excel compiles "on the fly" it's creating p-code which then gets compiled to machine code. Sometimes, particularly with heavy editing, the p-code gets corrupted. Copying the code out, deleting it, and copying back in forces Excel to regenerate the p-code.

I've solved some really strange behavior with this method. Hopefully it works for you too.

5
votes

I found the issue. One of my forms had a control on it that apparently stopped working, and this had a ripple effect.

The control in question was "Microsoft ProgressBar Control, version 6.0". I do not know WHY it stopped working, but removing the form (and, of course, all references to it) resolved the issue.

2
votes

I just had this problem, no luck with removing the loading controls or adding/moving references.

Fixed by adding a DoEvents for Internet Explorer like so:

Do
  DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Note too, that this value can bounce between "complete" and "busy" so a more robust loop here may be beneficial.

1
votes

My fix on this problem was to add the following references in VBA - Tools - References.

Microsoft ActiveX Data Objects 2.8 Library Microsoft ActiveX Data Objects Recordset 2.8 Library

1
votes

I saw this error pop up as a dialog box right before Excel stopped working. I was debugging a new script that opens several hundred excel files and pulls some info from each one. To resolve, I added several 'DoEvents' commands and my issue with this error went away immediately.

1
votes

Just a note for other people coming with "automation error". I had one too and found it to be a special case of integer overflow where it would break in the loop and give automation error instead of the integer overflow error. If you didnt find a solution above check if you are using the correct dimensions.

0
votes

Try to compile your program to a lower version of the OS.

0
votes

I had two "exact" blocks of VBA code, one executed, the other had the automation error. I ran the Debugger and it got stuck on the code block below:

Do While ie.readyState = READYSTATE_COMPLETE
Application.StatusBar = "Going to StackOverflow ..."
DoEvents
Loop

So, I pasted the code block that worked and the one that didn't into separate Microsoft word documents and then used the Review / Compare feature. It revealed the error:

Do While ie.readyState = READYSTATE_COMPLETE (equals sign - incorrect)

vs.

Do While ie.readyState <> READYSTATE_COMPLETE (double brackets - correct)

I highly suggest using Word to assist where visually it just isn't clear.

Yes!