phasmaphobic
Feb 11 2006, 11:04 PM
I'm working on creating a simple GM sheet for my weekly game. The sheet contains the attributes and skill and misc modifiers for all the characters so I can easily reference them.
I want to put in a simple dice roller tool at the top of the sheet. The tool would use the following drop-down menus, most likely activated by Combo Boxes
A: Character Name - this is pulled from the character info lists
B: Type of Test (EX: "Perception: Hearing") - this is from a list of predefined common tests, and determines Attribute and Skill necessary
C: Misc Mods - this is a drop-down list of numbers, ranging from -10 to 10, maybe more, applied as a dice pool mod.
Then a button which, when pressed, rolls the dice, checks for hits and glitches, and gives the result.
I'm fairly adept at excel, so working out the formulas is not proving to be much of a problem so far. The problem is mainly one of aesthetics: using the standard random functions in excel, the calculations occur EVERY TIME new data is entered. I want this one specific calculation to run ONLY when the button is pressed.
Thoughts? Suggestions? Any and all help is appreciated.
TinkerGnome
Feb 11 2006, 11:24 PM
Explore this line of thought. It may or may not work.
Hide the box where the random numbers are generated. Have the button press cause the randomization to occur and copy the numeric content of the box into a display box. This way, you get a random result only when you hit the button.
Not sure how well it'll work, though.
phasmaphobic
Feb 11 2006, 11:29 PM
| QUOTE (TinkerGnome) |
Explore this line of thought. It may or may not work.
Hide the box where the random numbers are generated. Have the button press cause the randomization to occur and copy the numeric content of the box into a display box. This way, you get a random result only when you hit the button.
Not sure how well it'll work, though. |
Do you know a formula for copying the contents of a cell into a display box?
TinkerGnome
Feb 12 2006, 12:06 AM
It's not a formula. You'll need to use a macro. This worked for me:
| CODE |
Sub display_random()
'
' display_random Macro
'
Range("A2").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub |
A2 holds the random number function and C2 is where I want it displayed. You should be able to freely change them.
phasmaphobic
Feb 12 2006, 12:10 AM
| QUOTE (TinkerGnome) |
It's not a formula. You'll need to use a macro. This worked for me:
| CODE | Sub display_random()
'
' display_random Macro
'
Range("A2").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub |
A2 holds the random number function and C2 is where I want it displayed. You should be able to freely change them.
|
Looks good. Might be a way of doing it.
Now that I have my data decided in the boxes, I need a way to calculate "random number from 1 to 6, X number of times, count the 5s and 6s"
phasmaphobic
Feb 12 2006, 12:33 AM
I've got all the variables in place and harvesting accordingly, now all I need is the formula. I need a formula that finds a random number X times, and then counts the number of times Y and Z occur. Hmmmm...
TinkerGnome
Feb 12 2006, 12:40 AM
Here's the hard way to do it:
Clicky
phasmaphobic
Feb 12 2006, 12:50 AM
| QUOTE (TinkerGnome @ Feb 11 2006, 04:40 PM) |
Here's the hard way to do it:
Clicky |
Wow, that's intense! Hahah.
On the "hidden dice sheet" there's a whole buttload of "#NAME?" errors all starting in column B.
Which leads me to believe that for some reason my randbetween function is not working. WTF...
EDIT: Doh. New install of excel, didn't have the add-ins enabled.
TinkerGnome
Feb 12 2006, 12:51 AM
Click tools->add-ins and enable the Analysis ToolPak. That'll fix the "#NAME?" errors. You need it because I'm lazy and was running RANDBETWEEN functions.
EDIT: Yeah. That.
I found an addin that is supposed to roll dice, but it only returns a solid result. Ie, you can get it to roll 3d6, but it'd just give you the sum.
phasmaphobic
Feb 12 2006, 12:54 AM
| QUOTE (TinkerGnome) |
| Click tools->add-ins and enable the Analysis ToolPak. That'll fix the "#NAME?" errors. You need it because I'm lazy and was running RANDBETWEEN functions. |
Beat ya to it =)
Now, to add Glitches. This should be fun...
You know, there HAS to be a simpler way to do this.
TinkerGnome
Feb 12 2006, 12:56 AM
| QUOTE (phasmaphobic) |
| Now, to add Glitches. |
Glitches aren't bad, actually. Just do a countif 1 and then do a comparison between hits and the result of that.
Edge is going to be the killer.
TinkerGnome
Feb 12 2006, 11:21 AM
The A+ answer is to do it all in a macro. This one works:
| CODE |
Sub roll_dice()
'
' roll_dice Macro
'
' You can change the following cells:
' F1 - Where you want the results to go
' C1 - The cell containing the number of edge dice to be rolled
' D1 - The cell containing the total number of dice to roll
'
[F1:F1].Value = ""
ones = 0
hits = 0
edge = 0
If [C1:C1].Value <> 0 Then
edge = 1
End If
For r = 0 To ([D1:D1].Value - 1)
newdie = Int(6 * Rnd) + 1
If newdie < 2 Then
ones = ones + 1
End If
If newdie > 4 Then
hits = hits + 1
[F1:F1].Value = [F1:F1].Value & " [" & newdie & "]"
Else: [F1:F1].Value = [F1:F1].Value & " " & newdie
End If
If edge <> 0 Then
If newdie > 5 Then
r = r - 1
End If
End If
Next r
[F1:F1].Value = [F1:F1].Value & " Hits:" & hits
gThreshold = [D1:D1].Value / 2
If ones >= gThreshold Then
If hits < 1 Then
[F1:F1].Value = [F1:F1].Value & " Critical Glitch!"
Else
[F1:F1].Value = [F1:F1].Value & " Glitch!"
End If
End If
End Sub |
I had a little trouble using cell names as references, so you'll need to do a find and replace to change the cell values to what you need them to be.
phasmaphobic
Feb 14 2006, 09:01 PM
After a bit of work, I've got it mostly worked out, including Edge, Glitches, Specializations, and more, with only one (unnecessary) macro that hits F9 (recalc sheet) when the "roll" button is pressed..
The problem with the original macro that copied the value, is that it copied the value
before the recalculation, and the glitch report I had wasn't up to par with that.
Anyway, if you wanna see the excel sheet, I'll email it to you. Be warned, I use a different set of rules options:
1. Hits on 4, 5, and 6
2. Edge adds itself as direct hits, and additionally opens the rest of the dice to the rule of 6
3. Edge dice do not glitch
4. We're using a fantasy setting =)
EDIT:
Here's a link to the file:
CLIKY CLICKY
phasmaphobic
Feb 14 2006, 09:10 PM
| QUOTE (TinkerGnome) |
The A+ answer is to do it all in a macro. This one works:
| CODE | Sub roll_dice()
'
' roll_dice Macro
'
' You can change the following cells:
' F1 - Where you want the results to go
' C1 - The cell containing the number of edge dice to be rolled
' D1 - The cell containing the total number of dice to roll
'
[F1:F1].Value = ""
ones = 0
hits = 0
edge = 0
If [C1:C1].Value <> 0 Then
edge = 1
End If
For r = 0 To ([D1:D1].Value - 1)
newdie = Int(6 * Rnd) + 1
If newdie < 2 Then
ones = ones + 1
End If
If newdie > 4 Then
hits = hits + 1
[F1:F1].Value = [F1:F1].Value & " [" & newdie & "]"
Else: [F1:F1].Value = [F1:F1].Value & " " & newdie
End If
If edge <> 0 Then
If newdie > 5 Then
r = r - 1
End If
End If
Next r
[F1:F1].Value = [F1:F1].Value & " Hits:" & hits
gThreshold = [D1:D1].Value / 2
If ones >= gThreshold Then
If hits < 1 Then
[F1:F1].Value = [F1:F1].Value & " Critical Glitch!"
Else
[F1:F1].Value = [F1:F1].Value & " Glitch!"
End If
End If
End Sub |
I had a little trouble using cell names as references, so you'll need to do a find and replace to change the cell values to what you need them to be.
|
Can you whip up a file with this as an example?
TinkerGnome
Feb 15 2006, 12:54 AM
phasmaphobic
Feb 15 2006, 11:48 PM
Thanks!
This helps immensely
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.