What causes the "Unspecified error automation error" in Worksheet_Activate?

Question

I have a worksheet with the name "Dates" (the name of the object is A_Dates) that needs to be calculated when it is activated (maybe it’s worth noting that this is in my Personal Macro Book). I regularly open workbooks that have too many calculations for me to have auto-calculation. Therefore, I have automatic calculation in manual mode and the following code on the sheet:

Private Sub Worksheet_Activate() A_Dates.Calculate End Sub 

This has worked great the past 3 months, day after day. Yesterday he stopped working. Now it throws this error in the declaration line:

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

I tried to change the way the sheet is referenced using:

 Sheets("Dates").Calculate 

and

 ActiveSheet.Calculate 

to no avail. I also included error handling:

 On Error Resume Next 

which does not bother him. I even went as far as:

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

and it is still displayed. I am completely at a loss. Help?

Additional Information

I have the following links and use them in various macros in this book:

Visual Basic for Applications

• Microsoft Excel Object Library 12.0
• OLE Automation
• Microsoft Office Object Library 12.0
• Microsoft Scripting Execution
• Microsoft Forms 2.0 Object Runtime
• Microsoft HTML Object Library
• Microsoft Internet Controls
• Microsoft ActiveX 2.8 Data Objects Library
• Microsoft ActiveX Data Objects Recordset 2.8 Library

Answer 1

I found a problem. One of my forms controlled it, which apparently stopped working, and it had a ripple effect.

This control was "Microsoft ProgressBar Control, Version 6.0." I don’t know WHY it stops working, but deleting the form (and, of course, all the links to it) solved the problem.

Answer 2

We faced the same problem, but with the twosystems - we have several people who successfully use the same macros, but they have a problem with the error "Automation error" "Unspecified error". Your answer helped me determine that the problem might be related to Microsoft ProgressBar Control. (Thank you very much)

But instead of deleting the form, I did not register and register MSCOMCTL.OCX on the PC of a user who had problems and returned to business again. I would like to know what led to the registration of the control to the south - this is not the first time I have had to monitor problems with this control.

To unregister and register a control:

Use "Elevated Command Prompt" (the command prompt is launched as an administrator), run the following commands:

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

NOTE: / u unregisters ocx

Answer 3

Whenever I get such strange errors, the first thing I do is clear the code http://www.appspro.com/Utilities/CodeCleaner.htm . This is a free add-on, but you can do it manually. Just copy your code from the module and into a text file (or right-click and select "Export"). Then delete the code in the module, compile and save and return the code.

When Excel compiles on the fly, it creates p-code, which is then compiled into machine code. Sometimes, especially with intensive editing, the p-code gets corrupted. Copy code, delete and copy back to Excel to recover p-code.

I decided some really strange behavior with this method. Hope this works for you too.

Answer 4

I solved this problem by adding the following links to VBA - Tools - Links.

Microsoft ActiveX Data Objects 2.8 Library Microsoft ActiveX Data Objects Record Set 2.8 Library

Answer 5

I saw this error as a dialog box just before Excel stopped working. I debugged a new script that opens several hundred Excel files and extracts some information from each. To solve this problem, I added several DoEvents commands, and my problem with this error immediately disappeared.

Answer 6

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

Fixed by adding DoEvents for Internet Explorer as follows:

 Do DoEvents Loop Until ie.readyState = READYSTATE_COMPLETE 

Also note that this value may reflect the change between completed and busy, so a more robust loop may be useful here.

Answer 7

Just a note for other people coming up with an “automation error”. I also had one, and I found that this is a special case of integer overflow, in which it breaks in a loop and throws an automation error instead of an integer overflow error. If you did not find the solution above, check if you are using the correct sizes.

Answer 8

Try compiling your program for a lower version of the OS.