Determine If Event Exists

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

Posted in Uncategorized

3 thoughts on “Determine If Event Exists

  1. 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 _

    so you need something like this (untested):

    Workbooks(“Book.xls”).VBProject _
    .VBComponents(Workbooks(“Book.xls”).CodeName) _

    – Jon

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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.