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
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
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!
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
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
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
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?
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!
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!
Video 13 – LIVE – Clear Conceptual Thinking!
The 13th video in the series (and the first that is livestreamed) sees us programme the knight moves. Before we get into that, we use a viewer suggestion to improve the efficiency of the conditional statements programmed last time. Thanks to WoodRodent for the suggestion – don’t forget to leave your ideas in the comments too, this is a team effort!
Chris talks about the importance of clear conceptual thinking and we sketch out possible knight moves on a new sheet in the file to allow us to visualise them. Chris creates a table to allow us to think about the possible moves in terms of rows and columns away from the knight square. With the concept clear, we set about trying to ‘translate’ it into VBA, using the previous move structure as a starting point.
The code is tricky but, with some help from the viewers (thanks to Mike in particular!) we manage to get the job done. We can now visualise possible knight moves by clicking on the knight. Next time, we will think about programming the pawn moves…
Live interaction allows us to touch on a number of interesting topics, including when it is appropriate to change the zoom level of a spreadsheet, and how to return the view of a worksheet to its first row and column. Make sure you join for the next skunkworks livestream and put your questions to Chris.
Video 14 – LIVE – Future-Proofing?
We are working through the process of translating the logic of chess moves into Excel VBA. In part 14 of the series, we explore how to programme the pawn moves. The pawn is the least powerful piece in chess; but, it presents unique challenges to the computer programmer: how do we programme the pawn’s first move, where it can cover two squares? How about the pawn’s unique diagonal capture? We will have to deal with these challenges as we move through the series.
Our approach is familiar to regular viewers. We aim to be steady and systematic, clear about the concept of our approach before dealing with the technical side, and we test frequently throughout the programming session. The ultimate aim is to keep our stress level down so that we can continue the programming session – without throwing the computer out of the window!
With this in mind, we programme the simplest ‘one-square’ pawn move by recycling a chunk of code that is working, and making tweaks. At the same time we touch on a discussion about ‘future-proofing’ spreadsheets. Our Excel VBA solutions should respond to changes in business circumstances; at the same time, the kind of dynamic coding required can be challenging and time-consuming. So what is the right balance to strike?
With the simple pawn move tested and accurate, we consider how to programme the pawn’s initial ‘two-square’ move. We look at how you can use existing resources (in this case, an existing variable) to get new things done. We invest a little more time to adopt an approach that is ‘future-proof’ in as far as it allows for rows to be inserted above the board.
In the next video we will explore how to programme the diagonal pawn capture moves which entails some new mechanisms in the spreadsheet’s engine. See you next week for the livestream!
Video 15 – LIVE – An Unexpected Challenge
This live edition begins with an unexpected challenge: functionality that we built in last time appears to have created a problem – the white knight moves are no longer accurate. No matter, we simply go into our debugging routine! Chris shows how to work through the code to isolate the problem and then fix it. In this case, we sacrifice some efficiency for simplicity – substituting two simple(r) constructs for one long and complicated one.
Crisis averted! Now we can continue with programming the piece moves. Or can we?! The pawn poses a unique challenge: its way of capturing is different to its way of movement. This means we have to identify where the other pieces are on the board in order to calculate the possible moves. The need to store this information creates more problems – we have to restructure the database on the ‘engine’ sheet, and make tweaks to the code to ensure everything is still functional.
With the that done, Chris sets about creating a routine to tell us the position of each piece on the board. We manage to express this information two ways: first, in terms of the Excel ‘cell’; second, in terms of the chessboard rank-and-file co-ordinate.
Throughout the video, various topics in Excel development are touched upon, including Chris’ analysis of world chess champion Magnus Carlsen’s problem-solving style – what can he teach us about how to work with Excel?
Video 16 – LIVE – The Pawn Captures
The main topic of the 16th episode in the Excel VBA chess series is programming diagonal pawn moves. Before programming these, however, we discuss the rhythm of Excel VBA development and take the opportunity to improve the efficiency of a routine we added last time using With … End With.
Computer programming is not ‘steady effort’; rather, there are spurts of activity between periods of conceptualisation and tidying up; here we are in the ‘tidying up’ phase. Remember – nobody wants to work in an untidy workspace; so take the time to tidy up code, and keep yourself coming back for more.
With that done, we move onto some conceptualisation – how can we describe the required code for the pawn moves in words, before moving into the VBA editor?
In this case, we are looking for a diagonal move AND and an opposing piece on the diagonal move square. With the concept clear, Chris sets about creating the required code, with some help from viewers in the chat! Thank you to everybody for watching, by the way.
Viewers of the channel will be familiar with how we handle the programming. Rather than creating the code ‘from scratch’, we replicate existing syntax that we know is working, and make the required tweaks. An attempt to simplify matters by combining together two lines of code meets with failure, but, no matter – we have backed up the file so can very easily ‘undo’ this.
Next time we will look at programming the pawn capture moves for the black pieces. Then, we have to think about how to programme the idea of a ‘blocking’ piece; Chris has been mulling over this difficult coding challenge for some time, but has no ideas yet for how to confront it – what do you think?
In between the coding activities, Chris assesses the state of business education, sets out the future direction of the channel, and mentions a funny clip about young people trying to use an old phone.
Why not leave a comment under the video on the YouTube channel? We would love to hear from you.
Video 17 – LIVE – A Safe Space?
So, where are we in the chess challenge? We can identify possible moves for pieces, but this assumes no other pieces on the board. How do we account for ‘blocking’ pieces – a piece on the same rank or file as the target piece, which limits its range of movement? This is clearly an important rule in chess that we cannot ignore. But, how on earth are we going improve the code to account for this?
In this livestream, Chris talks about his approach to this kind of daunting coding challenge. First, are you separating the concept from the implementation? As Chris explains, confusing conceptualisation with implementation, or trying to do both at the same time, can cause stress for the programmer. In other words, trying to work out what to do whilst trying to do it gets us in a real mess!
Chris suggests separating these two distinct phases. We explore this idea in the livestream by creating a new Excel file – a ‘safe space’ – for experimentation and conceptualisation. It’s like a playground away from the hustle and bustle of real-life – or lines and lines of VBA code in an Excel application. Simply looking at code consumes mental bandwith and limits our creativity, so why not open a new file to test a concept?
With the new file open, Chris sets about explaining and demonstrating his idea. It centres around controlling the mechanism that loops through the squares on the board, allowing us to identify possible moves. Chris argues that, in order to account for blocking pieces, direction is important: the looping mechanism has to start at the target piece and count away from it. If a blocking piece is encountered, any further moves in that direction are ruled out.
This is the concept at least! By combining together a table in the spreadsheets with three loops, a prototype version of the mechanism is created. To be developed further next week. What do you think about this idea? Leave a comment below the YouTube video.
Video 18 – LIVE – Exiting A Loop?
This shorter edition of the spreadsheet skunkworks packs a punch! Chris applies some sophisticated loop controls to the chess challenge. Perhaps you can find applications for the demonstrated techniques in your work?
This week, we try to work out how to deal with ‘blocking’ pieces, and how to eliminate candidate squares that are the ‘other side’ of the blocking piece. To do this, Chris sets up ‘move components’ (eg. up, down, left …) and uses a loop to work through each one for each piece. Then, for each move component, candidate squares are looped through; here, direction is important – the loop starts at the target piece and moves away. If a blocking piece is found, we need to exit the loop so that ‘impossible’ squares are eliminated. Easy!
After some demonstration and discussion, Chris implements a mechanism to exit the ‘for next’ loop and achieve the required effect. There it is, another small but important step towards our chess computer.
Towards the end of the livestream, Chris deals with viewer questions on Excel-related topics, and discusses some possible video ideas. Why not join the stream next week and put your questions to Chris? See you then.
Video 19 – LIVE – A Tough Session
What happens when a coding session goes wrong? In this Excel VBA chess challenge livestream, we see how to try to react when things do not go as planned. With a project as complex as this one, it is bound to happen! Watch as Chris implements some code, achieves an unexpected result, then wrestles with different options about how to proceed. In the end, we implement a workaround that sacrifices some simplicity but delivers the required functionality – almost!
See you next week for more #ChessChuesday
Video 20 – LIVE – Integrating New Ideas
It’s not quite in the ‘live development’ spirit of this series, but Chris took development offline prior to this week’s stream, to do some ‘heavy lifting’ that is difficult to execute live (as we found out last week).
The ‘heavy lifting’ in this case is the integration of new ideas from the separate ‘safe space’ Excel file (that we used last week) into the original file that contains most of the code.
In the first part of the livestream, Chris explains how he integrated the new code and reconciled the two routines (the old and the new), pointing out the features of the previous macro that have been replaced, and those that have been retained. A quick and limited demo shows the new code in action. It seems more efficient, flexible and powerful, but our job now is to ‘scale it up’ to get it work for all pieces, and for that crucial ‘blocking piece’ scenario.
In the rest of the stream, Chris takes viewer questions and discusses the upcoming ‘Excel Metaskills’ live event. Remember, you’ll receive exclusive advance notice of major live events on the Tiger mailing list (sign up on the Contact page of this website).
See you next week for more #ChessChuesday – Tuesday, 1800 UK time.
Video 21 – LIVE – Call For Collaborat
In this shortened stream, Chris sets the Chess Challenge off in a different direction. With a fair amount of audience interest, it seems sensible to open up the project to those willing to contribute. So, if you would like to help out with the chess challenge, get in touch with Chris via the Tiger Spreadsheet Solutions website.
No significant development place this week, but there is more coding to come next week.
See you in episode 22, Tuesday, 1800 UK time. #ChessChuesday
Video 22 – LIVE – A Return To Form?
In this stream, we get stuck into the heavy coding once more. Specifically, we set about implementing our new approach to programming piece moves.
Chris programmes movement patterns for the queen, bishop and knight. The usual techniques are on show: loops to loop through rows in the Excel file, offset to control position, and conditional statements to take the code one of two ways, according to if a move is possible or not. Programming the knight moves is a tricky challenge – Chris manages to get it done with the help of the viewers!
Thank you for watching and don’t forget to leave a comment below the video on the YouTube channel, Chris will get back to you.
See you in episode 23. #ChessChuesday
Video 23 – LIVE – The Pawn Moves (Again) And A Mistake!
In episode 23 of the chess challenge, we implement some of the pawn moves using our new coding approach. The programming throws up a number of challenges – how to account for ‘direction’, for example, since black pawns move down, but white pawns up. The humble pawn turns out to be one of the most difficult pieces to programme, and we haven’t even thought about promotion or ‘en passant’!
Did you spot the mistake at the beginning of the stream? Chris deletes some code from the VBA editor which is actual supports the king’s movement. Thank you to Jeff for pointing this out in the comments, it will be a priority in episode 24 to get this fixed!
Thank you for watching and don’t forget to leave a comment below the video on the YouTube channel, Chris will get back to you.
See you in episode 24. Tuesday, 1800 UK time.
Video 24 – LIVE – More VBA Pawn
In installment 24, Chris does more work on the tricky pawn moves. Some solid progress is made, but we do not quite manage to complete the programming of the diagonal capture moves.
The stream ends with Chris in a pickle trying to work out some complicated ‘application.worksheetfunction’ ideas. Application.Worksheetfunction is incredibly powerful but can be difficult to implement. Don’t worry – we’ll sort these out next time.
See you in episode 25, next week. #ChessChuesday
Video 25 – LIVE – A Hiatus
There is a no live coding in episode 25 of the chess challenge, but Chris does present the offline work he did to resolve the issues from the previous stream. By separating out the conditional statements, the logic is clearer and the diagonal pawn moves are functional using application.worksheetfunction to establish the colour of the possible ‘capture’ piece. This stops our pawns trying to capture pieces of their own colour! Remember to download the file from the website to check it out for yourself.
Next – an announcement. This will be the last Chess Challenge video for a short while. for reasons Chris explains on the stream. Will the chess challenge ever return? Watch this space!