Check if function or sub exists (1 Viewer)

Status
Not open for further replies.

GanzPopp

Registered User
Joined
Jan 14, 2013
Messages
37
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

Registered User
Joined
Feb 21, 2017
Messages
30
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
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
 
Last edited:

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
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
 
Status
Not open for further replies.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom