VBA - Function to check if a particular Macro exists

[Chris The Rock]

I'd like to loop through my workbook's modules and check to see if a macro exists. I'll have the name of the macro in a string variable (in the format MODULE.MACRO), and before I try to execute that macro, I'd like to validate that it exists.

A function that returns TRUE when the macro can be found is what I'm looking for.

Anyone done this before? Thanks in advance.

 

[BrianB]

This is the code to do the job :-

'=================================================================================
'- CHECK IF A MODULE & SUBROUTINE EXISTS
'- VBA constant : vbext_pk_Proc = All procedures other than property procedures.
'- An error is generated if the Module or Sub() does not exist - so we trap them.
'---------------------------------------------------------------------------------
'- VB Editor : Tools/References - add reference TO ......
'-    .... "Microsoft Visual Basic For Applications Extensibility"
'----------------------------------------------------------------------------------
'- Brian Baulsom October 2007
'==================================================================================
Sub MacroExists()
    Dim MyModule As Object
    Dim MyModuleName As String
    Dim MySub As String
    Dim MyLine As Long
    '---------------------------------------------------------------------------
    '- test data
    MyModuleName = "TestModule"
    MySub = "Number2"
    '----------------------------------------------------------------------------
    On Error Resume Next
    '- MODULE
    Set MyModule = ActiveWorkbook.VBProject.vbComponents(MyModuleName).CodeModule
    If Err.Number <> 0 Then
        MsgBox ("Module : " & MyModuleName & vbCr & "does not exist.")
        Exit Sub
    End If
    '-----------------------------------------------------------------------------
    '- SUBROUTINE
    '- find first line of subroutine (or error)
    MyLine = MyModule.ProcStartLine(MySub, vbext_pk_Proc)
    If Err.Number <> 0 Then
        MsgBox ("Module exists      : " & MyModuleName & vbCr _
               & "Sub " & MySub & "( )  : does not exist.")
    Else
        MsgBox ("Module : " & MyModuleName & vbCr _
            & "Subroutine   : " & MySub & vbCr _
            & "Line Number : " & MyLine)
    End If
End Sub
'-----------------------------------------------------------------------------------

You may be interested in my code here too :-
http://www.mrexcel.com/board2/viewtopic.php?p=1393196#1393196

 

[Chris The Rock]

Sweet! Thanks!