The Sudoku lab

Here we include the original box from our feature on how never to pay to play Sudoku again, which explains how the Excel file works. This version is longer and more comprehensive than that published in the magazine.

To obtain a free copy of the download, click here.

So how did we build the Sudoku Puzzler? If for you the answer is "I couldn't care less", then we hope you enjoyed the puzzle. But if, as we know some are, you're interested in more advancxed Excel functions, we'll give you a glimpse under the bonnet of our spreadsheet.

Click the tab at the bottom of the spreadsheet marked Sudoku Lab. This is the engine room. The challenge in building a Sudoku puzzle is how to spread the numbers 1 to 9 in random order over 81 squares but within Sudoku rules.

Using the formula =RANDOM() nine times, column A will show random number between 0 and 1; the value of them is unimportant (image 12). Then the formula =ORDER(A3;A$3:A$11) in cell B3 defines which place the number in A3 takes within this series. With nine numbers, that is always a number between 1 and 9. The cells below do the same thing: for each random number that sits next to them, they define which place it takes in the series. This is how it places, completely at random, the numbers 1 to 9. Press the button marked Draw and you will see grid being calculated in the blink of an eye.

Three in a pan
To consequently fill these numbers into the diagram, they are split in groups of three. And we sort these three below each other (image 13). The formula =LARGEST(B$3:B$5;1) in C3 places highest number from B3:B5 at the top. But after a while that becomes predictable: numbers 1and 2 are never the highest in a triplet and therefore will never be in the top. That’s why we let B12 to B14 give 1,2 and 3 at random (just like in the previous step) and replaced the 1 at the end by B12. Should there be a 3 in B12, then the lowest of the three will show in C3. And because the numbers in B12 to B14 change, the triplet from B3:B5 will always be shown in a different order (image 13). Both triplets underneath will take turns in scrambling the numbers that are shown in column B next to them.
The top of column D shows the numbers of the second triplet of column C, but in a different order. Underneath it shows the third triplet from column C, followed by the third triplet, again scrambled. This is how we fill the whole diagram. To verify we count every column, line and block: the outcome is always 45 (image 14).

Organising the chaos
This still contains a fixed order: 7, 4, 9 often appears. So the numbers need to be scrambled further. We can’t just mix up all the numbers of course, but columns can move within a group of three. We do that through sorting. First we copy the numbers to a new diagram, to be precise: we ‘copy’ and ‘paste special’ for ‘values’ only so just the numbers will transfer (not the underlying formulas). In the second diagram we again randomly place the numbers 1, 2 and 3, again from B12 to B14. Then we sort the first three columns (cells N2:P11) in the order of 1, 2 and 3 above them (so sorting from left to right, this is a setting within sorting). After that, the next triplets of columns will be sorted. Finally we do the same thing horizontally: every time a group of three will be sorted (from top to bottom this time). This copying and sorting is defined in a macro; u can see this happen when you click the button ‘draw’.
Now the chaos is complete and that is exactly what we want: the squares are all defined according Sudoku rules (image 15).

Levels
To understand how levels work we examined a few puzzle books. The level is defined by the quantity of numbers already given in a new puzzle. A Sudoku exercise on average will give 45 numbers when set at a simple level, compared to 36 at an average level and 27 at a difficult level. We figured: adding 1, 2 and 3 will produce an even outcome five times (to be specific in 1+1, 1+3, 2+2, 3+1 en 3+3). We again placed a random 1, 2 and 3 above and next to the third diagram, now from a different series of numbers (image 16). When we add these in a block of nine, five of those will have an even outcome. From nine blocks we thus get gives 45 numbers, see the areas on the left.

The same addition within every block will also give four odd outcomes (1+2, 2+1, 2+3 and 3+2). From nine blocks we thus get 36 numbers. And the outcome 4 appears three times (in 1+3, 2+2 and 3+1); so if we apply this in the whole diagram we get 27 numbers. Agreed?

The menu that allows you to choose a level generates on another worksheet a 1, 2 or 3 in U11 (invisible); that same number appears in P15.
With this information we have filled the whole diagram with formulas. The first one sits in Y3 and defines the chosen level 1 (simple). It then looks whether the number above its column is even, and in that case will take the number from N3 and transfer it to the puzzle. In level 2 (average) this only happens if the number is odd (see image 16) and in level 3 the number needs to be 4. This way each puzzle block will show either five, four or three numbers. If you press F9, the pattern of the puzzle will change. This is because it changes the numbers in columns AS and AT, which changes the 1, 2 and 3 in this diagram. This also explains why each time different squares show their numbers.

Symmetrical pattern
The symmetrical pattern is defined in the diagram below. The top lines match the diagram above it, the dark part contains formulas that ensure that diagonal squares are either filled or not filled. This creates symmetry. That’s why sometimes fewer numbers are shown, which brings more chance into the game (image 17). 

What are the macros doing?
A click on the Reset button on the first worksheet will bring back the original puzzle. Behind it sits a macro with three important steps. To see this code, r ight-click on the Reset button. Choose assign macro and then modify (image 18). This brings you to a Visual Basic window at Reset macro’s location.

The reset macro
The Visual Basic window contains green lines with an apostrophe for information and black lines that do the actual work, acting out commands.
The macro starts with Application.ScreenUpdating = False to reduce the flickering of the screen. De code Range("B2:J10").Copy copies B2 to J10 (the top left diagram) to the Entry diagram. Behind the Entry diagram sits Conditional Layout, which colours cells: red if a numbers was entered twice and green or yellow if to give hints. It hides a raft of formulas that we wouldn’t miss for the world! Click on cell L2 and then select in the Layout menu: Layout, Conditional Layout. That’s why the macro here uses modify, paste special, values to paste the new puzzle exercise. In Visual Basic language that is Range("L2" ).PasteSpecial Paste:=xlValues.
In addition, the macro closes help columns and lines (so you will always start without hints). This is done through the command Hidden = True (image 19).

How the timer works
The clock that keeps track of playing time is reset through the command Range(" F34").Value = Format(Now, "hh:mm:ss"). This places in cell F34 the current time in hours, minutes and seconds. When you click the Reset button this is your starting time. You can’t see this time in the worksheet because we made it light yellow, but you select the entire sheet (CTR+A) you can read it.
In the top of the Entry diagram sits the formula =IF(SUM(L2:T10)>=9*45;" Done! Your time is "&TEXT(NOW()-F34;"m:ss");"Entry:") This one looks at what the time is now, deducts the starting time in F34, calculates the difference in minutes and seconds and presents it together with ‘Done! Your time is’ as soon as all squares have been filled (and the sum is 45 in nine ways). The combi nation of macro and formula calculates your time the moment you’ve finished the puzzle (image 11). The explanation of other commands we’ve attached to the code itself.

New puzzle: a macro within a macro
For a new puzzle you click the button ‘New puzzle’. This one needs to place numbers at random, copy the whole solution to B100 (so you can do some cribbing), move the puzzle from the lab to the puzzle diagram and then move it to the entry diagram. Hold on a second: wasn’t the first step (placing numbers) done by the macro ‘Draw’? And didn’t the macro ‘Reset’ copy numbers from the puzzle diagram to the entry diagram. We don’t want the ‘New puzzle’ macro to duplicate all this as we don’t like double work. This macro can exercise the other macro ‘Draw’ and ‘Reset’. Will you look with us? You find the code by right-clicking on the button ‘new puzzle’, and then assign macro, modify.
The macro ‘new puzzle’ goes to the other worksheet and sets the macro ‘draw’ in motion. Once that’s done it copies the complete puzzle from cells N3 to V11 to B100 of the first worksheet, so you can do some cribbing later on (image 21).

The macro chooses on its own
Your choice of either random or symmetrical layout of numbers (in the menu) sets either a 1 of a 2, which we place in W11. The block starting from If then takes the correct diagram. The rule If Sheets("Solve Sudoku").Range("W11").Value = 1 Then looks at the values is W11. If there is a 1, you selected symmetrical and cells Y15 to AG23 will be copied: see the bottom diagram of the lab. Alternatively Y3 to AG11 will be copied. Is that a clever macro or what? And the exercise always ends up in the puzzle diagram, the top left of the worksheet.
Then the macro ‘Reset’ is activated: it copies this puzzle to the entry diagram and sets the timer to zero. You see, clicking the ‘New puzzle’ button also resets the clock, as the macro activates the ‘Reset’ macro. And with the final command ‘Beep’ the macro produces a sound to alert you that you can get going (image 21).
Seems like a long story but it all happens before you can blink your eyes.

Modifying the macro
You don’t have to drone into the code to make modifications. Here is a simple change. The command Beep in the code of the ‘New puzzle’ macro produces a sound. If there is an apostrophe left of this line, than the line is ignored. So if you don’t want the sound, type an apostrophe left of the line Beep (bottom of image 21).

Macro security
You might get a warning for viruses as soon as you open the file. Or you might click a button and read that the macro have been disabled. This has to do with macro security. Our Sudoku maker does contain a few macros but they really do not contain any viruses. Either way you need to do the following. Click on Extra, Macro, Security and select the window Average. Then close the file and open it again as this will change the security. In the meantime Excel can remain active. To do this click File, Close, and at save modification click on yes. To open you again click File and look at the bottom of the menu, it will have CID Sudoku Maker.xls. Click on it and it will open the file. At the question if macro’s can be enabled click yes. This way you’re in control of your macros every time. You can also pick low in the security window. Then there will never be a question and macros will be activated every time. The only thing to be cautious about is that the Sudoku bug doesn’t get hold of you!