[Rob Bovey]

"Charles Williams" wrote in message
...
I am looking for a good way of building an array of the Macros that a user
sees when they press Tools--Macros.

Hi Charles,

The functions below provide a quick and dirty method of doing this. Note
that I fail to check whether the code module has the Option Private Module
switch, as this was created for users who weren't going to be that
sophisticated, but that's an easy check to add.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''
''' Comments: Creates a list of all public subroutines located in the
''' specified workbook.
'''
''' Arguments: wkbTarget [in] The workbook whose macros we need to
''' enumerate.
''' aszMacroList() [out] A string array to be loaded with a
list
''' of public subroutines contained in
wkbTarget.
'''
''' Returns: Boolean True if any Public subs were located, False
''' otherwise.
'''
''' Date Developer Action
''' ------------------------------------------------------------------------
--
''' 02/18/03 Rob Bovey Created
'''
Public Function bEnumerateMacros(ByRef wkbTarget As Excel.Workbook, _
ByRef aszMacroList() As String) As Boolean
Dim lIndex As Long
Dim lCodeLine As Long
Dim szProcName As String
Dim szFirstLine As String
Dim modCurrentModule As VBIDE.CodeModule
Dim cmpComponent As VBIDE.VBComponent
Dim cmpComponents As VBIDE.VBComponents
lIndex = -1
Set cmpComponents = wkbTarget.VBProject.VBComponents
For Each cmpComponent In cmpComponents
''' We only care about standard modules.
If cmpComponent.Type = vbext_ct_StdModule Then
Set modCurrentModule = cmpComponent.CodeModule
For lCodeLine = 1 To modCurrentModule.CountOfLines
szProcName = vbNullString
''' Locate any subroutines in this module.
szProcName = modCurrentModule.ProcOfLine(lCodeLine,
vbext_pk_Proc)
If Len(szProcName) 0 Then
''' If this is a public subroutine with no arguments,
add it to the array.
lCodeLine = modCurrentModule.ProcBodyLine(szProcName,
vbext_pk_Proc)
szFirstLine = modCurrentModule.Lines(lCodeLine, 1)
If IsValidSubroutine(szFirstLine) Then
lIndex = lIndex + 1
ReDim Preserve aszMacroList(0 To lIndex)
aszMacroList(lIndex) = szProcName
End If
''' Skip to next procedure
lCodeLine = lCodeLine +
modCurrentModule.ProcCountLines(szProcName, vbext_pk_Proc) - 1
End If
Next lCodeLine
End If
Next cmpComponent
bEnumerateMacros = (lIndex -1)
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''
''' Comments: Determines if the specified procedure is a public subroutine
''' with no arguments.
'''
''' Arguments: szFirstLine [in] The first line of the subroutine to
check.
'''
''' Returns: Boolean True if the specified code line contains a
''' valid public sub, False otherwise.
'''
''' Date Developer Action
''' ------------------------------------------------------------------------
--
''' 02/18/03 Rob Bovey Created
'''
Private Function IsValidSubroutine(ByRef szFirstLine As String) As Boolean
Dim lOpenParen As Long
Dim lCloseParen As Long
''' Make sure the procedure is a public subroutine.
If szFirstLine Like "Sub *" Or szFirstLine Like "Public Sub *" Or _
szFirstLine Like "Static Sub *" Or szFirstLine Like "Public Static
Sub *" Then
''' Make sure the subroutine has no arguments.
lOpenParen = InStr(szFirstLine, ")")
lCloseParen = InStrRev(szFirstLine, "(")
IsValidSubroutine = (lOpenParen + 1 = lCloseParen)
End If
End Function

[Charles Williams]

Hi Rob,

Thanks that looks great .


Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Rob Bovey" wrote in message
...
"Charles Williams" wrote in message
...
I am looking for a good way of building an array of the Macros that a
user
sees when they press Tools--Macros.

Hi Charles,

The functions below provide a quick and dirty method of doing this.
Note
that I fail to check whether the code module has the Option Private Module
switch, as this was created for users who weren't going to be that
sophisticated, but that's an easy check to add.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *



'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''
''' Comments: Creates a list of all public subroutines located in the
''' specified workbook.
'''
''' Arguments: wkbTarget [in] The workbook whose macros we need to
''' enumerate.
''' aszMacroList() [out] A string array to be loaded with a
list
''' of public subroutines contained in
wkbTarget.
'''
''' Returns: Boolean True if any Public subs were located,
False
''' otherwise.
'''
''' Date Developer Action

''' ------------------------------------------------------------------------
--
''' 02/18/03 Rob Bovey Created
'''
Public Function bEnumerateMacros(ByRef wkbTarget As Excel.Workbook, _
ByRef aszMacroList() As String) As Boolean
Dim lIndex As Long
Dim lCodeLine As Long
Dim szProcName As String
Dim szFirstLine As String
Dim modCurrentModule As VBIDE.CodeModule
Dim cmpComponent As VBIDE.VBComponent
Dim cmpComponents As VBIDE.VBComponents
lIndex = -1
Set cmpComponents = wkbTarget.VBProject.VBComponents
For Each cmpComponent In cmpComponents
''' We only care about standard modules.
If cmpComponent.Type = vbext_ct_StdModule Then
Set modCurrentModule = cmpComponent.CodeModule
For lCodeLine = 1 To modCurrentModule.CountOfLines
szProcName = vbNullString
''' Locate any subroutines in this module.
szProcName = modCurrentModule.ProcOfLine(lCodeLine,
vbext_pk_Proc)
If Len(szProcName) 0 Then
''' If this is a public subroutine with no arguments,
add it to the array.
lCodeLine = modCurrentModule.ProcBodyLine(szProcName,
vbext_pk_Proc)
szFirstLine = modCurrentModule.Lines(lCodeLine, 1)
If IsValidSubroutine(szFirstLine) Then
lIndex = lIndex + 1
ReDim Preserve aszMacroList(0 To lIndex)
aszMacroList(lIndex) = szProcName
End If
''' Skip to next procedure
lCodeLine = lCodeLine +
modCurrentModule.ProcCountLines(szProcName, vbext_pk_Proc) - 1
End If
Next lCodeLine
End If
Next cmpComponent
bEnumerateMacros = (lIndex -1)
End Function


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''
''' Comments: Determines if the specified procedure is a public
subroutine
''' with no arguments.
'''
''' Arguments: szFirstLine [in] The first line of the subroutine to
check.
'''
''' Returns: Boolean True if the specified code line contains a
''' valid public sub, False otherwise.
'''
''' Date Developer Action

''' ------------------------------------------------------------------------
--
''' 02/18/03 Rob Bovey Created
'''
Private Function IsValidSubroutine(ByRef szFirstLine As String) As Boolean
Dim lOpenParen As Long
Dim lCloseParen As Long
''' Make sure the procedure is a public subroutine.
If szFirstLine Like "Sub *" Or szFirstLine Like "Public Sub *" Or _
szFirstLine Like "Static Sub *" Or szFirstLine Like "Public Static
Sub *" Then
''' Make sure the subroutine has no arguments.
lOpenParen = InStr(szFirstLine, ")")
lCloseParen = InStrRev(szFirstLine, "(")
IsValidSubroutine = (lOpenParen + 1 = lCloseParen)
End If
End Function