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
‘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
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.