20 June 2015

CCG Emulation using Excel VBA

I'm stuck in a hotel room for three weeks on a business trip.  During the week the pace is so busy there's barely any time to think but on the weekend there's nothing to do.  My two options are either killing my brain with TV or trying to learn something.  I chose the latter and decided to teach myself Microsoft Visual Basic for Applications (VBA), as employed by Microsoft Excel.

Excel and collectable card games (CCGs) go together like peanut-butter and jelly.  CCGs typically contain hundreds of cards each featuring a relatively large amount of information, making the sorting and filtering capabilities of a good spreadsheet program perfect for collection management and deck-building.   I've just started to tackle macro-writing and exploring the rest of VBA for unrelated work efforts; I'm quickly realizing how powerful VBA is.  And what better way to harness the power of Excel VBA than to try to create a CCG Emulator.

The Quest for the Grail CCG from 1995 is one of my favorites, especially since solitaire play is very clean with little changes to the multi-player rules.  QftG therefore seemed like a perfect choice for my experiment.

The project is still a long way from finished but I've made pretty good progress.  So far I've written code that allows the user to do a virtual "buy" of up to a box of 10 Starter packs and up to a box of 36 booster packs.

From there the program sorts the cards into the eight different general types (Combat Tactics, Companions, Domains, Events, Experience, Rewards, Spells, Warriors) that the player builds a deck from.

Once the cards for the deck are selected, the program shuffles not only those cards but all of the available Quest cards.  In Quest for the Grail the Quest cards represent obstacles to be overcome and reside in a separate deck.  The program creates this separate deck for the player.

That's as far as I've got.  Next will be to code the actual turn-by-turn play, which should be fairly easy.  Basically I'll write a new Userform that will pop up and give access to the appropriate number of cards and keep the rest hidden.

Here's a few pictures with explanations of what happens behind the scenes:

The starting page just has one button.  I'll throw some art from the game in the background once I get everything else coded.  When the button is pressed it brings up the next user data-entry form.

This form allows the player to do a virtual buy of Starter and Booster packs.  The cards are randomly generated from the lists on the "Common" "Uncommon" and "Rare" worksheets, with the frequency of the rarity of the cards based on the actual distribution of the real starters and boosters.  The program enters these values in a single column, then assigns the appropriate card type based on a lookup function.  It then sorts the cards and displays them on the next form.

This form has a lot going on.  First the multi-page box on the left features a page for each of the player card-types; each page includes two list boxes.  When the form initializes the right list box on each page is empty.  The left box shows all the available cards of a respective type; once the player chooses a card to add to the deck the chosen cards are moved to the right list.  When the right list of each card type is populated, the player clicks the "Build Your Deck and Shuffle" button and the program does just that.  The other thing this form does is allow a card preview.  If the player selects one (and only one) card in the left list box on the active page and then clicks the "Preview Card" button, the text boxes in the middle and on bottom of the page are populated with the card information.  I plan on scanning my entire card collection and then coding it so that when previewing a card the actual picture will appear in the gray box on the right.

I've a lot more work to do on this, but it's been fun.  Is it more trouble then it's worth?  Yes probably.  It is way easier to just shuffle some real cards and play.  There are a few advantages to VBA emulation however.

CCG emulation through VBA allows a player to virtually "purchase" far more cards than he could ever afford.  Also, it allows the player access to every card in the game.  No more desperately searching for an Ultra-Rare.

The final advantage, which I am most excited about, is that one could use card game emulation through VBA to do play testing of home-brew games before spending a significant amount of time on the playing pieces.  Instead of spending hours and hours laying out the graphics for self-published cards only to find that the game doesn't work, one could use VBA with just the minimal information needed for the game and check the rules.