• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

vba references/libraries

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!
0
azcalv408
Asked:
azcalv408
  • 3
  • 2
1 Solution
 
Curt LindstromCommented:
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
        .Columns("A:F").EntireColumn.AutoFit
    End With
     
    Application.ScreenUpdating = True
     
    Set oVBReference = Nothing
End Sub

BR,
Curt
0
 
azcalv408Author Commented:
those are active references...what about "available references" ? thanks
0
 
Curt LindstromCommented:
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.

BR,
Curt
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
azcalv408Author Commented:
is there code to select all available references? thanks
0
 
Curt LindstromCommented:
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,
Curt
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now