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]

... 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.

Here's what I did:

Option Explicit

Const nRoll         As Long = 10
Const nRnd          As Long = 16777216
Dim aiRnd(0 To nRnd + nRoll - 1) As Long
Dim bInit           As Boolean

Sub RicksNextRolls()
  Dim aiRoll(0 To 9) As Long
  Dim iRoll         As Long
  Dim iBeg          As Long
  Dim sOut          As String

  Init

  For iRoll = 0 To nRoll - 1
    aiRoll(iRoll) = DiceSum(6, 1)
    sOut = sOut & ", " & aiRoll(iRoll)
  Next iRoll
  MsgBox "Here are Rick's first 10 rolls:" & vbLf & vbLf & Mid(sOut, 3)

  For iBeg = 0 To nRnd
    iRoll = -1
    Do
      iRoll = iRoll + 1
      If iRoll > 9 Then Exit For
    Loop While aiRnd(iBeg + iRoll) = aiRoll(iRoll)
  Next iBeg

  sOut = ""
  For iRoll = nRoll To nRoll + 4
    sOut = sOut & ", " & aiRnd(iBeg + iRoll)
  Next iRoll

  MsgBox "I predict the next 5 rolls are " & vbLf & vbLf & Mid(sOut, 3)
  MsgBox "... and they are" & vbLf & vbLf & _
         DiceSum(6, 1) & ", " & DiceSum(6, 1) & ", " & _
         DiceSum(6, 1) & ", " & DiceSum(6, 1) & ", " & DiceSum(6, 1)
End Sub

Sub Init()
  Dim i             As Long

  If Not bInit Then
    For i = LBound(aiRnd) To UBound(aiRnd)
      aiRnd(i) = Int(Rnd() * 6 + 1)
    Next i
    bInit = True
  End If
End Sub

Function DiceSum(NumberOfSides As Long, NumberOfRolls) As Long
  Dim X             As Long
  Static IsRandomizedYet As Boolean

  If Not IsRandomizedYet Then
    Randomize
    IsRandomizedYet = True
  End If
  For X = 1 To NumberOfRolls
    DiceSum = DiceSum + Int(Rnd * NumberOfSides) + 1
  Next
End Function

 

[shg]

Your suggestion about catenating the rolls and using InStr might be faster.

BTW, I saw several cases where it 'guessed' wrong. Testing more rolls would reduce or eliminate that.

 

[Golden Age]

shg and Rick Rothstein; I was doing some Google searching looking for non-volatile help with RND and Randbetween for the exact same application as ilmatthias started with this thread. That being to use in a home based Excel role-playing game character maker. I want more randomization hence I wand to apply your static/public material but don't fully understand how to fit it into my code. I have a bazillion Command Buttons that each add a dice number depending on the number of sides of the dice. The total goes to a specific cell. I have included a counter that sends the number of times a user clicks the Command Button for that number of dice. I like being able to see the number of Command Button clicks.

I'm Copy-pasting in my code that is working:

Private Sub CommandButton8_Click() 'IQ Initial Attribute
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Dim IQInitialAttributeRoll As Integer
Dim T As Integer
Randomize
Range("C25").Activate
T = ActiveCell.Value
ActiveCell.Value = T + 1
Range("C24").Activate
IQInitialAttributeRoll = ActiveCell.Value
IQInitialAttributeRoll = IQInitialAttributeRoll + (Int(Rnd * 4) + 1)
ActiveCell.FormulaR1C1 = IQInitialAttributeRoll
ActiveSheet.Protect
End Function

Private Sub CommandButton13_Click() 'IQ Initial Attribute
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Dim IQInitialAttributeRoll As Integer
Dim T As Integer
Randomize
Range("C30").Activate
T = ActiveCell.Value
ActiveCell.Value = T + 1
Range("C24").Activate
IQInitialAttributeRoll = ActiveCell.Value
IQInitialAttributeRoll = IQInitialAttributeRoll - (Int(Rnd * 6) + 1)
ActiveCell.FormulaR1C1 = IQInitialAttributeRoll
ActiveSheet.Protect
End Sub

Private Sub CommandButton14_Click() 'IQ Initial Attribute
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Dim IQInitialAttributeRoll As Integer
Dim T As Integer
Randomize
Range("C31").Activate
T = ActiveCell.Value
ActiveCell.Value = T + 1
Range("C24").Activate
IQInitialAttributeRoll = ActiveCell.Value
IQInitialAttributeRoll = IQInitialAttributeRoll + ((Int(Rnd * 4) + 1) * 10)
ActiveCell.FormulaR1C1 = IQInitialAttributeRoll
ActiveSheet.Protect
End Sub




These are some of the Command Buttons I mentioned. The first is adding dice with 4 sides, the second is subtracting, and the first is adding rolls of a dice with 4 sides then multiplying that by 10. What should my VB code read instead if I want to get the most randomization as possible?

This all started because I wanted an Excel formula: =IF(skills!$GO$27=0,"",RANDBETWEEN(2,8)) to give a non-volatile number between 2 and 8. Just for clarity, skills!$GO$27 also has a formula that will read 0 or "2d8" depending on another cells' value. "2d8" means roll a dice with 8 sides twice. What should my formula read so it won't change once that random number is generated in the cell?

Thank you for your time and help.

Bryan