0

I have a Excel-VBA-Macro checking technical parameters in a while-loop that ends at a fixed point in time. To stop the macro before this point in time with a keyboard-input, I tried the Application.OnKey command in vba. The problem structure is illustrated by the following code example:

Sub EndlessLoop()
    Application.OnKey "{ESC}", "QuitLoop"
    Do While True
        ' Code for the check of technical parameters
    Loop
End Sub

Sub QuitLoop()
    ' Code for final operations before quit
End Sub

Execution of EndlessLoop keeps running endless after pressing ESC-key. Microsoft says, that OnKey ist actually not working (https://learn.microsoft.com/de-de/office/vba/api/excel.application.onkey): There is no way to currently detect the Command key in recent versions of Office VBA. Microsoft is aware of this and is looking into it.

Does anyone know a workaround for this problem?

As described, I tried some code examples with the result described above.

Task manager is no solution, because it just kills the appliction without executing the final operations before quit.

Public Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer does not work as well and ends with an error message.

GWD
  • 3,081
  • 14
  • 30
GhostRider
  • 1
  • 1
  • GetAsyncKeyState causes an error message? - Are we talking about `Err.LastDLLError` - because if VBA itself throws the error message, the problem is not with the DLL but with your implementation. - If your code does not produce inputs, you could maybe use `GetLastInputInfo` (https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-getlastinputinfo) – Lord-JulianXLII May 19 '23 at 12:57
  • 1
    The "not working" you mentioned and linked is talking about the command key: "Command * (asterisk) Only applies to Mac; may only work on Excel 2011 for Mac and not later versions." - so everything should work fine. I'm assuming the problem lies with your implementation - To start with: you are never actually Exiting your infinite loop. It just keeps running forever. How is it supposed to be stopped? - You should also be aware, that VBA is single threaded - meaning a your infinite loop blocks any other code from being executed (until it's done, which it is never) – Lord-JulianXLII May 19 '23 at 13:09
  • Look at [this answer](https://stackoverflow.com/a/74524618/12287457) for an example of how `GetAsyncKeyState` can be used to exit an infinite loop. – GWD May 19 '23 at 20:39

0 Answers0