Excel Sudoku Generator

In this post we see how to create Sudoku puzzles with Excel VBA macros. Excel Sudoku Generator creates completely random puzzles with three levels of difficulty. The VBA code uses decremental generation with algorithms to ensure removed numbers do not compromise a unique solution, with the peculiarity of not using any solver to do so. This version of Excel Sudoku Generator was shared for learning purposes and is not the most efficient though. A simpler version has been created to help readers better understand the VBA code: Excel Simple Sudoku Generator. An improved version to create professional Sudoku puzzles is also available for download here: Excel Sudoku Pro. You can also play Sudoku Pro online now!

Sudoku Pro 1000 Puzzles from Easy to Pro Level

Formatting a Sudoku Layout

The first macro creates the characteristic 9x9 Sudoku layout applying some basic VBA formatting properties to the range B2:J10. It is a good example to learn how to use common Cells and Range-related properties such as Text, Color, Borders, and other. In the second half of the macro, we see how to apply validation to a cell using VBA.

 

  Sub FormatLayout()

   

    'Clear content and format a 9x9 Sudoku layout

    Cells.Clear

    Cells.Interior.Color = vbWhite

   

    With Range("B2:J10")

        .ColumnWidth = 5

        .RowHeight = 28

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlCenter

        .Font.Size = 24

        .Font.Color = vbBlack

        .Font.Bold = True

        .Borders.LineStyle = xlContinuous

        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    End With

   

    Range("E2:G4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    Range("B5:D7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    Range("H5:J7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    Range("E8:G10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick

   

    'Format a cell to select puzzle level

    With Range("L2")

        .Value = "Level"

        .Font.Bold = True

        .Interior.ColorIndex = 15

    End With

   

    With Range("L2:L3")

        .HorizontalAlignment = xlCenter

        .Font.Size = 14

    End With

   

    Dim LevelList As String

    LevelList = "Easy, Intermediate, Difficult"

    With Cells(3, 12).Validation

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:=LevelList

        .InCellDropdown = True

        .ShowInput = True

    End With

   

    Range("L3") = "Easy" 'Level "Easy" will appear as default

   

  End Sub

 

 

 

Completely Filled Sudoku Grid

Excel Sudoku Generator creates puzzles using decremental generation, i.e. it first adds all the numbers to fill the entire Sudoku grid, and then removes some numbers to leave the gaps of the final puzzle. Did you ever attempt to fill an empty Sudoku grid from scratch? Give it a try!

 

There are several ways to do that. The easiest way is to create a trivial puzzle based on a simple mathematical formula. The simplest formula leads to a linear sequence as the one shown in the picture below.

That’s however not the best approach, and filling the grid randomly is preferred instead. There are several ways to do that (learn more about different techniques to fill the entire Sudoku grid in this other post).

The method used to add numbers in Excel Sudoku Generator is a modification of the brute force approach. You can find that simpler approach in this other post, which is probably a better example to understand how to use loops and conditional statements to fill the Sudoku grid with Excel VBA. It is less efficient and takes longer time though. The macro example below is also not perfect, but applies a backtracking algorithm to improve and speed up the process. There are yet other better ways to do it. For example, Excel Sudoku Pro uses a smarter backtracking algorithm, and writes/reads the values to/from an array instead of doing it directly in the worksheet. That speeds up the whole process and allows to fill the entire grid in less than a second.

 

  Dim Sudoku As Worksheet, SudokuGrid As Range

 

  Sub CreatePuzzle()

   

    Dim r As Integer, c As Integer, rr As Integer, rc As Integer

    Dim num As Integer, n As Integer, ir As Integer, ic As Integer

    Dim ref1 As Variant, ref2 As Variant, rep(10) As Integer

   

    Set Sudoku = ThisWorkbook.Sheets("SUDOKU")

    Set SudokuGrid = Sudoku.Range("B2:J10")

   

    'Clear previous sudoku numbers

    With SudokuGrid

        .ClearContents

        .Font.Bold = True

        .Font.Color = vbWhite 'This will hide numbers while filling the new puzzle

    End With

   

    'Loop to put numbers into the puzzle randomly (by number and by quadrant)

    num = 1

    Sudoku.Select

    Do

        For rr = 1 To 7 Step 3

            For rc = 1 To 7 Step 3

                Do

                Randomize

                r = Int(((rr + 2) - rr + 1) * Rnd + rr)

                c = Int(((rc + 2) - rc + 1) * Rnd + rc)

                If Cells(r + 1, c + 1) = "" Then

                    Set ref1 = Columns(c + 1).Find(What:=num)

                    If ref1 Is Nothing Then

                        Set ref2 = Rows(r + 1).Find(What:=num)

                        If ref2 Is Nothing Then

                            Cells(r + 1, c + 1).Value = num

                            Exit Do

                        End If

                    End If

                End If

                n = n + 1

                Loop Until n > 99

    back:

                If n > 99 Then

                    For ir = 2 To 10

                        For ic = 2 To 10

                            If Cells(ir, ic).Value = num Then

                                Cells(ir, ic).Value = ""

                            End If

                        Next ic

                    Next ir

                    n = 0

                    rep(num) = rep(num) + 1

                    GoTo cont

                End If

                n = 0

            Next rc

        Next rr

        num = num + 1

        rep(num) = 0

    cont:

        If rep(num) > 5 Then

            num = num - 1

            rep(num) = 0

            GoTo back

        End If

    Loop Until num = 10

   

    Call CopySolution

    Call FinishPuzzle

   

  End Sub

 

The macro adds numbers from 1 to 9 in a Do loop, to random positions within each block, and making sure each number complies with the Sudoku rules using the Find method to check the occurrences of each number in the row, column, and block. There is however a better approach to do that using the Excel CountIf worksheet function along with a function to return the address of each block. You can find more details and the code in this other post: Excel Simple Sudoku Generator

Once this first step is complete, we can save the grid as the solution of the Sudoku puzzle. The seconds step consists of removing certain numbers to leave the gaps of the final Sudoku puzzle.

Remove Numbers to Create the Puzzle

There are several techniques to remove numbers from a completely filled Sudoku grid and ensure a unique solution is not compromised. Ultimately, a solver can be used to confirm the puzzle is correct and does not have more than one solution. Excel Sudoku Generator applies some of these techniques to remove numbers for each level of difficulty. However, this version of Excel Sudoku Generator has the peculiarity of not using any solver to do so. The drawback is though, that this approach cannot generate Sudoku puzzles of great complexity or difficulty. An updated version that combines these techniques with a solver engine to generate puzzles of higher difficulty can be found here: Excel Sudoku Pro.  You can also learn more about the different techniques used to solve Sudoku puzzles with Excel VBA in this other post: Excel Sudoku Solver.

Despite the level of difficulty of Sudoku puzzles does not necessarily correlate with the number of hints given (rather with the set of techniques needed to solve it), this version of Excel Sudoku Generator sets some limits to reduce the impact of the randomness of the process, so the higher the difficulty, the more numbers are removed.

• Level Easy:  Randomly removes numbers that cannot be placed in any other cell within the same row, column, or block (RemoveRound1). It generates puzzles with approximately 45 initial hints that can easily be solved by single position.
• Level Intermediate: Removes numbers from an Easy generated puzzle that can only be placed in one other cell within the same row, column, or block, then blocking that row and column from having more numbers removed (RemoveRound2). Additionally, it checks for pairs (CheckDupletes) to avoid removing all numbers in pairs that would otherwise generate two slightly different solutions. This generates puzzles with a 38 to 40 initial hints, that can be solved by exclusion and single position.
• Level Difficult: Further removes numbers from an Intermediate generated puzzle, checking for pairs and triads within the Sudoku grid, and assigning a weightage to each position to determine which numbers are safe to remove. It generates puzzles with around 32 hints that can be solved by exclusion and block intersection techniques.

 

 

There have been several improvements to this version of Excel Sudoku Generator. You can check these other posts to get more info about generating and solving Sudoku puzzles with Excel VBA.

• Excel Simple Sudoku Generator
• Excel Sudoku Pro
• Excel Sudoku Solver
• Excel Sudoku Generation Techniques: An Overview

    
 

Download Excel Sudoku Generator