Author Archives: Chris Mortimer

What are Excel VBA Variables?

By Chris Mortimer

CLICK HERE to download the EXAMPLE FILE used in the video.

I like to think of an Excel VBA variable as a place to store information. Now, this definition is not particularly exciting! But, thinking about variables in this way can help understand them and maximise their impact in your work.

Excel VBA Message Boxes

Let’s explore this definition a little more. You could compare an Excel VBA variable to a cell in a spreadsheet – which is, of course, where we usually store information in Excel. Indeed, you could substitute a VBA variable for a cell in many Excel VBA routines and still get the job done; but, it would not be as slick or efficient. Nevertheless, ‘a place to store information’, a bit like a cell, is a reasonable conceptual starting point.

How do we typically use Excel vba variables in coding? Let’s look at the example of a loop. A loop is a repeated set of instructions that allows us to get mind-boggling amounts of work done. However, they require careful control (all of us have been stuck in an endless loop at some point!) VBA variables allow us to achieve control in a neat-and-tidy way.

We could, theoretically, reference any object in Excel to control a loop. For example, we could say ‘keep running the loop until the value in cell A1 = 10’. This would work perfectly well, if the value in cell A1 incremented up or down towards 10 as the loop ran. The obvious downside is that we would have to update an object in the spreadsheet (in this case, a cell) which diminishes the efficiency of the code. Further, a spreadsheet cell is usually visible to the user, which may not be desirable.

With this example in mind, what advantages does a variable offer? First, it is quick and easy for Excel to work with, because Excel can change the value that is ‘assigned’ to the variable very efficiently. Remember, a variable is a place to store information; it is faster for Excel to store and recall information using a variable, than to interact with the spreadsheet to change a cell value.

Second, a variable is not usually visible to the user. This means variables are easier to control because, most of the time, they are ‘hidden’. It is not that we don’t trust the user! Rather, variables control processes that should not be easily changeable, such as looping through code. Variables offer superior control to the programmer, which helps create robust applications that do not break.

With these obvious advantages, it intrigues me that people tend to shy away from using VBA variables. Why is this? As is often the case with Excel, it is problem of communication. The language of variables is not easy to understand, so people think ‘they must be for computer programmers only!’ String? Integer? Long? What is the point of these terms, apart from confusing and often deterring the layperson? Let’s dig into it…

In a practical sense, variables help Excel organise its memory – where Excel compiles information to display what we see on the screen. In an efficient application, you could compare Excel’s memory to a beautifully designed wardrobe – it has differently sized spaces for different items, making everything easy to store, and to find. This is the point of variables – they tell Excel the kind of information that is coming; Excel then allocates an appropriately sized space to store it. This is why we ‘declare’ variables (‘Dim counter as integer’ for example), to tell Excel the type of information (‘integer’), and to create a name (‘counter’) to refer to the variable.

What are some common variable types? An Integer variable can store whole numbers up to a value of approximately 32,000 and requires a small amount of memory (just two bytes!) A Long variable can store larger whole numbers but requires twice as much memory. Other variables store decimals (Single, Double), text (String), and other data types. You can find comprehensive lists online. The main variable types I use are Integer, Long, Single (for decimals), String (for text) and Boolean (true or false).

When declaring a variable, we must also name it. A simple letter (Dim ‘x’ as integer) would do the job; however, fans of the channel know that I prefer informative variable names. An informative variable name tells the programmer what the variable does. For example, ‘counter’ is an informative variable name because it counts the number of times a loop has run. I use ‘ChrisCell’ when looping through a collection of cells, because the variable (in this case a ‘range’ variable) represents a cell. We can use any name apart from words that Excel reserves for its own use (‘row’, ‘cell’ etc.) An informative variable name requires more typing but allows us to understand what is going on much better; for me this is a worthwhile trade-off.

So, why not try out some VBA variables? You can find examples of all the above variables in our tutorial videos. They are central to the cool macros we create. Developing basic confidence in their use should lead to slicker and more robust Excel implementations. Good luck and let us know how you get on!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Separate Worksheets to Files

Create any number of Excel files at the click of a button using Excel VBA

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

We receive many emails from people who spend too much time moving Excel worksheets to separate files. This is a common task: if you are creating reports for students in a school or payslips for employees in a company, for example, you have to do this or something similar. It’s time-consuming, takes lots of mouse clicks and leaves us thinking ‘there must be faster way!’.

Excel VBA Message Boxes

One of the key themes of our training is that a small set of VBA techniques can transform how you go about your work. We say they deliver ‘disproportionate benefit’. Some of these techniques are: using the the macro recorder; using VBA variables and applying loops. With this video series, you will learn how to get this task done at the click of a button and improve these critical skills.

So, how can we use Excel VBA to separate worksheets to files? Let’s go! Don’t forgot to download the Excel file (above) and work along with Chris…

Video 1 – Using the Macro Recorder to Get Started

‘INTELLIGENCE IS KNOWING WHAT TO DO WHEN YOU DON’T KNOW WHAT TO DO’.

We like this aphorism at Tiger. For many professionals, applying code to this task is a situation in which they would not know what to do – ‘how should I get started? I have no idea’. To avoid giving up, we have to tap into ‘metaskills’ – mental resources that guide our actions when we are stuck, and that enhance or limit our capacity to learn. How does this translate into Excel? If you have no idea how to begin a coding task, what is the required metaskill? The macro recorder, of course!

The macro recorder allows us to do something in Excel and record the corresponding VBA code. So, for this task, we hit ‘play’ to start the recorder, and then copy the first worksheet to a new file; once we stop the recorder (Chris almost forgets!) we can review the code in the VBA editor. This is pure gold for the amateur coder; even if you have no coding knowledge, you can ‘create’ code for whatever you are trying to do, then try to tune it up and get it working. Try it.

Video 2 – Tweaking Recorded Code, Introducing a Variable

In the second video, we review the code in the VBA editor and tweak it to get it working for our purposes. One problem often encountered when creating multiple files is that of file names; we cannot save two files with the same name to the same location, as Excel helpfully tells us!

We resolve this by introducing a VBA variable to store the desired file name – generated in the original, origin workbook – and to apply it to the new workbook. By the end of the video we have a routine to separate a single worksheet to a single file; though this in itself is not very exciting, it is an important intermediary step that allows us, in the next video, to apply powerful Excel techniques to quickly ‘scale up’ the code to work for multiple worksheets and files. We think ‘get it to work once, accurately and robustly, then apply the loop’.

Video 3 – Applying a Loop to Scale Up the Code

We are dealing with a ‘repeated’ set of instructions since we want Excel to do the same thing to multiple worksheets. The concept of ‘repetition’ can only mean one thing – loops. A loop is a powerful programming technique that repeats a set of instructions any number of times. But, like any powerful beast, loops require careful control!

In the final video, we look at how to set up and control the loop so that it ‘works though’ the database, pointing to the next row each time the loop iterates. This involves another Tiger favourite – the Excel VBA offset command. Loops and offset really are like fish and chips: they work beautifully together. Master their interaction to get a mind-boggling amount of work done at the click of a button. In this example, we set up the loop to work through the worksheets in the file, copy each to a new file, and to save each under an appropriate file name. A task that would take a few minutes, and cause some frustration, is now complete in just a few seconds.

What did you think about the Use Excel VBA to Separate Worksheets to Files videos series? Did you manage to save some time, and did you feel the power of the VBA techniques? We would love to know how you get on – contacts below.

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Working with Excel VBA Message Boxes

Tell the user what is going on and build their confidence in your spreadsheet

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

A true story…

We are currently working on a spreadsheet development project here in the UK. The project involves updating a ‘legacy’ spreadsheet – one that has been around for a while. It’s a typical situation in which we might intervene: the file has bits and pieces ‘bolted on’, and the person who was in charge of it has left the business. It’s causing anxiety.

The spreadsheet contains various buttons and macros that run on workbook events including opening the workbook, some of these macros are very powerful. Here is the thing: nobody actually knows what happens when the buttons are pressed. People are aware that some code runs, but the consequences of the macros are mixed at best: some reports are created but, elsewhere, inputs disappear and formats changed for no apparent reason. The client team is frustrated.

Excel VBA Message Boxes

What is missing here is helpful user interaction. The applications we develop must communicate with users to build their confidence in the file. One example is asking for permission to run a macro, and making sure the the user is aware of any adverse consequences such as losing pre-existing data; another example is notifying the user that a macro has run, and providing some helpful summary information from the analyses generated. Both are examples of a critical success factor in spreadsheet development: helpful user interaction. So, how do we use Excel VBA message boxes to create helpful user interaction?

This video series teaches you the basics of Excel VBA messages boxes. With the building blocks provided, you will be able to create powerful, dynamic message boxes that provide the user with the information they need, when they need it. First, we cover a simple message box. Then, we look at two possible applications of Excel VBA message boxes: first, to seek permission from the user to run a macro; second, to report back to the user after a macro is run. So, let’s get into it!

Video 1 – Simple Excel VBA Msgbox

In the first video, we look at how to create a simple message box. From the outset we deploy powerful coding concepts – we use activecell.value, for example, to make the message box display what is in the selected cell; this lends the message box a dynamic quality – it responds to different situations and provides appropriate feedback. We will harness this dynamic quality later. We also look at how to change the title of the message box from ‘Microsoft Excel’ – we don’t want Bill Gates taking all the credit!

Video 2 – Excel VBA Message Box for Confirmation

How can you (re-) build user confidence in your spreadsheet? One way is to ask the user to ‘commit’ to decisions using a confirmation message box before running code. We are all familiar with this idea from online forms and pieces of software – we are told what will happen, and given the opportunity to proceed, or to reverse the decision. This is particularly important in the spreadsheet environment where users may not be used to macros doing work for them. Correctly implemented, this kind of feature can build user confidence and supports user interaction – strong user interaction is one of the features of professional Excel applications. The important mechanism here is a ‘Yes / No’ message box combined with a conditional statement to take the code in one of two directions. Cool!

Video 3 – Excel VBA Message Box for User Feedback

The final application focuses on using message boxes to provide user feedback. We think that ‘involved’ macros (ones that take some time / do a lot of work) should be ‘book-ended’ by two message boxes – one to seek confirmation (above), and the other to indicate the macro has finished. The second message box, moreover, is an opportunity to provide some timely feedback for the user. Why make the user look for a critical piece of information in a spreadsheet when you could flash it up in a message box? This kind of user feedback further improves user interaction and should build user confidence in your application.

What did you make of the Excel VBA messages boxes series? These techniques should allow you to improve user interaction in your spreadsheet application, and create that ‘wow’ factor. Good luck! We would love to know how you get on – contacts below.

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA IF Conditional Statements for Beginners

Excel VBA IF statements can take your VBA coding to the next level

If you’ve learned the basics of VBA coding (perhaps you’ve watched our popular VBA for Beginners series), you’re ready to move to the next level. What is next? Excel VBA IF statements move you towards more advanced coding, and more powerful macros. You may have wondered how we tell Excel to execute only part of a routine, according to a value in a cell, or something else. This is the basic idea behind an Excel VBA IF (‘conditional’) statement.

Excel VBA IF

This series teaches you three techniques for dealing with a ‘conditional’ situation. These techniques are the techniques we use in our Excel development projects. But first, what is an Excel VBA IF statement? Why ‘if’?

Conditional statements allow us to instruct Excel to execute code only if a certain condition is met. A condition could relate to any object in Excel, but typically a condition relates to a value in a cell or VBA variable. For example, you might wish to count how many entries in a database are ‘male’, or write the word ‘male’ next to all the ‘male’ entries. In each case, we would ask Excel to do something only if a certain condition is met, in this case if the letter ‘M’ appears in a cell.

Got the idea? OK, let’s get into the first video! Don’t forget to download the files and work along with Chris.

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Video 1 – Simple Excel VBA IF Statement

In the first video, we learn how to create a simple if statement. A simple if statement involves just a single line of code, and is relatively easy to set up. In this example, we ask Excel to flash up a message box to tell us if an entry in a database is ‘male’ or not. You’ve just created your first Excel VBA IF statement. Well done, onwards!

Video 2 – IF – ELSE – END IF

So, we know how to ask Excel to do something if a condition is met. But, what if a condition is not met? What if we want Excel to notify us if the entry in the database is not a ‘male’? The next technique is IF – ELSE – END IF.

This technique allows us to direct the code in two directions. But, the required coding construct is more complicated and often confuses beginners; when using IF – ELSE – END IF, we have to remember that the instructions must begin on a new line. A simple if statement requires just a single line of code; IF – ELSE – END IF incorporates another condition and requires at least 5! One for if, else, end if, and at least one line for the instructions if the condition is met, and the instructions is the condition is not met. Don’t worry too much about it at this stage – just follow along with the video.

This can seem ‘code-heavy’; but the power of the routines, in our view, justifies the effort required to learn the syntax. In the video, Chris creates a routine to count up the number of males and non-males and in the database. The loop / conditional statement combination is VBA at its best; you can see how these techniques combine together beautifully to get a job done quickly. Chris even has time to validate the routine by using spreadsheet formulae to reach the same outcome. Nice!

Video 3 – Select Case

Oh no, more new syntax! Yes, select case is a new construct for us. But, this technique opens up a new world of possibilities. So far, We’ve dealt with conditional statements that direct the code in one or two directions. Select case allows us to direct the code in any number of directions, and even includes a ‘catch-all’ condition too. It is supremely flexible and powerful, and regularly features in our Excel development projects.

Chris implements select case in the video to count up the number of entries in three age categories (eg. 30-59 years old) in the database. But, he does not complete the report. Can you combine select case with a loop to get the information that the ‘client’ requires? Reference techniques from video 2, and let us know how you get on.

What did you make of the Excel VBA IF Conditional Statements for Beginners series? With these three techniques, we are confident you can deal with most situations where a conditional mechanism is required. Good luck!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Learning Tips: My Story

Chris’s Spreadsheet Story and 3 Excel Learning Tips

By Chris Mortimer

There can be no doubt about it, Excel is difficult!

You’re on an Excel learning website, so you’ve probably felt that difficulty yourself. In the below video, I talk about my spreadsheet story – how I got into spreadsheets, the difficulties I encountered and how I got over them. I share three Excel learning tips that I hope will push your spreadsheet skills forward.

Spreadsheet Story Still

In the below video, I talk about (1) finding an access point, (2) the importance of a mentor, and (3) the impact of good learning resources. What is your spreadsheet story? Tell me – leave a comment below the video on the YouTube channel.

Excel Learning Tips 1/3 – FIND AN ACCESS POINT

When people think about Excel, they tend to think about numbers. This leads to the misconception that spreadsheets are the preserve of statisticians and computer programmers. In the video, I argue that a spreadsheet is actually a social and visual problem as much as a technical, numbers-based one. This is because, to my knowledge, a spreadsheet has never created itself! A spreadsheet is created by a human being to help them do something. Further, a spreadsheet that cannot be understood is not useful, regardless of how powerful it may be; it must be well-presented. Therefore, spreadsheet development actually calls on visual and social skills as well as numbers-based ones.

I find this point of view intriguing (well, it is my own ;-)) and incredibly empowering. It opens up a topic that is considered ‘difficult’ to most people – because most people are confident with some aspects of social, visual or technical problems. With this in mind, you have to find your access point. Are you ‘artistic’? If so, you can arrange the elements of the spreadsheet in a way that helps the user assimilate the information. Do you like interacting with customers? If so, you can get started by understanding user needs and creating a detailed specification. After you have identified your access point, you can build other skills from this enclave of competence and confidence.

Excel Learning Tips 2/3 – FIND A MENTOR

Excel is a piece of technical software that, without specialist skill, is difficult to use. Too many people are asked, wrongly in my view, to ‘get Excel to work’ without the proper training. A critical factor for many successful Excel users is finding somebody to help. I was lucky to have helpful colleagues on my Masters programme, and some excellent lecturers. These people humanised a complicated topic and helped me through the early stages. So, try to find your mentor. If you’re looking for somebody to help you, bear in mind that skilled Excel users often like talking about spreadsheets! They may be happy to share their knowledge. So, approach somebody you work with or somebody you know, and ask them to help you. You may find they are flattered by the invitation.

Excel Learning Tips 3/3 – GET SOME LEARNING RESOURCES

‘Excel’ as a learning topic consists of multiple sub-topics: modelling, data analysis, VBA programming etc. You need a plan to navigate these sub-topics and organise your learning. I have often seen people get lost in ‘rabbit holes’ – obscure topics that don’t deliver much value. Now, this article is not a sales pitch for my YouTube channel, but it might be a starting point. I organise the video series so that these sub-topics can be easily understood, and so that the learner can work along with me and develop the skills. There is a multitude of Excel learning websites out there – find one that suits your learning style, and use the resources to navigate Excel as a learning topic.

I hope these three Excel learning tips help you with your learning journey. Good luck, and let me know how you are getting on.

What is your spreadsheet story? What stage are you at with your Excel learning? Leave a comment below the video on the YouTube channel.

Posted in Advanced Articles, Advanced Videos, Beginner Articles, Beginner Videos, Excel Guidance Articles, Excel Guidance Videos, Excel Guidance Videos & Articles, Intermediate Articles, Intermediate Videos, Uncategorized | Comments closed

Excel Database Formulae For Beginners

Create cool database analysis quickly with Excel Database Formulae for Beginners!

Excel is great for logging data and keeping small databases. But, very few people know how to get the analysis they need from the data quickly and without too much stress! Too often, people resort to sorting data and splitting up the database to simplify the problem; but, this ‘critical mistake’ complicates the workbook and is often counter-productive. What are the alternatives?

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Excel DB Pic

Correctly applied, Excel database formulae allow us to get the analysis we need quickly and without too much stress. A few hours learning the skills could have a dramatic impact on your work. Make sure you download the exercise files (above) and work along with Chris. Enjoy!

Video 1 – Introduction to Excel Database Formulae

In the first ‘in-car’ video, Chris talks about the real-world motivation for the series, and the value of the techniques covered. Excel offers a vast array of functions; but, we believe, not all are required to reach a good level of competence. Rather, a subset of techniques learned effectively can deliver disproportionate benefit. The powerful Excel database formulae covered in this series fall into this subset of important techniques. Chris also talks about a ‘critical mistake’ in spreadsheet development – how users tend to split up databases across multiple sheets in order to make the analysis easier. This actually serves to complicate matters and will discourage the user in the long run. Command of Excel database formulae allows you to get the analysis you need without complicating workbook structure.

Video 2 – COUNTA and COUNTIF

The first techniques video covers two basic formulae that are essential for working with databases. The COUNTA formula tells us how many entries are in a database, and the COUNTIF formula allows us to answer ‘single criteria’ questions such as ‘how many females are there in the database?’ Remember to download the spreadsheet files (link at the top of this article) and work along with Chris. Throughout this series, we use PC keyboard shortcuts to navigate Excel and speed up / de-stress the formula-building process. Try the keyboard shortcuts yourself and get Excel working for you like a pro!

Video 3 – SUMIF and AVERAGEIF

COUNTIF allows us to identify how many records conform to a particular ‘criteria’, or piece of information that interests us. The SUMIF and AVERAGEIF formulae allow us to perform an operation on records that conform to a criteria. What do we mean by an ‘operation’? The operation that SUMIF performs is to aggregate the data, whereas AVERAGEIF returns an average of data that interests us. So, you can get important summary measures such as aggregates and averages for subsets of the data without having to sort or split up the database. Cool! Throughout the series, Chris checks the accuracy of the Excel database formulae by sorting the database and applying a simple formula, effectively getting the same result a different way. It is good practice to ‘validate’ your analysis like this – it should help build confidence in the values that the database formulae return.

Video 4 – COUNTIFS

In this video, the Excel database formulae start getting really interesting! We move onto the topic of multiple criteria questions – analysis in which we wish to include data that conform to two or more pieces of information. How many males from the North region are in the database, for example? We deal first with the COUNTIFS formula. The COUNTIFS formula is similar to its close friend the COUNTIF (singular) formula, but allows us to include any number of criteria, whereas COUNTIF allows just one. The formula returns the number of records in the database that conform to all of the criteria we specify. With this level of sophistication, the formula can be tricky to build. Particular attention has to be paid to references and use of absolute, relative, and partial absolute references. Chris discusses references in the video and provides his tips for getting them right throughout the series. Trial-and-error is key!

Video 5 – SUMIFS and AVERAGEIFS

In the fifth video in the series, we deal with more ‘multiple criteria’ questions. In this case, we wish to do more than simply count entries that conform to multiple criteria; we actually want to perform an operation on the data – calculate an aggregate (SUMIFS), or a mean average (AVERAGEIFS). Chris works through each of these formulae step-by-step, building two analysis tables that provide powerful insights on the dataset. All in just over 15 minutes! Hopefully you’re now feeling the power of the formulae and can see applications in your real-life situation…

Video 6 – DSUM and DAVERAGE

The sixth and final techniques video in the series marks a departure from the previous formula-building approach. We examine two powerful Excel database formulae – DSUM and DAVERAGE. These formulae offer new possibilities: rather than having to edit a complicated formula to get the analysis we need, we can simply enter values into a table and watch the outputs change, cool! There is a trade-off however, setting up these formulae requires some preparatory work, and maximum precision is needed to get the formulae working. Once set up and validated, however, they provide quick, powerful, user-friendly analyses. We have found that on our real-world projects, the DSUM and DAVERAGE formulae can have a dramatic impact. Make sure you work along with Chris and get these Excel database formulae working for yourself.

Video 7 – Excel Database Formulae – Reflection and Discussion

In the final video of the series, Chris discusses an important issue in applying these formulae in the real world – how to account for a database that is changing in size. It is unlikely that your database is ‘static’; rather, you would want to add data to it over time. How can we set up the Excel database formulae so that the analysis encompasses the new data? Chris discusses three possible approaches and highlights his preferred option.

What did you make of the Excel database formulae for beginners series? Have you managed to get the formulae working yourself? Share your experience with the Tiger community on the platforms below:

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

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!

Video 13 – LIVE – Clear Conceptual Thinking!

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

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?

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

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

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

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

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

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?

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

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?

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

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

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

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

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

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 Collaborators

Video Link:
https://youtu.be/x2nSai9ofHo

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?

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

Video Link:
https://youtu.be/ovQ0ZWrLO2M

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!

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

Video Link:
https://www.youtube.com/watch?v=sfpFMwADMvw

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

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

Video Link:
https://youtu.be/HpFkWP5GTFo

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

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

Video Link:
https://youtu.be/oNHOc1jf1dA

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. The Chess Challenge will return in 2020.

——————————–

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

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Userforms for Beginners

Use Excel VBA to Create an Excel Userform and Manage a Database!

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Video 1 – Introduction

In the first ‘in-car’ video, Chris explains how Excel userforms can help with a typical Excel task – managing a database. This can be time-consuming and frustrating and, correctly implemented, Excel userforms can help! We briefly discuss the importance of two functions – adding new entries to a database, and editing existing ones, consider how an Excel userform might help, and highlight the pivotal role of VBA code. Why not download the completed Excel file to ‘get a feel’ for the topic? Then, download the starter file so you are ready to work along with Chris
Why are Excel userforms important and what are some applications?

Video 2 – Create a Userform, Add a ‘Control’

This video looks at creating a Excel userform in the VBA editor and adding a simple feature or ‘control’. We add a command button and assign some code to the button. In the future, the user will click on this button to transfer data from the userform to the database. We make the command button flash up a message box so that we can understand how the Excel userform, command button, VBA and message box are interacting. This is a simple example, but all userforms follow a similar basic process to get things done.

Video 3 – Build up a Userform, Add Labels, TextBoxes, ComboBoxes

In this video, we build up a userform to so that it can quickly collect data from the user. We look at three controls, and focus on two ways of gathering data – TextBoxes and ComboBoxes. Textboxes allow the user to make a ‘free text’ entry, whereas ComboBoxes require the user to choose from a menu. ComboBoxes must reference a list in the spreadsheet, and we look at how to get the VBA editor and spreadsheet to interact to achieve this. We also explore how to implement option buttons – another interactive and user-friendly Excel userform feature.

Video 4 – Maximise User-Friendliness

A userform that is not working well can be more of a hindrance than a help! In the fourth video in the series, we look at maximising user-friendliness to make the user experience as smooth as possible. We explore how to configure the TabIndex property in the VBA editor to allow the user to navigate the userform using the Tab key on the keyboard. We also look at how to make default values appear in the controls to speed things up for the user, in addition to some other important details that help create a ‘professional’ feel.

Video 5 – Link the userform to the spreadsheet using Excel VBA

This task involves multiple ‘objects’ – the workbook, the worksheet and the userform, among others. VBA is essential to make these objects interact effectively – it is the glue that binds the various elements together. In this video, we consider how to approach what can appear a complicated coding task. We identify a sensible and simple first step that transfers data from the userform to the spreadsheet, and implement it using VBA. Dealing with the option buttons, however, requires a more sophisticated coding concept, can you guess what?

Video 6 – Position data precisely in the database using Excel VBA

So far, we have used VBA to move data from the Excel userform to the database. However, we need a way to tell Excel exactly where to put the data in the database. If adding a new entry, we would want the data to appear at the bottom of the database, for example. In this video, we explore how to set up a mechanism to measure the size of the database and to convey this information to the VBA editor. The mechanism is ‘dynamic’ in the sense that it responds to changes in the size of the database. It’s a great example of a common theme in VBA – getting spreadsheet formulae and VBA commands working together to achieve powerful and precise control.

Video 7 – Control and Helpful User Interaction

This video is a break from the complex coding! We take a detour through two topics that are often ignored but which are critical if you want to take your spreadsheet to ‘professional’ level. We deal first with the topic of control. We can achieve good levels of control by using validation to check user inputs. In the video, we look at setting up a ‘custom’ validation control to check if a proposed name is already in the spreadsheet, or not. The second topic is user interaction. It is not always clear what has happened when a VBA routine has run and it is a good idea to let the user know. How can we make this happen in the Excel userform task?

Video 8 – Use an Excel Userform to Edit Existing Data

The ability to edit (rather than add a new) entry is an important function that presents a new challenge to the programmer. In this video, we consider the task and identify the main steps. We look at how to create a new, simple userform to allow the user to highlight the entry to be edited. We use an offset formula in the spreadsheet to define a ‘dynamic’ range that changes size according to the number of entries in the spreadsheet. Then, we create a mechanism to establish the row that the ‘target’ entry to be edited is on.

Video 9 – Load Data from a Database into an Excel Userform

In this video, we explore how to extract data from the database and to load it into an Excel userform. Note that our approach is NOT to create two separate userforms – one for data input, and one for editing, which would represent a duplication of effort; rather, we use the original userform and attempt to establish two ‘modes’ – one for new entry input, and the other for editing existing entries. We look at how to create a sophisticated position control mechanism to retrieve data from the database accurately, and to display it in the Excel userform. This sounds like a big job, but ‘recycling’ code that we know already works can help!

Video 10 – Creating a Sense of Mode – ‘Add New’ and ‘Edit Existing’

In the final video, we explore how to create the sense of ‘mode’ that we refer to above. This is an intricate task that involves ‘saving’ some information for use in subsequent VBA routines. We look at how to combine a worksheet, the Excel userform and the VBA editor to make this happen. The result is a powerful, interactive and robust Excel userform that allows the user to quickly add data to and edit a large database.

Note: Chris did do some ‘tidying up’ of the code after shooting, to improve the quality of the completed file. This involved improving the in-code annotations and the structure of the code. No new coding ideas were introduced, however, and the mechanisms all work in the same way. Don’t feel that you should copy every detail of Chris’ approach, however; you now have the tools to design and implement your own powerful Excel userform-based applications. Good luck!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions
https://twitter.com/TigSpreadsheets
http://tigerspreadsheetsolutions.co.uk

How did you get on with the video series? Were you able to apply the technique in your work? Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Create Spin Buttons – Really Precisely!

Use Excel VBA to Create Spin Buttons!

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Spin buttons are another great feature of Excel. Spin buttons speed up the process of data input by allowing us to change the value of a cell with a single button click. Cool! Correctly implemented, they can be a great time-saving feature. But, implementation can be problematic … If you have multiple rows in your spreadsheet, it can be time-consuming to creating lots of buttons. Moreover, getting them to line up neatly can be onerous. With some simple VBA coding, however, you can speed up this task and get it done with maximum precision. Welcome to the Use Excel VBA to Create Spin Buttons video series!

Video 1

In the first video, Chris demonstrates how to create a button, and how to link the button to a cell. It is important to ‘snap’ the button to the gridlines (use the Alt key on a PC) and to ensure that row heights are consistent – this will help create a neat and tidy look.

Video 2

In the second video, we look at how to use Excel VBA to create additional spin buttons. This may sound intimidating, but a basic routine like this is actually fairly easy to create. We use the macro recorder to record and examine some VBA code, then we make some tweaks to get it working for us. Finally, we add an instruction to the Excel VBA to ensure the buttons are positioned with maximum precision.

Video 3

In the third video, we explore how to ‘scale up’ the code. Some VBA to create a single button is great, but what if we want to create multiple buttons? This involves a powerful coding concept known as a ‘loop’. A loop works through a set of instructions a certain number of times, and is exactly what we need to create and position multiple buttons. Check it out!

Video 4

A spin button only works if it is linked to a cell – the cell that changes when the spin button is clicked. Clearly, however, we do not want to click through all of the spin buttons to manually link each to a cell. This would not be in the spirit of the video series! Can we achieve this using VBA instead? Find out in the last installment of the Use Excel VBA to Create Spin Buttons video series!

Was this video series helpful for you? What have you used spin buttons for? Do you have any feedback on the video series? We’d love to hear from you. Get in touch with Tiger using the platforms below!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions
https://twitter.com/TigSpreadsheets
http://tigerspreadsheetsolutions.co.uk

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Create Buttons For Super Fast Data Input!

How can we use Excel VBA to create buttons for super-fast data input?

Click here to download the example file used in the videos.

Any Excel user spends a lot of time doing data input. So why don’t we talk about it more? More efficient data input could save us a lot of time … and frustration!

What are the basic data input options? The first and most commonly used option is to simply type data in. This is what you did when you first used Excel, and you still do it now … a lot! It can mean annoying mouse / keyboard work, and time and effort to fix spelling mistakes, and other errors. Are they any better options?

Video 1 – Introduction

A dropdown menu is a good idea. With a single mouse click we can view possible inputs and select one. It is certainly an improvement on keyboard inputs, but still requires some fairly precise movements to manipulate the dropdown menu, which creates cognitive load, or ‘stress’! In a work situation it could be a distraction from your job.

At this point, let’s consider a real-life example. A classroom teacher would want to record student attendance, and might wish to use Excel or another piece of software to do this. This is where the benefits of super-fast data input come into sharp focus. The classroom teacher has a million things to think about: starting the lesson, student queries, have the students done their homework? In this situation, multiple mouse clicks and keyboard work is frustrating; indeed, it might mean that the teacher gives up on the Excel file altogether!

Super Fast Snap

We can use Excel VBA to create buttons for super-fast data input. This means that, rather than clicking around or using the keyboard, the teacher can make a single button click to input the required data. Moreover, mistakes can be quickly rectified by clicking the same button. With this level of usability, super-fast data input can make this kind of Excel-based tool a real asset to the classroom teacher.

How does it work? Well, multiple buttons are required. Anybody who has tried to create and manipulate multiple buttons in Excel knows that this can be onerous! There is nothing worse than copy / pasting and manually lining up shapes. With some basic coding ideas, however, we can dramatically speed up – and de-stress – the process. Watch the video series, work along in the download file, and learn the practical skills. Welcome to Excel VBA Beginners – Create Buttons for Super-Fast Data input!

Video 2

Video 3

Video 4

Video 5

Video 6

Video 7

Video 8

How did you get on with the video series? Were you able to apply the technique in your work? Contact us here.

Ever thought about how to cleanse a dataset using computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

How to Collate Sports Fixtures Results into a League Table in Excel

INTRODUCTION

How can you collate sports fixtures or results into a league table in Excel?

Click here to download the STARTER file.
Click here to download the COMPLETE file.

Sport enthusiasts might wish to create a league table from a set of results in Excel, but it can be difficult to know how to start. Without good pivot table or VBA programming skills, it is a complex task that cannot be completed in one fell swoop. It necessitates some clear, logical thinking about the required steps, and the creation of a well-structured Excel file.

Video 1 – INTRODUCTION

In the introductory video, Chris talks about the task, why it can be difficult to create a league table in Excel, and the best mindset to adopt.

The video series invites us to reflect on what a well-structured Excel file looks like. We propose a structure comprising three elements – a backend, calculations and frontend – and apply it in the video series. This is a good general structure to apply to your next Excel-based task!

Along the way, we apply Excel formulae that are essential in spreadsheet modelling including if, sumif, offset, match and vlookup.

As a starting point, Chris takes the results data from the 2015-16 Premier League season and works through the steps towards a league table. In the final video in the series, Chris tests the model created against the actual Premier League table. Will it be accurate?

Excel Sports Results

Video 2 – convert scores to a result using if

Video 3 – collate wins and losses using countif 1

Video 4 – collate wins and losses using countif 2

Video 5 – use sumif to find goal difference

Video 6 – use match and offset to create the league table


How did you get on with the video series? Were you able to collate the sports results into a league table in Excel? Contact us here.

Ever thought about getting started with computer coding in Excel?

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Custom Navigation: Excel VBA for Beginners

INTRODUCTION

What is an Excel custom navigation system? Switching between sheets, unless using keyboard shortcuts, means moving the mouse pointer all the way to the bottom of the screen. With multiple sheets and workbooks, this can be onerous – is there a better way?

Click here to download the STARTER file.
Click here to download the COMPLETE file.

Video 1 – INTRODUCTION

Watch Chris describe what the Excel custom navigation system is, why it helps, and how to create it.

The Excel custom navigation system is a neat alternative. It positions navigation buttons at the top of the worksheet, not at the bottom, facilitating smooth navigation. It also creates a distinctive visual effect, similar to a ‘cockpit’ or car dashboard, which gives the spreadsheet a professional look.

But, from the learning perspective, there is something more important. Building a custom navigation systems presents an opportunity to learn new coding techniques. Clearly, we do no want to have to create and position all the buttons manually! So, what coding techniques will allow us to do it quickly, precisely and minimise stress?

Excel Custom Navi

The Excel custom navigation system video series takes you through the main steps. We explore how to create multiple buttons, how to position them, how to trigger the right code, and how to copy them across sheets. Some of the code is quite intricate – you can see Chris get into a coding tangle, and work his way out!

Video 2 – Create a shape and duplicate it using VBA

Video 3 – Use VBA to position shapes

Video 4 – Use VBA to control text that appears in shapes

Video 5 – Use VBA to navigate to a sheet dynamically

Video 6 – Use VBA to copy / paste shapes across sheets


Why not try creating your own Excel custom navigation system? Let us know how you get on!

Read more about the #ExcelRevolution here. Or, follow us on Facebook and Twitter.

Posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed