Laggy and not working properly

[phobo3s]

i am using windows 10 64 bit and Ms Office 365 Version 2302

i have tried your example file but it is very laggy. even not working. 1 scroll takes 10 seconds and even then there is no scroll happening. There must be some kind of malfunction on my computer but i can't find it. is there a way to debug on analyze the situation. i understand that VBE is out of the options when we using this module. Can you help me?

[cristianbuse]

Hi @phobo3s ,

Indeed, code breaks would disable the mouse hook in order to prevent crashes. So, stepping through code is not an option while the hook is on.

You could add a few Debug.Print statements in the MouseProc method and turn some of the logic off to see what happens.

For example, replace the MouseProc method with this shorter version:

#If Mac Then
#Else
#If VBA7 Then
Private Function MouseProc(ByVal ncode As Long _
                         , ByVal wParam As Long _
                         , ByVal lParam As LongPtr) As LongPtr
#Else
Private Function MouseProc(ByVal ncode As Long _
                         , ByVal wParam As Long _
                         , ByVal lParam As Long) As Long
#End If
    Dim mStruct As MOUSEHOOKSTRUCTEX
    If IsVBEActive Then GoTo Unhook
    RemoveDestroyedForms
    If m_hWndAllForms.Count = 0 Then GoTo Unhook
    If CBool(IsWindowEnabled(m_hWndMainOwner)) Then GoTo Unhook
    If m_lastHoveredControl Is Nothing Then GoTo NextHook
    If Not CBool(IsWindowEnabled(m_lastHoveredControl.FormHandle)) Then GoTo NextHook
    CopyMemory mStruct, ByVal lParam, LenB(mStruct)
    If ncode = HC_ACTION Then
        If wParam = WM_MOUSEWHEEL Or wParam = WM_MOUSEHWHEEL Then
            Debug.Print mStruct.mouseData, Timer
        End If
    End If
    '
NextHook:
    MouseProc = CallNextHookEx(0, ncode, wParam, ByVal lParam)
Exit Function
Unhook:
    UnHookMouse
    GoTo NextHook
End Function
#End If

and then start the demo while having the VBE window side by side. You should be seeing the scrolls generate lines in the Immediate window like so:

If there is still lag then, the issue is probably caused by a delay in callback being called from outside. Please let me know how it goes.

[phobo3s]

thank you for your response. i have made the changes and more i added few debug.print statements the code looks like that now;

#If Mac Then
#Else
#If VBA7 Then
Private Function MouseProc(ByVal ncode As Long _
                         , ByVal wParam As Long _
                         , ByVal lParam As LongPtr) As LongPtr
#Else
Private Function MouseProc(ByVal ncode As Long _
                         , ByVal wParam As Long _
                         , ByVal lParam As Long) As Long
#End If
    Dim mStruct As MOUSEHOOKSTRUCTEX
    If IsVBEActive Then GoTo Unhook
    RemoveDestroyedForms
    If m_hWndAllForms.Count = 0 Then GoTo Unhook
    Debug.Print m_hWndAllForms.Count
    If CBool(IsWindowEnabled(m_hWndMainOwner)) Then GoTo Unhook
    Debug.Print "checkpoint0"
    If m_lastHoveredControl Is Nothing Then GoTo NextHook
    Debug.Print "checkpoint1"
    If Not CBool(IsWindowEnabled(m_lastHoveredControl.FormHandle)) Then GoTo NextHook
    Debug.Print "checkpoint2"
    CopyMemory mStruct, ByVal lParam, LenB(mStruct)
    Debug.Print "checkpoint3"
    If ncode = HC_ACTION Then
        Debug.Print "checkpoint4"
        If wParam = WM_MOUSEWHEEL Or wParam = WM_MOUSEHWHEEL Then
            Debug.Print "checkpoint5"
            Debug.Print mStruct.mouseData, Timer
        End If
    End If
    '
NextHook:
    Debug.Print "checkpoint-next"
    MouseProc = CallNextHookEx(0, ncode, wParam, ByVal lParam)
Exit Function
Unhook:
    UnHookMouse
    GoTo NextHook
End Function
#End If

and i get the following debug prints.

 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint4
checkpoint5
-78643200      50368,45 
checkpoint-next
 1 ' i think theese are normal mouse moves
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint-next
 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint-next
 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint-next
 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint-next
 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint-next
 1 
checkpoint0
checkpoint1
checkpoint2
checkpoint3
checkpoint4
checkpoint5
-47185920      50373,59 
checkpoint-next

it seems like normal things are happening but evet dragging the userform takes 3 seconds. it makes userform generally slow. Is this routine registers on my mouse and can it be stacking?

[cristianbuse]

@phobo3s ,

The routine add a mouse hook in the hook chain of the system. There might be another hook causing the issue.

Are you by any chance using any 3rd party mouse software like X-Mouse Button Control. If yes, see this workaround.

[phobo3s]

no i am not using a software for mouse. where can i see the hook chain of the system? because accidently i have crashed your application a few times. can it be the issue?

[cristianbuse]

The hook chain is managed by the operating system. My hook is attached to the Application main thread so if you crashed it then the hook got removed by the system anyway.

If you don't have any mouse software then the next thing you could try is to run Windows in Safe Mode and then try my demo. If if works fine then there must be some installed software interfering with it.

[phobo3s]

i debugged everwhere and find out this line is creating the lag CopyMemory mStruct, ByVal lParam, LenB(mStruct) if i comment this out, Debug.Print mStruct.mouseData, Timer this part runs like a river. But program not running as well :) why i am copying my memory so slow? is that still can be interference?

[phobo3s]

YES! At last! i used your library and problem resolved!! LibMemory.MemCopy VarPtr(mStruct), ByVal lParam, LenB(mStruct) used it with your memory library. problem solved. there must be some kind of "getting slow as more used" kind of thing. i am feeling like wizard apprentice. thank you for your patience and help. i learned a lot.

[cristianbuse]

@phobo3s ,

Very interesting. All API calls are slow on VBA7 and of course that's why I even created the LibMemory library because CopyMemory is about 650 times slower on my computer. However, I did not expect single calls within the MouseProc method to be so slow.

Thank you for letting me know!

I did not even have the CopyMemory initially as the lParam parameter was declared directly As MOUSEHOOKSTRUCTEX but for whatever reason that wasn't working properly in newer versions of Office.

Will make some amends to this repo! Thanks again!

[phobo3s]

I thank you for learning opportunity. i wish some day i can be nearly as proficient as you.

[cristianbuse]

Hi @phobo3s ,

I just pushed a version without CopyMemory. Would you be able to test and see if it works? It does not require the memory library either. Thanks!

[phobo3s]

I tested, it works perfectly. Why there is a copy memory line before then? :) It is beyond my comprehension but i will try to understand it. thanks again.

[cristianbuse]

Why there is a copy memory line before then? :)

Well, because it was crashing a year ago in a certain Office 365 version but it seems that Microsoft fixed the issue.

It is beyond my comprehension but i will try to understand it. thanks again.

I will try to explain. Consider the following code:

Option Explicit

Private Type Point
    x As Double
    y As Double
    z As Double
End Type

Sub Main()
    Dim p As Point
    With p
        .x = 100
        .y = 200
        .z = 50
    End With
    PrintPoint p
End Sub

Sub PrintPoint(ByRef p As Point)
    Debug.Print "X:", p.x
    Debug.Print "Y:", p.y
    Debug.Print "Z:", p.z
End Sub

There is a custom structure called Point. In the Main method, we declare a variable p of type Point and we fill in the data, then we call another method called PrintPoint to print the details to the Immediate Window. Note that a custom type/structure can only be passed ByRef and cannot be coerced into a Variant meaning that they cannot be added to a Collection or an array of type Variant. I'm sure you're following so far.

Back to this mouse scroll repo. The mouse hook maintained by the system tries to call the MouseProc method. If we were to make an analogy with our example above, the system call would be the Main method which exists outside of VBA and the call is made through the COM machinery and the PrintPoint is called (much like MouseProc is called).

For whatever reason, when called like this, the Application was crashing in that specific version of Office and I made this commit in July 2022.

Instead of passing the structure, I passed just the pointer (memory address) of the structure and then proceeded to copy memory. We can do the same in our example, like this:

Option Explicit

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)

Private Type Point
    x As Double
    y As Double
    z As Double
End Type

Sub Main()
    Dim p As Point
    With p
        .x = 100
        .y = 200
        .z = 50
    End With
    PrintPoint VarPtr(p) 'We're only passing the address of the structure
End Sub

Sub PrintPoint(ByVal ptr As LongPtr)
    Dim p As Point

    CopyMemory p, ByVal ptr, LenB(p)

    Debug.Print "X:", p.x
    Debug.Print "Y:", p.y
    Debug.Print "Z:", p.z
End Sub

As you can see, the result is the same but instead of letting VBA do the memory manipulation, we do it ourselves with CopyMemory.

Hope this is helpful in understanding what was going on!

Thanks again for testing and for the feedback!

[phobo3s]

wow. thank you for a lesson like explanation. i understand it very clear now. API usage and memory management has allways been like a mystery to me. Untill now. can you recommend me some kind of source to learn API?. Again thank you.

[cristianbuse]

Dear @phobo3s ,

When you actually need an API, the Microsoft documentation is pretty thorough. For example: CopyMemory. A good API documentation will always explain the parameters, the return value, special cases and notes. Same is applicable to MAC like this iconv API.

I would not suggest you learn any APIs unless you use them. Using an API is like using any other external library, like RegEx or WinHttp.

You will learn much more by trying to solve specific problems because it will lead you to read and learn about whatever is needed.

If you're looking to learn about general VBA, then there any many resources out there. A good place to start would be awesome-vba. There are links to articles but even better, there are links to other people's repositories and you can learn a lot by trying to re-create some of that functionality or even better if you contribute yourself to some of those repos.

[phobo3s]

I understand, will try to do more different stuff. Again thank you for your time and help. Have a good day :)

[cristianbuse]

Thanks, you too!