vba references/libraries

Posted on 2007-08-08
Last Modified: 2012-06-27
i want to loop through all the available references (objects/libraries) in excel vba. Examples are Outlook Object and MS word objects. How do I do that? thanks!
Question by:azcalv408
    LVL 18

    Expert Comment

    by:Curt Lindstrom
    This macro will display the active references

    Sub List_ActiveReferences_VBAProject()
         'Intentionally use of late binding but if we want to
         'use early binding then we set a reference to the
         '"Microsoft Visual Basic for Applications Extensibility 5.3" for 2000 and above
         'For Excel 97 the name is "Microsoft Visual Basic for Applications Extensibility"
        Dim oVBReference As Object
        Dim wbBook As Workbook
        Dim wsSheet As Worksheet
        Dim i As Long
        Set wbBook = ThisWorkbook
        Set wsSheet = wbBook.Worksheets("Sheet1")
        Application.ScreenUpdating = False
        With wsSheet
            .Range("A1:F1").Value = _
            Array("Description", "Name", "GUID", "Major", "Minor", "Path")
            For Each oVBReference In wbBook.VBProject.References
                i = i + 1
                .Cells(i, 1).Value = oVBReference.Description
                .Cells(i, 2).Value = oVBReference.Name
                .Cells(i, 3).Value = oVBReference.GUID
                .Cells(i, 4).Value = oVBReference.Major
                .Cells(i, 5).Value = oVBReference.Minor
                .Cells(i, 6).Value = oVBReference.FullPath
            Next oVBReference
        End With
        Application.ScreenUpdating = True
        Set oVBReference = Nothing
    End Sub


    Author Comment

    those are active references...what about "available references" ? thanks
    LVL 18

    Expert Comment

    by:Curt Lindstrom
    I guess one way to do it would be to select all of the available references and then use the macro to print all the details.


    Author Comment

    is there code to select all available references? thanks
    LVL 18

    Accepted Solution

    You can do it in code like this (showing one reference only

    Sub Make_VBS_Ref_MCSACAL()
        On Error Resume Next
         'in case ref exists
         'Microsoft Calendar Control 11.0
         ActiveWorkbook.VBProject.References.AddFromGuid "{8E27C92E-1264-101C-8A2F-040224009C02}", 7, 0

    'Add all available controls you want to set here in the same way

        On Error GoTo 0
    End Sub

    To find out the GUID, unless you already know all details, you can select all available references in the Visual Basic Editor by selecting Tools>References and tick all available references.

    Best Regards,
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now