PDA

View Full Version : [SOLVED] Is There a "Find" for the VBA Macro List?



Cyberdude
03-30-2005, 08:17 PM
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
03-30-2005, 08:21 PM
Where are the macros you want to search through? Is it one workbook, or several workbooks in a certain folder?

Cyberdude
03-31-2005, 11:04 AM
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
03-31-2005, 11:38 AM
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
03-31-2005, 11:45 AM
JKwan, do I smell a KBase entry?? ;)

JKwan
03-31-2005, 11:48 AM
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
03-31-2005, 11:56 AM
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. :yes

Cyberdude
03-31-2005, 04:16 PM
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
03-31-2005, 05:17 PM
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
04-01-2005, 02:42 PM
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.