Why does this VBA code break Excel?

Question

I am trying to attach a hammer image onto the mouse cursor on Excel. It works fine without the Do Loop, that is it moves the image close to the mouse cursor when I run the macro using a button on sheet1. The problem starts when I try to make the image follow the cursor on-the-fly, using a Do Loop. In the past, I was able to make that happen without any errors, but I did not save that Excel workbook. Now, I am trying to do it again and it crashes Excel every time. I might get lucky and get a "Left method of object failed" or a "Top method of object failed" error, but that does not really help. Here is the VBA code:

Option Explicit

Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Type POINTAPI
    x As Long
    y As Long
End Type

Sub MoveHammer()

    Dim lngStatus As Long
    Dim typWhere As POINTAPI
    Dim activate As Boolean
    activate = True

    Do While activate = True
    lngStatus = GetCursorPos(typWhere)

    Sheet1.Shapes("hammer").Left = 0.75 * (typWhere.x - 77)
    Sheet1.Shapes("hammer").Top = 0.75 * (typWhere.y - 274)
    Loop
End Sub

"hammer" is the name of the picture object that I have inserted. Thanks.

Why are you trying to kludge this yourself instead of just using SetCursor(). You seem to be aware of the WinAPI, because you're using it for GetCursorPos(). — Ken White
– Ken White, Commented May 21, 2018 at 12:32
@JohnColeman OMG YES! How can I be so blind and naive? Of course I would need a DoEvents statement. Thank you so much! Type this as a response so I can mark it. — SteliosM
– SteliosM, Commented May 21, 2018 at 13:00
@KenWhite the reason is because I do not want to set the cursor to a certain position. I simply want to have that image follow the cursor. So I am grabbing the position of the cursor with GetCursorPos(). — SteliosM
– SteliosM, Commented May 21, 2018 at 13:02
I didn't say SetCursorPos(). I mentioned SetCursor(). They don't do the same things. :-) — Ken White
– Ken White, Commented May 21, 2018 at 16:37

John Coleman · Accepted Answer · 2018-05-21 13:03:19Z

1

Since VBA lacks multithreading, in order to get responsiveness while inside of the loop you will need a DoEvents inside of the body of the loop.

answered May 21, 2018 at 13:03

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does this VBA code break Excel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related