Being able to create a unique list of values is an essential skill for any football trader working with Excel. Whether you need team information, leagues or something else, the ability to ‘remove duplicates’ is important. These days, Excel offers us a few options, including the UNIQUE formula available in Excel 365. At the start of the video, I demonstrate the UNIQUE formula and discuss some of its pros and cons. But, this being Heatmaps season 2, we’re going with a VBA-based solution. So, let’s get into that!
Getting into the VBA editor, our first job is to ‘promote’ a variable from routine to module level. This allows the variable to pass its value from one macro to another and facilitates the ‘modulated’ approach we’re trying to implement in this series. Rather than having to re-write complicated VBA code, we can now simply run another macro in the module to get the range definitions we need – the principle of modulation in action!
Though our aim, eventually, is to create a unique list of team names, some preparation is required. Which is why, in the video, I deal with (yet another!) dynamic range definition, this time of the home team column on the data sheet. Yes, we’ve been doing a lot of dynamic range definitions! Perhaps too many. But, that’s part of the VBA ‘maximalist’ approach and, once they are all defined, you can move onto the tasks that will really add value. As I have explained more than once in this series, the best approach to VBA projects is usually to ‘front-load’ the heavy coding, as we are doing in these videos. Onwards!
Topics and Techniques
Excel UNIQUE formula
A modulated coding approach
Concept of ‘Scope’ applied to named ranges and variables in Excel
‘Promoting’ a variable to module level
.Clear vs. .ClearContents in VBA
More dynamic range definition!
Using a breakpoint in the VBA editor
Storing important values in the Engine sheet
For-Next loop to loop through all teams
In this session, we complete the task of building a unique list of values using Excel VBA. With the main coding structures (a macro with variables and loops) in place after session 5, it’s time to implement the sophisticated code to bring everything together. This will be challenging so, as always, let’s take it step-by-step - with testing at each stage.
I first undertake an ‘easy’ (!) task to get things moving in the right direction. The ability to move (or ‘transfer’) data around a file is a great strength of Excel VBA, and the simple a=b concept (where ‘a’ is the destination and ‘b’ the origin) is all you need to get started. I implement a simple line of code for us to build on.
Next, it’s time to move to a higher level of sophistication. In this case, this means ‘conditionality’ – a function to add a team to the bottom of the list, but only if it’s not already in the list. That’s the logic, and it’s important to articulate it clearly – I always speak it out loud. With the logic clear, we can select a technique – and the combination of application.worksheetfunction and COUNTIF is perfect for the job. The addition of offset means teams are always added to the bottom of the list in ‘dynamic’ fashion. Powerful stuff!
Finally, how to sort this unique list of teams? Since it’s logical to display them in alphabetical order. This requirement leads to a discussion about the three sources of VBA code and the application of a brilliant facility in Excel – the macro recorder. I demonstrate how to use the macro recorder to generate code, then subsequently tweak and integrate it into the main routine to get the job done. Onwards!
Topics and Techniques
Transfer data with an a=b operation in Excel VBA
A conditional statement with IF
The syntax of IF statements in Excel VBA
Using Compile in the VBA editor to check syntax
Application.worksheetfunction with COUNTIF
Add a value to the bottom of a list dynamically with offset and a variable
Running a big test – VBA showboating!
Testing for dynamic quality in VBA
How to sort data with Excel VBA
The 3 sources of VBA code – organic, recycled, recorded
Using the macro recorder
Tweaking and refining recorded code in the VBA editor
Integrating recorded code into the main routine
A schoolboy error (don’t try this at home!)
Do you absolutely need to know do-until loops to get things done in Excel VBA? Well, no. In fact, 95% of jobs can be done with the simpler loop types we’ve seen many times in Excel (VBA) for Football Traders: for each and for next. However, as I demonstrate in the video, do until does present new and intriguing possibilities. This session should help you appreciate the unique benefit of do until and understand how it might fit into your Excel tool kit.
In terms of practical application, the video focuses on the creation of odds brackets – the other (horizontal) axis in our heatmap table. Odds brackets are a basic analysis for any football trader, so being able to create these analyses quickly is a must. Not only that, with VBA you can analyse any number of odds brackets – of any size – at the click of a button.
In the video, I work through the tricky logic of the do-until loop. To give maximum flexibility to the user in terms of odds brackets generation, the macro must account for user inputs for the minimum and maximum values in the odds range, and the size of each bracket. This is all possible, but requires a step-by-step approach and (optionally) lots of variables! We get the job done with the usual steady-and-systematic method, enjoying a minor ‘punch-the-air’ moment at the end of the session when the do-until loop works its magic …
Topics And Techniques
Simple one-line conditional statement in Excel VBA
‘Promoting’ a variable to module level
The advantage of using variables: principle of portability
Integer vs. double variables for storing numbers
Importance of ‘initialising’ variables
Managing named ranges with the name manager
Getting started with a do-until loop
Using a condition to exit a do-until loop
Do until vs. loop until
Testing a do-until loop
Incrementing a variable with a = a + 1
Another example of offset working within a loop
A minor punch-the-air moment!
If you’ve invested time getting your Excel VBA application to work (and why not?) then you might also want it to look good. VBA can do most jobs in Excel (though it shouldn’t be applied to everything) included making your spreadsheet look appealing. And don’t worry, we’re not going to simply record reams of code in the macro recorder; rather, we’ll explore the technique I use in my VBA applications to get exactly the appearance I need – every time.
My technique involves ‘storing’ formats on a supporting sheet. I use a ‘Format Store’ sheet, these days, as a generic sheet for this purpose. But, given the Engine sheet isn’t too busy at the moment – we can use it for this job. The beauty of using a format store is you can create any format and simply copy into the range where you need that formatting. So, changes can be made quickly and easily in the spreadsheet with no need to access the VBA editor – cool!
In the video, I talk you through the process, step-by-step. As you will see, the coding requirement is relatively light. And, given that it offers total flexibility for the appearance of the spreadsheet, this session is well worth a look.
Topics And Techniques
Concept of a ‘format store’
Generic sheet roles – a ‘format store’ sheet?
Role of the conditional formatting rules manager
Basic Excel colour scheme ideas
Named range naming conventions
How to copy / paste special with Excel VBA
Exiting cut-copy mode with Excel VBA
.Clear vs. .Clearcontents in Excel VBA
Using breakpoints to debug VBA code
Using Ctrl + Enter to autofill variable names
My million-pound Excel VBA application!
A powerful VBA application deserves an attractive and user-friendly interface since this where the user interacts with the file. So, our first job in this video is to improve the interface.
This could be problematic since we have referenced cells in Excel VBA – so moving them around could create inaccuracies in the code. This is where named ranges help. I also show you how to use the find facility in the VBA editor to quickly understand if a cell is referenced in a macro. The improved layout means I’m already feeling ‘more enthusiastic’ about working with the file …
But it’s not just about layout – where things are on the interface. Formatting and data validation are important aspects of the user experience, too. We dealt with formatting in the previous video and I add some additional points here – I recommend, at least, to change from Calibri (usually the default font type) to something else! If you have a ‘user’ this shows them you’ve at least thought about the appearance of the interface. Arial is my go-to font for Excel development – smart, readable and space-efficient, though I also accept Comic Sans for sentimental reasons, being a child of the 90s.
Cell-based data validation is essential in VBA applications for avoiding errors and developing robustness. Data validation such as dropdown menus means inputs are predictable. This helps with matters such as variable type selection (since variables often ‘receive’ data from the spreadsheet), and a host of other things. Directive error messages tell the user what to do if an input is acceptable, smoothing the user experience. Remember too, data validation does not just mean dropdown menus – a multitude of options are available including numerical controls such as ensuring a number is within a range. Use them! And ease the pressure VBA …
The rest of the video is concerned with calculating the implied odds that will populate the heatmap table. With the main mechanisms (such as dynamic range definition) already in place, it’s more a case of re-organising existing structures than creating new ones.
We’re getting there, and soon everything should come together. We’ll relish that ‘punch-the-air’ moment when it comes!
Topics And Techniques
Helpful user interface layout in VBA applications
Create an ‘analysis last generated …’ message
Use the find tool in the VBA editor
Use the format dialogue box to optimize border formats
Apply data validation to user input cells with a custom error message
Insert a button to trigger a macro
Create a confirmation message box with Excel VBA
Calling other macros with Excel VBA
‘Promoting’ a variable to module level
COLUMN formula
When and when not to use dropdown menus
Application.worksheetfunction in Excel VBA
Directive error messages