1

I need Mouse hook in userform:

Private Sub UserForm_Initialize()
    SetHookInOtherInstance
End Sub

Private Sub UserForm_Terminate()
    RemoveHookInOtherInstance
End Sub

and it work fine in the same instance, but I want to use another to prevent Excel from crashing while debugging etc (that's the best idea from this question ) and just nothing happens.

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr
Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As LongPtr, ByVal lpFn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As LongPtr) As LongPtr
Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As LongPtr, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As LongPtr

Private i As Long
Private HookHandle As LongPtr
Private ExcelOtherInstance As Excel.Application

Private Const WH_MOUSE = 7
Private Const HC_ACTION = 0




Public Sub SetHookInOtherInstance()
    Set ExcelOtherInstance = New Excel.Application
    ExcelOtherInstance.Visible = True
    ExcelOtherInstance.Workbooks.Add
    ExcelOtherInstance.Workbooks.Open ThisWorkbook.FullName, False, True
    ExcelOtherInstance.Run "SetHook", GetCurrentThreadId
End Sub

Public Sub RemoveHookInOtherInstance()
    On Error Resume Next
    ExcelOtherInstance.Run "RemoveHook"
End Sub

Public Sub RemoveHook()
    On Error Resume Next
    UnhookWindowsHookEx HookHandle
End Sub

Public Sub SetHook(ThreadId As LongPtr)
    HookHandle = SetWindowsHookEx(WH_MOUSE, AddressOf NewProc, 0, ThreadId)
End Sub

Public Function NewProc(ByVal lngCode As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    If lngCode = HC_ACTION Then
        Application.StatusBar = CStr(i)
        i = i + 1
    End If
    NewProc = CallNextHookEx(HookHandle, lngCode, wParam, lParam)
    Exit Function
End Function

Any ideas?

Community
  • 1
  • 1
ingwarus
  • 413
  • 2
  • 11
  • `GetCurrentThreadID` is going to return the ID of the thread you are running the original code in. I suggest you change the `SetHook` routine to internally use `GetCurrentThreadID` – Rory Apr 19 '18 at 10:01
  • its not a mistake! I want to have hook, made by other instance but working on original one. So when I stop the code manually while debugging then other instance will crash not original. – ingwarus Apr 19 '18 at 10:08
  • Oh I see. I don’t think that will work as you’re trying to call callback code in one instance from a different instance. – Rory Apr 19 '18 at 10:12
  • callback code is in ExcelOtherInstance so that shouldn't be a problem – ingwarus Apr 19 '18 at 10:22
  • But your hook and callback are in separate threads, which I don't think will work. In fact, I'm not even sure that you can set a hook on a different thread. – Rory Apr 19 '18 at 10:23
  • my hook and callback functions are in the other instance (i've opened the same workbook with callback function too). The only problem is that hook should work on original thread, but it should be normal. i thought that hooks are made for that. – ingwarus Apr 19 '18 at 10:28
  • You're trying to set a hook on a separate thread, which I don't think will work. If it did, you still couldn't use a VBA proc in a different thread as the callback - it would require a dll as far as I know. – Rory Apr 19 '18 at 10:30
  • [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). VBA isn't really the place to be doing this. What are you actually trying to accomplish with the hook? Are you doing something with the hook that can't be handled any other way? – theB Apr 19 '18 at 12:47
  • 1
    I've linked my previous question. I need mouse hook for many things (listbox scrolling etc.) Please, tell me why I shouldn't use hooks in vba? – ingwarus Apr 20 '18 at 09:13

0 Answers0