Check if function or sub exists

GanzPopp · Feb 13, 2013

Hi there,

The following code will check whether a given procedure, function or sub exists in one of your VB projects modules:

Required reference:
-Microsoft Visual Basic for Applications Extensibility

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

There are most likely other ways of doing this, but for me this sufficed.

AndrewS · Aug 4, 2017

I struggled to get this to work.
It would find procedures in the first module only.

Checking the error number on each pass through the for-next loop, revealed that Err.Number was not resetting on each pass, so that when it found a match, Err.Number was still 35.

Amending the code to reset Err.Number as below has made it work correctly.

I'm now using Access 2016: maybe the "stickiness" of error numbers is a new "feature" since GanzPopp posted the above?

Code:

Function ProcedureExists(strProcName 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
      Err.Number = 0
      p = mo(i).ProcBodyLine(strProcName, vbext_pk_Proc)
      If Err.Number <> 35 Then
         Exit Function
      End If
    Next
    ProcedureExists = False

End Function

isladogs · Oct 22, 2017

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.

Code:

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 
            Exit Function
        End If
    Next
    
    CheckProcedureExists = False
    
End Function

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

Guus2005 · Oct 26, 2017

I created this some time ago. A module with a few functions and procedures to search, sort and beautify code.

https://access-programmers.co.uk/forums/showthread.php?t=210180

HTH

isladogs · Jun 9, 2018

Post #4 has been hiding for several months! Just discovered and approved.
Recommend reporting your own posts to moderated areas to significantly speed things up

Check if function or sub exists

GanzPopp

Registered User.

AndrewS

Registered User.

isladogs

MVP / VIP

Guus2005

AWF VIP

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread