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:

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

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