Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

 

Function ProcedureExists(ProcedureName As String) As Boolean

   Dim m As Module, mo As Modules, i As Integer, p As Integer

   ProcedureExists = True

   On Error Resume Next

   

   Set mo = Application.Modules

   For i = 0 To mo.Count - 1

       p = mo(i).ProcBodyLine(ProcedureName, vbext_pk_Proc)

       If Err.Number <> 35 Then

           Exit Function

       End If

   Next

   ProcedureExists = False

End Function

Code Samples for Businesses, Schools & Personal Use

Check Procedure Exists

 

Public Function CheckProcedureExists(ProcName As String) As Boolean

 

   Dim m As Module, mo As Modules, p As Long, q As Long

 

   'next line acts as late binding so no need for reference Microsoft Visual Basic for Applications Extensibility 5.3

   Const vbext_pk_Proc = 0

   

   CheckProcedureExists = True

   

   On Error Resume Next

   

   Set mo = Application.Modules

   For q = 0 To mo.Count - 1

       p = mo(q).ProcCountLines(ProcName, vbext_pk_Proc)

       

       If p > 0 Then 'procedure exists

          ' Debug.Print ProcName, mo(q).Name, p

           Exit Function

       End If

   Next

   

   CheckProcedureExists = False

   

End Function

Typical usage:

CheckProcedureExists("GetCurrentUser")

 

I prefer this method anyway as

1. it works consistently ... at least for me

2. its not relying on an error being triggered

 

 

A similar method can be used to determine if a procedure exists within a specified module:

Return to Code Samples Page

 

Public Function DoesProcExist(ByVal ModuleName As String, ByVal ProcName As String) As Boolean

 

    Dim ProcStart  As Long

   

   'next line acts as late binding so no need for reference Microsoft Visual Basic for Applications Extensibility 5.3

    Const vbext_pk_Proc = 0

 

    On Error GoTo Err_Handler

 

    ProcStart = Application.VBE.ActiveVBProject.VBComponents(ModuleName)._

            CodeModule.ProcStartLine(ProcName, vbext_pk_Proc)

    DoesProcExist = True

 

Exit_Handler:

   On Error Resume Next

   Exit Function

 

Err_Handler:

   Resume Exit_Handler

   

End Function

Typical usage:

DoesProcExist("modFunctions","GetCurrentUser")

DoesProcExist("Form_frmMain","Form_Open")

 

 

 

NOTE:

The code line Const vbext_pk_Proc = 0 is NOT required in either function if you are using the VBA reference

Microsoft Visual Basic for Applications Extensibility 5.3