Removing Missing References from Excel Workbook using VBA

Question

ISSUE:

Is there a way to remove missing references (references that have prefix: "MISSING" mentioned before their name) from an Excel workbook using VBA? I have a macro-enabled Excel workbook which I have to share with my colleagues from time to time. The workbook uses a special add-in that utilizes specific references. However, due to a limited number of licenses, not everyone has that add-in installed in their Excel, which is why the references associated with that add-in show up as "MISSING" in their workbooks and throw compilation errors when they try to run any macro. I don't want my collegues to go poking around the developer tab and uncheck the "MISSING" references each time they get the file. Is there a way this can be automated using VBA?

Issue Screenshot

STEPS TAKEN ALREADY:

• I already tried the following code but it didn't work.

Option Explicit

Sub References_RemoveMissing()

Dim theRef As Variant, i As Long         
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    If theRef.IsBroken = True Then
        ThisWorkbook.VBProject.References.Remove theRef
    End If
Next i

End Sub

When I ran the above code, I got an error on line:

ThisWorkbook.VBProject.References.Remove theRef

The error stated: "Run-time error '-2147319779 (8002801d)': Object library not registered"

Error Screenshot

• Before running the above code, I made sure that "Microsoft Visual Basic for Application Extensibility 5.3" is checkmarked/ticked in my MS Excel reference list and placed above the missing references (which I did by increasing its priority).
• My excel security settings were also set to "Trust Access To Visual Basic Project".

I looked up on google, this seems to be a pretty old issue. I found a bunch of old links suggesting the same coding solution but none of them seem to work for me.

Excel VBA prevent from importing missing references (didn't work for me)

https://support.microsoft.com/en-us/topic/how-to-check-and-remove-incorrect-project-references-in-the-visual-basic-editor-in-word-7ba187a6-9dfd-1288-8f08-d1f01ea02a3f (didn't work for me)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b90ed5cc-6bd1-46b4-bbea-de4a15521b26/detect-and-remove-missing-references-in-vba-code?forum=exceldev (didn't work for me)

POSSIBLE ALTERNATE SOLUTION:

If the missing references cannot be removed using VBA, is it possible to have an external Python-based script embedded within excel VBA that runs and removes the "MISSING" references?

Any help on this would be greatly appreciated! :)