I’ve completed the first version of my Sudoku helper workbook.
I went ’round and ’round about how to set up this conditional formatting without having to set up each cell individually. Using Gary Waters’ help, I came up with a non-VBA formula to simulate the COUNTIF function to be used with noncontiguous ranges. I even changed all my named ranges so I could pare it down to 253 characters – conditional formatting only allows 255. For some reason, I still couldn’t put that formula in CF without an error. I’m not sure why.
Eventually, I gave up and decided to use VBA. At least this way it would be understandable to someone else, albeit really, really slow.
rRow As Range, _
rCol As Range, _
rCheck As Range) As Boolean
Dim rCell As Range
For Each rCell In Union(rGrid, rRow, rCol).Cells
If rCell.Value = rCheck.Value Then
IsNotValid = True
Exit Function
End If
Next rCell
End Function
This made it nice and easy to enter the conditional formatting. Once set up, I came to the realization that people won’t want to wait 10 minutes for the workbook to calculate every time they enter a number. Ten minutes may be a bit of an exaggeration, but it was pretty darn slow and I have a fairly new machine. One problem is that conditional formatting formulas are volatile even if their arguments don’t change. Every number entered caused 729 conditional formatting formula calculations. The sheer volume of calculations coupled with the fact that there was a VBA function involved made it unbearable.
My first thought was to limit when the function calculated. A few loops in the function would take more time, but the reduction in total function calls would be worth it. I set up this elaborate scheme to only allow the function to run for those cells whose precedents changed. Oh yeah, except for one problem: the function still calculated. It didn’t go through the meat of the function, but the code still ran. That means the function was returning FALSE for all those cells not “affected”. While it was fun to watch the colors change, it wasn’t producing the correct result.
Then, lying in bed, it dawned on me that I had an efficient calculation engine at my disposal. If I only wanted to recalculate cells whose precedents changed, then it I would want it to mirror the way a worksheet calcs. “Hmmm”, I thought, “where am I going to find a worksheet?” That’s right, I’m working in Excel. Duh!
I spent a good deal of time entering and copying the conditional formatting and I wasn’t looking forward to doing it again. I wrote this little sub to do the dirty work for me. It puts the CF formula in a cell off to the right, deletes the CF, and re-adds the CF pointing to the cell.
Dim rCell As Range
Dim sForm As String
For Each rCell In Range(“D1:AV36”).Cells
sForm = “”
On Error Resume Next
sForm = rCell.FormatConditions(1).Formula1
On Error GoTo 0
If Len(sForm) > 0 Then
rCell.Offset(0, 60).Formula = sForm
rCell.FormatConditions(1).Delete
rCell.FormatConditions.Add(xlExpression, , _
“=” & rCell.Offset(0, 60).Address).Interior.Color = vbBlue
End If
Next rCell
End Sub
Muuuuch faster. Now that I’m using worksheet cells, I can rid myself of the VBA function and go back to a pure worksheet function solution. Nah, maybe some other time. It’s fast enough for me.
Note that the purpose of this workbook is to help you solve the puzzles. It doesn’t solve them for you. Andy Pope already has a nice workbook for that. Also, the workbook does not generate puzzles to solve. You have to enter the “givens” yourself. I thought it would be a nice feature to be able to create puzzles, but it was too hard. I even went so far as to try stealing Andy’s code for it, but in the end I just left well enough alone.
I sat my wife down to solve a few puzzles using this workbook and I came to appreciate the value of usability studies. What seemed natural and intuitive to me was simply befuddling to her. She wasn’t using my nice white and blue visual cues to solve the puzzle, she was solving it as she would by hand. She helped me with the color scheme and border thicknesses so that it would be easier to see which available numbers were associated with which cells. She didn’t necessarily like the magenta choice for given values, so I don’t want to attribute that to her. Even when I forced her to use the whites and blues, it wasn’t as easy for her as I’d hoped. After the first couple, though, she was knocking them out quickly. I guess it just takes a couple of attempts to train your eye to see the gaps. I wrote a little About blurb to try to lessen that learning curve.
If you love to solve these puzzles, you probably shouldn’t download the workbook. It kind of takes all the challenge out of solving them. For easier puzzles, you can solve them as fast as you can type the numbers in (lots of bunnies). Even the puzzles designated as “super tough” in my Sudoku book only take about five minutes and that’s simply the time it takes to scan the rows, columns, and grids for white numbers.
As always, your comments are welcome.
It works like a charm!
Not that I doubted about it.
http://www.croexcel.com/uvod/sudoku.zip
Regards
zeljko
Zeljiko
On opening your Sudoku.xls, my other open Excel workbooks, and Excel herself, are invisible and unavaliable. Only the userform is visible. When the userform is closed, excel is gone and my work is lost.
Don’t ever ever ever write something like that again.
Beste wishes Harald
Dear Harald,
Sorry about little program error.
Now is everythin OK.
U have to be more carefull next time and always use SAVE AS. ;-)
Best regards Zeljko.
Dick,
I’ve just sent you a revised copy of DKsudoku using normal formulas iso your UDF. Much faster :)
I’ve added 81 named formulas to keep track of the available numbers per cell using FREQUENCY function.
=Frequency((Row1,Col1,Grid1),Bins)
The formulas used for the CF now look like:
=IF(ISBLANK(CELL01),N(INDEX(FREQ01,G3)>0);1)
Rgds, Jurgen Volkerink (aka keepITcool)
I ve got one that I put into Word using Word VBA just for a bit of challenge. I also had an Access Sudo Solver which ran using only queries but it fails on the most fiendish ones where simple logic is no longer sufficient.
How do you modify the possible numbers, e.g. whittle down the possibles? For example if you see the same pair of numbers in two cells in a row, then those numbers cannot be in any other cell in that row. How do you mark them as unavailable in those other cells?