Last Updated 4 Jan 2019
This article was originally written as a reply to this thread Check if function or sub exists at Access World Forums
The original version of the code on the forum was:
CODE:
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
At first, this seemed perfect for my needs.
The idea is that error 35 is triggered if the procedure is not found in a module and the function returns false.
If the procedure is found, error 0 is triggered and the code returns true.
At least that's the theory!
For some reason, it worked fine for 24 hours then stopped working returning error 35 even when the procedure exists.
Oddly, creating a fresh copy of the database caused the code to work again ... for a while before stopping again.
It seems the code wasn't properly reset after each loop
I changed the code as follows after a bit of experimenting. The new version now works perfectly for me.
This searches for & counts the number of lines in the specified procedure.
CODE:
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:
CODE:
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
Return to Code Samples Page
Return to Top