Share via

how do I get Microsoft VBA Extensibility 5.3 library for use in Excel 2010 VBA?

Anonymous
2012-07-02T16:24:31+00:00

I want to create some simple utilities to show information, starting from VBPROJECTS. When I try to use that collection in a VBA procedure, an error message appears that I do not have the VBIDE library. My research tells me only that VBIDE is in Microsoft VBA Extensibility 5.3 library. Where can I get that from and how do I install it in Excel?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2012-07-03T01:46:24+00:00

    The following code lists the Modules and Subs in a worksheet. Note the comment re editing the sheet name for output.

    The arguments for the subs will be in the sub names as listed.

    Note: As well as requiring the Microsoft Visual Basic for Applications Extensibility 5.3 you also  need to do the following.

    On a worksheet select File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings and then check the box against Trust access to the VBA Project Object Model.

    Error in comment re alternative code for processing another workbook corrected since original post. Workbook should be xlsm type; not xlsx.

    Private Sub ListModulesAndSubs()

        Dim wb As Workbook

        Dim vbComp As VBComponent

        Dim wsList As Worksheet

        Dim i As Long

        Dim strCodeLine As String

        Dim strProcBodyLine As String

        Dim strModule As String

        Set wb = ThisWorkbook

        'Alternative code to previous line if workbook _

         being processed is not ThisWorkbook containing the code

        'Set wb = Workbooks("My Other Workbook.xlsm")

        Set wsList = wb.Sheets("Sheet3")    'Edit "Sheet3" to required sheet for output.

        wsList.Range("A1") = "Module"

        wsList.Range("B1") = "Sub Routine"

        wsList.Range("A1:B1").Font.Bold = True

        For Each vbComp In wb.VBProject.VBComponents

            With vbComp.CodeModule

                For i = .CountOfDeclarationLines + 1 To .CountOfLines

                    If Trim(.Lines(i, 1)) <> "" Then

                        If strProcBodyLine <> .Lines _

                                (.ProcBodyLine(.ProcOfLine(i, _

                                vbext_pk_Proc), vbext_pk_Proc), 1) Then

                            strModule = vbComp.CodeModule

                            strCodeLine = .Lines(i, 1)

                            With wsList

                                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) _

                                    = strModule

                                .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) _

                                    = strCodeLine

                            End With

                            strProcBodyLine = .Lines(.ProcBodyLine(.ProcOfLine(i, _

                                        vbext_pk_Proc), vbext_pk_Proc), 1)

                        End If

                    End If

                Next i

            End With

        Next vbComp

        wsList.Columns("A:B").Columns.AutoFit

    End Sub

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments No comments

13 additional answers

Sort by: Most helpful
  1. Anonymous
    2012-07-05T01:13:53+00:00

    I want to create some simple utilities to show information, starting from VBPROJECTS. When I try to use that collection in a VBA procedure, an error message appears that I do not have the VBIDE library. My research tells me only that VBIDE is in Microsoft VBA Extensibility 5.3 library. Where can I get that from and how do I install it in Excel?

    Instead of creating your own utilities, you might want to consider the "XML Documentation" and other features that are part of the free Excel add-in MZ-Tools 3.0, available at www.mztools.com.

    -  Mike Middleton, www.MikeMiddleton.com

    Thanks for the information, but fortunately I have already got what I wanted as a result of OssieMac's very detailed information.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2012-07-03T19:54:48+00:00

    Dear OssieMac,

    A supplementary question: how do I apply this technique to cover all (or selected) VBprojects that appear in the Project Explorer pane?

    When I started this the other day, I found out how to list all those projects by name but could not get the module details. Your answer provided me with the details, but meanwhile I had forgotten how to cover the projects themselves! Could you possibly fill in the blanks in the following approximate code snippet for me, please:

    dim proj as <>

    For each proj in <.>VBProjects

        call ListModulesAndSubs(proj)

    next proj

    I myself can make any necessary adjustments to the code of ListModulesAndSubs().

    Thanks again for your help.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2012-07-03T00:27:06+00:00

    This has helped me get started, but I am too new at this game to work out where to go from here.

     My object is to create an index of all the procs in my own projects so that I can:

    1. document them all
    2. access/edit any of them without having to search everywhere to find a particular name.

    What I want to do, in essence, is to print/store:

    For each project

       name/filename

       For each module

          number/sheet

          For each proc -

             name

             type

             arguments

    I think this information must be related to the VBIDE library (?), but I do not know where VBIDE stands in the object/class hierarchy.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2012-07-02T16:29:38+00:00

    Was this answer helpful?

    0 comments No comments