Where should I put VBA.DoEvents and Application.OnKey if I use an infinite loop?

Question

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.

Answer 1

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.

Answer 2

Why are you using an infinite loop? Gah.

Instead use VBA's WithEvents model to wait for input.

And use Application.Ontime to call a macro, which calls itself again at a set interval. If you Infinite loop, you never destroy objects in that sub, and VBA will run out of memory, crashing the game. When you have even like a side scroller, that moves the frame or something, and you use AOT, then instead of one fluid big sub, you get 1 frame, then 2 frame, and you can change the trajectory of the AOT calls, by using WithEvents to change logic in your AOT calls so that it branches to a different AOT macro.