Persistent Application level Events after user resetting variables ?

[Jaafar Tribak]

Hi all.

Here is the deal - Suppose the application is hooked upon opening the workbook like so :

Private WithEvents AppEvents As Application

Private Sub Workbook_Open()
 
    Set AppEvents = Application
 
End Sub

Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
 
    MsgBox "hello!"
    
End Sub

This should keep the application hooked as long as the AppEvents Variable points to the application object. However if the Project is reset by the user or simply some code accidently errors out , this will inforce the reinitialization of all variables including the AppEvents variable and obviously the application will cease to be hooked.

Any ideas on how to work around this and make the application's events persistent throughout ?

Regards.

 

[Jaafar Tribak]

Bump .

Any thoughts ? Still stuck on this one.

Regards.

 

[Jaafar Tribak]

WORKBOOK DEMO.

Here is a workaround that seems to actually work. Basically, by declaring the WithEvents AppEvents Workbook Property as Public or wrapping it in a Public Function, one could access it from outside the excel process using automation.

The code below creates an out of process VBS file on the fly that runs a continious loop within which the AppEvents Property is constantly being assigned the application object thus allowing the excel events to persist until the workbook is closed even if the the VBIDE is reset during the excel session either by accident (ie the code erroring out during execution) or by editing/debugging the code.

This hack can also be easily amended to work for other Class based events ie ( Commandbars, ActiveX controls etc )

I was hoping to achieve this by blocking the memory assigned to the AppEvents variable using some memory API functions but I am not even sure if that is possible. That would certainly be more elegant and a good learning exercise.


Code in the WorkBook Module :

Option Explicit
 
Public WithEvents AppEvents As Application
Private Const PERSISTENT_HOOK_FILE As String = "C:\PersistentHook.vbs"
 
Private Sub AppEvents_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)
 
    MsgBox "Excel remains hooked after reseting the VBIDE ! "
 
End Sub
 
Private Sub Workbook_Open()
 
    Call PersistentHook
 
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Kill PERSISTENT_HOOK_FILE
 
End Sub
 
Private Sub PersistentHook()
 
    Open PERSISTENT_HOOK_FILE For Output As #1
 
        Print #1, "Dim oWb"
        Print #1, "On Error Resume Next"
        Print #1, _
        "Set oWb = Getobject(""" & ThisWorkbook.FullName & """)"
        Print #1, "Do"
        Print #1, "Set oWb.AppEvents = oWb.Parent"
        Print #1, "Loop Until oWb.Parent.ActiveCell Is Nothing"
        Print #1, "Set oWb = Nothing"
 
    Close #1
 
    Shell "WScript.exe " & PERSISTENT_HOOK_FILE
End Sub

Regards.

 

[Jaafar Tribak]

Fresh workbook example.

 

[billbrunt]

Jaafar -

I've not found some decent docs which explain the constructs used in your code so I'm somewhat clueless as to what its doing.

I do have a code workbook and it works great and opens and closes many different workbooks which hold user data and behaves as expected.

In your example, it assumes this code resides in the Workbook object but I'm not able to place there as the code for application resides in a seperate workbook.

Perhaps you could provide some guidance either to some docs or an explanation.

Many thanks in advance.

- Bill

 

[Jaafar Tribak]

Jaafar -

I've not found some decent docs which explain the constructs used in your code so I'm somewhat clueless as to what its doing.

I do have a code workbook and it works great and opens and closes many different workbooks which hold user data and behaves as expected.

In your example, it assumes this code resides in the Workbook object but I'm not able to place there as the code for application resides in a seperate workbook.

Perhaps you could provide some guidance either to some docs or an explanation.

Many thanks in advance.

- Bill

Hi Bill.

I am not sure I understand the problem you are having but what the code does is to ensure the application level events are always working even if the VBProject is reset and the the variables are reinitialized.

If the code you have resides in a seperate workbook you will need to put the code in that workbook module. Alternatively you could place the code in the Personal.xls workbook or an addin so it is always running each time you open Excel.