I'm creating a game in VBA and I don't know how to make Application.OnKey work. Why do I need it? As you can guess, I want an object in my game move when a player presses a key.
A game is basically an infinite loop in a subroutine RunGame(). If I put Application.OnKey as a workbook event without running RunGame(), then it works, so my keyboard and Application.OnKey can understand each other. However, when I run RunGame(), then the key listener doesn't work. I'm sure it's because I put it in a wrong place. The point is I tried it everywhere and have run out of ideas! Could you help me with it? I'm pasting a reproducible example here.
Module "main":
Option Explicit
Sub RunGame()
Call Application.OnKey("{Down}", "OnDownKey")
Do While True
'
'
'Some game's stuff here
'
'
Call WasteTime(1)
Loop
End Sub
Module "utils":
Option Explicit
Sub WasteTime(TimeToWaste As Single)
Dim i As Long
For i = 1 To TimeToWaste * 1000
VBA.DoEvents
i = i
Next
End Sub
Sub OnDownKey()
MsgBox "You pressed down key"
End Sub
By the way, if there's anything to change in this little example's logic, I'll appreciate your tips.
I've got to get to a solution, so I'll respond to my own question. Tips from @Peyter helped me find the way.
First of all, an infinite loop seems absolutely OK to me, I feel that it works more stably than Application.OnTime. There's also Chip Pearson's method, but I didn't find the reason of using it instead of the infinite loop (the result seemed the same).
Second of all, I left Aplication.OnKey be too. I used a solution from this site. This is some Windows function and works very well. There is one issue, though. From now on, a pressed key will mean to Excel both an extra activity and a normal activity. For example, when I press down arrow, I choose a cell below a cell I'm in. I wanted to turn off the regular behaviour for arrows and letters, but I didn't find any pretty way to do it. So I used an ugly one ;) All in all my functions are as shown below.
Module main:
Option Explicit
Sub RunGame()
Do While True
'
'
'Some game's stuff here
'
'
Call WasteTime(1)
IsKeyPressed
Loop
End Sub
Module utils:
Option Explicit
Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Long
Private Const VK_DOWN = &H28 'DOWN ARROW key
Sub WasteTime(TimeToWaste As Single)
Dim i As Long
For i = 1 To TimeToWaste * 1000
VBA.DoEvents
Next
End Sub
Sub IsKeyPressed()
If GetAsyncKeyState(VK_DOWN) <> 0 Then
OnDownKey
End If
End Sub
Sub OnDownKey()
MsgBox "You pressed down key"
End Sub
It works. I still don't know where to put VBA.DoEvents
, but in the loop it works well. The line Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Long
says hello to a Windows function which now will work for us. Private Const VK_DOWN = &H28 'DOWN ARROW key
is a definition of down arrow key which now will be called VK_DOWN. IsKeyPressed()
checks if the user pressed some key. If GetAsyncKeyState(VK_DOWN) <> 0
checks if it's VK_DOWN which was pressed. Surprisingly for me, this Windows functions works very well with VBA, there are no lags!
Now, what did I do to block the regular behaviour of pressing arrows or letters? I chose one cell in which I wanted to stay - in my example it's AG1. Then I used worksheet events (double click on a sheet on the left in VBA editor and you'll open it). I used two functionalities there:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[AG1].Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AG$1" Then
Application.EnableEvents = False
[AG1].Clear
[AG1].Select
Application.EnableEvents = True
RunGame
End If
End Sub
The first one makes it impossible to escape from AG1, because each time you change the selection, VBA selects AG1 for you.
The second one fires when you try to change value of a cell. The subroutine will clear the cell, select it and then start RunGame(). Why is the last thing needed? Because when the user starts typing something, the infinite loop pauses and when the user hits Enter, the infinite loop doesn't continue. I don't really know if it's paused or finished. But hopefully I will make do without using letter keys, so I don't have to worry.