Before finding this thread, I had searched the internet for a simple function to check if a procedure exists.
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. I've no idea why its flaky for me.
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.
If count>0, the procedure exists and the function returns true.
Public Function CheckProcedureExists(ProcName As String) As Boolean
Dim m As Module, mo As Modules, p As Long, q As Long
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
CheckProcedureExists = False
I prefer this method anyway as
- it works consistently ... at least for me
- its not relying on an error being triggered
NOTE: code requires VBA reference Microsoft Visual Basic for Applications Extensibility