Prevent from importing missing references

Working as VBA developer brings a lot of different issues. One of the obstacles is the Office version. Apart of various behaviours of the same thing, there always will be something new, which was not avaiable in the previous version. In this article I’m going to show You how to prevent from importing missing references.

If You are using macro made on the different Office version or different working station (regional settings, but it is another story) there is a possibility, that You will meet sometimes compile error Can’t find project or library.

What is most important in such situation is to stay calm, don’t freak out. There is some reference missing.

You can fix this by yourself and there are several options to solve the problem.

But lets start with a little background story.

Background story

I created tool in Office 365, which was using PowerPoint library. So when it was opened on Office 2013 there was Microsoft PowerPoint 16.0 Object Library missing. Thanks to that I could create specified PowerPoint objects in my code.

Dim PowerPointApp As PowerPoint.Application
Dim draft As PowerPoint.Presentation
Dim slide As PowerPoint.slide

Remove missing references manually

First of all, You need to go to the Reference window (in VBE: Options/References).

Uncheck the missing reference and find the actual one. In this case it was Microsoft PowerPoint 15.0 Object Library.

After that click OK and it is done. Problem solved.

Remember to first uncheck the MISSING reference and then check the actual for the current Office version. In other case after clicking the OK button You will get the warning about name conflict and your changes in the reference window will still be not applied.

Remove missing references automatically

The second solution (by Rik Sportel) I found on my favourite online community for developers. The code is simply all about looping through checked references and if any is broken (missing), uncheck it.

Option Explicit
Sub RemoveReferences()
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As VBIDE.Reference

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject

    For Each chkRef In vbProj.References
        If chkRef.IsBroken Then
            vbProj.References.Remove chkRef
        End If
    Next

    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub

But…

If You launch this You’ll get an error.

To get rid of this error You need to check Trust Access to the VBA Project object model (File/Options/Trust Center/Trust Center Setttings/Macro Settings/Trust Access to the VBA Project object model).

First go to File/Options/Trust Center/.

Then Trust Center Settings…/Macro Settings/ and check Trust Access to the VBA Project object model.

Unfortunately having it checked all the time is not the safest idea and also for this reason your company can block this setting.

Additionally that code was only removing missing reference , not adding. For this I also found the solution, by Siddharth Rout.

This automatic approach requires lots of code and in my opinion is not the safest solution.

Fortunately there is another solution, which is my favourite one.

Use late binding

In the end I figure out, that the best solution will be to cut off from any additional library, which can be out of date on another work station.

To implement this solution in my code I had to go from Early Binding:

Dim PowerPointApp As PowerPoint.Application
Dim draft As PowerPoint.Presentation
Dim slide As PowerPoint.slide

To the Late Binding:

Dim PowerPointApp As Object
Dim draft As Object
Dim slide As Object

That last solution fixed all the problems in the easiest and fastest way.

To sum up

If You are creating the tool, which will be used on multiple workstations on different Office versions, use Late Binding to avoid such issues. This will prevent your code from importing missing references on other computers. It is a little bit slower than Early Binding and harder to work with (Excel can’t work out what is the Object type, so IntelliSense can’t provide tips), but solved all the problems of missing references.