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

[Jaafar Tribak]

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]

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]

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]

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]

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]

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]

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

 

[hymced]

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]

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