Search code examples
excelvbagame-engine

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


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.


Solution

  • 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.