Automation error

[mcs51mc]

To all the macro specialists out there…
When running a macro I sometimes got an “Automation error”. When I say sometime, it’s really sometime; I can run the macro +100 times with different selections (resulting in different parts of the macro executed) with no problem and the next time BANG Automation error
The complete message box looks like:
Run-time error -2147417848(80010108):
Automation error
The object invoked has disconnected from its clients.
Four buttons: Continue (disabled), End, Debug and Help
Both End and Debug result in a new message box:
Excel has generated errors and will be closed by Windows. End of the story

Now the strange part….
When I restart Excel, open the workbook and go to the VBA code no subroutine name appear in both “General” and “Declarations” drop down boxes. I can see all the code but it seems that Excel can’t split it in different controls-code, routines and functions…
I can start the macro (button on a sheet), all the controls works even the code behind the controls works but at a certain part in the code BANG Automation error

One thing is sure; you can forget about that file, it won’t work well any more.

For that reason I keep a copy of a working workbook. I make a copy of the copy, rename it and I can go on.

I’m sure there’s a better way to work, resolve the Automation error, but where do I start … …

Any help is greatly appreciated
If you need further information, let me know

 

[BrianB]

It is very easy to corrupt a macro module - especially when making lots of changes during development.

A way of "cleaning" the code is to copy/paste it into Notepad (not Word), delete the old module, then copy/paste the code back into a new module.

This also refers to worksheets - so, with a big project, at some late stage it is a good idea to remake everything starting from a new workbook and copy/pasting formats & formulas (ie. contents, not complete sheets) to new sheets.

Also, make sure that, with multiple versions of Excel, do the setup in the oldest version (and keep it that way).

 

[mcs51mc]

The code is not in a module but in a User form. Is it enough to copy all the code to Notepad leaving all controls on the module untouched, close Excel, open Excel again and paste all the code back?
Or do I really have to delete the user form also?

Is it better to create subroutines for each control in seperate modules and write "Call ControlABC_Sub ()" in the event routine of the control than writing all the code directly in the event routine of the control?

I didn't change anything since June 2005 and last monday BANG "Automation error"

Is there really no way to find out what "The object invoked has disconnected from its clients." means?? I mean: what object, which client, I didn't realize I connect something least of all disconnect it again

 

[BrianB]

As stated, my *guess* is that some sort of corruption has occurred. This is suggested by the fact that your file has been working for a long time. In this context the error mesage is meaningless.Excel has just tried to find something appropriate.

In this situation there are no rules. We have to do what we can to get things working. This might mean setting everything up again. This is a matter for trial and error.

 

[erik.van.geit]

hello, mcs51mc,
and hello, Brian

just another idea
I have encountered situations like this
1. useform has initialize_event taking values from sheets
2a. some info on the sheets is erroring (divide by zero, or #N/A, ...)
2b. or a variable dependent of sheetsitutation cannnot be properly set a a certain moment (Application.WorksheetFunction.Vlookup, ...)
3. so a control on the form cannot be "fed" without error
instead of displaying the bug-errormessage at the appropriate line you get "automation error"

possible solution
1. set a breakpoint at start of initialize_event (or jus before calling it) and continue step by step using functionkey F8
2. not a real solution but good to test if it is a bug of the kind would be to insert a line "On Error Resume Next": if you don't get the bug anymore, great chance you will get results with "solution point 1."
3. place quotes before some suspected lines to temporarily disable the code: this is more specific searching than "On Error Resume Next"

kind regards,
Erik