User defined non-volatile function adding specified number of randbetweens

[ilmatthias]

Using Windows 7 and Excel 2013.

I'm trying to create a function to facilitate table top role-playing (i.e. Dungeons & Dragons). I want the formula to accomplish a randbetween for a specified number of times.

I would also like the formula to be non-volatile (i.e. doesn't recalculate every time I do something else in Excel).

As a bonus, I would love to be able to change the die being rolled (e.g. a 6-sided die, 20-sided die, etc.).

So far, I've been able to cobble together a formula that rolls a only a set die (d20) and that is also non-volatile.

Function Die20()
Low = 1
High = 20
Randomize
Die20 = Int(Rnd * (High + 1 - Low)) + Low
End Function

At the least, I would like to be able to type =Die20(3) and have it basically return the result of randbetween(1,20)+randbetween(1,20)+randbetween(1,20).

At the most, I would like to be able to change the high range number so that it looks like =Die(6,3) and it returns the result of randbetween(1,6)+randbetween(1,6)+randbetween(1,6), without recalculating the formula when I do other things in excel.

What I'm NOT looking for is a function that multiplies one randbetween result (e.g. =ranbetween(1,20)*3) because that multiplies 1 roll, not adds 3 distinct rolls.

Any help you guys can give me would be appreciated! Thanks!

 

[shg]

Welcome to the board.

One way:

Function RollEm(nSides As Long, nDice As Long) As Long
  Dim i As Long
  
  Randomize

  For i = 1 To nDice
    RollEm = RollEm + Int(Rnd * nSides + 1)
  Next i
End Function

 

[Rick Rothstein]

Give this function a try...

Function DiceSum(NumberOfSides As Long, NumberOfRolls) As Long
  Dim X As Long
[COLOR=#0000ff][B]  Static IsRandomizedYet As Boolean
  If Not IsRandomizedYet Then
    Randomize
    IsRandomizedYet = True
  End If[/B][/COLOR]
  For X = 1 To NumberOfRolls
    DiceSum = DiceSum + Int(Rnd * NumberOfSides) + 1
  Next
End Function

Note: The highlighted code lines are there to make sure that Randomize is executed only once per session (you get a better mix of numbers doing Randomize one as opposed to doing it every time). See my mini-article here for the reason why..

VB's Randomize Function Should Be Run Only Once Per Session

 

[shg]

I disagree with Rick's suggestion and analysis when Rnd is used in this fashion.

Rnd generates a fixed sequence of numbers from a 16 million number pattern when the VBE is reset. Randomize changes the starting point in that sequence, but the sequence is exactly the same.

If you randomize once, then some (non-trivial) level of effort would allow you to predict the next roll after observation of many rolls; in other words, you could figure out where you are in the pattern. Randomizing each time removes that predicatability.

In principal, the same is true of the RAND() function, except that the pattern is over a million times longer (and it cannot be initialized).

EDIT: Not that any of that matters for D&D ...

 

[Rick Rothstein]

I disagree with Rick's suggestion and analysis when Rnd is used in this fashion.

Rnd generates a fixed sequence of numbers from a 16 million number pattern when the VBE is reset. Randomize changes the starting point in that sequence, but the sequence is exactly the same.

If you randomize once, then some (non-trivial) level of effort would allow you to predict the next roll after observation of many rolls; in other words, you could figure out where you are in the pattern. Randomizing each time removes that predicatability.

I am going to stick with my original statements and disagree with your disagreement. I am just wondering if you read what I wrote in the article I posted the link to? If not, I would suggest you do so. Personally, I think you would be hard pressed to identify any small sequence from among a list of 16 million numbers with the intention of guessing the next number to appear. Also, in looking at the list of people who downloaded the demonstration workbook I posted in it, it appears that you were not one of them... you might want to do that as well.

 

[ilmatthias]

Either way, as far as I'm able to tell, both of your suggestions work perfectly for the purpose I envisioned!

Thanks for all your help!!

 

[Rick Rothstein]

In principal, the same is true of the RAND() function, except that the pattern is over a million times longer (and it cannot be initialized).

Good point about RAND (and RANDBETWEEN for that matter) being a better sequence. Given that, this amended code would probably be better to use then (plus it is more compact as well)...

Function DiceSum(NumberOfSides As Long, NumberOfRolls) As Long
  Dim X As Long
  For X = 1 To NumberOfRolls
    DiceSum = DiceSum + WorksheetFunction.RandBetween(1, NumberOfSides)
  Next
End Function

 

[shg]

I am going to stick with my original statements and disagree with your disagreement.

After only 10 rolls of a single six-sided die, it's better than even money that the rest of the rolls can be predicted.

Whatever the merits of randomizing only once, they don't apply here.

 

[Rick Rothstein]

After only 10 rolls of a single six-sided die, it's better than even money that the rest of the rolls can be predicted.

Assuming that is true, it would not matter for this current tread as the individual rolls are not being reported to the user, rather, only the sum of the numbers are... I seriously doubt anyone would be able to make a prediction of the next sum given the past sums.

As for your claim, the only way I can think for that to be true is, one, if your statements that Rnd always works from the same 16 million series of numbers and that Randomize just selects a starting point within those 16 million numbers is correct. Given that, I could see creating a loop running from 1 to 16000000 and concatenating all the Rnd's generated (with some delimiter between them) to produce a searchable text string of all possible Rnd numbers and then creating a function that concatenates the ten (or whatever number) of generated numbers (using the same delimiter) and then uses the InStr function to locate that concatenated sub-series within the larger series and then finding the next number(s) after the sub-series.

 

[shg]

Assuming that is true, it would not matter for this current tread as the individual rolls are not being reported to the user, rather, only the sum of the numbers are... I seriously doubt anyone would be able to make a prediction of the next sum given the past sums.

I expect it would take fewer rolls to predict if you used more dice (e.g., DiceSum(6, 3)), just as it would if you used a die with more sides (e.g., a 20-sided die would require only about 6 rolls to synchronize.)