I was trying to help out in an UA discussion Form/report Document Hasmodule? in which Stuart was trying to find a way to determine if a form or report object had a module.
Now, the obvious answer is to simply use the HasModule property. But no, in this instance that wasn’t a viable option. Stuart needed a way to determine whether a form/report had a module without needing to open the object first as is required by the HasModule property.
A little head scratching later I came up with 3 functions that should accomplish the requested task and do not require opening the object beforehand. Perhaps these can serve others.
My first attempt was to simply iterate through all the VB Components one by one and try to find one with a matching name to the object we are looking for.
'---------------------------------------------------------------------------------------
' Procedure : Obj_HasModule
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if a Form or Report has a VBA code module
' Alternative to HasModule that doesn't require the object to be open
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjName : Name of the Report/Form to check for an associated module
' sObjType : Object Type -> "Form" or "Report"
'
' Usage:
' ~~~~~~
' Obj_HasModule("Employee List","Form")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-03-05 Initial Release
' 2 2018-03-06 Additional check for the Type to avoid confusing
' conflicting standard/class modules
'---------------------------------------------------------------------------------------
Public Function Obj_HasModule(ByVal sObjName As String, sObjType As String) As Boolean
On Error GoTo Error_Handler
Dim VBComp As Object
Dim sSearchFor As String
Select Case sObjType
Case "Form"
sSearchFor = "Form_" & sObjName
Case "Report"
sSearchFor = "Report_" & sObjName
End Select
With Application.VBE
For Each VBComp In .ActiveVBProject.VBComponents
If VBComp.Name = sSearchFor And VBComp.Type = 100 Then
Obj_HasModule = True
Exit For
End If
Next VBComp
End With
Error_Handler_Exit:
On Error Resume Next
If Not VBComp Is Nothing Then Set VBComp = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Obj_HasModule" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
In my second attempt, I wanted to try and improve the performance of iterating over an entire collection and decided to simply attempt to bind to object module and if an error occurred then this meant it didn’t exist.
'---------------------------------------------------------------------------------------
' Procedure : Obj_HasModule2
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if a Form or Report has a VBA code module
' Alternative to HasModule that doesn't require the object to be open
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjName : Name of the Report/Form to check for an associated module
' sObjType : Object Type -> "Form" or "Report"
'
' Usage:
' ~~~~~~
' Obj_HasModule2("Employee List","Form")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-03-05 Initial Release
' 2 2018-03-06 Additional check for the Type to avoid confusing
' conflicting standard/class modules
'---------------------------------------------------------------------------------------
Public Function Obj_HasModule2(ByVal sObjName As String, sObjType As String) As Boolean
On Error GoTo Error_Handler
Dim VBComp As Object
Dim sSearchFor As String
Select Case sObjType
Case "Form"
sSearchFor = "Form_" & sObjName
Case "Report"
sSearchFor = "Report_" & sObjName
End Select
With Application.VBE
Set VBComp = .ActiveVBProject.VBComponents(sSearchFor)
If VBComp.Type = 100 Then
Obj_HasModule2 = True
End If
End With
Error_Handler_Exit:
On Error Resume Next
If Not VBComp Is Nothing Then Set VBComp = Nothing
Exit Function
Error_Handler:
If Err.Number <> 9 Then
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Obj_HasModule2" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function
Out of personal curiosity, my third attempt employed the use of the undocumented SaveAsText to export the object definition to a text file and then read it back looking to the term ‘CodeBehindForm’ which indicates the presence of a VBA module for that object. This probably isn’t the best approach to employ as it is making unnecessary read/write to the HD when this can all be done without using simple VBA (as shown above), but I wanted to see for myself.
'---------------------------------------------------------------------------------------
' Procedure : Obj_HasModule3
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if a Form or Report has a VBA code module
' Alternative to HasModule that doesn't require the object to be open
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjName : Name of the Report/Form to check for an associated module
' sObjType : Object Type -> "Form" or "Report"
'
' Usage:
' ~~~~~~
' ? Obj_HasModule3("Employee List","Form")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-03-05 Initial Release
'---------------------------------------------------------------------------------------
Public Function Obj_HasModule3(ByVal sObjName As String, sObjType As String) As _
Boolean
On Error GoTo Error_Handler
Dim sFile As String
Dim sSaveAsTextContent As String
Dim iFileNumber As Integer
'Determine the temo folder' location
sTmpFldr = Environ("temp") & "\"
'Save the object to text
Select Case sObjType
Case "Form"
sFile = sTmpFldr & "Form-" & sObjName & ".txt"
Application.SaveAsText acForm, sObjName, sFile
Case "Report"
sFile = sTmpFldr & "Report-" & sObjName & ".txt"
Application.SaveAsText acReport, sObjName, sFile
End Select
'Read the text file looking for VBA content
iFileNumber = FreeFile
Open sFile For Input As #iFileNumber
Do Until EOF(1)
Line Input #1, textline
If Trim(textline) = "CodeBehindForm" Then
Obj_HasModule3 = True
Exit Do
End If
Loop
Error_Handler_Exit:
On Error Resume Next
Close #iFileNumber 'Close our handle to the text file we were reading
Kill sFile 'Delete the text file now that we're done with it
Exit Function
Error_Handler:
If Err.Number = 32584 Then
'The specified object does not exist! What do we do?
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Obj_HasModule3" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl), _
vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function