Dave in the newsgroups has an add-in with application level events, one of which fires a Before_Close event. The problem is that some of the workbooks also contain the event procedure and some don’t. For those that do, the event effectively fires twice; once at the workbook event level and one at the application event level. I tried to develop a function that would help him determine if the workbook had such an event procedure.
Function HasBeforeClose(wbTarget As Workbook, _
Optional sUniqueText As String) As Boolean
‘Returns True if the workbook contains a Before_Close event
‘macro which optionally contains some text.
‘wbTarget is the workbook to be searched.
‘sUniqueText is a string – omit it to determine if the event
‘procedure exists, include to determine if the event procedure
‘contains the text.
Dim exProj As Object
Dim exModule As Object
Dim lProcStart As Long
Dim lProcCount As Long
Dim i As Long
Const sMODNM As String = “ThisWorkbook”
Const sPROCNM As String = “Workbook_BeforeClose”
Const vbext_pk_Proc As Long = 0
‘Create a reference to ThisWorkbook
Set exProj = wbTarget.VBProject
Set exModule = exProj.VBComponents(sMODNM).CodeModule
‘Find the start of the BeforeClose procedure
On Error Resume Next
lProcStart = exModule.ProcBodyLine(sPROCNM, vbext_pk_Proc)
lProcCount = exModule.ProcCountLines(sPROCNM, vbext_pk_Proc)
On Error GoTo 0
‘If the procedure isn’t found, return False
If lProcStart = 0 Then
HasBeforeClose = False
Else
‘Find the unique text in the procedure. If no unique text is
‘supplied, this will always be True.
If InStr(1, exModule.Lines(lProcStart, lProcCount), _
sUniqueText) > 0 Then
HasBeforeClose = True
End If
End If
End Function
Dave Peterson astutely pointed out that for Excel 2002 and above, the Trust Code setting will cause problems with this. I use Excel 2000, but I suppose I should learn something about this security setting. It’s on my list.
This procedure could be generalized more, but I wonder if it’s worth it.
could a flag of some sort not be used?
Another problem besides the Trust Access to Visual Basic Project setting is the name of the workbook’s ThisWorkbook class module. Some people (like me) change this to something more descriptive.
This will break:
Workbooks(“Book.xls”).VBProject _
.VBComponents(“ThisWorkbook”).CodeModule
so you need something like this (untested):
Workbooks(“Book.xls”).VBProject _
.VBComponents(Workbooks(“Book.xls”).CodeName) _
.CodeModule
– Jon
I personally wouldn’t check for existence of strings within modules. My approach would be to leave that as an external auditing exercise.
That leaves the following as a much shorter check:
Sub test()
Dim bln As Boolean
bln = False
On Error Resume Next
bln = (ThisWorkbook.VBProject.VBComponents(ThisWorkbook.CodeName).CodeModule.ProcStartLine(“Workbook_Open”, vbext_pk_Proc) * 0 = 0)
On Error GoTo 0
MsgBox bln
End Sub
PS: your site rocks – I love every day of it.