VBA dice roll with addition

Question

VBA dice roll is very easy thing even for me, but I need specific type, when rolling 6 means you can roll again and add the two rolls together (plus when you roll 6 twice in a row, you get third roll and so on).

I have tried two approaches, but both failed

Sub roll_dice_1()

Dim result As Range

Set result = Range("A1")

result = Application.WorksheetFunction.RandBetween(1, 6)

If result = 6 Then
  result = result + Application.WorksheetFunction.RandBetween(1, 6)
  Do Until Application.WorksheetFunction.RandBetween(1, 6) <> 6
  result = result + Application.WorksheetFunction.RandBetween(1, 6)
  Loop
   Else
End If

End Sub

This one however can produce result of 12, which is clearly impossible, because twice 6 should give third roll

Sub roll_dice_2()

Dim result As Range

Set result = Range("A1")

result = Application.WorksheetFunction.RandBetween(1, 6)

If result = 6 Then
  Do Until Application.WorksheetFunction.RandBetween(1, 6) <> 6
  result = result + Application.WorksheetFunction.RandBetween(1, 6)
  Loop
   Else
End If

End Sub

This one works even worse, because it can return 6.

I tried search high and low, but all that I got were simple codes for simple throws, rolls with two dices and rolls when certain results can be rerolled. Again all options pretty easy, unlike this one

Answer 1

Your issue is you're generating a random number to test against and then generating a different one to add to your result. They need to be the same. Also VBA has it's own random function.

Sub roll_dice()
    Dim result As Integer, roll as Integer
    Dim lowerbound As Integer, upperbound As Integer

    lowerbound = 1
    upperbound = 6

    result = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

    If result = upperbound Then
        roll = result
        Do While roll = upperbound
          roll = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
          result = result + roll
        Loop
    End If

    MsgBox result
End Sub