17 Oct 2019

I got this crazy idea for an Excel game while drinking a Space Invader IPA from www.amsterdambeer.com It turned out to be a great way to learn more VBA!

(Excel file found below using VBA. Read instructions carefully before using)

 

Inspiration & Concept

The Space Invader logo was my inspiration for this mini game. Memories of early 1980s computer games passed through my mind while I waited for my burger to cook.

 

 

 

 

 

 

I came up with the idea of making a character move around the screen to capture the beer logos.

How would the character move?

 

 

The Hero

This is our hero who needs to collect beer in order to celebrate Spreadsheet Day with his friends!

 

He needs to collect beer in a timely manner while avoiding nasty beer!

 

 

 

 

Moving Around

I had never used the VBA OnKey syntax. I figured this was a great time to use it! OnKey changes the behavior of the keyboard. I used it to assign macros to the arrow keys.

Sub TurnOnArrows()
Application.OnKey “{RIGHT}”, “MoveR”
Application.OnKey “{LEFT}”, “MoveL”
Application.OnKey “{Down}”, “MoveD”
Application.OnKey “{UP}”, “MoveU”
End Sub

I attached a simple action to “MoveR” and the others to ensure that it was working.

NOTE: in the file I have carefully documented how to return to the keyboard’s default behavior (so arrows won’t run the macros). There are two automatic ways and also a manual button.

This is the code that restores the default arrow behavior:

Sub TurnOnArrows()
Application.OnKey “{RIGHT}”
Application.OnKey “{LEFT}”
Application.OnKey “{Down}”
Application.OnKey “{UP}”
End Sub

After experimenting I settled on combining Selection.Cut with ActiveCell.Offset(0, rightvalue).Activate and Selection.Paste to make him move around the screen.

rightvalue is a variable but it made the most sense to move him 8 squares each time an arrow key was pressed (character is 8X8).

So finally….I was able to press the arrows and move him! Cool!

 

 

Collecting Beer

I had a vague memory of the VBA intersect method. It took a LONG time to get the code just right.

For each arrow click I had to check if the character intersected with the placement of the four beers.

Set me_beer = Application.Intersect(Range(“me”), Range(“grid!$AZ$14:$BH$27,grid!$BX$32:$CD$43,grid!$W$46:$AE$59,grid!$AW$51:$BC$62″))

This checks if named range me intersects with any beer in sheet grid. Beautiful 🙂

If there’s no intersection then simply move him in the direction of the clicked arrow key.

If there is intersection (meaning he grabs a beer) then increase the beer score! Yes, I created a way to track the beers collected and subtract points for touching nasty bad beer (don’t buy cheap beer for your friends on Spreadsheet Day!). Check out sheet ‘stats’. A perfect score is 36.

 

 

Beer Collecting Stats

Check out sheet ‘stats’. A perfect score is 36. How fast can you collect your beer and share it with your friends? Can you get 36 points in less than 30 seconds?

 

 

Excel File

My exciting Excel Beer Game v3.0 should entertain you for 1 or 2 minutes. Please save and close all other Excel files before opening my Excel file.

Yes it’s a bit lame but the point is to make learning fun! Now I know how to use intersect and onkey!

 

 

Advanced Functionality

Maybe I could make the Space Invaders move around a bit. It would be harder to capture them! Or what about customizing the character’s features? That seems possible but I ran out of time. Maybe for the next version!

 

 

Great Beer!

Here are some beer companies from Ontario Canada. Great variety and taste:

 

 

 

 

 

 

 

I’m not getting paid to say that their beer is great but if you want to send me some free swag or beer that would be fantastic!

 

 

About Me

 

 

 

 

 

I remember the classic games from the early 1980s. Sometimes we would change the code for games on the TRS80.

In 7th grade we were given computer time on the Vic20. I had this funny idea to change some code and output a message about our principal Mr. Durfy. Fortunately I didn’t use any bad words because just as I ran the code I realized that he was right behind me looking over my shoulder. Man….I was so lucky that he found it amusing!

Post a comment