CODEWITHSUNDEEP
excelvba
Hutsan
Hutsan

Reputation: 13

ReDim during a loop causing Run-time error 9

I've been trying to create a program to automatically solve sudoku puzzles using VBA in Excel as a project.

I've come across a problem where my arrays aren't getting the correct values assigned to them after going through a loop to pick them up. I've had this design work for a 3x3 matrix but it's having problems with a 9x3x3 (I. E. A full sudoku puzzle.)

I've checked the ReDim documentation and realised that it can only reassign the last dimension of an array. Because of this I've tried to work the logic so it never needs to change the size of the first two dimensions. I'm still getting an error that when trying to call a value from the array, I am supposedly overreaching the extent of the array. I've gone through the program step by step using the debugger and checked that it assigns all values correctly, which it appears to. I'm at my wits end as to why it isn't working so is there any advice that you fine people can give me?

Dim guessArray() As integer
Dim blockArray() As Integer
Dim blockCheckArray () As integer
Dim correctArray () As Integer
Dim lenBlockArray (3, 3, 1) As Integer
Dim lenBlockCheck(3, 3, 1) As integer
Dim lenBlockCheckArray (3, 3, 1) As integer
Dim counter as integer

'Putting the sudoku into an array


ReDim guessArray (9, 9) As integer

For i = 1 to 9

    For j = 1 to 9

         guessArray (i,  j) = Cells (i, j)

    Next

 Next

 'Creating an array to find the numbers that are in a quadrant

 ReDim blockCheckArray(3, 3, 1) As integer

 For a = 1 to 3

     For b = 1 to 3

         For i = 1 to 3

             For j = 1 to 3

                 'Looking through each quadrant for any number != 0 and dumping them into an array

                 If guessArray (i + ((a - 1) * 3), j + ((b - 1) * 3)) <> 0 Then

                     lenBlockArray (a, b, 1) = lenBlockArray (a, b, 1) + 1

                     ReDim Preserve blockArray (3, 3, lenBlockArray (a, b, 1)) As Integer

                     blockArray (a, b, lenBlockArray (a, b, 1)) = guessArray (i + ((a - 1) * 3), j + ((b - 1) * 3))

                  End If

             Next

         Next

     Next

 Next

 'Writing out the numbers in the quadrants for debugging

counter = 1

 For a = 1 to 3

     For b = 1 to 3

         For i  = 1 to lenBlockArray (a, b, 1)

             Cells (counter, i + 10) = blockArray (a, b, i)

         Next

     Next

 Next

I'm using Excel 2010. This is my first post on stackOverflow and it's from my mobile (I'm doing this project at work in my downtime) so if there is any change to formatting or any specifics about the errors that I'm getting, feel free to ask.

Upvotes: 1

Views: 229

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Your problem is that you are resetting the 3rd dimension of blockArray based on whatever the current value of lenBlockArray(a, b, 1) is - but the various values in that array could be different - so if lenBlockArray(1,1,1) was 3, and therefore blockArray was dimensioned as (3,3,3), then when you set lenBlockArray(1,2,1) to 1 you will then redimension blockArray to (3,3,1).

You should redim blockArray to the greater of UBound(blockArray,3) and lenBlockArray(a,b,1).

If UBound(blockArray, 3) < lenBlockArray(a, b, 1) Then
    ReDim Preserve blockArray (3, 3, lenBlockArray (a, b, 1)) As Integer
End If

Upvotes: 1

Related Questions