Displaying and Shuffling the Puzzle in Excel VBA

After pressing the START button, the puzzle is displayed for the user, as shown in Figure 11.13. The corresponding procedure Starten() is explained below in parts.

Part 1: Variable Declarations

Sub StartGame()
    Dim AbortSub As Boolean
    Dim shp As Shape
    Dim FilePath As String
    Dim PieceWidth As Integer
    Dim PieceHeight As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Name1 As String
    Dim Name2 As String
    ...

• The Boolean variable AbbruchSub tracks whether the procedure should abort, for example, if the user presses START again during an ongoing game.
• Sh is a reference to a single Shape object.
• Datei holds the path and filename of the image file for the puzzle.
• Breite and Hoehe store the width and height of one puzzle piece.
• i and k control nested loops for creating puzzle pieces.
• Name1 and Name2 store the names of two puzzle pieces to be swapped.

Part 2: Game Restart Confirmation

AbortSub = False
If GameActive Then
    If MsgBox("You are not finished yet. " & _
              "Do you really want to restart?", _
              vbYesNo, "New Start") = vbNo Then
        AbortSub = True
    End If
Else
    GameActive = True
End If
If AbortSub Then Exit Sub

...

• When the START button is pressed, the program checks if a game is already active (SpielAktiv).
• If active, the user is asked whether they want to end the current game and restart.
• Choosing NO sets AbbruchSub to True, immediately exits the procedure, and keeps the current game intact.
• Choosing YES keeps AbbruchSub as False, allowing the procedure to continue and shuffle the pieces.
• If no game is running, SpielAktiv is set to True, and the puzzle pieces will be shuffled for the start.

Part 3: Prepare Puzzle Pieces

DeleteAllPieces
FilePath = ThisWorkbook.Path & "\paradise.jpg"
Set shp = ActiveSheet.Shapes.AddPicture( _
    FilePath, msoFalse, msoTrue, 10, 10, -1, -1)
PieceWidth = shp.Width / 5
PieceHeight = shp.Height / 5
shp.Delete

• All existing puzzle pieces and gridlines are deleted.
• The path and filename of the image are stored in Datei.
• The AddPicture() method inserts the image as a shape and returns a reference to it.
• Parameters:
  • First: image file path.
  • Second (msoFalse): the image is embedded, not linked (so changes to the original file do not affect the embedded copy).
  • Third (msoTrue): the image is saved with the workbook.
  • Fourth and fifth: position offsets (Left and Top) in points.
  • Sixth and seventh: width and height (-1 means use original size).
• The puzzle consists of 25 pieces arranged in a 5×5 grid. The width and height of each piece are calculated as one-fifth of the full image dimensions.
• The inserted image shape is deleted after measuring, as it was only needed to determine piece size.

Part 4: Creating Puzzle Pieces

For i = 1 To 5
    For j = 1 To 5
        Set shp = ActiveSheet.Shapes.AddPicture( _
            FilePath, msoFalse, msoTrue, _
            100 + j, 10 + 4 * i, -1, -1)
        With shp.PictureFormat
            .CropLeft = (j - 1) * PieceWidth
            .CropRight = (5 - j) * PieceWidth
            .CropTop = (i - 1) * PieceHeight
            .CropBottom = (5 - i) * PieceHeight
        End With
        shp.Name = "P" & i & j
        shp.OnAction = "ClickPiece"
    Next j
Next i

• The image file is inserted 25 times to create 25 puzzle piece objects.
• Each piece is positioned slightly more to the right and down than the previous to create visible spacing.
• The CropLeft, CropRight, CropTop, and CropBottom properties of PictureFormat crop each piece to show only the respective segment of the full image.
• For example, the top-left piece (i=1, k=1) has no crop on the left or top (crop values 0), and 4/5 of the width and height cropped off on the right and bottom.
• Pieces are named according to their grid position, e.g., « P11 » for top-left, « P12 » right next to it, « P21 » below « P11 », and « P55 » for bottom-right.
• The OnAction property assigns the procedure « Klick » to each piece so that clicking it triggers the selection handler.

Part 5: Shuffling Puzzle Pieces

    For i = 1 To 100
        Name1 = "P" & Int(Rnd * 5 + 1) & Int(Rnd * 5 + 1)
        Name2 = "P" & Int(Rnd * 5 + 1) & Int(Rnd * 5 + 1)
        SwapPieces ActiveSheet.Shapes(Name1), _
                   ActiveSheet.Shapes(Name2)
    Next i
    FirstClickActive = True
    Set shp = Nothing
End Sub

• The puzzle pieces are shuffled by swapping two randomly selected pieces 100 times.
• Piece names are generated randomly for both pieces to be swapped.
• The references to the two pieces are passed to the procedure Tauschen(), which performs the swap.