Excel VBA Sudoku Puzzle Solver using Backtracking Algorithm

A while back I created a Sudoku puzzle solver using the backtracking algorithm in Python. I decided to re-create that same solver as an Excel VBA. The translation was straight forward. Instead of using Tkinter user interface I used an Excel worksheet for the individual puzzle cells. Simple command buttons are used to clear, start, and stop the puzzle.

The results was a nicer looking puzzle with bold outer and 3×3 groups. The biggest issue is the use of copy and paste on the user interface, which will modify border styles. A simple solution was implemented to re-draw the borders when clearing or starting a new puzzle.

' copy/paste changes borders
Private Sub setBorders()

    Dim borderRange As Range    ' range to set border
    Dim ws As Worksheet         ' puzzle worksheet
    Dim i, j As Long            ' index counters
    
    Application.ScreenUpdating = False  ' turn off updates while fixing borders
    
    Set ws = ThisWorkbook.Sheets("SudokuPuzzle")
    ' put border around each cells
    For i = 2 To 10
        For j = 2 To 10
            ws.Range(Cells(i, j), Cells(i, j)).BorderAround LineStyle:=xlContinuous, Weight:=xlThin, Color:=vbBlack
        Next j
    Next i
    
    ' thick on outside
    ws.Range("B2:J10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ' thick in 3x3 groups
    ws.Range("B2:D4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E2:G4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("G2:J4").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ws.Range("B5:D7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E5:G7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("H5:J7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    ws.Range("B8:D10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("E8:G10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    ws.Range("H8:J10").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbBlack
    
    Application.ScreenUpdating = True
    
End Sub

The solving performance speed was about 100 times slower than Python. The solved puzzle shown below was finish in about 1 second using Python where the Excel VBA took more than 80 seconds to solve using the identical solving algorithm.

I wanted to provide the macro enable Excel workbook for download by WordPress doesn’t allow .xlsm files (and for good reason). Sorry.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s