6

I'm using setWindowHookEx and it works fine:

Private HookHandle As LongPtr

Sub RemoveHook()
    UnhookWindowsHookEx HookHandle
End Sub

Sub SetHook()
    Dim lThreadID As LongPtr
    lThreadID = GetCurrentThreadId
    HookHandle = SetWindowsHookEx(WH_MOUSE, AddressOf foo, 0, lThreadID)
End Sub

Public Function foo(ByVal nCode As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    foo = CallNextHookEx(0, nCode , wParam, lParam)
End Function

My problem is: When any unhandled errors (from any other macro) occures, and I have to end macro, Excel crashes and dies. (I'm guessing, that hook is still alive, but foo function is no longer in memory)

Is there any way to prevent Excel crash?

I use it from many modules, and I don't want make error handling everywhere.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ingwarus
  • 413
  • 2
  • 11
  • In my experience 95% of Excel crashes are due to memory issues, which are due to objects and other similar items being mishandled. I haven't worked with hooks but is there, by chance, a way that you are supposed to "release" or "clear" or "close" them when finished with them? If so, build error handling that does that as soon as an error occurs. Also, don't use `On Error Resume Next` and always make the first line of your module `Option Explicit`. – ashleedawg Mar 19 '18 at 12:41
  • ...also, your first line says "it works fine"... but what's causing the errors? – ashleedawg Mar 19 '18 at 12:42
  • @ashleedawg Hook works fine, but when I get error anywhere else, Excel crashes and dies. – ingwarus Mar 19 '18 at 13:02
  • It works fine, but what are you supposed to do with it when you're finished with it? Set hook = nothing ? hook.close ? hook.clear? (I don't know that answer; I'm asking you and suggesting that there may be an "end process" for the hook that you haven't considered (check documentation?) and if so, you should have an `On Error Goto...` that takes of of that "cleanup". – ashleedawg Mar 19 '18 at 13:04
  • Also, see similar questions (albeit different languages [here](https://stackoverflow.com/a/3124266/8112776) and [here](https://stackoverflow.com/a/17813755/8112776) and [here](https://www.experts-exchange.com/questions/10320393/SetWindowsHookEx-keep-crashing-the-program-that-I'm-hooking.html) for clues about crashes and other unexpected behaviours. – ashleedawg Mar 19 '18 at 13:08
  • There is a way to "end process" - "(...)When any unhandled errors (from any other macro) occures, and I have to end macro(...)" – ingwarus Mar 19 '18 at 13:11
  • Is that a question? Do you need to know how to run code if an error occurs? ( I can give an example if you like.) – ashleedawg Mar 19 '18 at 13:12
  • no it's not. it's a way to "end process"... I can't handle hook errors in normal way after that and that is what i'm asking for. – ingwarus Mar 19 '18 at 13:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167090/discussion-between-ingwarus-and-ashleedawg). – ingwarus Mar 19 '18 at 13:17
  • I thought you said the hook wasn't causing the errors? I'll post a simple error handling procedure as an anyway, maybe you should try adding it to your code and see what happens when an error occurs with that in place. – ashleedawg Mar 19 '18 at 13:23
  • 3
    Install the hook in a second instance of Excel (New Excel.Application) so it won't be interrupted by the code you are debugging. – Florent B. Mar 19 '18 at 15:49
  • 1
    It sounds to me like you need to add another hook to intercept the VBA Error.Raise Event...take a look at [this](https://www.mrexcel.com/forum/excel-questions/1024295-intercepting-resetting-vba-editor-well-unhandled-errors-safe-subclassing.html). – Profex Jul 25 '18 at 13:33

0 Answers0