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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,806
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.

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

Code:
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 :

Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
*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.
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
... 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?

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

Forum statistics

Threads
1,224,454
Messages
6,178,766
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top