# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  -2147417848 automation error the object invoked has disconnected from its clients

## ajaykgarg

Hi

I get this error if I move away from excel to some other window while my macro is running.

*-2147417848 automation error the object invoked has disconnected from its clients*

I was not getting this earlier. 

MSDN says "Make sure that you set Option Explicit at the top of each file in your project."

I am adding some sheets at run time. How can I add Option Explicit at the top of added sheets? And, will that solve my problem? 

Are there any otehr suggestions?

Thanks
Ajay

----------


## shg

You're not adding code to those sheets as you add them, are you?

You can start by ensuring it's at the top of every module initially.

----------


## ajaykgarg

No, I am not adding any code there.
What could be other reasons for this error?
I was not getting it earlier. Suddenly, started getting this since past few days.

----------


## shg

That seems to resolve it in many cases, after you declare your variables appropriate to usage.

What are you doing that's exotic?

----------


## ajaykgarg

Well, it is a complicated code (to a newbie like me, anyways) running into more than 5k lines of code that adds sheets, rows and columns, merges cells, deletes sheets, creates graph, adds controls at run time, etc, to name a few.
(But, I have written the entire code. So,I know what the code does.)

Pl. note that 
a) This error was not occuring ealrier. Started occuring only recently.
b) I ran my old version (about a month old) of code. That is also giving this error now. So, any recent code change doesn't seem to be reason.
c) The error comes only when I move focus away from the excel to some other appliction, while macro is running.

Can you elaborate more on "declare your variables appropriate to usage"?

thanks
Ajay

----------


## ajaykgarg

Any thoughts on this, anyone?

----------


## shg

> more than 5k lines of code



In how many modules? 

How large is the largest module when exported as a bas file?

----------


## ajaykgarg

Biggest module is 5547 lines of code (about 500-1000 of that will be commented code). And a sheet has about 600 lines of code. Two other sheets have less than 50 lines each. 

Biggest module is 279KB when exported as a bas file.

----------


## shg

Think you need to chop it up; 64K is the upper limit, but I'd expect (don't know for sure) that that's for code, not comments. 

Meanwhile, do you indeed have Option Explicit at the top of all used modules?

----------


## ajaykgarg

Hi

I chopped up my module into 4 such that the biggest bas file is 61k.

I re-checked that I have Option Explicit at the top of all used modules.

But this still didn't help.  :Frown: 

I have got following code in "ThisWorkbook". Could this be a problem, since the issue comes only when I move away from this file, to another file, while macro is running?
I commented this, but the problem persists.




```
Please Login or Register  to view this content.
```

----------


## royUK

You don't want error handlers in code until fully tested, unless it displays the error number etc

----------


## ajaykgarg

Hi

Thanks. Your suggestion worked!!
I took off error handlers, and at least now I know *where* I am getting the error.
This is the line that errors:-



```
Please Login or Register  to view this content.
```


What is wrong with this replace statement?

tia
Ajay

----------


## ajaykgarg

Hi

Does anyone have any suggestions / workaround for this problem please?

thx
Ajay

----------


## KasiaFavourite

I have the same message popping up - only recently. It comes up when a user has to select a Forename from drop down to search for a patient record....




```
Please Login or Register  to view this content.
```


the line with lst master list becomes highlighted when I ask debugger to show me what's wrong. Any ideas?

----------


## arthurbr

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## KasiaFavourite

Very very sorry. Forgotten about this! Thought it may be easier ! Creating new thread already! Again - very sorry !

----------

