• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / Excel apps / Mastermind in Excel

Mastermind in Excel

2013/05/15 by Frans

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.

Mastermind game
Mastermind game

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.

Mastermind addin
Mastermind addin

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.

Mastermind scoreboard
Mastermind scoreboard

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.

Mastermind DFD
Mastermind DFD

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.

Mastermind setup
Mastermind setup

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.

Filed Under: Excel apps Tagged With: Custom tab, event trapping, Ribbon, Userforms

Reader Interactions

Comments

  1. Stephen O'Brien says

    2014/08/21 at 10:58

    cool game for when i’m bored, thanks 🙂

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in