VBA Minesweeper: Check border cells for mine count?

Question

I have not found much of an answer in VBA on this, but I am making a Minesweeper 5x5 board, and am trying to have cells surrounding a mine to display how many mines are touching it. I am able to get the middle 9 to work, ie the spaces with an "X":

-----
-XXX-
-XXX-
-XXX-
-----

But I'm having serious trouble getting the bordering cells to also count. Here is my code:

For i = 2 To 4
    For j = 2 To 4
        If BombArray(i, j) <> "X" Then
            BombArray(i, j) = 0
                If BombArray(i + 1, j - 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i + 1, j) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i + 1, j + 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i, j - 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i, j + 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i - 1, j - 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i - 1, j) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
                If BombArray(i - 1, j + 1) = "X" Then
                    BombArray(i, j) = BombArray(i, j) + 1
                End If
        End If
    Next j
Next i

I have my index set to 2 to 4 because otherwise Excel throws me an Error '9': Subscript out of range.

Any help would be greatly appreciated.

score 1 · Answer 1 · answered Jul 29 '18 at 19:54

You don't need to set an array or offset to every cell surrounding the current cell. Just create a big range for minesweeper and a small (3 x 3) range to count the bombs around it. Do the rest within the loops.

See the code below, everything is explained in the comments. Feel free to change the center cell address and minesweeper range for a bigger minesweeper game.

Sub myminesweeper()
Dim rng As Range, r2 As Range, c1 As Range, c2 As Range, center_cell As Range, irng As Range
Dim cnt As Integer
Set center_cell = Range("E7") 'set center cell of minesweeper range
Set rng = Range(center_cell.Offset(-3, -3), center_cell.Offset(3, 3)) 'set minesweeper range
                                                                      '(simply change the offset numbers)
                                                                      'currently it is 7 x 7
For Each c1 In rng 'loop through cells in minesweeper range
    cnt = 0 'reset the counter
    If c1.Value = "X" Then GoTo Skip 'skip calculation if there is mine
    Set r2 = Range(c1.Offset(-1, -1), c1.Offset(1, 1)) 'set 3 x 3 range to check mines
    For Each c2 In r2 'loop through cells in 3 x 3 range
        Set irng = Application.Intersect(c2, rng) 'check if the cell is within minesweeper range
        If Not irng Is Nothing Then 'if the cell is in range
            If c2.Value = "X" Then cnt = cnt + 1 'check if there is a mine. if so, add 1 to cnt.
        End If
    Next c2
    c1.Value = cnt 'set the cell's value to total count of mines around it (cnt)
Skip:
Next
End Sub

score 0 · Accepted Answer · answered Jul 29 '18 at 19:24

Test to see if you're on the boundary before you test the cell:

For i = 1 To 5
    For j = 1 To 5
        If BombArray(i, j) <> "X" Then
            BombArray(i, j) = 0
                If j <> 1 And i <> 5 Then
                    If BombArray(i + 1, j - 1) = "X" Then BombArray(i, j) = BombArray(i, j) + 1
                End If
                If i <> 5 Then
                    If BombArray(i + 1, j) = "X" Then BombArray(i, j) = BombArray(i, j) + 1
                End If
                'Etc.
                '...
        End If
    Next j
Next i

VBA Minesweeper: Check border cells for mine count?

2 Answers2