[Last Call] Learn how to a build a cloud-first strategyRegister Now


vba references/libraries

Posted on 2007-08-08
Medium Priority
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
  • 3
  • 2
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 19660320
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

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

Expert Comment

by:Curt Lindstrom
ID: 19667085
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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

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

Accepted Solution

Curt Lindstrom earned 500 total points
ID: 19674120
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,

Expert Comment

ID: 20209496
Forced accept.

EE Admin

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Six Sigma Control Plans

834 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