Reply to thread

Message: <blockquote data-quote="Magus Coeruleus" data-source="post: 4417638" data-attributes="member: 1704">Hey everyone,  I’d like to share something I whipped up in Excel 2007 to help track initiative in combat and keep track of PC defenses, skill mods, and ability mods. It has two sheets, one for the initiative table (“InitTable”) and one as a roster of combatants (“Combatants”). A macro (called RollInit) rolls d20, adds the mods, breaks most ties, and sorts in initiative order.  I have assigned a hotkey and a button for the macro, so in a game I just copy and paste rows from “Combatants” into the table in “InitTable”, hit a button, and combat starts.  My players don’t mind losing out on physically rolling their initiative in exchange for letting us go from suspense immediately to fighting with no bookkeeping delay—very nice.<img src="http://www.enworld.org/forum/attachment.php?attachmentid=36076&stc=1&d=1218140464" alt="" class="fr-fic fr-dii fr-draggable " data-size="" style="" />    Technical Notes: I did this in Excel 2007 and it uses a macro.  I saved a copy in the older (Excel 97-2003) format but have no idea whether/how it works.  I also know that macros can be a pain to allow.  In case you have issues enabling my macro but know how to create your own I have included the code as a text file so that you can create an empty macro and paste it in so that it’s “native” to your system (not that I can guarantee that will give it permission to work, either).  I assigned a hotkey combo to the macro and created a button in the QuickAccess section for it, but that sort of thing is not part of a macro itself but part of your Excel settings, so you’ll need to do that yourself if you like (I recommend it). Please note that there is a free <a href="http://office.microsoft.com/en-us/products/HA101686761033.aspx" target="_blank">Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 file formats</a> available.  I mention this in case you are using an older version of Excel, find that the old file version I saved doesn’t work, and want to see if the compatibility pack will let the new file version work for you (not that I know if it will).  I do not know whether/how this can be made to work in Open Office and have no plans to try but if anyone else wants to please feel free to share.  I am not a programmer/developer and made this for my own use and want to share.  I don’t have the skills to turn this into something extravagant and think I’m pretty much done with it but if you have any suggestions/comments please share and if anyone wants to use this as a starting point for something be my guest.  How it works:SHEET 1 is called "InitTable".  There is a table object currently spanning A1:AG5 where you see nice formatting.  I don’t know if this is the same outside of Excel 2007.  Alternating row colors are preserved even if you insert, delete, or sort rows.  You can insert rows/columns between existing ones or you can expand the table by click-dragging the right-angle symbol at the bottom right of the table.  Here’s a tour of the columns and how they work:  Order.  No formula.  Final initiative calculations are pasted here as values by the macro. Values can be manually added/altered mid-encounter (e.g. due to ready/delay). Decimals are part of the tie-breaking solution (see below) and I show 2 decimal places in case I need to enter someone manually and need that level of precision to know where they should go.  You can click someone’s Order cell to see the full number, and you can change the column’s formatting to show just integers or more/fewer decimals without changing the actual number.  Combatant.  I use green for PCs, navy for allies, red for enemies.  AC, Fort, Ref, Will. Self-explanatory.  Does not impact initiative but I like having this info readily available.  InitTot, d20, InitMsc, InitMod. The action happens here.  For InitMod manually enter the standard modifier (usually Dex mod + half level. For InitMsc manually enter the total situational mods (e.g. my players usually have +2 for having their Warlord nearby). d20 generates integers between 1 and 20.  InitTot adds the other ones up and breaks most ties by adding two tiny fractions: one based on the sum of InitMsc and InitMod so that ties go to the combatant with a larger standard+situational modifier to the d20 roll, and one based on a very small random number, to break ties if even the combatants’ total modifier is also the same.  These additions are so small that they should not affect the order unless there is a tie to begin with.  You may wish to write-protect the InitMod and d20 columns since you’re not supposed to enter anything manually into those cells. Note that in Excel lots of things you do will cause random numbers to be recalculated, which could wreak havoc once you have your order determined.  Therefore, the macro copies the numbers from InitTot and then pastes them as simple values into the Order column. Recalculation of formulae thus doesn’t affect the Order column.  Use the Order column, NOT the InitTot column, to manually change initiative during combat.Skill and Ability Mods. Like defenses, these are here for convenience and have no bearing on initiative calculations.  I bolded PCs mods for trained skills as a visual cue.  I colored Insight and Perception differently because they are so commonly used.  I didn’t create passive check columns since I prefer to save space and just mentally add 10.  Note also that a lot of this info is missing for non-PCs.  That’s because I don’t use this to track everything in combat, just initiative; I use monster cards instead, so I only add what I need/want for them, but you can add whatever you like.Adding/deleting columns. You can add/delete columns after the Initiative-calculating ones and that should not affect the macro, but I don’t know whether doing so before those columns screws it up by making InitTot, d20, InitMsc, InitMod something other than columns G through J.SHEET 2 is called "Combatants".  The headers are the same but there is no special table object. You can create your roster here and then copy and paste entire rows from here into the table on the InitTable sheet to put them in a combat. As I said before, you can enter as much or as little info as you like as long as you provide the standard and situational initiative modifiers (InitMod and InitMsc). Rows below are ready to add more creatures.  Xs in the InitTot column remind me that these do not have info needed to properly calculate initiative but that the formulae are in place.  You see all of those numbers in the d20 column because every time calculations are made those random numbers get generated, but they will be recalculated of course when you use them in the table so don’t worry and certainly don’t delete those cells since they contain the formula to generate d20. Make sure that if you add/delete columns to customize this you do it BOTH on this sheet and the InitTable sheet so that they match properly.</blockquote>

Verification