Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2013, 03:21 AM   #1
GanzPopp
Newly Registered User
 
Join Date: Jan 2013
Posts: 37
Thanks: 1
Thanked 15 Times in 14 Posts
GanzPopp is on a distinguished road
Check if function or sub exists

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.

GanzPopp is offline   Reply With Quote
The Following User Says Thank You to GanzPopp For This Useful Post:
AndrewS (08-04-2017)
Old 08-04-2017, 05:12 AM   #2
AndrewS
Newly Registered User
 
Join Date: Feb 2017
Location: UK
Posts: 30
Thanks: 37
Thanked 2 Times in 2 Posts
AndrewS is on a distinguished road
Re: Check if function or sub exists

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
AndrewS is offline   Reply With Quote
Old 10-22-2017, 01:08 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Check if function or sub exists

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-23-2017 at 02:30 AM.
isladogs is offline   Reply With Quote
Old 10-25-2017, 11:13 PM   #4
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,491
Thanks: 44
Thanked 80 Times in 75 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Check if function or sub exists

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/for...d.php?t=210180

HTH
Guus2005 is offline   Reply With Quote
Old 06-09-2018, 12:08 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Check if function or sub exists

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
check if record exists ianking Queries 6 08-18-2008 02:39 PM
Check if value already exists ya5irha55an Forms 2 07-19-2006 03:02 AM
Check if Value Exists Len Boorman Modules & VBA 2 01-19-2005 05:23 AM
Function to check if a table exists? suzie_q Modules & VBA 2 12-13-2002 07:58 AM
How to check that a DAO exists aziz rasul Modules & VBA 4 05-09-2001 02:55 AM




All times are GMT -8. The time now is 07:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World