Jaafar Tribak
Well-known Member
- Joined
- Dec 5, 2002
- Messages
- 9,596
- Office Version
- 2016
- Platform
- 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.
I hope this topic may arouse the curiosity of others and together reach a decent and acceptable solution.
Regards.
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 FunctionI hope this topic may arouse the curiosity of others and together reach a decent and acceptable solution.
Regards.
