Information Systems Skills in Action

VBA Games - All In One File

2009-04-08T21:02:00.005-06:00

I decided to combine all the games into one file for all those who prefer all the games in this format.

To download the excel file containing all the games below click here. (.xlsm file format)

VBA Excel - Yahtzee

2009-04-02T16:43:00.009-06:00

The last game I created for right is Yahtzee. Below is the score sheet and it is completely interactive. When the player clicks “New Game,” it asks how many players will be playing. For each player, they enter their name which is then populated into the spreadsheet. When a player has finished rolling what they want, they then click inside the cell to put their score. The computer then calculates according to the dice and criteria needed and puts the correct value into the cell. Any time during the game, players can click “Rank” and it will reorganize the scorecard placing the leader at the top.

Use Ribbon Buttons
Even though the buttons to start a new game, roll the dice, and rank all players are found on the board, I also included them in the ribbon for convenience.

Rolling
The player clicks “Roll” which randomly rolls the dice. The player can then choose which dice they want to keep by either clicking anywhere within the cells or the checkmarks next to the dice. The selected dice will change to the color black. After three rolls, the player is required to place their score in the score sheet. The game prompts the user to place their score if they try to roll more than three times. The two photos below are an example of what it looks like before the user selects the dice they want to keep and after they have selected the dice.

To download Yahtzee click here. (.xlsm file format)

VBA Excel - Scrabble

2009-04-02T16:41:00.010-06:00

Below is the Scrabble board which lists quantity of each letter, score for each letter, the tiles remaining (upper right-hand corner), etc. There are four buttons to click to continue with game play: “New Game”, “End Turn”, “Undo”, and “Swap.”

Use Ribbon Buttons
Even though the buttons to start a new game, end your turn, undo tile placement, and swap out your tiles are found on the board, I also included them in the ribbon for convenience.

New Game Button
By clicking the “New Game” button, it will clear the board and start a new game. Before clearing the board it will ask how many players you want to play in the game (1-4 players). The user form prevents the user from entering any character except one through four. After entering the number of players then it asks for each player’s name and populates the tiles next to the player’s name.

After starting a new game, the first player takes their turn by highlighting the center cell. By highlighting the cell, a prompt asks the player to enter one of the tiles they currently own. If the player enters an invalid character or a letter they don’t own, then nothing happens. After a player enters a letter it is replaced with a dash (-). The next screen shot shows the user form that opens up where the player enters one of their letters.

The player can then continue building the rest of the word off the center tile. It will only prompt the user to enter a letter if the player can build there. This game tests all directions and handles tile placement for the first, second, and consecutive tiles.

In the case of a blank tile, the player enters space and it changes the user form to look like the one to the right. Here the player can enter any letter they want. When the blank tile is placed on the board the letter shows up in red font so that it isn’t scored.

End Turn Button
After the player finishes their turn, they click the “End Turn” button. Clicking the “End Turn” button checks the validity of the word(s) added to the board, scores the points earned (factoring in the word and letter multipliers for all word combinations), and advances to the next player’s turn. If the word is invalid it will undo the tiles and advance to the next player. The error message form to the left is an example of what happens if the player places an invalid word on the board. It displays all the words that are not valid. The valid word check compares against the excel dictionary. If the player uses all seven tiles they will receive an extra 50 points to their score.

Undo Button After a player has placed tiles and they decide against their decision, they can click the “Undo” button and it will erase all the tiles placed, replace all the tiles in the players list, and start the turn over. The player can then begin placing tiles again.

Swap Button On the occasion that the player receives a bunch of tiles they don’t want or they can’t place anything because of the combination of tiles, the player can choose to swap out one, two...or all the tiles that they currently have if they want. Every letter entered into the form replaces the same letter on the worksheet with a dash. If a player deletes a letter they entered, it is placed back into the spreadsheet. Below is an example of what it looks like when a player decides to swap out tiles.

To download Scrabble click here. (.xlsm file format)

VBA Excel - Monopoly

2009-04-02T16:39:00.013-06:00

This game is taken from an example I found on the internet. However, I completely reworked it so that the code is more robust. It has four players and has a lot more functionality in some areas.

The first screen shot is a Monopoly game in progress. Game play starts by entering the number of players (from 2 to 4 players) which will clear the board if needed. If you want to change human players to computer players, click the cell that says “Player #” at the top of a player’s property list. To roll, click the “Roll Dice” button in the middle of the board or in the excel ribbon. If the next player is a computer it will automatically roll. After purchasing property, the property will show up in the player’s property list and mark the land tile as “Sold P#.” If the property is already sold it will deduct the rent owed. If the space isn’t property, appropriate action is performed such as drawing a community chest or chance card. Mortgaging or unmortgaging property will change the status back and forth from “Sold P#” to “Mort P#” and mark it red if it is mortgaged. Buying houses/hotels will place a house shape on the board. Most of these elements of the game are visible in the image below.

Use Ribbon Buttons
To reduce clutter on the screen, I decided to add all actions performed into the menu bar as shown in the next screenshot. By clicking on the add-ins tab in excel, you have access to start a new game, roll the dice, buy/sell houses, mortgage/unmortgage property, offer deals to other players, change the board name between five different editions, or save and close at the click of a button. A computer player will automatically perform all these actions according to the logic I programmed. Some of the buttons won’t be enabled if the player doesn’t have the minimum requirements to perform a given operation. For example, buying houses will be disabled if the player doesn’t own a monopoly.

Buy Property
When a player lands on a property that isn’t owned, this window will pop up giving the player the opportunity to purchase the property or put it up for auction:

If the player decides to purchase the property the title deed will pop up displaying details about the property such as rent, cost of houses, and mortgage value. Rent is highlighted in red and with more of the same color property the red highlight will increase (See Pennsylvania Railroad Title Deed). If a player owns a monopoly without houses, the rent on site only will double.

After a player owns property, he/she can double-click the property name in their property list and it will display the title deed once again.

Auction Property
If the player declines purchasing property or doesn’t have enough funds to do so, the property is put up for auction. The window below pops up where the player after the one who declined the property starts the bid. The starting price is at half the value of the property. The bidder can increment the current bid by as much as they want. If a bidder passes, their status changes from “Active” bidder to “Passed.” After all but one bidder passes, the last bidder wins the bid and receives the property at the “Current Bid.”

Force Mortgage or Sell Houses
In the case that the player owes more cash than they have, the window below will pop up showing that the player either needs to mortgage property, sell houses, or declare bankruptcy by quitting.
By clicking on “Mortgage Property” a window to mortgage property is shown as discussed in the next section. If the player doesn’t have any houses, then the “Sell House” button is disabled as shown. In the event that any player declares bankruptcy, all property, cash, get out of jail free cards, etc. shift from the bankrupt player to the player where rent is owed. If rent is owed to the bank, all property goes back to the bank.

Mortgage/Unmortgage Property
When a player opts to mortgage or unmortgage property whether by choice or not, the mortgage window is made visible (see the next picture for a screen shot). In this window there are two pages: a mortgaging page and an unmortgaging page. The player decides which property they want to mortgage or unmortgage. After doing so, the window is refreshed to show current property status. When a property is mortgaged, the font of the property is set to strikethrough as shown in the next screenshot. By mortgaging, the player adds the mortgage value to their current cash. When unmortgaging, the player will be charge a 10% interest fee.

Trade Property
Anytime during the game when two players have property, a player can propose a trade as long as they are one of the two players with property. By proposing a trade the window below opens and loads the player’s property in the left side of the window. The player can then select a player to trade with and it populates the other player’s property in the right side of the window. Property can be selected for trade and multiple selections can be made as shown in the screen shot. Additionally, the player can designate a certain amount of cash they offer or demand with the trade. After all fields are filled, the player then clicks “Offer” which will proceed with the offer.

The player making the offer is then prompted to confirm the offer is correct before sending the offer to the other player as shown in the screen shot at the bottom right of this page.

After the player makes the proposal, the recipient receives the offer as shown in the window below. If the player is a computer, everything is handled in the background and the computer will accept or reject the offer. The recipient player can evaluate the offer and decide whether or not they accept or decline the offer. If the offer is declined, the offering player is notified that the offer was rejected.

Buy/Sell House
Buying and selling houses or hotels on property is set up very similar to the “Mortgage Property” window. When a player has a monopoly they can purchase houses or hotels. The window below pops up and the player can purchase a house at the cost shown. Every time a building is purchased, the window is refreshed so the player has to build houses and hotels evenly. After purchasing a house or hotel, it is placed on the board as shown in the image below as a green house or a red hotel.

After building houses or hotels on property it updates the property title deed as seen in the screen shot to the left. Since Connecticut Avenue has a hotel built on it, the line about owning a hotel is colored red.

Get Out of Jail
Whenever a player is in jail, when they roll the dice the window to the right opens and prompts the user to either use a chance or community chest card if they have one, pay the $50 fine, or roll the dice to attempt to roll a double. After three rolls of no doubles, the player is forced to pay the fine and leave jail.

The get out of jail free cards are shown in the player’s property list right next to the cash as seen to the left.

To download Monopoly click here (.xlsm file format)

VBA Excel - Connect Four

2009-04-02T16:36:00.005-06:00

With Connect Four, starting from some code I borrowed online, I decided to revamp it and make it more customizable. The additions I made to the game include playing two players, more robust code, and a few other changes. The first picture is a screen shot at the start of a game.

If you click on the pinkish red cell on the right it toggles between one and two player game play. Also, clicking on one of the two pinkish red cells on the left determines which player starts the game.

The next screen shot is a game in progress. The object of the game is to get four in a row, either vertically, horizontally, or diagonally.

To download Connect Four click here. (.xlsm file format)

VBA Excel - Boggle

2009-04-02T16:34:00.004-06:00

By clicking "New Game" all the tiles in the boggle board are reshuffled. You can start the timer by clicking "Start." It will start counting down. Once the time runs out, it will beep and the board will clear so that players can't continue writing down anymore words. If you choose to, you can click the start button again and it will stop the clock. I want to get a list of valid words printing out into the spreadsheet, but I don't have this functionality working yet.

Use Ribbon Buttons
Even though the buttons to start a new game and run the timer are found on the board, I also included them in the ribbon for convenience.

To download Boggle click here. (.xlsm file format)

VBA Beginnings

2009-04-02T12:48:00.004-06:00

During my free time I have decided to work on building games using VBA with Excel. I have had a fun time programming these games and have spent several hours on it. This first post is a description of what I have done and why.

My reasons for putting this together is for several reasons. First, when I go on business trips, occasionally I will take my wife with me. We both love board games, but unfortunately it would take up too much space to log our board games around with us. After a day’s work, when I get back to the hotel, my wife and I can better relax by playing a game. In addition, being relaxed will help me prepare for the next day’s work.

Second, I wanted to learn and perfect skills I recently didn't have. VBA has been great for me because at one of my jobs I have reduced a task from 30 hours to 7 hours. In addition, I have learned skills to help me automate many other processes. I plan on using my skills in VBA for more productive uses in the future.

Finally, I am seeking a job and the economic situation has been working against me. By showing that I programmed these games, it could be a very visible way of showing off my talents so that I stick out and receive job offers.

The games I am including on this blog are as follows: Boggle, Connect Four, Monopoly, Scrabble, and Yahtzee.

The Beginnings

2008-09-12T17:56:00.002-06:00

On top of the 30 hours of work and 15 hours of classes, not including homework, I am setting this blog up to record solutions to some of the most irritating technological problems I have had. In addition, I am including personal research topics that I have found very interesting.