-1

I am writing a few codes to create my worm game inside excel and I have bumped into this problem. Every time I activate the macro, it tells me "End Sub Expected" an highlights over the "Sub Movement". Could somebody explain why this is happening and how I can fix it?

Code:

Sub Movement()

Private Sub Text1_KeyDown(KeyCode As Integer, shift As Integer)
Dim Wait As Integer

For Counter = 1 To 1E+43
If KeyCode = vbKeyA Then
ActiveCell.Interior.ColorIndex = 1
ActiveCell.Offset(0, -1).Select
ActiveCell.Interior.ColorIndex = 3
Next Counter

End Sub
End Sub

Thank

~Hydro

Hydrotronics
  • 5
  • 3

1 Answers1

1

You literally have one sub inside another. VBA does not work like this.

Perhaps you meant to do something like:

Private Sub Text1_KeyDown(KeyCode As Integer, shift As Integer)
    Movement KeyCode
End Sub

Sub Movement(KeyCode As Integer)
    Dim Wait As Integer
    For Counter = 1 To 1E+43
        If KeyCode = vbKeyA Then
            ActiveCell.Interior.ColorIndex = 1
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Interior.ColorIndex = 3
        End If
    Next Counter
End Sub

Note: I don't see the variable Wait being utilized. Perhaps you have not gotten that far...

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • What would the Wait be used for? I am a little new to this stuff so please explain? And what is the KeyCode doing? Is that refering to the other macro? Would the wait be used for making sure ur excel doesn't crash or something? I did get this code off the internet and i think i know what it is doing... but i didn't know what the Wait was for... – Hydrotronics Oct 17 '15 at 18:13
  • ??? Umm you tell me. It is your code. You defined `Wait` with a `Dim` statement. I was only pointing out that after defining it you have not used it yet. I really do not have an idea of what you intended to do with it. `KeyCode` on the other hand is a built-in parameter of the Text1_KeyDown() event procedure. It represent the code number of the currently depressed key on the keyboard. YOUR CODE checks to see if that is the "A" key. – Excel Hero Oct 17 '15 at 18:17
  • 1
    You got it off the internet? Good. Then you won't mind if I say it is terrible code... – Excel Hero Oct 17 '15 at 18:21
  • Oh right. Well, I have just taken the code and run it and my excel crashed XD. is there like a sleep function? or a pause or a delay? because i think that i could possibly prevent excel from dieing by putting a code saying "sleep(0.02)" (That Would Be In Lua, I Think) or something along those lines. – Hydrotronics Oct 17 '15 at 18:26
  • No i wouldn't mind you saying that it is terrible code XD. Unless you were talking about using a loop to check for a keypress to create the cell movement, cuz that was my idea, i just got different bits off the internet and stuck them together, hoping that it works XD – Hydrotronics Oct 17 '15 at 18:26
  • Look here: http://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time – Excel Hero Oct 17 '15 at 18:28
  • 1
    Please accept this question as it directly resolves the "End Sub Expected" compile error. You do that by clicking the large check mark next to the answer. – Excel Hero Oct 17 '15 at 18:47
  • Apparently it's not large enough.. have an upvote ;-) – Mathieu Guindon Oct 17 '15 at 19:26