Compile Error: "Object Library Invalid..."

If you’ve ever come across the ‘Compile Error’ message you’ll know how much fun they can be to correct. 

Quite often when this message appears you’ll find yourself faced with the VBE telling you that ‘Left’ is not a valid function !  The first thing you should check is what references you are using.  If you’ve ever written some code on one version of Excel and tried to role it out to users with an older version you’ll have seen this many times. 

When you add references in an Excel 97 file and use it in Excel 2003 the references will automatically update.  If you try and do that the other way round the references do not ‘back-date’.  You’ll need to go to the references box and un-check the ‘Missing’ reference.  Then look through the list and find the older version which is available to the Excel version you are using.

The most recent one I found displayed the following message:

Object Library Invalid or Contains References to Object Definitions that could not be found.

I’ll add here that I was working in an office which predominately used Excel 97 with several PCs with Excel 2003.  A little retro there ;)

The error occurred in Excel 2003 using an Add-In (.xla) created in Excel 2003 !  I checked for missing references and found none.  The file opened and the code ran without issue on other machines, both Excel 97 and Excel 2003. 

After turning to Google I found this thread at ozgrid.com.  The answer; deleting the all the EXD files left by previous iterations of the project. EXD files cache ActiveX component information. If the component changes without removing the EXD file (which is created when the component is first used), then the system will be out of sync. 

To delete the EXD files go to command prompt and type the following DOS commands:

• > CD \Document and Settings
• > DEL /S /A:H /A:-H *.EXD

To explain what that does I’ll quote from the thread:

Essentially the command recursively deletes all your hidden and exposed exds. That will make all your ActiveX components load a bit slower the first time you use them again, but it should also clear out the problematic ones.

So, I’ll keep my fingers crossed that the above works for you too.