Excel spreadsheet for Game Masters

In this blog post, I’ll introduce the Excel spreadsheet I created for my latest Assassin Game: Chocolate of Vile.

I designed Chocolate of Vile game module with flexibility in mind. So I ditched the Mastersheet template (by Geoff@mangafox) I had always been using and wrote a new one from the ground up to please the perfectionist inside me. While the new spreadsheet is designed for party Mafia games, I believe it can come into handy for D&D Dungeon Masters or any RPG pencil-and-paper games hosts as well.

Before I begin, here’s the download link for the Excel spreadsheet I created:

http://1drv.ms/1KDn828

You’ll need to enable macros to view the sample codes but if you don’t trust macro-enabled spreadsheets from the internet (and damn you should not), you can just disable macros to get the base template and add your own VBA scripts later.

There are five worksheets in this spreadsheet: Main, Queries, Inventory, Abilities and Roles and Core library. Each section of this blog post will cover one of these worksheets; its purposes and tips on how to adapt it to your game

Core library

The Core library defines the abilities, items and objects you wish to use in your game module. The primary fields in Core library tables are quite self-explanatory:

Ability Description Class Type
Trickster Copy the property of a piece of chocolate in your inventory and paste it onto three other pieces. The targeted chocolates can be in anyone’s inventory. Ability Active

There are extension fields for status-inducing abilities, ability duration and additional parameters. A typical assassin game won’t last more than two weeks so the upper limit of 99 days is used.

Macros referencing the core library will support up to 200 rows (198 actually, minus the headers). If you use insert row operation native to Excel, all Excel functions will automatically update the limit accordingly. However, some VBA macros will require manual updating.

In general, the data contained in the core library won’t change in run-time, they remain constant and you don’t have to worry about them unless you’re doing live updates to patch up an error.

Abilities and Roles

When you first create a game module, you’ll need to design some roles. To create a role, simply fill in the ROLES TABLE in this worksheet. The table support secondary factions and personal goals as well as a secondary field you can use to customize the rolesheet.

Role Gender Affiliation Faction Personal goal
Lance Male Student Council The Secret Lovers Identify the other two powers

In Chocolate of Vile AG, this secondary field is “Gender” which dictates which of the two stock images the players will get from the little Gender -> Image table below. I’ll go deeper into rolesheet generation in Queries section.

Gender Image
Female https://i.imgur.com/x7feznD.png

Assigning an ability to a role is very simple. Once you have created an ability in the core library, you can use the ABILITIES table in this sheet to assign any number of abilities to any number of roles. As you might have noticed, there’s almost no automation in this sheet. This is because I leave the possibility of multiple players having same roles as well as run-time ability gaining open.

ID Ability Owner Role
1 Investigator Lance
2 Gentle Hand Miyuki

You can add abilities on “Owner” basis instead of “Role”. It works for any combination as you please.

ID Ability Owner Role
1 Investigator FreakY
2 Gentle Hand Tetisheri
3 Gentle Hand FreakY Maria
4 Gentle Hand Miyuki

The last table in this sheet is ONGOING STATUSES table. This table keeps track of statuses and duration of the statuses on “Player” basis. Originally, I planned to write a VBA script that will resolve the statuses everyday (removing expired statuses and applying the effect of each status) but I never really feel the need due to the lack of players in Chocolate of Vile.

But if you ever feel the need to design one, it’ll be best to resolve all your statuses at the same time in the ability chain. I’ll talk about ability chain in Main section.

Inventory

Similar to Abilities and Roles sheet, Inventory contains a master INVENTORY table. In normal situations, you won’t need a separate inventory table. If you’re creating Items in the Core library, you can treat the items in the same manner as normal Abilities.

In Chocolate of Vile, the inventory system needs to keep track of the displayed information and the hidden information. If you are going to adapt this into a normal fantasy game, you can use the inventory system here for Unidentified objects, Rarity tagging, Soul binding mechanics and so on.

Conditional formatting automatically blacks out the deleted items (their owner is assigned Null value) and it also highlights items that have different Owner value from Tag value. I also set up a RANDOMIZED EFFECTS table to quickly assign new items with randomized effect from the effect list.

Finally, the DELETE & CREATE LOG is also available for day-by-day tracking in case you need to review for, say, an ability that revert all actions by a selected player since the start of game. Yes, I had one such ability in one of my previous AGs: The Butterfly Effect. This log can be updated manually or automatically with macro. In this case, the Deleted items are tracked by macro  and I keep track of the Created items.

Day ID Item Action
1 4 Chocolate (alexfilia) Deleted
1 25 Chocolate (alexfilia) Created
1 26 Chocolate (alexfilia) Created
1 27 Chocolate (alexfilia) Created
1 28 Chocolate (alexfilia) Created
2 2 Chocolate (DarkLucy) Deleted

Main

All the aforementioned sheets are used in module design. They won’t be touched, except for the Inventory page, while the game is being hosted. For daily game update, you will use “Main” worksheet.

You may have noticed that I’m using yellow background to highlight the headers of fields that should be updated manually (and carefully, I kid you not). The “Main” sheet has a lot of those yellow fields compared to other sheets. And for good reason it is.

Everyday, your task as the GM is to collect player’s votes and actions and add the data to this worksheet. Yes, you only ever need to update this one sheet and press the buttons in MACROS area in the top-down order. The order is decided by you in the design stage and this order is usually known as “Ability chain” or “Action tree”.

So how do you get these buttons? Well, you write them in VBA. Here’s the link to a VBA tutorial:

http://www.excel-easy.com/vba.html

You will need to write VBA scripts for every ability you add to the Core library. There are a few specifications you will use to code these ability modules:

  • Input: The script should take the parameters from ACTIONS table and look up which entries are calling its ability.
  • Input: The script should cross-check with any other restrictions, bonuses or applicable parameters in the “current session database” (a term I will use to collectively refer to INVENTORY, ABILITIES, ONGOING STATUSES and ROLE ASSIGNMENT tables)
  • Output: The script should update the database automatically and produce debug messages if necessary.
  • Output: The script should return a “Yes”  to the “Checked?” column in the rows it have already finished tabulating.

I personally prefer leaving the input data validation part for conditional formatting and manual inspection.

For example,

The “Abl_Eating_Click” subroutine for Eating button takes in the following ACTIONS table:

Day Player Action Object Target Params Checked?
1 alexfilia Eating 4
1 Hoffman274 Gifting 17 FreakY tag, property
1 Tetisheri Eating 6 Yes
1 alexfilia Achievement Sweet tooth (2)
1 alexfilia Idol’s Charm 2x Sweet tooth
2 FreakY Trickster 15 6 Yes
2 FreakY Trickster 15 17 Yes

When I input Tetisheri’s Eating entry on day 1. The conditional formatting is showing red object, meaning, the targeted object is currently not in Tetisheri’s inventory. Hence, the eating action is invalid. I marked it with a “Yes” in the “Checked?” column to prevent its execution. The same method works for Stun and Void abilities. You simply put a check there to prevent the entry from being processed down the Ability chain.

The macro will then filter the list by day. I’ll input 1 in the “Enter current day” prompt to process day 1 data. The table then would be:

Day Player Action Object Target Params Checked?
1 alexfilia Eating 4
1 Hoffman274 Gifting 17 FreakY tag, property
1 Tetisheri Eating 6 Yes
1 alexfilia Achievement Sweet tooth (2)
1 alexfilia Idol’s Charm 2x Sweet tooth

“Abl_Eating_Click” will now pass any entries in the above able that have “Eating” in “Action” column and nothing in the “Checked?” column to “Abl_Eating_Module”. The entry being passed will be:

Day Player Action Object Target Params Checked?
1 alexfilia Eating 4

“Abl_Eating_Module” takes “player = alexfilia”, “target = 4”, “day = 1” parameters. It will:

  1. Automatically switches to Inventory sheet
  2. Updates the “Owner” field of item with ID 4 to “Null” value.
  3. Tracks the deletion by adding a “Deleted” entry to the DELETE & CREATE LOG table.
  4. Then, it grabs the value in “Property” field and updates the ONGOING STATUS table in Abilities & Roles sheet with a new entry, referencing the “Duration” field in Core library table and “day = 1” parameter from before to generate Start date and End date.
  5. Finally, it exits and lets “Abl_Eating_Click” subroutine adds a check to the processed entry in the action table and passes it the next valid entry.

I only write VBA macros for abilities that are used frequently or require too many calculation steps but you can theoretically automatize everything in your game. Your daily task  as a GM would be reduced to inputting the data into this one sheet, clicking the buttons and writing the DP. This minimizes the chance of tabulation errors as a bonus.

And of course, you have all the necessary parameters to design back-tracking abilities, complex achievement systems and even more.

Queries

Fantastic! Now you have set up a database for your game and you have written macros that can do the updating for you. You’ll also need to generate the properly formatted report forms and rolesheets from the database to post in forums/blogs/websites/etc. This is the primary purpose of Queries sheet.

Designing the query forms will use more native Excel formula than VBA but VBA is a fine solution too. For the Excel formula approach, I use the methods described in this excellent blog post for index-match array querying:

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

One limitation of this approach is that you’ll have an upper limit of how many items you can query at once before the list becomes too long for quick copying. The solution is dynamically extending the range of items being queried using “Page” value. A single page value is used for statuses, abilities and items pages.

VBA approach wouldn’t have such limitations so if you want to invest your efforts in long-term direction, go for VBA. Find below a sample status report query generated for player alexfilia using QUERY FORM. The code generated is designed to be compatible with EGScan forums (vBulletin).

Susan
alexfilia (player)

You need to consume 5 more chocolates before end game;

You have the following statuses;

Status: Lust

A random piece of chocolate from your inventory is transferred to the source player of this status everyday.

You have the following abilities;

Active: Suggestion

Change a targeted player’s vote to match your own

Passive: Idol’s Charm

Double the number of chocolate received from achievements.

 

You have the following items in your inventory;

Item #27: Chocolate (alexfilia)

This chocolate is poisoned with the devilish of toxin. Tasteless, oderless and colorless, Vile toxin ensures a painful and insufferable death to anyone eating this.

Item #38: Chocolate (alexfilia)

This chocolate has a strong herbal scent and the green hue of leaf. Nullifies the effect of another piece of chocolate being eaten in the same day.

Item #51: Chocolate (#Unknown)

This chocolate has a peculiar taste. One bite of this upsets the stomach so much that attending lectures won’t be possible for a while. Nullifies the eater’s vote for a day.

Item #52: Chocolate (alexfilia)

Loosen the tongue of even the secretive of men. This chocolate forces the eater to answer anything he or she was asked in the discussion thread truthfully for a day. Increases the number of chocolate the eater has to eat before end game by three for every lie detected.

Item #54: Chocolate (alexfilia)

This chocolate has a strong herbal scent and the green hue of leaf. Nullifies the effect of another piece of chocolate being eaten in the same day.

Advertisements

Published by

fujihita

Self-learner, designer, author and programmer.

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