Spreadsheet Skunkworks! Excel VBA Chess Challenge

Can we create a chess computer in an Excel spreadsheet using Excel VBA?

Welcome to the Spreadsheet Skunkworks! In this video series, Chris tests the boundaries of what is possible in Excel. You will not find the usual step-by-step tuition here; the focus is on experimentation and creativity. Expect plenty of coding challenges, and some nice surprises about what is possible in Excel…

Skunk Snap

We will seek to use Excel’s functions and Excel VBA to create the functions of a chess computer. We do not know to what extent this is possible but, ultimately, we would like to challenge somebody to a game on Chess.com using our spreadsheet-based system. Along the way, we aim to learn lots about Excel VBA and its capabilities.

Some key questions:
– How can we represent a chess board and chess pieces in Excel?
– Can we make Excel understand where the pieces can move?
– Can we ‘teach’ Excel some simple chess principles (capture the other pieces, move your pieces to good squares), and get Excel to recommend a move to make?

What do you think about the Spreadsheet Skunkworks series? Do you have any ideas for the Chris? Don’t forget to leave a comment on YouTube.

New videos in the series will be added to this post.

Click here to go directly to the Spreadsheet Skunkworks playlist on the YouTube channel.

Video 1 – Use Excel VBA to create a chessboard effect in an Excel spreadsheet

Click here to download the completed file used in the video.

In the first video, the challenge is to create a visual ‘chessboard’ effect in Excel. Chris first looks at how to do this ‘manually’ and presents some time-saving tips. Then, we explore how to use Excel VBA to speed up what could be a time-consuming task. Chris first records some code to format a cell and employs a ‘loop within a loop’ to colour the 64 squares of the chessboard. Some VBA techniques on show include ‘do – until’ loops and conditional statements. Enjoy!

Video 2 – Use a ‘remote referencing’ approach to create a chessboard effect, and allow user control of square formats

Click here to download the completed file used in the video.

In the second video in the series, Chris improves code produced in the first. We explore how to implement a ‘remote referencing’ approach to creating the chess board which achieves efficiency improvements. We also build in a mechanism to allow the user to control the appearance of the squares, and look at some cool cell fill options. VBA techniques in the video include for / next loops, loop within a loop, integer and boolean variables, and copy / paste (special) coding. Finally, Chris introduces his new puppy, Kuda!

Video 3 – Use VBA to select a range of cells

Click here to download the completed file used in the video.

In the third video in the series, Chris deals with the topic of selecting a range of cells using VBA. This requires some intricate coding but, once the technique is mastered, its applications are powerful. Chris refers to the technique as the ‘range, range, range!’ technique because three components are required – Range(Range 1, Range 2); range 1 refers to the top left-corner of the range to be selected, and range 2 to the bottom-right – with all cells between the two ranges being selected. See how Chris applies this technique in the video to quickly change the formatting of the number / letter references for the chess board. Chris also discusses how to best represent the chess pieces in Excel – using cells, or shapes?

Video 4 – For Next Loop, Offset and Variables to create and position pieces

Click here to download the completed file used in the video.

We currently have a chess board, but no pieces! In the fourth video in the series, we explore how to create the ‘pieces’, and position them accurately. It would be possible to do this ‘manually’ using shapes and dragging them around the board; but, it would be better to automate this process with a ‘reset’ button, to set the pieces up for a new game.

What coding techniques are required to get this job done? Chess comprises 32 pieces in total, so we’ll use a loop, combined with a table in the spreadsheet, to repeat the ‘positioning’ process while changing important details for each piece. Some Tiger favourites are on show here, including the offset command and integer variables. We’ll also create a ‘backend’ sheet to hold information that the user does not need to see.

Video 5 – The Power of Loops

Click here to download the completed file used in the video.

You could say that the last video was heavy in terms of coding, and light in terms of what we achieved in a practical sense. But, this is typical of most coding tasks: intensive programming is required at the beginning to set up the various mechanisms but, once the foundation is in place, we can quickly ‘scale up’ the code to get jobs done quickly. This is why Chris describes coding tasks as ‘front-loaded’.

In the fifth video, we firm up the foundation and then ‘scale up’ the code by populating a table in the backend, and adjusting the loop control to iterate through the code multiple times. This allows us to generate a row of pawns, positioned precisely on the board. What facilitates this awesome click-of-a-button functionality? We are witnessing the power of loops…

Chris also talks about the important ‘subset’ of skills in Excel VBA that create disproportionate value for the programmer. There is so much to learn in VBA, and in Excel more generally. Chris argues in the video that a small number of techniques, combined together, is all that is needed to create awesome Excel applications. What are some of the main components of this ‘subset’? If you follow Tiger’s videos, you will know that loops, variables and offset feature prominently.

Video 6 – Organising Code and Harnessing a Loop

Click here to download the completed file used in the video.

The next video starts with the ‘dry’ topic of code organisation in the VBA editor. Code is usually organised into ‘modules’ in the VBA editor; well-organised consists of routines (‘macros’) that are grouped by theme, and located in named modules. Moreover, as the name of a routine should describe its function, the name of a module should give us a clue about what the routines inside it do. As more routines are created, it is important to periodically step and back and tidy things up. Yes, things might be understandable to you, but would a colleague or a customer be able to make sense of things? What about you in six months’ time?! A professional coder allocates time to keeping things neat-and-tidy, and understands that, in the long run, this approach is more time-efficient anyway.

Now, the exciting stuff! In this video, Chris creates the starting position for a chess game, which consists of 32 pieces positioned on the correct squares. This task sounds onerous but, by harnessing the loop created in the previous video, we can get it done ‘at the click of a button’. The key mechanism is the interaction between the visual basic editor and a table on the ‘Engine’ sheet in the Excel file. It is easy to type information into the table; looping through a set of instructions 32 times, the VBA editor takes this information and, via a set of variables, uses it to position objects on the spreadsheet. The video shows how, with the basic ‘for-next loop’ mechanism established, things can be ‘scaled up’ very quickly to get a mind-boggling amount of work done.

As a bonus, Chris shows how to use text formulae such as =RIGHT, =LEFT and =LEN to quickly manipulate text, and avoid excessive typing.

Now, we’re almost ready for a game of chess!

Video 7 – Let’s Get Organised!

Click here to download the completed file used in the video.

It’s been a while since the last installment of the series and it’s time to ‘tidy up’ the file. It is important to keep things ‘neat-and-tidy’ in the VBA Editor, and in the spreadsheet file generally. In the VBA Editor, this means periodically deleting unused code and (re-) organising code into named modules; in the spreadsheet, it means ensuring the correct elements are positioned on the correct sheets, among other things. Code is difficult enough without the distraction of a messy work environment; this ‘tidying up’ creates more mental bandwith for the task at hand.

In the video, Chris moves some cells to the supporting sheet, and tweaks / tests the code to get it working with the new setup. Then, we get into the VBA editor, clear out some old modules and rename the remaining ones, and add some helpful annotations. Finally, buttons are added to trigger routines such as the macro to set up the pieces, and to delete them (without deleting the buttons we had just created!) The result is a tidy VBA editor and clean user interface. Onwards!

Video 8 – Navigating the board

Click here to download the preparatory file for this video.

Click here to download the completed file used in this video.

Now we have pieces, a board and a tidy file, it’s time to think about ‘moving’ the pieces. Clearly, identifying accurate moves will be tricky given the complexity of the rules of the game; therefore, a systemic approach is necessary, taking the rules one-at-a-time. In this video, Chris creates a mechanism to begin to identify possible moves using (embedded) loops, conditional statements and position control with .cells and .offset. The coding foundations in this video should allow for ‘rules’ to be efficiently programmed in future installments.

The end result seems modest: we have simply listed the cell references of all the cells in the board area. The benefits of the approach can be difficult to spot, and a code-weary Chris struggles a little to explain them in the video! So what are they?

First, this approach works wherever the ‘Board_Area’ named range moves to; therefore, the code is flexible – it works should requirements change ie. should the user wish to change the position of the board.

Second, the code uses two counting variables which can be harnessed in future to home in on accurate moves. With two counting variables representing rows and columns, we can use logic such as ‘if variable a = variable b, then this square must be on a diagonal relative to the position of the piece’. Therefore, the coding setup in the video should allow us to efficiently identify possible moves, according to the move rules for the particular piece, in the next video.

Key techniques used:
– application.caller
– message box
– for next loop
– .cells
– .offset
– if then conditional statement
– boolean variable

Video 9 – A Sense of Mode and A Dog

Click here to download the completed file used in this video.

How can we communicate possible piece moves to Excel, in precise terms? In this video, we harness the ‘loop within a loop’ setup created in the previous video to home in on possible moves. The two looping variables provide the perfect apparatus for defining possible moves: when the row variable equals the column variable, for example, the cell must be on a diagonal relative to the origin cell (ie. the square that contains the piece). Chris exploits this logic to identify cells positioned on a diagonal – therefore highlighting possible bishop moves (assuming no blocking pieces!)

Next, how about highlighting possible moves by changing cell formatting? Code to format cells can be complex; in the video, Chris records the code to change a cell border, and discovers it is pretty complicated! So, an alternative approach is implemented.

Finally, a sense of mode. When a piece is clicked, possible moves are highlighted. But, what if the user wishes to ‘switch off’ possible moves? Suppose the player wishes to move a different piece, for example? Is this possible? Of course it is! In the video, Chris uses a cell on the engine sheet to create two modes, and a conditional statement to create a completely different outcome according to the active mode.

Techniques used:

– if then conditional statement
– .clearcontents
– call one macro from another
– use of the macro recorder
– exit sub

Video 10 – Efficiency Improvements

Click here to download the completed file used in this video.

Chris uses the concept of a ‘critical stress level’ to explain the purpose of this video. This is how you might manage your programming sessions – by monitoring your stress level, avoiding moments of high tension (that could end the programming session!), and generally keeping things as smooth as possible. To do so should progress your project – and prolong your programming career!

So, how can you manage your stress level? There are a number of things you can do that may appear routine and even boring, but make things smooth and manageable in the long run. Chris refers to them as ‘housekeeping’ tasks. They include testing, maintenance and efficiency improvements. In this video, we look at how to improve the efficiency of the routine we created in episode 9; the routine works but is ‘laggy’ – it takes some time to execute and this is bound to cause frustration for programmer and user alike. What we are aiming for is ‘slick’ functionality that keeps the user (and programmer!) coming back for more.

With this idea in mind, we revisit the code and implement improvements. In this case, we replace inefficient copy / paste code for more direct and precise border format changes. The efficiency improvement is significant – make sure you work along with Chris and feel the difference yourself, it might motivate you to reconfigure some of the code in your commonly-used files.

What other ‘housekeeping’ tasks are there? Well-annotated code is easy-to-understand and helps us recall the purpose of code when we have not worked with a file for some time. It is important to take advantage of these moments of high familiarity to go through the code and ensure everything is clear; capitalise on the time you have invested to understand it – and save time in the future.

Speaking of stress, did you feel your stress levels rise during this programming sessions? These practices should help you enjoy programming, and ensure you keep wanting to come back to a file to improve it. The practices are essential in a big project such as this one. Onwards!

Video 11 – Some Sexier Coding?

Click here to download the completed file used in this video.

With the previous video’s ‘housekeeping’ tasks complete, it’s time to push on with the ‘sexier’ Excel VBA coding. In his opening remarks, Chris comments on the rhythm of application development – periods of dry consolidation activities (as in the previous video) alongside spurts of quick development. With a solid foundation in place, we seek to achieve such a spurt in this video!

Let’s remind ourselves where we are. We have told Excel how to identify the ‘diagonal’ squares relative to any cell on which a ‘piece’ (or shape) is placed. Though this is a useful first step, it is hardly very practical in the big scheme of things. So, how can we refine the existing routine and move towards identifying accurate moves?

We establish a conceptual framework for this complex process. Each piece can move in one of a number of ways: in straight lines, or diagonally, for example. So, we set up a table on an existing backend worksheet to communicate this to Excel, with column headers representing possible move ‘types’ eg. diagonal or straight.

This is a good example of how to use a worksheet in combination with VBA code; though the worksheet is not absolutely necessary (we could programme all this in the VBA editor), with lots of information to input, check and amend later, the visibility and accessibility of a sheet in the file represents the best option. So, let’s begin to populate the table.

With some of the basic information populated, we consider how to extract information from the table. Suppose we click on a bishop which can move diagonally but cannot move in a straight line; this information is in the table and we can use a worksheet function in Excel VBA to extract it.

By accessing worksheet formula such as VLOOKUP through VBA, ‘Application.worksheetfunction’ is an efficient way to work with a spreadsheet; but programming these functions is not easy because the usual prompts that Excel provides in a worksheet are simply not available to us. It requires a high degree of familiarity with spreadsheet formulae and their components. Our usual methodical approach helps: we build it up step-by-step, testing along the way, and finally debugging to achieve the required outcome. Nice!

Finally, we test the mechanism created. Correct movement patterns for diagonal movements are displayed on the board. Now, how can we scale up this approach for other movement patterns? See you in the next video!

Video 12 – The King Moves!

Click here to download the completed file used in this video.

In video 12, we seek to code other move types, using the previously-coded ‘diagonal move’ as a blueprint.

Before getting into the coding, however, Chris tweaks the approach to eliminate the ‘Dia_Poss’ variable; the variable-based approach would have created a multiplicity of variables that may have slowed down code execution. Though the new approach, demonstrated in the video, does use more syntax, the fact it does not require variables makes it Chris’ preference, though to some extent this is a question of coding style. What do you think?

With the new, leaner routine in place and tested, Chris looks at other possible move types. First we deal with ‘straight line’ moves that a rook would make. We ‘talk it out’ to make the concept crystal clear, and then code and test with our usual ‘steady and systematic’ approach. Success!

With the required updates made to the supporting database on the Engine sheet, the diagonal and straight line moves are done; so, what is next? The king’s movement pattern poses a new challenge. He moves both diagonally and in a straight line, but only a single square. How would we go about coding this kind of limited movement?

Here Chris shows the importance of clear conceptual thinking by actually sketching out the possible moves in Excel. This leads to an idea about how to translate the movement pattern into VBA coding using the two position control variables – Row_Count and Col_Count. The testing shows that our approach works.

Before concluding – a challenge: can you conceptualise and code the knight’s movement pattern? We will try that next week – see you then!

——————————–

What do you make of the spreadsheet skunkworks series? Any questions about Excel VBA? We would love to hear from you. Contact us here.

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.