Excel 2010 macro issue

[cdchapman]

I have a feeling this issue may be specific to the company I work for, but any help and guidance would be appreciated.

Currently we use a mixture of Office 97, 2003 and 2007. Later this year we are going to be updated to Office 2010 and certain users are currently testing Office 2010 to ensure this meets our requirements.

One of the issues we have encountered is opening spreadsheets that contain macros that have been created in either Excel 97, 2003 or 2007. In these versions of Excel all macros work fine, but if the spreadsheet is loaded into Excel 2010, the macros when called (usually by the user pressing a button on a sheet), error with 'Compile error - Object library invalid or contains references to object definitions that could not be found'. I have checked all installed object libraries (via the Tools -> Preferences menu in VBA) on all versions, and they seem the same. The area/person(s) who originally wrote the VBA code have since been disbanded/left the company, so I am having trouble finding anyone within the company who is able to help us. This is a business critical spreadsheet which is not only used internally within the company but also by a number of external people via a link from our website, so we need to solve this issue as soon as possible.

Can anyone offer any help or advice on what may be causing this error and what can be done to correct it?

 

[cdchapman]

I have now solved this problem by searching Google and microsoft.com and found the following links:

http://www.lessanvaezi.com/delete-exd-files-to-fix-object-library-invalid-error/


http://support.microsoft.com/kb/957924/en-us

Basically this can happen after installing a certain Windows update: http://support.microsoft.com/default.aspx?scid=kb;en-us;932349. The update installs new versions of certain Visual Basic 6 Runtime files.

There is a corresponding EXD file that is created for each of the runtime files, when you use the controls provided by the runtime files in VBA. When the runtime files are updated, these EXD files are now invalid and hence this error occurs. To fix the error, you need to delete the EXD files and they will be re-created as needed.

On the PC's in the company I work for, these .exd files are contained in the following folder (Windows XP):

C:\Documents and Settings\USER\Application Data\Microsoft\Forms\

 

[alexaronson]

When you tested the program on the PC that had 2010, did you get any error messages from running it, or did you get the error messages when running the debuggar?

The reason that I am asking, is because I developed an application on 2007 and sent it to an offsite user who has 2010 and they are having problems with the file, but do not get any error messages when trying to run the macro.

Thanks for your time in responding.

 

[cdchapman]

The error appeared when an Excel 2010 user clicked a button on a worksheet that was linked to a VBA macro. Looking at the highlighted error line in VBA I could see that Excel did not even get to the main part of the macro as the highlighted line was the first DIM statement within the macro.