Is There a "Find" for the VBA Macro List?

[Cyberdude]

I could REALLY make good use of a "Find" command that could locate a macro name in the list of macros. Since they are in alphabeteical order, why not just look up the name, you query? Regretably I oft times forget what I called the bloody thing, except maybe it contains the string "Cre8" somewhere in its name. I'd write a Find macro if I had the
VBA skills to process the macro list. Presumably, I would have to write code to actually create a list suitable for VBA processing. But I have no idea where they keep that info.

[Jacob Hilderbrand]

Where are the macros you want to search through? Is it one workbook, or several workbooks in a certain folder?

[Cyberdude]

The list is the one that I see when I click on TOOLS->MACRO->MACROS. For example, I have a workbook named Test.xls that's got a LOT of macros, and when the list of those macros is combined with the one for Personal.xls, it is quite lengthy. It's unlikely that I could search that list, so I would probably have to construct a list of my own, in much the same way you can create a list of all the sheet names in a workbook. I just have no idea how to use VBA to retrieve those macro names. They have to be stored somewhere. If I could retrieve the list, then I could write a FIND macro to search it.

[JKwan]

Try this, put this in a module and run "GetVbProj". This will give you a list of procedures.

Option Explicit
'Need the declaration for the following constants or
 'check the reference to Microfost Visual Basic for Applications Extensbility x.xx
Const vbext_pp_none As Long = 0
Const vbext_pk_Proc As Long = 0
Dim x As Long
Dim aList()

Sub GetVbProj()
Dim oVBC As Object
    Dim Wb As Workbook
    x = 2
    For Each Wb In Workbooks
        For Each oVBC In Workbooks(Wb.Name).VBProject.VBComponents
            If Workbooks(Wb.Name).VBProject.Protection = vbext_pp_none Then
                Call GetCodeRoutines(Wb.Name, oVBC.Name)
            End If
        Next
    Next
    With Sheets.Add
        .[a1].Resize(, 3).Value = Array("Workbook", "Module", "Procedure")
        .[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
        Application.Transpose(aList)
        .Columns("A:C").Columns.AutoFit
    End With
End Sub

Private Sub GetCodeRoutines(wbk As String, VBComp As String)
    Dim VBCodeMod As Object
    Dim StartLine As Long
On Error Resume Next
    Set VBCodeMod = Workbooks(wbk).VBProject.VBComponents(VBComp ).CodeModule
    With VBCodeMod
        StartLine = .CountOfDeclarationLines + 1
        Do Until StartLine >= .CountOfLines
            ReDim Preserve aList(1 To 3, 1 To x - 1)
            aList(1, x - 1) = wbk
            aList(2, x - 1) = VBComp
            aList(3, x - 1) = .ProcOfLine(StartLine, vbext_pk_Proc)
            x = x + 1
            StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
            vbext_pk_Proc), vbext_pk_Proc)
            If Err Then Exit Sub
        Loop
    End With
    Set VBCodeMod = Nothing
End Sub

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
    i = 1
    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

Sub Add_External_Reference_()
     ' Need to Referece - Microsoft Visual Basic for Applications Extensibility 5.3
Dim rVBReference As VBIDE.Reference
    Dim wbBook As Workbook
'The GUID to Microsoft Scripting Runtime.
    Const stGuid As String = "{420B2830-E718-11CF-893D-00A0C9054228}"
    Const stName As String = "MS Scripting Runtime"
Set wbBook = ThisWorkbook
On Error GoTo Error_Handling
With wbBook
         'Iterate through the collection of active external references in
         'the VB-project.
        For Each rVBReference In .VBProject.References
            If rVBReference.GUID = stGuid Then
                MsgBox "The library of " & stName & " is already active!", _
                vbInformation
                GoTo ExitHere
            End If
        Next rVBReference
         'Create the external reference in the VB-project.
        .VBProject.References.AddFromGuid stGuid, 1, 0
        MsgBox "The reference to " & stName & " is created!", vbInformation
        GoTo ExitHere
    End With
ExitHere:
    Set rVBReference = Nothing
    Exit Sub
Error_Handling:
    MsgBox "Unable to create the reference as " & stName & vbCrLf _
    & " is not available on this computer.", vbCritical
    Resume ExitHere
End Sub
 
 'Delete an active external references
Sub Delete_Reference()
     ' Need to Referece - Microsoft Visual Basic for Applications Extensibility 5.3
    Dim wbBook As Workbook
'The easiest way is to use the description of the library.
    Const stDescription As String = "Scripting"
Set wbBook = ThisWorkbook
On Error GoTo Error_Handling
With wbBook.VBProject.References
         'Delete the reference.
        .Remove .Item(stDescription)
    End With
MsgBox "The reference is removed!", vbInformation
ExitHere:
    Exit Sub
Error_Handling:
    MsgBox "The reference does not exist!", vbInformation
    Resume ExitHere
End Sub

[Zack Barresse]

JKwan, do I smell a KBase entry??

[JKwan]

I guess I could do it. However, I cannot take credit for it. I found it in somewhere in the WWW - mine you very handy. I just hate to take credit where it is not my own.

Let me know.

[Zack Barresse]

That's ok. Everybody starts somewhere. Half my stuff originated as somebody else's work (which is how I learned). It would be great if available in the KBase though. Looks very handy.

[Cyberdude]

Hey, JKwan, looks like great stuff! I just copied it to my test module, now it will take me a bit of time to study it. Thanx again.

[johnske]

Hi Cyber,

You could also try this - it lists the names of all procedures in the active workbook (you need to set a reference to Microsoft Visual Basic For Applications Extensibility 5.3 for this as well):

Sub ListOfMacros()
Dim N&, Count&, MyList(200), List$
Dim Component As VBComponent
For Each Component In ActiveWorkbook.VBProject.VBComponents
With Component.CodeModule
Count = .CountOfDeclarationLines + 1
Do Until Count >= .CountOfLines
MyList(N) = .ProcOfLine(Count, vbext_pk_Proc)
Count = Count + .ProcCountLines _
(.ProcOfLine(Count, vbext_pk_Proc), vbext_pk_Proc)
Debug.Print MyList(N)
List = List & vbCr & MyList(N)
If Count < .CountOfLines Then N = N + 1
Loop
End With
N = N + 1
Next
MsgBox List, , "List of Macros"
End Sub

[Cyberdude]

Hi, johnske!
I took JKwan's code and emblished it for my own taste and came up with a really nice solution to 1) list all the procedure names in the active workbook (excluding Personal), and 2) to find the one I'm looking for using a typical FIND scan that I wrote. Now you offer me another set of code to study, and I'm gonna do it. Fascinating stuff (to me). Thanx to both of you for the help.