In this post an Excel worksheet implementation of the well known Mastermind game. I developed the Mastermind part of this workbook several years ago. For this showcase app I added a scoreboard, and turned it into a so called dictator application.
Dictator app
Dictator applications take control of the entire Excel session, modifying the user interface to make it appear and behave like a normal windows program.
(Professional Excel Development, by Bullen, Bovey, Green. 2005, p 143.)
Mastermind takes control of the entire Excel session simply by creating a new Excel instance in which the workbook runs privately. The new Excel instance is started from an add-in.
So, you shouldn’t open the Mastermind game file from explorer (you will get a message if you do), in stead you must open or install the add-in, go to the add-ins tab and run the Mastermind game file. The add-in creates a new, private instance and opens the game workbook, which modifies the userinterface (ribbon and keyboard). Now the user can’t open or create workbooks, or do anything else that is not available in the Mastermind interface. The workbook is in control. All the user can do is play the game and have fun.
Main advantage of this approach is that we avoid the need to store settings on activation of the workbook, and restore these settings on deactivating (with the formulabar being the exception here). We simply don’t allow other workbooks in our instance. For this reason class CApplication traps the WorkbookOpen event and redirects other workbooks to a new Excel instance.
The Mastermind board
The visual part of the Mastermind board is found in worksheet SBoard. The module of this sheet uses two classes: class CBoard creates and manages a collection of the 76 peg objects, which are of class CBoardPeg. Furthermore class CBoard traps several worksheet events to implement the user interaction as described in A true click event on worksheets.
Mastermind’s ribbon starts from scratch, hiding all tabs but the File tab. Also all contextual tabs and most commands from the File menu (backstage) are hidden. A custom tab is added with command buttons:
- File Close, which is cloned from the ribbon;
- New Game, which resets the board;
- Zoom In/Out/100%: resize the Excel main window to your preferences;
- Scoreboard, a “toggle” button to show/hide the Scoreboard userform.
All keys and key combinations of the keyboard are disabled by means of class CKeyboard. Exceptions are the usual windows system keys like Alt, Ctrl-Shift-Esc, Alt-Ctr-Del, etc.
The Scoreboard
The user’s results are saved in the MastermindScores.xml file. On workbook open the file is read into a table on hidden sheet SScoreboard. Userform FScoreboard takes this table as input and presents the results in a listview control. When the user has a new result the table and the userform are updated, and the table data is saved/exported to the xml file.
The userform is an easy to re-use general purpose object. All the userform needs to know is it’s source listobject, and then it builts itself dynamically based on the properties of the table (headers, widths, alignment, etc). The userform stores it’s position on the screen, after each show it returns to it’s previous position. The userform is modeless.
The data flow diagram (dfd) below shows how the main functions and data stores relate to each other.
A data flow diagram is a modeling technique I often use. Solid lines are true data flows, dashed lines are control flows.
The User
The workbook takes application.username as the username. It searches this name in the table, and creates a new row if this user doesn’t exist. The user cannot choose or change his name. Mastermind is intended to be used on a single pc where two or more users can logon but will not play simultaneously. Mastermind is a single user play and is not suitable as a multi-user application in a network.
Setup
It is assumed that the three Mastermind files (add-in, game and scores) are stored in one location. In that case, if you run the add-in, it will always find the game file.
However, if you prefer you can store the add-in file in one location and the other files in another. For example, you can install the add-in as described in Add or remove add-ins. In that case you need to tell the add-in where to find the game file. For this you can use the Mastermind Setup userform, available from the add-ins tab.
Setup stores the location in the registry. The xml scores file must always be in the same location as the game file; if the scores file can not be found the game workbook will create a new one.
Password protected and read only
The vba-projects in add-in and game files are password protected, which is required for adequate error handling. Furthermore, the add-in opens the game workbook in readonly mode. This is not required, but now it’s obvious that the game workbook doesn’t store any data, which makes it easy to update.
Excel as code breaker?
In this app Excel is code maker and you are code breaker. If you use an optimal strategy you can break every hidden code in 4.3 guesses average, and a maximum of 5. To remind you to this these figures are presented on the Scoreboard, labeled Koyama/Lai. In 1993 they found the optimal strategy, improving Knuth’s strategy of 1977. So, now it’s your challenge to create an Excel workbook in which you are the codemaker, and Excel the code breaker using the Koyama/Lai strategy! Mastermind 2.0? See: Kenji Koyama, Tony W. Lai, ‘An Optimal Mastermind Strategy’. Journal of Recreational Mathematics, 1993.
Mastermind in the cloud
There are many sites where you can play a game of Mastermind. On several sites you can find Excel implementations. For example:
- In CodeCracker Excel MVP Andy Pope uses a userform as board, which makes it possible to drag and drop the pegs (on a sheet this is not possible).
- Andrew Engwirda (another Excel MVP) also used a userform to create a “sneaky” MasterMind and named it Rate Calculation. Fun. Code updated in 2012.
- On Mr Excel’s site you can find a worksheet implementation of the game (entry 12).
If you are looking for The Ultimate Mastermind Experience then I would recommend ColorCode by Dirk Laebisch. No Excel, no VBA, just a great app!
Conclusion
This post is about Mastermind in Excel. However, my primary goal in developing this showcase app was to bring together a few techniques to create a full dictator app as defined above. The Mastermind part was just for fun!
I hope this pixcel helps. Excel!
Download
Mastermind-v1.zip
Dowload the zip-file, open it and copy the three files to a directory. Start the add-in file, go to the Add-ins tab and select Play.
cool game for when i’m bored, thanks 🙂