Don't get me wrong, I love Excel VBA!  But, it's possible to get in a real tangle with Excel's coding language which can be extremely frustrating.  So, the first question you need to ask yourself is: do you actually need Excel VBA?

The Only 4 Things You’ll Ever Do In Excel

It’s true – you’ll only ever do four things in Excel, regardless of whether you’re football trading, or doing something else.  I argue if you can grasp this idea, it could have career-changing effect.

The Importance Of Structure And Layout (don’t put your kitchen in your garden)

Question: where in your house is your kitchen?  It’s not at the bottom of the garden, is it?  The way you structure your spreadsheet files suggests it might be!  I explain all in this section: helpful structure and layout really is the key to Excel success in football trading and other applications of Excel.

Usage 1/4: Key Concepts In Data Input In Excel

What’s Excel mainly used for?  Logging data.  ‘Chris, I know that!’  Ok, so why aren’t you focusing on it more in your practice?  Poorly inputted data is at the root of most Excel problems.  Let’s start by understanding some key concepts about data input in Excel, with some examples from the world of football trading.

Excel Tools And Techniques To Support Data Input

You might be familiar with dropdown menus and how to build them, but what other forms of data validation might be useful for football traders and professionals working with Excel more generally?  I point out some useful data input tools and techniques in this section.

Usage 2/4: Key Concepts In Data Analysis In Excel

The need to understand datasets drives the second usage of Excel.  How can we quickly and effectively interpret large datasets?  Football traders are trying to do this all the time.  We start by building a conceptual foundation in which I offer my simple definition of ‘analysis’: it should turn data into information in a way that helps people like you.

Excel Tools And Techniques To Support Data Analysis

Been avoiding pivot tables so far in your Excel career?  You could be missing out!  In this section, I identify tools and techniques to support data analysis in Excel, including my favoured approach: using Excel’s powerful database formulae such as DSUM.  That beautiful dashboard to support your football trading or other activity could be closer than you think …

Usage 3/4: Key Concepts In Modelling In Excel

‘Modelling’ is one of many misunderstood terms in Excel.  ‘Send me the Excel model!’ Your boss may have asked you.  But, in truth, relatively few Excel files are actually ‘models’.  A model refers specifically to an Excel file that expresses real-world relationships using formulae and the concept of ‘input-process-output’.  Odds compilation is one example football traders will be familiar with.

Excel Tools And Techniques To Support Modelling

If you’re a formula demon, you’ll love Excel modelling!  That’s because Excel formulae, and particularly the ‘big 5’ formulae I’ve identified through my career, are integral to any functional Excel model.  In this section, I share what the ‘big 5’ formulae are, and point out other useful Excel tools to help get you modelling like a pro.

Usage 4/4: Key Concepts In Automation In Excel

Finally, the reason you probably subscribed to the Tiger mailing list: Excel automation using the magic of VBA.  In this section, we explore key concepts around ‘automation’, so you can understand the specific tasks with which VBA might help. My argument that many tasks are best done with other tools in Excel may surprise you …

Excel Tools And Techniques To Support Automation

This section is the simplest of the lot: the only tool you need is … VBA!  But, in terms of sophistication, VBA is on a higher order of magnitude compared with other facilities in Excel.  That’s because VBA is a programming language.  Like any human language, it’s complex and takes some time to learn, but is also supremely flexible and powerful.  We'll feel that power in the rest of this series - see you in video 2.

Topics And Techniques

Finally, some coding!  In this video, we'll get your version of Excel set up for VBA and learn how to write and trigger a short computer programme called a 'macro'. 

There are a few things to bear in mind before launching into the VBA Editor, however.  For example, make sure your default file save format is .xlsm - this should avoid the disastrous prospect of code disappearing altogether!  Yes, this can happen, and without much warning ...

Once we're set up, we get familiarised with VBA editor, create a module (the place where VBA code is usually housed), and create a macro.  Yes, you're now a computer programmer!  The fun only starts here, however: make sure you take the time experiment and build on the examples we cover in the session.  Make it a positive addiction!

Finally, we explore the potential of Excel's macro recorder.  This unique facility is an interesting way to generate code.  Remember, code doesn't have to be written organically; it could be recycled, or recorded using the macro recorder.

With the basics of VBA code covered, we're now ready to dive into five topic areas that will allow you to build a firm foundation in Excel VBA.  See you in the next session!

Topics And Techniques

We can guarantee one thing in Excel - we'll have plenty of 'cells' to work with!  So, a good first step in Excel programming is to learn how to use VBA to manipulate them.  We get started with our foundational a=b concept where destination=origin, and apply this idea to put a value into a cell with VBA.

We then build on this example to do more sophisticated things, transferring data from one cell to another, and then from one sheet to another.  Finally, we introduce the concept of a variable (simply a place to store information in VBA's memory) and look at how to manipulate them using the a=b concept.  The session ends with a VBA error message; don't worry - you'll encounter plenty of these in your career!  Let's understand what's happening in the next session ...

Topics And Techniques

In this session, we first explore in more depth the concept of a 'variable'.  Variables can be tricky to understand, so it's important you work on the concept first: I like to compare variables to … your wardrobe!  See my explanation in the video - does it resonate with you?  A variable is simply a place to store information.

With the concept clear (hopefully), we look to build our understanding by exploring different variable types.  Along the way, we encounter two typical error messages you'll see frequently when using variables, and uncover why you should make sure you're using the right variable type.  As we see, error messages are no big deal and are an essential part of VBA learning - you'll grow to love them, I hope!

With our variable vocabulary enriched, it's time to attempt something more ambitious.  In the video, I set up a routine to 'validate' a cell entry, before calling another routine.  This shows us the power of module level variables and how we can interact between routines to get jobs done.  Cool!

But there's more.  The final topic in the video covers object variables.  We learn how to declare an object variable and how, by understanding that Excel consists of objects organised into collections, we can get a mind-boggling amount of work done quickly.  Along the way, we meet two more advanced VBA techniques: conditional statements and loops.  More on them soon.  Onwards!

Topics And Techniques