Generating form analysis is a tricky task that can only really be done using Excel VBA. We need to resist the tempation to 'jump straight in', and take the time to take step a back, view the whole task, and create a plan.  Since, if the concepts are not clear in our own head, the technical stuff is virtually impossible! 

In this video, we use 'pseudo-code' to draw up a blueprint for this sophisticated VBA routine.  Make sure you download the file and work along with Chris.  Next - the process of translating these instructions from human language to Excel VBA.

Tools And Techniques

- planning and conceptualising a VBA routine
- making annotations in the VBA editor

Now we've built a 'blueprint' for this VBA routine using comments in the VBA editor, we're ready to start creating the code.  Note that creating the code is much easier when we have structured comments to work with!  It's actually a case of 'translating' the comments to VBA, line-by-line.

With that in mind, we set about creating the code.  The first job is to create the required variables (simply places to store information that helps with code execution) - this is easy enough with the annotations already there.  Then, we're ready to put in the main code infrastructure - in this case, a 'loop within a loop'.  We need this construct because our goal is to check each line of data for each team in the fixtures.  It's incredibly powerful and has so many applications in VBA.

We set these mechanisms up with our usual 'code a bit, test a bit' approach. Did you get the mechanisms working too?  Let us know in the comments below the video.

Tools And Techniques

- variable declaration
- different variable types
- for each loop
- loop within loop
- one line conditional statement
- using msgbox for testing

How are you with conditional statements in Excel VBA?  We'll see in this session! 

Conditional (IF) statements are an essential part of Excel VBA routines, and of coding structures more generally.  A conditional statement checks if something is happening (if a 'condition' is met) then takes the code in one of two or more directions.  You could compare it to the IF formula in the Excel worksheet.  In the context of football data, conditional statements allow us, for example, to distinguish between home and away teams.

In this session, I show you the 3 constructs I use to articulate all conditional statements in Excel VBA, from a simple 'one-line' if statement, to the supremely powerful 'Select Case' construct.  For the purpose of form analysis, these powerful constructs allow us to translate the '1X2' data to W, L or D.

Also look out for some cool position control ideas using .Offset and .Cells (which is your favourite?)

Tools And Techniques

- Simple one-line IF statement
- If-Else-End if
- Select Case
-.Offset
-.Cells

With the main parts of the macro to create form analysis complete, it's time to start perfecting the code to get exactly the output we require.  In this video, we look at several advanced coding ideas including dynamic position control using .cells, variables and loops.

The main learning outcome from this session, however, is learning how to test, debug and improve a powerful Excel VBA routine.  With a complicated macro such as this, things never go entirely as planned, so it's essntial (1) to respond in the right way emotionally (without throwing your laptop of the window) (2) to have a debug routine to enter into.  Code not working?  No problem - we know it's all part of the game.  So, let's get into our debug routine, and let's get this code working smoothly ...

Tools And Techniques

- dynamic position control
- combining .cells(x,y) with a variable
- benefits of named ranges
- static vs. dynamic code
- advanced debugging with the VBA editor
- filtering

At the beginning of the video, we make a tweak to the code to 'shift' the away form over to the right. Et, voila!  A complete form analysis generation routine, with all the results in the right place.  Take the time to test it, then, if you've got it working, run it a few times and appreciate the power of VBA.  It really is a thing of beauty ...

But, is this routine complete?  A few things are missing. What about clearing out previous analyses, for example?  Then, surely we'd want some colouring on the form analysis to give a 'traffic light' feel?  Also - what about some 'summary' analysis: counting up the number of wins, losses and draws, and generating an average points value?

Next, we set about creating the summary analysis using a new facility for the form analysis series - application.worksheetfunction.  Should you use this all the time for calculations in Excel?  Probably not.  But, applied sparingly, it's incredibly powerful and we cover a simple example in this video - with a more difficult 'homework' challenge revealed at the end.  Will you take it on?!

Tools And Techniques

- Concept of 'increment' variable
- Advanced position control
- Using breakpoint to debug
- COUNTIF formula
- Partial absolute reference ($)
- Introduction to application.worksheetfunction
- Application.worksheetfunction with COUNTIF

Our next challenge is to 'dynamically' define a range to help us count the number of wins, losses and draws in a team's form.  What does 'dynamically' mean in this sense?  It means defining the range in such a way that it works for any team, home or away, on any row in the fixture data.  In other words, static ranges such as Range("A1:A2") simply won't work!

It's a real challenge in Excel VBA and one that might defeat an aspiring Excel developer.  Let's give ourselves the best chance of success by breaking it down and building it back up, step-by-step.

In the video, I take you through the process of conceptualising, building and testing this powerful dynamic mechanism.  There are no new techniques; rather, existing techniques are combined together in a new and even more powerful way.  The result?  The ability to count the number of wins, losses and draws in a team's recent form, with just a few lines of code.  It's a great example of dynamic position control that should find application in your other Excel analyses too.

Tools and Techniques

- .Cells referencing technique
- Range(range,range)
- String variable
- Testing with Msgbox
- COLUMN
- Application.WorksheetFunction
- COUNTIF

In the final video in the series (at least for now!) we look to complete the form analysis and to present it effectively.  We've successfully calculated the number of Ws, Ls, and Ds from the last 6 games for the home team; the next job: to do the same thing for the away team.

How to do that?  There is no need to create any new variables, or to change the code structure significantly.  In the video, I show you how to get this job done quickly using an existing variable, with only a minor change to the coding.  That's what happens when you design VBA routines with scalability in mind: you can quickly adapt them and scale them up.  We also introduce the code to give us the 'average points over the last 6 games' - a useful measure of recent form, in a single cell.  Awesome!

With the main job now complete, how can we improve the presentation of the analysis?  We use normal cell formats to create a 'professional look', and implement some cool conditional formatting too.  At the same time, we discuss the pros and cons of conditional formatting whilst learning how to use the conditional formatting rules manager to achieve control.

Tools And Techniques

'- .Cells referencing technique
- Scaling up a VBA routine
- Application.worksheetfunction
- VBA Round function
- VBA Format function
- Cell formats for a professional look
- Conditional formatting
- Conditional formatting rules manager