VBA4Play Part 1: Movement and Collision Detection

This is the first part of a new series called VBA4Play. The goal here is to use Arena.Xlsm as a learning tool to get more people to have fun with VBA. Ultimately, what I’d like to do is create new games together with you using the tools that I am going to be writing about in VBA4Play. To that end I want people to come up with their own ideas for games and put them in the comments or experiment with VBA and write some code for a future game. If your code becomes featured on VBA4Play, I may ask that you guest write the article in a step-by-step fashion so that everyone can follow along. I’m going to try to make this feature accessible to beginners so I apologize in advance to those of you who already have coding experience. Hopefully both beginners and advanced VBA users can learn from each other and build something amazing together. To that end, I am only going to guide the discussion, I would like this to be a community project so the power to see where this thing goes is in your hands.

Okay, now let’s get started!

Part 1: Movement and Collision Detection

Step 1: Getting Started

VBA is the back-end programming language used for excel macros. This is how Arena.Xlsm was coded. To access the visual basic interface, you will need to enable the developer ribbon. Here is a handy guide: http://office.microsoft.com/en-ca/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx Note that it may be different depending on which version of Excel you are using.

Once you have the developer ribbon on, click Visual Basic to access the interface.

Step 2: Basic Coding

Click on “ThisWorkbook” in the Microsoft Excel Objects. Automatically you should see a new subroutine that looks like this:

Private Sub Workbook_Open()

End Sub

This is code that is run as soon as the workbook is open. In Arena.Xlsm, this is where it prompts as to whether or not you want to start a new game or load a saved game.

For our purposes we just need this to spawn the player in a pre-identified location so that we know where the player is. Make the subroutine look like this:

Private Sub Workbook_Open()

Move.NewGame

End Sub

This will call the subroutine NewGame in the Module “Move”. Lets make that now!

Click insert->Module

This will create a new module called “Module1”. Lets rename this to “Move” using the properties window. This module is where we will have the rest of our code for this part. Lets now create the NewGame subroutine that we called earlier.

Sub NewGame()

End Sub

If you want to, you can verify that this is working by creating a Hello World! application by adding the following line of code into the NewGame subroutine.

MsgBox(“Hello World!”)

Now if you save, close, and reopen the workbook you will see this:

HelloWorld

Congratulations! You just made Excel do something that has nothing to do with spreadsheets! 😀

Step 3: Game Setup

We’re going to want the top of our module to look like this:

Option Explicit
Option Base 1

Public PlayerPositionRow As Integer
Public PlayerPositionColumn As Integer
Public PlayerCell As String

The Option Explicit simply requires us to declare variables as a type rather than have excel guess at what our variables are. The Option Base 1 makes excel count from 1 rather than 0… basically it just makes everything make a lot more intuitive sense when you are reading code. The 3 “Public” statements are our variable declarations. Public means that those variables are accessible anywhere in the workbook. A variable is a piece of data that your computer keeps in storage. These variables will allow us to keep track of where the player is as they move around. Integer variables are positive or negative whole numbers (within the range of -32,768 and 32,767) and String variables store text.

Now we’ll want to revisit our NewGame subroutine and use it to create the starting conditions:

Sub NewGame()
Range(“N12”).Select
ActiveCell.FormulaR1C1 = “:)”
PlayerPositionRow = 12
PlayerPositionColumn = 14
PlayerCell = Cells(PlayerPositionRow, PlayerPositionColumn).Address
End Sub

This will spawn our player in cell N12 when the worksheet is opened. We are then assigning that location to the variables: PlayerPositionRow, PlayerPositionColumn, and PlayerCell.

Step 4: Moving and Collision Detection

For this part we’re actually going to need 4 subroutines (or 8 – depending on whether or not you want the player to be able to move diagonally). Each subroutine will need to be a variant of this:

Sub MoveLeft()

Range(PlayerCell).Activate
If (ActiveCell.Offset(0, -1).Range(“A1”).Formula = “XX”) Then

Else
Selection.ClearContents
ActiveCell.Offset(0, -1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “:)”
PlayerPositionColumn = PlayerPositionColumn – 1
PlayerCell = Cells(PlayerPositionRow, PlayerPositionColumn).Address

End If

End Sub

What this is doing is activating what we know to be the current player’s cell then checking if the location we want to move to is a valid spot. If it is a wall “XX” then nothing happens. If it’s not a wall, then it clears the current selection, goes to the location that we want to move to, selects it, puts a new smiley in there and updates our variables so that we can keep track of where the player currently is. The offset function is key to this working properly. The parameters for offset are (Row, Column) where the current cell (the player) is (0,0). This means that since we want to move left we need to look at offset(0,-1) as it is a column less than the one that we are currently on. You’ll want to repeat this process for the remaining directions:

Sub MoveRight()

Range(PlayerCell).Activate
If (ActiveCell.Offset(0, 1).Range(“A1”).Formula = “XX”) Then

Else
Selection.ClearContents
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “:)”
PlayerPositionColumn = PlayerPositionColumn + 1
PlayerCell = Cells(PlayerPositionRow, PlayerPositionColumn).Address

End If
End Sub

Sub MoveDown()

Range(PlayerCell).Activate
If (ActiveCell.Offset(1, 0).Range(“A1”).Formula = “XX”) Then

Else
Selection.ClearContents
ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “:)”
PlayerPositionRow = PlayerPositionRow + 1
PlayerCell = Cells(PlayerPositionRow, PlayerPositionColumn).Address

End If

End Sub

Sub MoveUp()

Range(PlayerCell).Activate
If (ActiveCell.Offset(-1, 0).Range(“A1”).Formula = “XX”) Then

Else
Selection.ClearContents
ActiveCell.Offset(-1, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “:)”
PlayerPositionRow = PlayerPositionRow – 1
PlayerCell = Cells(PlayerPositionRow, PlayerPositionColumn).Address

End If

End Sub

Step 5: Calling Macros

So we now have macros that do what we want to do but we need to create a way for the user to actually interface with those macros. For simplicity, I am using a simplified version of the Arena.Xlsm interface, but you can design whatever you want. To assign a macro to an object simply right-click on the object and select “Assign Macro…” Do this for all of your directional buttons. Note that you can also create a button to link to your new game subroutine if you want the player to be able to reset (however this will require you to do some additional checking to ensure you are not creating multiple copies of the player each time you reset. In Arena.Xlsm, I redraw the arena so that the player does not show up in multiple spots when a new game starts).

VBA4Play-Part-1

You now have the beginnings of a game in Microsoft Excel! 🙂

I hope you’ve enjoyed this. Everything discussed in this article can be found in the excel workbook here: VBA4Play Part 1. Please comment below if there is something you want to see in the next part of VBA4Play, or if you have a game idea that you want the community to build together. Also I’d love to get any feedback, did this type of format work well for you? Have you learned something from all of this? If you are more advanced and want to submit your own workbook and tutorial for VBA4Play please e-mail me.

More: VBA4Play Part 2: Animation in Excel

Advertisements
    • bensonby
    • April 5th, 2013

    I had a brief look over your codes, and found that the codes are hard to read and maintain. I guess VB(A) is the only language you are familiar with? I am pretty sure if you are to add one more column in your arena, you will have to spend hours modifying it. I recommend you start learning another programming language to improve the programming skills… As a simple example, the 4 functions moveUp, moveDown, moveLeft, moveRight should be combined in just one function. I think you have noticed how many duplicated codes the 4 functions have. The duplication is referred to by one of the famous programming principle: DRY. (Don’t Repeat Yourself)

    • I never claimed to be a professional programmer 🙂 I am a chartered accountant by profession so my inefficient code is a reflection of that. I programmed Arena.Xlsm as a hobby. Part of the desire of VBA4Play was to get community involvement so the professionals and the hobbyists could work together to make something cool. 🙂

      • bob reckerdorn
      • April 15th, 2013

      Seriously? He wrote an RPG in a spreadsheet — that, in and of itself, is the feat. Millions of people have written games in more advanced languages — there’s nothing special about that. And if he had written this game in, say, C, nobody would care — it would have been a cheesy game. But this is a brilliant accomplishment — something most people would have probably said couldn’t be done. Wanna put it in programming terms — it’s a proof of concept. POCs are not MEANT to be easy to read or maintain. Or put it in Agile terms — he wrote what needed to be written. You wanted him to code for the future possibility of adding a column — if he never decides to expand the maps, that coding work — that you are suggesting — would have been pure waste. So get off the programmer-high-horse and give the man some props for an awesome creation!

        • John
        • April 17th, 2013

        Quite right. The code’s not bad for someone with his level of experience – I’ve seen far worse from professionals.

        And as someone who earns a living from VBA, I’d add that it’s quite a capable language, just that most people assume it isn’t because they played with it years ago and could never quite get it to work correctly (without stopping to wonder why)…

    • Plinitch
    • April 5th, 2013

    good job, I will surely follow this series, I loved the game! Congratulations!!!

    • Olivier
    • April 6th, 2013

    French congratulations ! I play your game from France and i read carefully your vba series. I play myself with vba and excel but only in a “professional” way….I’m sure i will discover some trics with your vba series thanks to share your skills ! Bonne continuation sur cotre site, j’attend avec impatience les autres series !

  1. I’m also a VBA programming accountant. You can totally modify the game to get rid of the joystick buttons and just use the cursor keys to control movement. 🙂

    • Can I still do this and still maintain Excel 2007 compatibility? I don’t want to get rid of the joystick buttons because I think most people don’t have an understanding of how to control a spreadsheet-game. But I would definitely be willing to have keyboard input options.

        • John
        • April 17th, 2013

        You can, but not if you’re wanting to add Mac compatibility. That kind of thing requires Windows API’s, and needless to say it won’t work on the fruity OS.

        • Jared Palardy
        • August 28th, 2013

        In order to use cursor keys, all you need to do is call your movement macro using the Application.OnKey method using the desired cursor as an argument. Basically any key can be mapped to a macro this way. I’m working on my own VBA/Excel based RPG and your game is a great proof-of-concept. I am an accountant (CPA-candidate) as well. Keep up the awesome work.

    • Andrew
    • April 16th, 2013

    You, sir, are my hero. I’m an accountant also, and my co-workers think I’m magic with VBA, but I am no where near as fancy as you. I think the idea of VBA discussions is awesome, and I look forward to reading more. My hat is off to you.

    • Rich
    • June 26th, 2013

    I really learned a lot from this, will there be a part 2?

    • Yes, I’m hoping to do a part 2 soon. I was trying to figure out whether to do it on Artificial Intelligence or Animation or something else entirely, it depends what I’m trying to build. I was hoping that the community would want to make a type of game and then I would make tutorials on how to make that game. However, that didn’t happen so I’ll probably try to keep it simple. If you have any suggestions please let me know!

    • 1 day later: https://carywalkin.ca/2013/06/28/vba4play-part-2-animation-in-excel/

      Thanks for encouraging me to actually get on this!

  2. You may be too far into development for this, but here is a tip:

    The Select command isn’t required if you are writing the code, it is only generated when you record macros and select cells.
    Instead of the lines:
    Range(“N12″).Select
    ActiveCell.FormulaR1C1 = “:)”

    You can simply write:
    Range(“N12”).Value = “:)”

    (the FormulaR1C1 is also from recording the macro, and since 🙂 isn’t a formula, Value is a “better” way to do it).

    You did say you want beginners to learn from professionals and vice-versa, so perhaps someone will find this useful.

    • ^^ agreed completely. I have select in there because in the game (Arena.Xlsm) the select is used for targeting the ranged attacks, if the player is moving and not in combat, the select resets to the player rather than maintain focus on the enemy.

  3. Good to know you’ve thought about it 🙂

  4. I just tried this sample for Part 1 and when I reset the game, my old player is still on the screen. This sub fixes that problem, and can be attached to the ResetPlayer button for those who want it.

    Sub ResetPlayer()
    ‘ Clear the current player position and restart the game.
    Range(PlayerCell).Value = “”
    Call NewGame
    End Sub

    (Note,using Call to call the NewGame subroutine is optional, you can just write the word NewGame and it will work as well).

  1. June 28th, 2013
  2. September 17th, 2013
  3. September 17th, 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: