Missing Reference Error - Fix Not Working For Other Users

[AndyPandy27]

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

 

[Dermot]

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]

Hi,
Try this code in ThisWorkbook module:

' 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]

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