Detect Excel application minimize/restore?
in [Visual Basic]
|
From: Karl E. Peterson on 21 Dec 2009 14:37 RB Smissaert formulated the question : > OK, ignore that last e-mail. Had another go with your code and it looks all > fine now. > Very neat indeed and thanks for that. Well, I'm still battling stability issues, it seems. I tried moving it into a global class module that gets created (and sets the hook) in Workbook_Open. Dunno what the problem is, but I seem to be blowing up Excel with some regularity. :-( > Just one question, don't I need CallNextHookEx as suggested in the examples > in the API Guide? Yeah, that'd be a good idea. At least when you're not wanting to return a 1 to prevent the impending action. -- ..NET: It's About Trust! http://vfred.mvps.org
From: Karl E. Peterson on 21 Dec 2009 14:47 It happens that Peter T formulated : > Well it certainly works:-) Mostly. I'm seeming to have some issues with setting the hook on Workbook_Open. Not sure what's up with that yet. > Safe, I think that depends - > I appreciate this hook is safer than conventional subclass & callback. Even > so, if the code is in plain view the user could crash Excel (say) by adding a > new procedure to the normal module. In practice probably most wouldn't edit > the module with the form showing and hook running, but some would (I crashed > Excel doing just that). > > However if hidden in a password locked project that the user has no business > looking at, I guess all would be fine. Unless, and maybe you could clarify - > I noticed if I Excel closes unexpectedly and the UnhookCbt not called, next > time Excel starts with the same file, the hook is still running and no way to > stop it. It's probably better to not have it running during development. At least not when it doesn't need to be. Yeah. > Resources: There's no timer and I notice the VBE's caption flickering with > the hook running. So I assumed the loop is continuously hogging resources. I > was surprised though that it didn't seem to impact much on the timing of a > test loop (maybe about 10%). So I put a counter in the callback, seems like > it's triggered about 10-50 times/second, strangely variable but even so not > that much in the way of resources. Is that also your view, particularly in a > VBA environment? Those callbacks are primarily HCBT_CLICKSKIPPED which you get everytime Excel pops a mouse message off its queue. They're irritating, and it'd be nice if you could tell the hook which notifications you want and which you don't. > Other observations: > > I was curious about this comment of yours which indeed is backed up in the > MSDN link > >> With CBT callbacks, you're handed notificatation of what's *about* to >> happen, not what just happened as with so many other hooks. > > IOW, a kind of 'Before_WindowAction' event. Yep. > I mentioned previously the > purpose (OP's objective) for the 'event' to be able to do the following > depending on the window state - > > If Excel is minimized H = 0 Else H = xlApp.Hwnd > SetWindowLongA userform-hWnd, GWL_HWNDPARENT, H > > With my previous implementation also had to hide/re-show the form as a kludge > to 'effect' the change. However with this pseudo 'before-event' the > hide/re-show no longer necessary! At least I assume that's the reason, does > that make sense? Yeah, I think it does. You're not reacting anymore. Now you're planning ahead. >> The only really tricky thing here is routing the execution back from the >> callback into the userform. And that's not all that tricky. :-) > > (Snipped -) you have it arranged to for the callback to call a similarly > named procedure in the form. It could be like that but I don't see why the > entire do-stuff can't also be in the actual CBTProc callback function (or in > another function in the normal module), or indeed in a "CApplication class" > as you suggest. I think it certainly could, and maybe should, be directly within the callback. It was an exercise to see where it might be routed. I'm playing with the application class now (created in Workbook_Open and destroyed in Workbook_BeforeClose. It's not very pretty. It seems to blow-up Excel the first time I open it. But if I then re-open Excel, and it opens it as a recovered workbook, all is well, and I can open/close/open/close as many times as I want. > (in your other recent post to me) >> I'll be curious what you guys think of using the CBT hook for these >> purposes. > > I certainly think it has possibilities, even in VBA. There are all sorts of > reasons for wanting Excel's resize event; though perhaps not as a solution > for the OP's particular objective (the maximum one second delay using VBA's > simple built in OnTime method is fine). > > There are also other events I'd like to know about and this might be the way. > One thing I've wanted to be able to do for years is to know when user clicks > a scrollbar in an Excel workbook. That changes the window's 'VisibleRange' > but without triggering any event exposed to Excel. I have tried subclassing > in the past (and that c-dll approach), got results but with the expected > downsides of being risky and resource intensive. Possible, but I don't see it immediately. > Many thanks Karl for posting this CBT hook demo. It's certainly extended my > limited knowledge of what's possible, although not yet my full understanding > which is quite another matter <g> We can go offline, if this bores the group, or to pass samples back and forth? -- ..NET: It's About Trust! http://vfred.mvps.org
From: Karl E. Peterson on 21 Dec 2009 15:03 It happens that Karl E. Peterson formulated : > It happens that Peter T formulated : >> (Snipped -) you have it arranged to for the callback to call a similarly >> named procedure in the form. It could be like that but I don't see why the >> entire do-stuff can't also be in the actual CBTProc callback function (or >> in another function in the normal module), or indeed in a "CApplication >> class" as you suggest. > > I think it certainly could, and maybe should, be directly within the > callback. It was an exercise to see where it might be routed. I'm playing > with the application class now (created in Workbook_Open and destroyed in > Workbook_BeforeClose. It's not very pretty. It seems to blow-up Excel the > first time I open it. But if I then re-open Excel, and it opens it as a > recovered workbook, all is well, and I can open/close/open/close as many > times as I want. This is very weird. I have a spreadsheet that... Opens and sets the hook perfectly fine *if* you: * Double-click it in Explorer, * Close it and reopen it without shutting down Excel, * Let Excel recover it after a crash. Blows up Excel if you: * Open it from the Open taskpane, * Open it from the MRU file list, * Open it with File-Open. Ever heard of such a thing? If I put a Stop before the hook-setting code, and press F5 when that gets hit, it never crashes. -- ..NET: It's About Trust! http://vfred.mvps.org
From: Peter T on 21 Dec 2009 15:56 "Karl E. Peterson" <karl(a)exmvps.org> wrote in message > This is very weird. I have a spreadsheet that... > > Opens and sets the hook perfectly fine *if* you: > * Double-click it in Explorer, > * Close it and reopen it without shutting down Excel, > * Let Excel recover it after a crash. > > Blows up Excel if you: > * Open it from the Open taskpane, > * Open it from the MRU file list, > * Open it with File-Open. > > Ever heard of such a thing? > > If I put a Stop before the hook-setting code, and press F5 when that gets > hit, it never crashes. > I was going to give a longer reply later (I'm off for a little while). In the meantime here's what I put together earlier as a basis for Excel events. I haven't tested your F5 issue, but if this doesn't work try deferring 'StartEvents' with OnTime in the workbook open event ' code in ThisWorkbook module Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) StopEvents End Sub Private Sub Workbook_Open() StartEvents ' or maybe 'Application.OnTime Now, "StartEvents" End Sub ' normal module Option Explicit Private cAppEvents As cAppEvents Sub StartEvents() Set cAppEvents = New cAppEvents Set cAppEvents.mXLApp = Application End Sub Sub StopEvents() Set cAppEvents = Nothing End Sub Public Function CBTProcABC(ByVal nCode As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long Call cAppEvents.DoHookStuff(nCode, wParam, lParam) End Function ' code in cAppEvents Option Explicit Private Declare Function SetWindowsHookEx Lib "user32" _ Alias "SetWindowsHookExA" _ (ByVal idHook As Long, _ ByVal lpfn As Long, _ ByVal hmod As Long, _ ByVal dwThreadId As Long) As Long Private Declare Function UnhookWindowsHookEx Lib "user32" _ (ByVal hHook As Long) As Long Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long Private Const WH_CBT As Long = 5 Private Const HCBT_MOVESIZE = 0 Private Const HCBT_MINMAX = 1 Private Const HCBT_QS = 2 Private Const HCBT_CREATEWND = 3 Private Const HCBT_DESTROYWND = 4 Private Const HCBT_ACTIVATE = 5 Private Const HCBT_CLICKSKIPPED = 6 Private Const HCBT_KEYSKIPPED = 7 Private Const HCBT_SYSCOMMAND = 8 Private Const HCBT_SETFOCUS = 9 Private Const SW_MAXIMIZE = 3 Private Const SW_MINIMIZE = 6 Private Const SW_RESTORE = 9 Public g_hHook As Long Public mlXLhwnd As Long Public WithEvents mXLApp As Excel.Application Private Sub Class_Initialize() mlXLhwnd = Application.hwnd ' xl2002+ g_hHook = SetWindowsHookEx(WH_CBT, _ AddressOf CBTProcABC, _ 0&, _ GetCurrentThreadId()) End Sub Private Sub Class_Terminate() If g_hHook Then Call UnhookWindowsHookEx(g_hHook) g_hHook = 0 End If End Sub Public Sub DoHookStuff(nCode As Long, wParam As Long, lParam As Long) Dim sCode As String Static n As Long If wParam = mlXLhwnd Then n = n + 1 Select Case nCode Case HCBT_MOVESIZE: sCode = "HCBT_MOVESIZE" Case HCBT_MINMAX: sCode = "HCBT_MINMAX" Select Case WordLo(lParam) Case SW_MINIMIZE: sCode = "HCBT_MOVESIZE, SW_MINIMIZE" Case SW_MAXIMIZE: sCode = "HCBT_MOVESIZE, SW_MAXIMIZE" Case SW_RESTORE: sCode = "HCBT_MOVESIZE, SW_RESTORE" End Select Case HCBT_QS: sCode = "HCBT_QS" Case HCBT_CREATEWND: sCode = "HCBT_CREATEWND" Case HCBT_DESTROYWND: sCode = "HCBT_DESTROYWND" Case HCBT_ACTIVATE: sCode = "HCBT_ACTIVATE" Case HCBT_CLICKSKIPPED: sCode = "HCBT_CLICKSKIPPED" Case HCBT_KEYSKIPPED: sCode = "HCBT_KEYSKIPPED" Case HCBT_SYSCOMMAND: sCode = "HCBT_SYSCOMMAND" Case HCBT_SETFOCUS: sCode = "HCBT_SETFOCUS" Case Else: sCode = "Else" End Select Range("A1:C1").Offset(n) = Array(sCode, wParam, lParam) End If End Sub Private Function WordLo(ByVal LongIn As Long) As Integer ' Low word retrieved by masking off high word. ' If low word is too large, twiddle sign bit. If (LongIn And &HFFFF&) > &H7FFF Then WordLo = (LongIn And &HFFFF&) - &H10000 Else WordLo = LongIn And &HFFFF& End If End Function ' ' other application level events Private Sub mXLApp_NewWorkbook(ByVal Wb As Workbook) End Sub Private Sub mXLApp_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window) End Sub For testing simply do Min/Max/Restore Excel. Delete the 'debug' cells as & when. Regards, Peter T PS I'm happy to go off-line if this VBA stuff is OT for this group, my address is in the Reply-To
From: RB Smissaert on 21 Dec 2009 17:07
> PS I'm happy to go off-line if this VBA stuff is OT for this group, my Can I suggest to either keep it going here (I guess quite a few members of this forum find this interesting) or to move it to excel.programming? RBS |