Creating the Game: Boggle

[PSL]

Hello,

The game of Boggle is played with a set of sixteen letter cubes, which are standard six-sided dice except that they are marked with letters of the alphabet instead of numbers. The cubes are rolled and arranged into a 4x4 square.


I am trying to replicate this game on Excel.


Now it might seem like a simple use of

=randbetween(char(code("A",code("Z")))

.
But the trouble is that the game isn't as random as it may seem. The dice as so created so as to maximize the word formation. Here are the standard's. (assuming a normal dice with 6 faces)


1 = AAEEGN 2 = ELRTTY 3 = AOOTTW 4 = ABBJOO 5 = EHRTVW 6 = CIMOTU 7 = DISTTY 8 = EIOSST 9 = DELRVY 10 = ACHOPS 11 = HIMNQU 12 = EEINSU 13 = EEGHNW 14 = AFFKPS 15 = HLNNRZ 16 = DEILRX
Thus the 16 dice formations.


Now to for a 4x4 grid each cell would have a letter from one of the dice.


Eg.


1 4 8 13

5 16 7 2
3 9 11 14
12 2 6 10


This could be a possible formation. With each number representing a letter from the above set.


Any suggestion on how to go about the same?


Cheers.!

[Paul_Hossler]

Starting point

Option Explicit
Sub Boggle()
    Dim Dice(1 To 16) As String
    Dim Roll(1 To 16) As Long
    Dim i As Long, c As Long
    Dice(1) = "AAEEGN"
    Dice(2) = "ELRTTY"
    Dice(3) = "AOOTTW"
    Dice(4) = "ABBJOO"
    Dice(5) = "EHRTVW"
    Dice(6) = "CIMOTU"
    Dice(7) = "DISTTY"
    Dice(8) = "EIOSST"
    Dice(9) = "DELRVY"
    Dice(10) = "ACHOPS"
    Dice(11) = "HIMNQU"
    Dice(12) = "EEINSU"
    Dice(13) = "EEGHNW"
    Dice(14) = "AFFKPS"
    Dice(15) = "HLNNRZ"
    Dice(16) = "DEILRX"
    'do 100
    For c = 1 To 100
        Randomize
        For i = LBound(Roll) To UBound(Roll)
            Roll(i) = Int((6 * Rnd) + 1)
        Next i
    
        For i = LBound(Roll) To UBound(Roll)
            ActiveSheet.Cells(i, c).Value = Mid(Dice(i), Roll(i), 1)
        Next i
    Next c
End Sub

Paul

[PSL]

Hey Paul,

Thanks for the reply!

Well this Code seems to create a 16X100 grid! I'm simply looking for a 4x4 grid.

[mdmackillop]

 
Option Explicit

Sub test()
     Dim arr()
     Dim Nums
     Dim N, L
     Dim Ltr As Long, i As Long, j As Long, k As Long
     Range("A1:D16").ClearContents
     Range("A1:D16").Font.ColorIndex = xlAutomatic
     arr = Array("AAEEGN", "ELRTTY", "AOOTTW", "ABBJOO", "EHRTVW", _
     "CIMOTU", "DISTTY", "EIOSST", "DELRVY", "ACHOPS", "HIMNQU", _
     "EEINSU", "EEGHNW", "AFFKPS", "HLNNRZ", "DEILRX")
     Nums = Split(No_Repeat_Random_Numbers(0, 15, 16))
     For i = 1 To 4
          For j = 1 To 4
               Ltr = Int(Rnd() * 6 + 1)
               Cells(i, j) = Mid(arr(--Nums(k)), Ltr, 1)
               'For debug
               Cells(i, j).Offset(6) = arr(--Nums(k))
               Cells(i, j).Offset(6).Characters(Start:=Ltr, Length:=1).Font.ColorIndex = 3
               Cells(i, j).Offset(12) = Ltr
               'end of debug
               k = k + 1
          Next j
     Next i
End Sub
 
'Dave Hawley - Ozgrid
Function No_Repeat_Random_Numbers(Bottom As Integer, Top As Integer, Amount As Integer)
     ' No_Repeat_Random_Numbers Macro
     ' Will generate x unique random numbers between any 2 numbers you specify.
     Dim iArr As Variant
     Dim i As Integer
     Dim r As Integer
     Dim temp As Integer
     Application.Volatile
     ReDim iArr(Bottom To Top)
     For i = Bottom To Top
         iArr(i) = i
     Next i
     For i = Top To Bottom + 1 Step -1
          r = Int(Rnd() * (i - Bottom + 1)) + Bottom
          temp = iArr(r)
          iArr(r) = iArr(i)
          iArr(i) = temp
     Next i
     For i = Bottom To Bottom + Amount - 1
          No_Repeat_Random_Numbers = No_Repeat_Random_Numbers & " " & iArr(i)
     Next i
     No_Repeat_Random_Numbers = Trim(No_Repeat_Random_Numbers)
 End Function

[Bob Phillips]

Function Boggle()    
     Dim Dice(1 To 16) As String 
     Dim UsedDice(1 To 16) As Long
     Dim DiceIndex As Long
     Dim i As Long, j As Long
     Dice(1) = "AAEEGN"
     Dice(2) = "ELRTTY"
      Dice(3) = "AOOTTW"
      Dice(4) = "ABBJOO"
      Dice(5) = "EHRTVW"
      Dice(6) = "CIMOTU"
      Dice(7) = "DISTTY"
      Dice(8) = "EIOSST"
      Dice(9) = "DELRVY"
      Dice(10) = "ACHOPS"
      Dice(11) = "HIMNQU"
      Dice(12) = "EEINSU"
      Dice(13) = "EEGHNW"
      Dice(14) = "AFFKPS"
      Dice(15) = "HLNNRZ"
      Dice(16) = "DEILRX"
      For i = 1 To 4
            For j = 1 To 4
                 Do
                      DiceIndex = Int((Rnd() * 16) + 1)
                 Loop Until IsError(Application.Match(DiceIndex, UsedDice, 0))
                 UsedDice((i - 1) * 4 + j) = DiceIndex
                 Cells(i, j).Value2 = Mid$(Dice(UsedDice((i - 1) * 4 + j)), Int(Rnd() * 6 + 1), 1)
          Next j
      Next i
End Function

[Bob Phillips]

<snip>

Malcolm,

RandBetween is an analysis toolpak function .

[Paul_Hossler]

Well this Code seems to create a 16X100 grid! I'm simply looking for a 4x4 grid.

I just used a 1 to 100 loop to see how 100 rolls (Boggles?) would look

I figure you can arrange one roll (16 numbers) in what ever format you'd like

Paul

[mdmackillop]

Malcolm,

RandBetween is an analysis toolpak function .

Thankd Bob,
I've changed it your your methodology.

[Paul_Hossler]

Could you explain why the Do Loop? It looks like it's to 'roll' ( = assign a RN) to each die in a random order??

Do 
   DiceIndex = Int((Rnd() * 16) + 1) 
Loop Until IsError(Application.Match(DiceIndex, UsedDice, 0))

It would seem unnecessary since there are 16 dice, and each die is a independent event, with the order of the dice not important (you can re-arrange them).

Paul

[mdmackillop]

Hi Paul,
It appears you cannot rearrange them
Wikipedia

The game begins by shaking a covered tray of sixteen cubic dice, each with a different letter printed on each of its sides. The dice settle into a 4x4 tray so that only the top letter of each cube is visible. After they have settled into the grid, a three-minute timer is started and all players simultaneously begin the main phase of play.

[Paul_Hossler]

Mac -- thanks for the correction. Tic-Tac-Toe is more my speed.

So each of the 16 dice are randomized (1-6), and then the entire group of 16 is then arranged into a random 4x4 grid. It's that second step that the Do Loop is handling

Paul

[Bob Phillips]

Could you explain why the Do Loop? It looks like it's to 'roll' ( = assign a RN) to each die in a random order??

Do 
   DiceIndex = Int((Rnd() * 16) + 1) 
Loop Until IsError(Application.Match(DiceIndex, UsedDice, 0))

It would seem unnecessary since there are 16 dice, and each die is a independent event, with the order of the dice not important (you can re-arrange them).

Paul

I also took the order to be not important, so that loop is just to get an array of dice numbers randomly, that is one that hasn't been used previously.

[GTO]

I also took the order to be not important, so that loop is just to get an array of dice numbers randomly, that is one that hasn't been used previously.

So even though you thought rolling ea die in order would be no problem, as the dice cannot be re-arranged after being cast, even your 'accidental' code works better. Sigh... My try of course had the die being cast 1 thru 16 ea time...

Well, even though my attempt may have turned out less-than-stellar, that is a nice trick to learn :-) I would not have thought of using the failure of Match to preserve/set a value.

I would mention that in following Malcom's lead, I found links that state that the letters 'Q' and 'U' are on a single face of a die, which would seem to mean that we are missing a letter (for the sixth face of that die).

PSL: Did you get your values from actual game pieces?

[PSL]

@ mdmackillop, Paul, xld: Thanks a lot! Works like a charm. Done a couple of changes and added a few things. Will post the final game once it's done.

@GTO: Took the standard dice value's. There are variations from game to game, but the occurance of each letter is accurate. Eg. E appears 13 times.

EDIT: Q is to be interpreted as 'Qu' for the sake of this game. Here Q and U are two separate letters. So if Q comes up, it's to be treated as 'Qu' and if U comes up, well it'll be treated as 'U'.

Cheers..

[GTO]

Hi PSL,

Thank you very much for the added info and clarification. I hope you do get a chance to post when done, I look forward to it :-)

Thanks again,

Mark

[klintY]

Im really sorry for bumping old thread, i was searching similar clarification, thanks a lot! Now i can finally complete my game!