PDA

View Full Version : [SOLVED] Check for presence of VBA Procedure in all modules



vanhunk
07-09-2013, 07:04 AM
Good day,


The following is code provided by Chip Pearson, my problem is how to apply it to determine if a Procedure called "TestProcedure" is present in one of the modules of the workbook:


From Chip Pearson:

Testing If A VBComponent Exists
This code will return True or False indicating whether the VBComponent named by VBCompName exists in the project referenced by VBProj. If VBProj is omitted, the VBProject of the ActiveWorkbook is used.
Public Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean
' This returns True or False indicating whether a VBComponent named VBCompName exists in the VBProject _
referenced by VBProj. If VBProj is omitted, the VBProject of the ActiveWorkbook is used.
Dim VBP As VBIDE.VBProject
If VBProj Is Nothing Then
Set VBP = ActiveWorkbook.VBProject
Else Set VBP = VBProj
End If
On Error Resume Next
VBComponentExists = CBool(Len(VBP.VBComponents(VBCompName).Name))
End Function


I would appreciate any assistance.
I have tried to use it but get "False" even though the procedure does in fact exist!

Thank you

Simon Lloyd
07-09-2013, 07:45 AM
You'll need to read Chips site properly on how to call and use this function, Chips gone to great lengths to have a detailed well laid out explanation.

(your copy and past put everything in to one line so most of the code was commented out, i've adjusted that for you)

mikerickson
07-09-2013, 05:53 PM
This OP code will test if a VBComponent is present.
This will test if a procedure exists in some module of the indicated workbook.



Function ProcedureExists(proceedureName, wb As Workbook) As Boolean
Dim myComp As VBComponent
Dim myModule As CodeModule
Dim procType As Variant

For Each myComp In wb.VBProject.VBComponents
Set myModule = myComp.CodeModule
On Error Resume Next
For Each procType In Array(vbext_pk_Proc, vbext_pk_Get, vbext_pk_Let, vbext_pk_Set)
ProcedureExists = (myModule.ProcStartLine(proceedureName, procType) <> 0) Or ProcedureExists
Next procType
On Error GoTo 0
Next myComp
End Function

snb
07-10-2013, 04:28 AM
or
Function ProcedureExists(procName, wb As Workbook) As Boolean
On Error Resume Next

For Each cp In wb.VBProject.VBComponents
For j = 0 To 3
ProcedureExists = cp.CodeModule.ProcCountLines(procName, j) > 0
If ProcedureExists Then Exit Function
Next
Next
End Function

vanhunk
07-31-2013, 03:13 AM
Thanks Guys, you were a great help as always!!!