Excel VBA -- Compile Error: Automation Error

[cebalaw]

I wrote an Excel VBA program for my company that has been utilized for about a year with over 350 users, all running Windows XP and Office 2007. A handful of users who receive a new laptop (same as mine), have experienced a "Compile Error: Automation Error" when they attempt to run the program. There are no error codes, and the compile error points to a procedure declaration that compiles fine on other PCs: "Public Function KeyFound(strKey As String, SourceCollection As Collection) As Boolean". I have commented out the procedure only to get another compile error at a different procedure. I checked for missing references and found none; I have disabled any Add-Ins; I tried to delete all .EXD files; nothing works. One user reinstalled Office and got the program to run, another, needed to have IT rebuild his laptop and got it to work. I need to find the problem so I don't have to keep having users rebuild their laptops. Any help would be greatly appreciated.

 

[SkipVought]

hi,

It happens, from time to time, when I get my laptop refreshed (replaced), that some dll or a driver or something has been missed.

Your colleague, "reinstalled Office and got the program to run" and other had to "have IT rebuild his laptop." Sad, but true. IT sometimes drops the ball in such instances.

So here's the evidence: 1. It ran in a previous environment. 2. It fails to run in the new environment. THEREFORE, someting in the new environment is MISSING. I'd discuss this with your IT coordinator.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cebalaw]

I did, they told me they don't support macros

Then they pushed me to a Microsoft support number, who told me they don't support Office.

I know something is missing, just wondered if anyone has ever found a way to determine. The VBA error is providing no leads. My fear is that when we move to Windows 7 in a couple months I'm going to get 300 angry users.

 

[SkipVought]

I did, they told me they don't support macros

Of course they don't!

You asked them the wrong question!

"I have an Excel application that worked BEFORE I got my new laptop and now it's not working. I need to have Office reinstalled."

Of course, it might be something that is not related to the Office installation if you use ODBC drivers or link to other non-office applications. Or it could be a deliberate short installation of Office, without certain features. IT will not help you figure out WHICH problem it could be. I have had to figgure that out by trial and error. And one problem may be different than another.

Keep a log of complaints. The force of MANY could be used to present a case to IT, especially if management gets involved.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}