Macro Help

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Is it possible to programatically gain access to a list of
macros available in an Excel workbook? I need to populate
a combo box with all available macro names. thanks
 
Matt,

You'll need to add a reference to 'Microsoft Visual Basic for Applications
Extensibility' (VBA: Tools | References)

Private Sub CommandButton1_Click()
Dim vbc As VBComponent, i As Long, j As Long, strProcName As String,
strTemp As String

For Each vbc In ThisWorkbook.VBProject.VBComponents
If vbc.Type = vbext_ct_Document Or vbc.Type = vbext_ct_StdModule
Then
With vbc.CodeModule
i = .CountOfDeclarationLines + 1
Do Until i >= .CountOfLines + 1
strProcName = .ProcOfLine(i, vbext_pk_Proc)
j = i: strTemp = .Lines(j, 1)
Do Until Left(strTemp, 4) = "Sub " Or Left(strTemp, 9) =
"Function "
j = j + 1
strTemp = .Lines(j, 1)
Loop
i = i + .ProcCountLines(strProcName, vbext_pk_Proc)
ComboBox1.AddItem vbc.Name & ": " & Mid(strTemp, 1,
InStr(1, strTemp, "(") - 1)
Loop
End With
End If
Next
End Sub


Rob
 
Back
Top