Improve code performance for Sudoku

[groston]

I wrote a spreadsheet to help me with Killer Sudoku. The spreadsheet has nine rows which are used to input the numbers seen in (up to) nine cells and a cell for the total value. The VBA macro starts by reading everything in - the numbers are read into the two-dimensional array vals, where the first index is the row number, the second index is the nth value in the row, and the array cnt holds the number of numbers in the row. The values are added and if the sum equals the total value, the numbers in the cells are stored. Following is the 'complex' code that does this:

Please Login or Register  to view this content.

The program works without issue, but it can become excruciating slow. I just did this experiment to quantify the problem. I started with two rows of values and worked my way up. Each row had all nine numbers and the total was 5*number of rows. I then timed how long it took the macros to complete. Here are the results:

• 2 cells, sum 10: < 1 sec
• 3 cells, sum 15: < 1 sec
• 4 cells, sum 20: 1 sec
• 5 cells, sum 25: 2 sec
• 6 cells, sum 30: 45 sec

I didn't bother going to 7 rows because I would still be waiting for things to complete. Why on earth is this taking so long? I can attach the actual spreadsheet is anyone cares.

[6StringJazzer]

What are the values in cnt(1) through cnt(9)? It would be better if you showed us all the code. It would be even better to attach your file so we can run the code. We can't run this excerpt.

If you are doing an exhaustive iteration, the number of passes through the loop grows exponentially. You get up to big numbers really fast.

[6StringJazzer]

I am updating your title because VBA isn't slow; this particular code is slow.

[groston]

Yes, I know that the number of passes through the loop grows exponentially, but the leap seems beyond believable.

[6StringJazzer]

I can't figure out how this whole thing works, but if I fill numbers in B1:J9, then every element of array cnt is 9, which gives you over 387 million operations to perform, times the 9 lines of code in that operation. So that's going to take a while. Let me know if I misunderstand how your code works.

[groston]

You are absolutely correct, but the actual performance makes no sense to me. When I put numbers in B1:J5, this means that about ~59,000 operations are needed and executing took 2 seconds. However, when I put numbers in B1:J6, which is just nine times as many operations, executing took 45 seconds, basically three times longer than expected. This makes no sense!

[6StringJazzer]

I was looking at this again today but still have no clue as to how to set up your data for a run. Please give instructions for how to enter data on Sheet1 to get this to run. I know it's been nearly a month so if you're no longer interested, let me know that too.

[PrizeGotti]

I'm pretty sure there are recursive ways to do this. So many nested loops throughout the modules. I will take a look and see if I can suggest anything, but as Jazza said, need more info on the set up.