Missing Reference Error - Fix Not Working For Other Users

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
138
Hi All,

I'm hoping someone can help me with (what I think is) the final piece of a VBA puzzle.

I created a "tool" in Excel which gets sent to >150 users. It's makes fairly extensive use of VBA, including a couple of Forms.

Recently, after having made no updates to the Excel Workbook or to the VBA it contains (i.e. nothing changed in the tool), my users have started to tell me they are getting error messages. The error message is:

"Compile error in hidden module: ThisWorkbook. This error commonly occurs when code is incompatible with the version of, platform, or architecture of this application. "Click "Help"...".

After some digging, I discovered that (including on my own version of the tool), if you go into the VBA Editor > Tools > References there was one which started with "MISSING". I'm reasonably familiar with this issue (although not with all the very specific details), so I simply unchecked that missing reference, and everything worked fine for me. Having done a LOT of additional error-checking and investigation I'm now certain that the issue is 100% related to this reference, all other aspects of the Workbook and the VBA had been working flawlessly for years, and since removing this MISSING reference, everything else is working too.

NB - I'm also pretty certain that the MISSING reference was related to a Calendar (Date Picker) I had added to one of the Forms in the tool. That was causing some problems - so I removed that (and all references to it within the VBA), replacing that with some basic native Excel Data Validation. Again, I'm confident that the removal of that Date Picker and the references to it was successful. It's just this left-over VBA reference causing the problem.

I then edited my "Master" version of the tool, which gets sent to the >150 users (unchecking that missing reference), saved it all and then emailed it out.

However, this did NOT fix the issue. Despite unchecking the missing reference, saving and sending out, all my users are reporting that this "fix" has not worked.

Is there a way of fixing a MISSING Reference centrally (i.e. by me) and then the issue be fixed for all the recipients of that updated Workbook?

I hope this is clear - any questions, just let me know and I'll endeavour to answer.

Many thanks,

AP
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
If the error still relates to a missing reference, surely your users can tell you which one is shown as missing in their reference list? I would perhaps try to find a user willing to help you debug it live, on their system.

And if you do find you need to adjust another reference, I don’t think there’s any way to distribute that to your users without sending another version. That setting is buried in the workbook file, which itself is either a binary file (xlsb) or zipfile (xlsm), and changing it programmatically would require getting users to run another program to do it, and would be far more trouble than sending another version. It can’t be done with VBA, to my knowledge.

Have you explored removing references and defining your VBA objects using late binding? At least in that case, if it broke, you’d know exactly what line broke it.
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,814
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi,
Try this code in ThisWorkbook module:
VBA Code:
' Code in ThisWorkbook module
Private Sub Workbook_Open()
  Dim Ref
  Dim s As String
  On Error Resume Next
  For Each Ref In Me.VBProject.References
    If Ref.IsBroken Then Me.VBProject.References.Remove Ref
    If Err Then
      s = s & Ref.Description & vbLf
      Err.Clear
    End If
  Next
  If Len(s) > 0 Then MsgBox "Unable To Remove Missing Reference(s):" & vbLf & s, vbCritical, Me.Name
End Sub
 

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
138
Hi Dermot and ZVI,

Thank you both very much indeed for taking the time to read my query and responding - it's greatly appreciated.

@Dermot - I hear what you're saying, thanks again; I am able to determine the exact reference which is "MISSING", and I have already tried creating another version (with the necessary corrective action included). However, it seems, despite it being a whole new update (albeit created via "Save As..." vs re-creating the entire Workbook from scratch), the fix does not work for others. Only when I take control of their machine remotely, enter the VBA Editor and manually uncheck the MISSING reference does the fix stick. Very odd. I'm not familiar with late binding - so I'll look into that one.

@ZVI - really appreciate the code. I shall take this, and see if I can get it to work on the next person who raises the query of the error appearing.

Thanks again both - very grateful,

AP
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top