The "HQ and SQ dice chances" Excel sheet is a sheet that can help people determine the optimal combination of dice for whatever situation they are trying to evaluate. Users can assign up to 10 dice for either HeroQuest (including the German colored dice) or for StarQuest / Space Crusade in varying combinations. Once done, pressing the "Calculate odds!" button will then run through the permutations of the dice involved and display the chances for each specific amount of symbols to be rolled using said dice. The code executes fast, even if up to 10 different dice have been selected.
Not included in this sheet, this data can then further be used to plot in a graph or compared to the results of other dice rolls, etc ... to find the most optimal combination for a given situation.
The "Calculate odds!" button triggers some macro code and therefor it is needed to allow macro code to run, otherwise the calculations aren't possible. The macro code itself is not protected by password and can be accessed easily. People interested can therefor easily check what the code does (note that I didn't supply comments with the code - but the variables used are pretty self-explanatory); it's placed in "Module 2" under the "Modules" section of the program code.
Hopefully, this will prove to be a useful tool for people who are looking for a way to evaluate the dice rolls of whatever they're designing!
It is also relatively easy to add new custom dice to the sheet and use it in the macro code. In order to add new dice to the sheet, the steps detailed below need to be taken.
1) Open the sheet in Excel and select the "Dice" tab at the bottom.
2) The list of dice shown is labeled "Dice_Names" and covers the dropdown menu's available on the "Distribution" tab. When adding new dice, it's important to make sure it becomes a member of the "Dice_Names" nametag. You can do this by either inserting a new line between the existing ones, or by editing the nametag entry through the management interface for nametags and include the extra entry.
3) Make sure the name of the die you add starts either with "HQ" for HeroQuest dice or "SQ" for StarQuest / Space Crusade. The macro code behind the sheet uses these first two letters to determine to which game the die belongs.
4) Optional: Add some fancy color coding if you want, akin to the ones already there; it helps to set them visually apart.
5) Fill the proper columns with values for each of the 6 faces of the die in each of the 3 listed entries. Quantity matters here; for instance a double skull is shown as a 2 in the column matching that face of the die. The order in which you put them in doesn't matter: during the permutation calculations, it loops through all die faces anyway. Do make sure however to fill in a 0 for faces that don't contain any symbol of the kind.
6) The 7th column of each entry is the maximum occurence of the respective symbol on the die in question. Make sure your new die also contains this value, as the calculations use this value too. Complete this for the 3 categories for HeroQuest dice or for the 1 category of the StarQuest / Space Crusade.
7) If you've done step 2 correctly, the dropdown menu's in the "Distribution" tab should now show the die you just added. You can add conditional formatting to the dropdown menu's for your new die by simply CTRL-select them all and then opening the conditional formatting option. Examine the existing ones (note: only works for Office 2010 and higher; Office 2003 is not as extensive in this area) and copy the existing formula. Create a new one and paste the formula in it. Then assign the various formatting options as you see fit.
DISCLAIMER: I am not responsible in any way if using this sheet and its calculations causes your computer to freeze, your house to burn down or the universe to end in a sudden and spectacular way, nor will I bring you coffee while you work
. Using this sheet and its code is public and free.
1BP, 
2BP, 
3BP, 
1BP, 
2BP, 
3BP, 
4BP, 
5BP. US 
.
I have come across an error of some kind when attempting to use the new coloured dice that have been released (Olive, Grey and Ivory). I've been through the instructions for adding dice and think I have carried out the steps correctly. I have also tried just altering one of the existing rows to reflect one of the new dice types with the same result, alas.
