PDA

View Full Version : Can you intercept a keystroke during a long loop



TheAntiGates
01-11-2012, 01:10 PM
I have a long slow loop and I'd like to set some particular code in motion if a particular keystroke was pressed.

Keypress or Keydown look like they're for forms only in VBA - correct me if I'm wrong. I can set up application.onkey okay, but it doesn't appear to be respected (while code is "running"). I inserted a doevents in the loop but still no go. By the way, I used Control-1 ("^1") which should be acceptable even though it is preassigned to open the options/format dialog.

The only two ways I've interrupted code loops is by escape key (works some times but not all times; I'm curious why) and by control-break. I'm seeking something a little more robust, so I can have the code clean up objects and reset Application.Calculation, EnableEvents, etc.

XL03 on Win XP (of course! XL07 is the worst redesign in scientific history!!)

Kenneth Hobs
01-11-2012, 03:16 PM
Looping and looking for a key or combination of keys other than the OnKey combinations is cpu intensive. As such, it can fail to capture the keys depending on what all your loop is doing.

Look into the API function GetKeyState(). Here is one thread where I showed how to use it. http://www.vbaexpress.com/forum/showthread.php?t=38366

TheAntiGates
01-11-2012, 03:57 PM
Thanks and yes I feared the CPU hit. I'll consider that solution.
http://www.autohotkey.com/docs/commands/GetKeyState.htm
has a good discussion on GetKeyState and using Sleep as you may have been alluding.

As you mentioned about what might be in the loop, e.g. within a For Each c... (and maybe With c also),
If InStr(1, UCase(CStr(c.Value)), mySearchStr) = 0
or If c.Interior.ColorIndex = ...
or If c.Font.Strikethrough

and these might be applied to UsedRange. None of what I would call I/O exists; I just have operations and tests on cells on Activesheet.

TheAntiGates
01-11-2012, 04:07 PM
Regarding the GetKeyState approach:
http://blogs.msdn.com/b/oldnewthing/archive/2004/11/30/272262.aspx
explains that GetAsyncKeyState (http://msdn.microsoft.com/library/en-us/winui/winui/windowsuserinterface/userinput/keyboardinput/keyboardinputreference/keyboardinputfunctions/getasynckeystate.asp) is the better answer here. Perhaps that would erase the concern on timing and "missing" strokes.

Kenneth Hobs
01-11-2012, 06:12 PM
I have used both. You should use what works best for you.

If you want to read about how we used those APIs in WordPerfect, these threads might help. VBA code would be similar. There are plenty of VBA code examples as you found though.
http://www.wpuniverse.com/vb/printthread.php?t=12821
http://www.wpuniverse.com/vb/printthread.php?t=29268