# Challenging problem : How to make Excel subclassing safe and stable ?



## Jaafar Tribak (Oct 6, 2009)

Hi all.

This has been intriguing me for years and i've looked and asked everywhere but nobody seems to know the answer.

On most operating systems, subclassing office applications from within VBA crashes the application and worse yet, it can downright lock the whole system.

this is a known issue and the best advice offered by the experts is either to run the subclassing code from an external dll(no self-contained vba code) or just to avoid using this technique altogether.

But given the great potential and flexibility that subclassing can offer , i don't want to give up hope . Note that with subclassing, one can intercept and handle dozens of events that are not natively exposed by the excel object model.-Imagine how much control one could gain.

Anyway along my ongoing experimentations, i noticed that if i go to the VBE and manually stop and reset the VBE then run the Subclass code, the code works smoothly and the application doesn't crash. However , if i don't stop and reset the VBE first then the application crashes.

I thought about automating this by reseting the VBE through code before subclassing the application  (via the VBIDE object model) but i hate this approach because automating the VBE depends on the user security settings and also looks messy.

So far, the only workaround i've come up with is to completly destroy the VBE window which is the one causing all the problems ! That's sure an extreme and cheesy hack but at least we have identified the VBE as the culprit.

here is an example that subclasses the excel application that works. It simply catches the mouse moving over the excel title bar. Once you run the code, you won't be able to find the VBE as it will be already destroyed.

Please, save your work before trying this just in case.


```
Option Explicit
 
Private Declare Function SetWindowLong Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Private Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long, _
ByVal MSG As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function DestroyWindow Lib "user32.dll" _
(ByVal hwnd As Long) As Long

Private Const WM_NCMOUSEMOVE As Long = &HA0

Private lOldWinProc As Long
Private lXLhwnd As Long

Sub SubclassExcel()
 
    Const VBECLASSNAME As String = "wndclass_desked_gsk"
    Dim lVBEhwnd As Long
    
   [COLOR=seagreen][B] 'retrieve the VBE window handle.
[/B][/COLOR]    lVBEhwnd = FindWindow("wndclass_desked_gsk", vbNullString)
    
    [COLOR=seagreen][B]'retrieve the excel window handle.
[/B][/COLOR]    lXLhwnd = FindWindow("XLMAIN", Application.Caption)
 
   [B][COLOR=seagreen] 'destroy the VBE window completly ![/COLOR][/B]
    DestroyWindow lVBEhwnd
    
    [COLOR=seagreen][B]'hook the excel application.[/B][/COLOR]
    lOldWinProc = SetWindowLong _
    (lXLhwnd, GWL_WNDPROC, AddressOf WindowProc)

End Sub

Sub UnsubclassExcel()
 
 SetWindowLong lXLhwnd, GWL_WNDPROC, lOldWinProc
 
End Sub

Private Function WindowProc _
(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
    Select Case uMsg
    
        [COLOR=seagreen][B]'update cell A1 on moving the mouse
          'over the excel title bar.
[/B][/COLOR]        
        Case WM_NCMOUSEMOVE
        
            Range("a1") = Range("a1") + 1
    
    End Select
    
    WindowProc = CallWindowProc _
    (lOldWinProc, hwnd, uMsg, wParam, lParam)
 
End Function
```
 
I hope this topic may arouse the curiosity of others and together reach a decent and acceptable solution.

Regards.


----------



## Jaafar Tribak (Oct 7, 2009)

Persistence pays off ! - here is an interesting addition to this difficult task .

With the help of _WinSpy_, i have managed to figure out what the custom Class messages are for reseting the VBE !

This means that we now have a much safer way to subclass excel and all its children windows without fearing an immediate crash of the system and all this without needing to automate the VBE or destroy it as i did in the previous post and obviously all done from within a standard self-contained VBA code. 

In a nutshell, the whole process for achieving this can be broken in the following steps :

*1-* Reset the VBE via a couple of _PostMessage_ API functions.
*2-* Storing the subclassed window _hwnd_ as a Property of the Desktop
via the _SetProp_ API function. (This is needed because reseting the VBE in the previous step reinitializes all the variables)
*3-* Setting a one time windows timer from which to set the subclass.
*4-* Implementing the actual subclassing.

Here is a WORKBOOK DEMO.


This is the main code in a standard module :

I have heavily commented the code for better readibility.


```
Option Explicit
 
Private Declare Function SetWindowLong Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Private Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long, _
ByVal MSG As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetDesktopWindow Lib _
"user32.dll" () As Long
Private Declare Function ShowWindow Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
 
Private Declare Function SendMessage Lib "user32.dll" _
Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As Any) As Long
 
Private Declare Function PostMessage Lib "user32.dll" _
Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
Private Declare Function SetTimer Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
 
Private Declare Function LockWindowUpdate Lib "user32.dll" _
(ByVal hwndLock As Long) As Long
 
Private Declare Function GetProp Lib "user32" _
Alias "GetPropA" _
(ByVal hwnd As Long, _
ByVal lpString As String) As Long
 
Private Declare Function SetProp Lib "user32" _
Alias "SetPropA" _
(ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal hData As Long) As Long
 
Private Declare Function RemoveProp Lib "user32" _
Alias "RemovePropA" _
(ByVal hwnd As Long, _
ByVal lpString As String) As Long
 
Private Const GWL_WNDPROC   As Long = -4
Private Const WM_USER As Long = &H400
Private Const WM_NCMOUSEMOVE As Long = &HA0
Private Const WM_SETREDRAW As Long = &HB
 
Private Const VBE_CLASS_NAME As String _
= "wndclass_desked_gsk"
Private Const EXCEL_CLASS_NAME As String _
= "XLMAIN"
 
Private lOldWinProc As Long
Private lVBEhwnd As Long
 
Sub Safe_Subclass(hwnd As Long)
 
    [COLOR=seagreen][B]'don't subclass the window twice ![/B][/COLOR]
    If GetProp(GetDesktopWindow, "HWND") <> 0 Then
        MsgBox "The Window is already Subclassed.", _
        vbInformation
        Exit Sub
    End If
 
    [COLOR=seagreen][B]'store the target window hwnd as a desktop[/B][/COLOR]
    [B][COLOR=seagreen]'window for later use property.[/COLOR][/B]
 
     SetProp GetDesktopWindow, "HWND", hwnd
 
    [COLOR=seagreen][B]'retrieve the VBE hwnd.[/B][/COLOR]
 
     lVBEhwnd = FindWindow(VBE_CLASS_NAME, vbNullString)
 
    [COLOR=seagreen][B]'prevent flickering of the screen[/B][/COLOR]
   [COLOR=seagreen][B]'before posting messages to reset[/B][/COLOR]
    [COLOR=seagreen][B]'the VBE window.[/B][/COLOR]
 
    LockWindowUpdate lVBEhwnd
 
    [COLOR=seagreen][B]'do the same with the desktop in the background.[/B][/COLOR]
 
    SendMessage _
    GetDesktopWindow, ByVal WM_SETREDRAW, ByVal 0&, 0&
 
    [B][COLOR=seagreen]'stop and reset the VBE first to safely[/COLOR][/B]
    [COLOR=seagreen][B]'proceed with our subclassing of xl.[/B][/COLOR]
    
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H30, ByVal 0&
 
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H33, ByVal 0&
 
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H83, ByVal 0&
 
    [COLOR=seagreen][B]'run a one time timer and subclass xl[/B][/COLOR]
    [COLOR=seagreen][B]'from the timer callback function.[/B][/COLOR]
    [B][COLOR=seagreen]'if subclassing is not installed within[/COLOR][/B]
    [COLOR=seagreen][B]'the timer callback,xl will crash ![/B][/COLOR]
    
    SetTimer GetProp(GetDesktopWindow, "HWND") _
    , 0&, 1, AddressOf TimerProc
 
End Sub
 
Sub UnSubClassExcel(hwnd As Long)
 
   [COLOR=seagreen][B]'remove the subclass and cleanup.[/B][/COLOR]
 
    SetWindowLong hwnd, GWL_WNDPROC, lOldWinProc
    RemoveProp GetDesktopWindow, "HWND"
    lOldWinProc = 0
 
End Sub
 
Private Function WindowProc _
(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
   [COLOR=seagreen][B]'illustration example.[/B][/COLOR]
 
    On Error Resume Next
 
    Select Case uMsg
 
       [COLOR=seagreen][B]'increment cell A1 by 1[/B][/COLOR]
[B][COLOR=seagreen]       'when moving the mouse[/COLOR][/B]
[B][COLOR=seagreen]       'over the xl app title bar.[/COLOR][/B]
 
        Case WM_NCMOUSEMOVE
            Range("a1") = Range("a1") + 1
 
   End Select
 
   [COLOR=seagreen][B]'allow other msgs default processing.[/B][/COLOR]
 
    WindowProc = CallWindowProc _
    (lOldWinProc, hwnd, uMsg, wParam, lParam)
 
End Function
 
 
Sub TimerProc(ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long)
 
    [B][COLOR=seagreen]'we lost the hwnd stored in the lVBEhwnd var[/COLOR][/B]
[B][COLOR=seagreen]   'after reseting the VBE so let's retrieve it again.[/COLOR][/B]
 
    lVBEhwnd = FindWindow(VBE_CLASS_NAME, vbNullString)
 
    [COLOR=seagreen][B]'we no longer need the timer.[/B][/COLOR]
 
    KillTimer GetProp(GetDesktopWindow, "HWND"), 0&
 
   [COLOR=seagreen][B]'allow back drawing on the desktop.[/B][/COLOR]
 
    SendMessage GetDesktopWindow, WM_SETREDRAW, ByVal 1, 0&
 
    [COLOR=seagreen][B]'hide the VBE.[/B][/COLOR]
    
    ShowWindow lVBEhwnd, 0&
 
    [COLOR=seagreen][B]'unlock the window update.[/B][/COLOR]
 
    LockWindowUpdate 0&
 
   [B][COLOR=seagreen]'and at last we can now safely[/COLOR][/B]
[COLOR=seagreen][B]   'subclass our target window.[/B][/COLOR]
    
    lOldWinProc = SetWindowLong _
    (GetProp(GetDesktopWindow, "HWND"), _
    GWL_WNDPROC, AddressOf WindowProc)
 
End Sub
```
 

Here is an usage example that subclasses the excel main window to catch the all mouse moves over the application title bar.


```
Option Explicit
 
Sub SetSubClass()
 
   [COLOR=seagreen][B]'let's subclass the main[/B][/COLOR]
[COLOR=seagreen][B]   'excel application window.[/B][/COLOR]
 
    Call Safe_Subclass(Application.hwnd)
 
End Sub
 
Sub RemoveSubClass()
 
   [COLOR=seagreen][B]'unsubclass the window.[/B][/COLOR]
 
    Call UnSubClassExcel(Application.hwnd)
 
End Sub
```
 
Following is an important safety measure in case the user doesn't remove the subclassing before closing the workbook/application.

code in the workbook module :


```
Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    [COLOR=seagreen][B]'IMPORTANT !!![/B][/COLOR]
[B][COLOR=seagreen]   '============[/COLOR][/B]
 
   [COLOR=seagreen][B]'if the subclassing is not removed[/B][/COLOR]
[B][COLOR=seagreen]   'before closing the workbook[/COLOR][/B]
[B][COLOR=seagreen]   'the system can crash !!!![/COLOR][/B]
 
    Call UnSubClassExcel(Application.hwnd)
 
End Sub
```
 

I have only tried this on 2 machines Win XP SP2 excel 2003 and Win XP excel 2000 and it worked very well.

If anyone tries this, please give me some feedback as i'd like to know if this works reliably accross different systems.

Regards.


----------



## Zack Barresse (Oct 8, 2009)

Windows XP Pro SP3, Excel 2007 SP2: success.  It's not a one-to-one count either, as a constant movement will keep the count going up at a very high rate.  Holding the cursor still will incriment it with the timer at a slower rate (appears to be at standard timer rate).  Is this the expected behavior?  But yes, worked well.


----------



## Jaafar Tribak (Oct 8, 2009)

Zack. Thanks very much for the feedback.



> Is this the expected behavior?


 
Yes. The _WM_NCMOUSEMOVE_ message is fired even when the mouse is still over the non client area of the hooked window so it is the expected behaviour.

Capturing the above message was just a random choice. I could have set the code to intercept other messages as well. The actual challenge that prompted me to put this code together was to see if one could subclass excel from within VBA without making the application crash which is a known and documented issue.

So i am glad it worked well . This should open the way for many many uses. In fact i just applied this technique a while ago HERE.

Having said that and before i get too excited, Subclassing, Hooking or running any other code that uses _CallBack_ functions are inherently dangerous as VB just can't handle runtime errors while using CallBacks. This means that the code must not error out and that one cannot edit,debug the code while the application is subclassed as that would immediatly cause it to crash !

Regards.


----------



## rayzinnz (Aug 19, 2014)

Remarkable!
It works fine on Excel 2010 on Windows 7 64bit. I've managed to port it to capture mouse rolls in a chartsheet to allow data drilldown.
Thanks so much.


----------



## CJ_ExcelEverything (Sep 15, 2017)

*dusts off old thread*

Many years later and this still seems to very relevant. Truly awesome work. I initially tried code from Dragokas published more recently, which he states is a simplified version of this code plus some unsubclassing based on SC_CLOSE messages. However he did miss the fact that resetting the VBE kills of global variables.

In the end I remixed some of the code which I'll post later after some clean up. Some additions, questions and ideas:

Additions:

Conditional compilation with #VBA7  and #WIN64  defines to allow running on 64 bit CPUs and Office versions
Moved "desktop variable" to named range stored in workbook (can also use executemacro4 variables) which simplifies the code
Built in an additional "flag" using another named range to make sure one doesn't double (un)subclass. It also allows a sheet to display the status of subclassing. You can set the name range as hidden from VBA so its more difficult for an end-user to mess with it.


----------



## CJ_ExcelEverything (Sep 15, 2017)

Some comments on Dragokas' code


The Application.OnTime function seems to work without needing another Lib declaration for the timers
The unsubclassing from within the WindowProc upon receiving a WC_close message seems like a good idea, but may need some debugging to make sure (a) the WC_close message only comes from closing the subclassing workbook (not any other) and (b) that you can still pass the WC_close message along to the original WindowProc safely and not call a nulled lOldWinProc function
The use of the lVBEhwnd and IsSubclassed variables doesn't work because of the VBE resetting (even is its declared as static), which is why in Jaafar's code he is careful to restore the variable from elsewhere/reinitialise

Questions and ideas:

The lOldWinProc may need to be stored in a desktop variable or named range as well
Anyone ever tried and compared the alternative subclassing API's from comctl32.dll (see example)? I'm not to keen to try it out only to find it still suffers from the same clashes with the VBE. Supposedly it is "safer" because it maintains the function pointers for you i.e. you can slot in and out from a chain of function pointers (assuming another program may subclass your subclassed function). You also don't have to remember the original function pointer.
Is it really necessary to lock the window updates for the VBE and desktop? I hardly notice any flickering, maybe sometimes when the VBE is open, but then...
If the VBE is open, the chances are you or your users are coding with the VBE and likely resetting it / recompiling code while you are subclassing the Excel window - which would likely lead to a crash. How about be subclass the VBE as well, intercept any resets to first unsubclass and then resubclass several seconds later (using again a timer)?


----------



## CJ_ExcelEverything (Sep 15, 2017)

Lastly my thoughts on why the VBE crashes occurs and why subclassing the VBE as well would help (disclaimer: I had one compiler course more than 10 years a go - I'm merely guessing and waiving hands here).

Little is known about how exactly the VBA compiler works , only that code gets compiled to p-code (platform independent) and then translated, probably "just-in-time" in a virtual machine to machine code (queue wikipedia entries for VBA, p-code, virtual machines and just-in-time compilation, as well as this forum post). What is clear is that when you hand your WindowProc function pointer over to user32.dll it is definitely fully compiled machine code (probably riddled with some callbacks to debugging handlers etc.). It may well be that the mere fact that you request a function pointer with "AddressOf" forces the virtual machine to precompile the whole function into memory. The problem is then this: we have no control over when that memory gets destroyed and the code recompiled. If at any point the VBE recompiles and changes to actual memory allocation for our WindowProc's compiled machine code, Windows will direct the CPU's instruction pointer to blank or junk memory space and lock-up either Excel or worse the whole PC.

So, my guess as to why "resetting" the VBE works/helps: we're postponing the inevitable. You are decreasing the likelihood that the VBE is recompiling our WindowProc into new memory space anytime soon. Additionally, the mere fact that we are putting everything into one code module instead of sheet functions may also influence the way in which the compilation is being handled.

Now, how to make it even safer?
1. Find ways to make the VBE maintain the windowproc code in memory for longer
2. Find ways to detect or intercept the resetting/recompiling and quickly unsubclass before windows makes a call to nowhere

Things to try: 
1. Put our WindowProc call in its own module (might not get affected by calls and recompiles in other modules and sheets)
2. Keep a global variable or a static local variable up pointing to our WindowProc (currently that "AddressOf WindowProc" is passed to the windows library as a temporary variable)
3. See if there's no "static" or "protected" declaration keywords in VBA which would help retain it in memory
4. Try to make an actual copy of the compiled code as stored in memory into our own variable, then provide Windows with a pointer to that memory space (probably problems with "data" memory vs "code" memory will occur)
5. Store the oldWindowProc pointer in safe memory to make sure we can always unsubclass
6. Re-subclass every few minutes, following the same sequence to reset the VBE etc
7. Subclass the VBE, intercept the reset message, unsubclass and queue up a re-subclass with a timer
8. Write a clever subclassing "chainer" routine in a c-library which would act as a safety-net between the VBA routine and the original window handler. It would detect if the VBA WindowProc pointer no longer contains the original compiled code and then simply "unsubclass" itself and give control back to the original handler. On the VBA side you can monitor for a change in the pointer address then re-subclass, or make a call to the chainer routine to enquire its status

Ps. I just remembered Dragokas' code doesn't have the "On error resume next" code in the WindowProc


----------



## Jaafar Tribak (Sep 19, 2017)

Hi Cj_ExcelEverything,

Thanks for all the valuable points you have made. I had already tried many of the ideas you mentioned before posting my code but could never prevent excel from crashing when the vbe is reset either by pressing the vbe stop button or by an unhandled error occurring while the subclassing is installed.

I have in mind writing a small standard dll in C++ and subclass excel from within the dll .. That, hopefully, should solve the crashing of excel should the vbe be reset hence making subclassing the excel application stable and much safer. Never tried this dll approach but I think should work (hopefully)


----------



## CJ_ExcelEverything (Sep 19, 2017)

Jaafar Tribak said:


> ... could never prevent excel from crashing when the vbe is reset either by pressing the vbe stop button or by an unhandled error occurring while the subclassing is installed.


Yeah the unhandled error would be almost impossible to catch. Have to tried my suggestion #7  which could at least catch the stop button reset?



Jaafar Tribak said:


> I have in mind writing a small standard dll in C++ and subclass excel from within the dll .. That, hopefully, should solve the crashing of excel should the vbe be reset hence making subclassing the excel application stable and much safer. Never tried this dll approach but I think should work (hopefully)


This is kind off what I had in mind with #8 . It might be possible to write a "generic" subclassing dll that would still allow users to code their own windowprocs in VBA. The generic handler would only pass execution on to the VBA windowproc pointer if it can verify that the VBA code is still in memory - which might be easier said than done. I'm not sure if Windows would allow a direct memory comparison between "code" space and "variable" space.


----------



## Jaafar Tribak (Oct 6, 2009)

Hi all.

This has been intriguing me for years and i've looked and asked everywhere but nobody seems to know the answer.

On most operating systems, subclassing office applications from within VBA crashes the application and worse yet, it can downright lock the whole system.

this is a known issue and the best advice offered by the experts is either to run the subclassing code from an external dll(no self-contained vba code) or just to avoid using this technique altogether.

But given the great potential and flexibility that subclassing can offer , i don't want to give up hope . Note that with subclassing, one can intercept and handle dozens of events that are not natively exposed by the excel object model.-Imagine how much control one could gain.

Anyway along my ongoing experimentations, i noticed that if i go to the VBE and manually stop and reset the VBE then run the Subclass code, the code works smoothly and the application doesn't crash. However , if i don't stop and reset the VBE first then the application crashes.

I thought about automating this by reseting the VBE through code before subclassing the application  (via the VBIDE object model) but i hate this approach because automating the VBE depends on the user security settings and also looks messy.

So far, the only workaround i've come up with is to completly destroy the VBE window which is the one causing all the problems ! That's sure an extreme and cheesy hack but at least we have identified the VBE as the culprit.

here is an example that subclasses the excel application that works. It simply catches the mouse moving over the excel title bar. Once you run the code, you won't be able to find the VBE as it will be already destroyed.

Please, save your work before trying this just in case.


```
Option Explicit
 
Private Declare Function SetWindowLong Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Private Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long, _
ByVal MSG As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function DestroyWindow Lib "user32.dll" _
(ByVal hwnd As Long) As Long

Private Const WM_NCMOUSEMOVE As Long = &HA0

Private lOldWinProc As Long
Private lXLhwnd As Long

Sub SubclassExcel()
 
    Const VBECLASSNAME As String = "wndclass_desked_gsk"
    Dim lVBEhwnd As Long
    
   [COLOR=seagreen][B] 'retrieve the VBE window handle.
[/B][/COLOR]    lVBEhwnd = FindWindow("wndclass_desked_gsk", vbNullString)
    
    [COLOR=seagreen][B]'retrieve the excel window handle.
[/B][/COLOR]    lXLhwnd = FindWindow("XLMAIN", Application.Caption)
 
   [B][COLOR=seagreen] 'destroy the VBE window completly ![/COLOR][/B]
    DestroyWindow lVBEhwnd
    
    [COLOR=seagreen][B]'hook the excel application.[/B][/COLOR]
    lOldWinProc = SetWindowLong _
    (lXLhwnd, GWL_WNDPROC, AddressOf WindowProc)

End Sub

Sub UnsubclassExcel()
 
 SetWindowLong lXLhwnd, GWL_WNDPROC, lOldWinProc
 
End Sub

Private Function WindowProc _
(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
    Select Case uMsg
    
        [COLOR=seagreen][B]'update cell A1 on moving the mouse
          'over the excel title bar.
[/B][/COLOR]        
        Case WM_NCMOUSEMOVE
        
            Range("a1") = Range("a1") + 1
    
    End Select
    
    WindowProc = CallWindowProc _
    (lOldWinProc, hwnd, uMsg, wParam, lParam)
 
End Function
```
 
I hope this topic may arouse the curiosity of others and together reach a decent and acceptable solution.

Regards.


----------



## Jaafar Tribak (Sep 19, 2017)

Cj_ExcelEverything,

I think subclassing the vbe would be very messy and one would end up with two subclassed windows excel + the VBE .. And that is assuming that one could intercept the stop button + unhadled errors which I doubt very much.




> It might be possible to write a "generic" subclassing dll that would still allow users to code their own windowprocs in VBA. The generic handler




Yes that is what I have in mind and I have already written some subclassing C++ testing code and called it from excel .. Pressing the vbe stop button doesn't crash excel which is great but I noticed that excel slows down a little bit specially when passing all the messages to the window procedure in vba.

I'll carry on experimenting with this and if anything interesting comes up I'll post it here.


----------



## hymced (Sep 25, 2017)

Hi Jaafar !

thank you for your code, and the time you must have spent on this !

but I am experimenting an issue:
I was looking for a way to detect if a Workbook_Close event was triggered by a Workbook.Close or an Application.Quit while having a single Workbook open in Excel. So I found your solution for intercepting the WM_CLOSE messages, and eventually tried to quit Excel with its single open Workbook that implements your method.

Problem: Excel crashes. 

This does not happen if a simply close the Workbook but not the Application (CTRL+W)

Do you have any idea why and how to solve this ?


----------



## hymced (Sep 25, 2017)

I have also tried relocating the WindowProc in another module as you did here but it does not solve it :

https://www.mrexcel.com/forum/excel...trigger-macro-soon-excel-window-selected.html

I have also tried using LongPtr variable and PtrSafe keyword in the declaration of SetWindowLongPtr has suggested here :

http://www.jkp-ads.com/articles/apideclarations.as

By the way, my config if it has any importance :
Windows 10 64 bit
Office 2016 32 bit


----------



## hymced (Sep 25, 2017)

The following prevents Excel from crashing after the Workbook has been closed. But it is a dirty workaround since it assumes the Workbook_Close event has been triggered by the entire Excel Application quitting, and not just the Workbook closing...

It could be improved by detecting the WM_CLOSE message, but its too much restrictive in my opinion since there are other way to close the Application (with its single Workbook) without having any WM_CLOSE message poping up...

So I am still looking for a proper way to prevent the crash in this particular case  
My guess is that it has something to do with the 'Address Of WindowProc' and some memory not being freed/accessible, I don't really know...
I have also tried to reset the VBE again by sending the 3 magic messages you have found Jaafar, but it does not do the trick either...


```
Option Explicit

Dim AppQuitting As Boolean
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)


    If Not AppQuitting Then
        Call RemoveSubClass
        Cancel = True
        AppQuitting = True
    End If
    
    Application.OnTime Now(), "CallApplicationQuit"
    
End Sub


Sub CallApplicationQuit()    Application.Quit
End Sub
```


----------



## Jaafar Tribak (Sep 25, 2017)

Hi hymced,

Welcome to the board.

I have just finished writing a C++ dll (14 kb) that can be used for subclassing excel without the vbe crashings issue ... I'll be posting it along with the VBA code and a workbook example later on.

Stay tuned


----------



## Jaafar Tribak (Sep 26, 2017)

Take a look here : https://www.mrexcel.com/forum/excel...or-well-unhadled-errors-safe-subclassing.html


----------



## hymced (Sep 26, 2017)

Wonderful Jaafar, I will take a look to it ! I have in fact just been learning how to develop a very simple C++ XLL add-in recently to get previous value of the Application.Caller of a UDF without having a circular reference warning 

Any way, I discovered that using (your solution for) subclassing with instances of custom class always end up with Excel crashing when quitting, and without the BeforeClose event handler triggering, and without being able to catch the WM_CLOSE message either 

To reproduce, simply add this and tried quitting Excel (not only closing the Workbook, which works fine...) :

*ThisWorkbook Module:*



```
Private cls1 As Class1


Private Sub Workbook_Open()
    Call SetSubClass
    Set cls1 = New Class1
End Sub
```


*Class1 Module:*



```
'
```


----------



## hymced (Oct 4, 2017)

I have finally solved my problem. 

When using 'Call SetSubClass', any existing initiated variable or object instantiated used before is released. that no surprise. but in fact, any other attempt to initiate other variable or to instantiate object later in the parent procedure of the 'Call SetSubClass' until its End Sub statement is also futile. No memory will be allocated, variables won't be initialized and objects will be Nothing

Now, to be more precise, it appears you will have to way 1 sec before VBE is able again to hold variables/objects using Application.OnTime Now()+TimeSerial(0,0,1) in the parent proc of the 'Call SetSubClass' statement:

Public Sub Parent_Proc
    Call SetSubClass
    Application.OnTime Now() + TimeSerial(0,0,1), "SomeProcToInitVariables"
End Sub

I have found an exception: if the parent proc is the Workbook_Open event handler, the extra 1 sec of delay is unnecessary, the VBE is able to allocate memory just after the End Sub statement:

Private Sub Workbook_Open()
    Call SetSubClass
    Application.OnTime Now(), "SomeProcToInitVariables"
End Sub

My problem is now solved. Thank you Jaafar for your code to make Excel subclassing safe and stable, and sorry if I have polluted a bit this thread while testing it 

*My final adding to make your solution even more stable : forcing the VBE to load just before posting the magic messages!*


```
...
    'store the target window hwnd as a desktop
    'window for later use property.
    
    SetProp GetDesktopWindow, "HWND", hWnd
    
    'load the VBE
    
    Application.VBE.MainWindow.Visible = True
    
    'retrieve the VBE hwnd.
    
    lVBEhwnd = FindWindow(VBE_CLASS_NAME, vbNullString)
    ...
```


----------

