locked
References Library error when code run on different versions of Office RRS feed

  • Question

  • I have a set-up where an Excel file with macros enabled and which has references to MS Word is located on a computer running Windows 10 and Office 2013.  It is frequently accessed, changed and saved from another networked computer running Windows 10 and Office 2016.  When it has been changed on the new Office 2016 computer and saved back to the Office 2013 computer and we attempt to open it again on the older, Office 2013 computer, if we try to run a procedure that calls MS Word we get error message "Compile Error.  Can't find project or library".  That can be cleared by resetting the References via the VBE, Tools, References to the references appropriate to Office 2016.

    In writing the procedures I had thought that I would avoid this problem by using Late Binding, but apparently not.  All my object variables are defined as “object”, and I use “Create Object” at the appropriate point.

    I am now therefore trying to write a procedure to find broken references when the file is first opened and to change them to the references appropriate to the computer being used.  I have found in “Excel 2007 VBA Programming with XML and ASP” by Julilitta Korol, the following code that is supposed to highlight the broken links.  However, when run on the older Office 2013 version after saving on the Office 2016 computer, it again produces the same “Compile Error. ...“ message.  I note that it uses Early Binding again, but as an amateur I am rapidly approaching the limits of my coding skill, and I was not able to adapt it to Late Binding.

    Private Sub Workbook_Open()

       Dim objVBProj As VBProject

       Dim objRef As Reference

       Dim refBroken As Boolean

       Set objVBProj = ThisWorkbook.VBProject

       ' Loop through the selected references in

       ' the References dialog box

       For Each objRef In objVBProj.References

       ' If the reference is broken, get its name and its GUID

          If objRef.IsBroken Then

             Debug.Print objRef.Name

             Debug.Print objRef.GUID

             refBroken = True

          End If

       Next

       If refBroken = False Then

            Debug.Print "All references are valid."

       End If

    End Sub

    I would be very grateful of any comment on whether my proposed solution is a sensible one, how I might get the procedure to work, or for any other suggestion on how I might go about sorting this out.

    Andy C

    Wednesday, May 4, 2016 4:06 PM

Answers

  • Have a look at the link below on Early binding and Late binding.

    What you need to do is dimension the variables as simply objects and don't set the references as in Tools -> References and then you will not have a problem. You will find that constants used with the called application will need to be set to their numeric value.

    The problem arises when the references are set to a version of an Office application and then the code is run on a machine with an earlier version of the Office application. If Late binding is used then the version of Office is unaffected.

    I usually create the initial code using the References and Early Binding because the intellisense kicks in. However, after the development is finished, I edit the code and make it late binding and remove the references.

    If you can't get your code to work with late binding then post the code you have to call Word and I will have a look at it for you.

    https://support.microsoft.com/en-us/kb/245115


    Regards, OssieMac

    • Marked as answer by AndyColRomsey Thursday, May 5, 2016 4:30 PM
    Thursday, May 5, 2016 10:47 AM

All replies

  • Have a look at the link below on Early binding and Late binding.

    What you need to do is dimension the variables as simply objects and don't set the references as in Tools -> References and then you will not have a problem. You will find that constants used with the called application will need to be set to their numeric value.

    The problem arises when the references are set to a version of an Office application and then the code is run on a machine with an earlier version of the Office application. If Late binding is used then the version of Office is unaffected.

    I usually create the initial code using the References and Early Binding because the intellisense kicks in. However, after the development is finished, I edit the code and make it late binding and remove the references.

    If you can't get your code to work with late binding then post the code you have to call Word and I will have a look at it for you.

    https://support.microsoft.com/en-us/kb/245115


    Regards, OssieMac

    • Marked as answer by AndyColRomsey Thursday, May 5, 2016 4:30 PM
    Thursday, May 5, 2016 10:47 AM
  • OssieMac

    Thanks for looking at this.  I had set the references, even though I had used late binding.  I will remove the  references, and the go through to set the constants to numeric values.  I will get back to you, hopefully to report success.

    Andy C

    Thursday, May 5, 2016 11:17 AM
  • OssieMac

    Your advice worked to make the procedure work exactly as required on both the Office 2013 and the Office 2016 computers.  Thanks for that, and particularly for the link, which I had not seen before.  I have read several  similar, fairly comprehensive descriptions of binding, but none has mentioned the fact that the references should NOT be included, nor the advice that constants need to be set to the numeric values.  I hope that the title of this query will be sufficient to bring your advice to the attention of others with similar problems.

    With thanks  Andy C

    Thursday, May 5, 2016 4:30 PM