No announcement yet.

Setting a VBA Library Reference

  • Filter
  • Time
  • Show
Clear All
new posts

  • Setting a VBA Library Reference

    Hi, all.

    I know that I've seen some code here that sets a VBA Library reference, but I didn't have a need for it then.
    As it turns out, now I do need this code.

    We are currently running W2k, and the system I've developed creates new user files based on a template.
    This template was created using wk2, so has the Excel 9 library reference.
    However, when using an XP machine to create a new file, it creates a reference to the Excel 10 library.
    Then, when using the newly created file on a W2K machine, there is a missing library error.

    We are currently rolling out XP to all our users, but this will take place over a period of about 6 months.
    Untill all users are on XP, we will need to force a reference to Excel 9, which is why I need the code.

    Thanks for your help.

  • #2
    I think XL-Dennis posted that code. You could search his posts or U2U him, I am sure he will help.
    Hope that Helps


    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.


    • #3
      I looked thru all the posts from xl-dennis of the last 3 months and didn't see anything.

      Anyway, I've got it partially solved.

      First, I added this code to the template, and run it from the Worksheet_Open event handler
      Public Sub CheckReferences()
          Dim i As Integer
          For i = 1 To ThisWorkbook.VBProject.References.Count
              If ThisWorkbook.VBProject.References(i).IsBroken Then
                  ThisWorkbook.VBProject.References.Remove  _
                  Exit For
              End If
          Next i
          If Application.Version = "9.0" Then
              ThisWorkbook.VBProject.References.AddFromFile _
       "C:\Program Files\Microsoft Office\Office\msoutl9.olb"
              ThisWorkbook.VBProject.References.AddFromFile  _ 
      "C:\Program Files\Microsoft Office\Office10\msoutl.olb"
          End If
      End Sub
      I created the file using W2K, and then opened it using XP, and it worked fine. I saved it from XP, then opened it from W2K. I get an error on the reference remove statement, which says the reference is not registered. However, when the Add statement executes, the error message says 'reference name already in use'.

      Any ideas?


      • #4

        You might want to lok into the code lines:
        If Application.Version = "9.0" Then

        Also this will be :
        If Application.Version = "10.0" Then

        your codes for each procedure

        I recall code is different from XL2000 and ExcelXP

        Kind regards


        BTW XL-Dennis is a very close friend of mine and he is a little busy right now on his Swedish web site, I expect him back in a week or so.


        • #5
          Hi Steve,

          You need to use GUIDs. Have a look at the example workbook - you can list all of the references in the workbook using the routine "Grab_References2" and you can set references using the routine "CreateRef_Library_MicrosoftGraph8" (set up for the Graph 8 object library at the moment but easily amended to suit your requirements).

          Thanks to NateO for the first routine and Ivan for the second.

          Cross-poster? Read this: Cross-posters
          Struggling to use tags (including Code tags)? : Forum tags


          • #6

            This is an internal company application, and we all have identical PCs with identical images on them. We know that if it is not version 9 (W2K) it will be XP.
            But thanks for the tip - if it were an app for use by folks with many different versions, I would have to code for that.

            When the reference on the file is to Outlook 9, and I open the file on XP, the Remove and the .AddFromFile (Outlook10) work fine.

            Once it is saved in XP and you attempt to open it with W2K, the .Remove statement fails, generating a 'Not Registered' error. Then when it gets to the .AddFromFile (Outlook 9) statement, another error occurs stating there is a reference name conflict.
            If I stop and manually remove the missing reference, then resume, the .AddFromFile statment (Outlook 9) works fine.

            It looks like there is no automated way to remove a missing reference.

            By the way, I'm developing using W2K.

            Thanks to both of you for your input.


            • #7
              Here's what I decided to do on this:

              In the before_save event, remove the reference to Outlook.

              In the workbook_open event, add the reference as follows:

              if version = 9.0 add Outlook 9 reference, otherwise add Outlook 10 reference. This will be OK because I know the users have either version 9 (W2K) or version 10 (XP).

              However, there is a new setting in Excel XP on the Macros, Security, Trusted Sources page that doesn't exist in Excel 2000: Trust access to Visual Basic Project. By default (at least on our image) it is unchecked, so the code to add a reference is not allowed. If I check the box, the code above works fine.

              So, the question now is how can I set this checkbox with code so that i don't have to require the users to manually check this box? Any ideas?


              • #8
                Hi all,

                I got an U2U-message from brewguy99 about this thread so here I am :wink1:

                There are two different approaches we can use to establish reference to an external object library, either early binding or late binding.

                The early binding means that we set a reference via the Tools | Reference in the VB-ediitor. This approach works well if we only have to deal with one version of an object library, like Outlook 9.0 or Outlook 10.

                However, by using late binding we create, via code, a reference to the present library no matter which version is in use.

                You may read more about it here:
                INFO: Using Early Binding and Late Binding in Automation

                the drawback with late binding is that it is executed slower compared the early binding however it provide at least a solution

                What I can understand from Your first mail is that the template in use is regular saved and since You have both 2K and the XP-version of XL You get an error due to version-conflict of Outlooks library.

                Below You find the late binding approach:

                Option Explicit

                Sub Late_Binding()
                Dim olObj As Object
                Set olObj = CreateObject("Outlook.Application")

                'Your code here

                Set olObj = Nothing
                End Sub

                Please mail Your present code for working with Outlook objects in order to give You more assistant.

                Kind regards,
                Kind regards,

                .NET & Excel | 2nd edition PED | MVP


                • #9

                  Thank you for your wisdom. By changing all my object references to late binding I was able to totally remove all the added VB Library references.

                  As a note to anyone else, I was using early binding, and used some named constants (olFolderInbox, olMailItem), and with late binding these constants are no longer defined. I just looked at the Outlook Object browser to find out their values, and then defined them as constants within the sub.

                  An added bonus is that the library no longer has to be embedded in the file, which reduces the file size considerably.

                  I also no longer need to include the reference to the VBA Extension library.

                  Thanks again,


                  • #10
                    Youre welcome and Im glad it solved the issue

                    Kind regards,
                    Kind regards,

                    .NET & Excel | 2nd edition PED | MVP