By Ross Mclean 10/12/04

Q: How do computer programmers spell hell?
A: .DLL

Are you seeing messages like any of these?

Ok this is what's happening. At some stage in the spreadsheets life a reference has been made to some object or library. At the time, the computer made a link to the object or library and every time it needed information about the object or library it used this link to go an get it. For example; say I added a special type of button to a form. Lets call it “Ross's_Button”. I got this button from a web site and downloaded it onto my PC, but when I send the spreadsheet to someone else they don't have any information about “Ross’s_Button”, because they have never downloaded the button! So when Excel tries to find the information about “Ross's_Button”, it can’t. This is more or less what's happening. With different computer builds and different versions of Excel this can happen even with common place objects and libraries. Frequently it’s not that the reference object is missing, but that the path for the link is slightly different.

There are many other situations that might cause the above behaviour, defragged you hard disk lately?, upgraded excel?, loaded a new addin? installed a new program!

If your using Excel 2000 or later it's normally easy to remove the reference:

Open the VBE (Alt + F11), from the Tools menu open the Reference dialog. The missing reference has, wait for it, "MISSING" in front of it. To remove it you can just uncheck the box next to it.

In most cases this solution is ok, but sometimes it doesn't work - or by removing the reference you lose some part of the spreadsheet you are using.

In my experience Excel 97 can often be a right pain when you try and remove a reference. Here are a number of things you can try:

If using excel 97, open the spreadsheet in a later version, remove the reference and save the file. The reference will now be removed from the spreadsheet and will not be required when you open the spreadsheet back in 97.

The following applies to all versions of excel, and may be useful is your having difficulties.

 

Re-establish the reference.
Try to re-establish the reference: Instead of un-checking the checkbox, use the browse button (on the right of the Reference dialogue) and try and find the file. Once you have located it selected it and click open, this should re-establish the reference.

The full file path for the reference should be shown at the bottom of the References dialog, which should also open up in this directory. If you can't find the file, make sure you've selected the "all files" option from the “File of type” drop down at the bottom of the browse window.

Some times even the above wont fix the problem. I have in that past had to create a new folder, copy the troublesome reference into it and the re-establish the reference at this location. If you do this, it's a good idea to delete the old reference, and then try again to establish the reference in the original directory (winn32 or system32) – i.e., copy the reference back into the original folder and reference this.

Along the same lines, another method I’ve employed is to make a “fake” reference file. Create a new text file and changed the name and file extension to the one the References dialog is looking for. Move this file in to the required directory and make a reference to it. (Again the information about the filename, extension, and location can all be found on the References dialogue.)

Of course there is no functionality, but it can be a way round the problem. Once the reference is made it can be removed from the reference dialog more readily.  

 

Get a copy of the .dll and install that.
Another option might be to find a copy of the file in question and load it on to your PC. Many of the reference libraries come packaged with office, so a search of the hard drive may turn something up, others can be found on the web. If you still can't find it, maybe you could get a copy off a another PC. A word of warning however, some object and libraries are copyright protected, so just be careful, what you download or copy.

 

One final tip.
If the reference in question refers to some kind of control, remove all of the controls you may have on your forms etc.  and remove the control from the tool box.

 

Side note.
It is possible to register libraries etc. programmatically, here are a couple of links that introduce the topic!
From Ivan F Moala’s excellent website:
http://xcelfiles.homestead.com/WhatsUpClass.html
And from Devx:
http://archive.devx.com/vb/free/articles/vb2themax/10tips/fb0201-6.asp

 

More information:
And if all else fails !
http://support.microsoft.com/kb/q160870/
and,
http://support.microsoft.com/?scid=kb;en-us;184981
Ouch! :-(