EXCEL User Defined Function - Can't find project or library without any MISSING reference

Question

I developed a user defined function in my computer and save the document as XLSM. When I distributed it to other users in my company, they get "Can't find project or library" error as long as they open the workbook and then hit "Enable Macros".

If they hit Debug, the yellow line will fall on the head line of the user defined function, like this

Then I checked the Tools - Reference of VBE, but find nothing missing in all computers (mine and other users').

But the Reference is a little bit difference,

In my computer, it is

In other users' computer, it is

The only difference is the highlighted line. In my computer it is Microsoft office 16.0 Object Library, since I installed Project 2016 with Excel 2013. While others' are 15.0.

By the way, I get the above picture before I hit the "Enable Macro" button since once I hit it, the error message will show for infinite times and I will never be able to go into the Reference in VBE. Therefore it is possible that there will be a MISSING reference when the code is actually running.

Answer 1

This likely happens when you use early-binding somewhere in your code (I don't see this happening in the codepart on the screenshot).

A workaround would be to change your code to use late-binding. Please have a look at the answer of Siddharth Rout in Preventing Excel VBA compile errors due to users having an older version of MS Office (MS Outlook) installed? for more information.

Answer 2

This problem is very likely caused by the different versions of Microsoft Office Object Library.

You can try to export the VBA code and remove the VBA module in your xlsm file, and then reinsert the VBA code back in users' computers. Usually the problem will be solved.

The reason why this problem occurs is that Excel actually DOES save the compiled code of VBA, though we never see any compiled codes or compiled packages. When other users open your Macros Enabled file, Excel will automatically run the compiled machine code instead of the readable source code. Due to different interfaces (I guess it is called interface?) for different libraries in machine codes, Excel cannot find the library and error occurs.

So you can also try to decompile your VBA code before you distribute your XLSM files. Visit this page to download the tool for decompiling your XLSM file: VBA Decompiler