Let's establish some motivation for the Excel VBA Simulation programme!

In the first lesson, I explain the purpose of the course and how it should push forward your Excel VBA skills.  I also explain the overall structure of the course, give suggestions about how to tackle it, and introduce the learning journal.

In lesson 2, I introduce you to the basic simulation mechanism in Excel.  Most Excel simulations are driven by the same basic mechanism - so let's learn it immediately!  The mechanism consists of a probability table, VLOOKUP formula and random number.  We also learn how to validate the mechanism to ensure the outputs conform to the desired probability distribution.  Here you go, your first Excel simulation!

Never touched Excel VBA before?  No matter, in this course we build from absolute beginner level.  VBA delivers unique benefits that no other functions in Excel can, and these benefits really help our Excel VBA business simulations.  Let's get started with VBA and begin to feel those benefits now ...

Excel VBA simulations allow us to build simulated data sets at the click of a button.  Now that's cool, but how do we know those datasets are accurate?  It's vital to be able to quickly check the validity of a dataset.  To do that, let's harness some of Excel's data analysis formulae.  Not only will these help you with Excel business simulation, they will benefit your Excel practice more generally.  Chris calls them his 'secret weapons'!

So far, we have explored how to build our own 'custom' probability distributions.  Now, that's cool, but it's not always necessary.  That's because statistics provides us with pre-configured probability distributions that commonly occur in business, and in life generally.  Yes, statistics can be practical and useful!  In this lesson, we explore the most widely used 'pre-made' probability distribution - the normal distribution.

Now you've tasted the power of Excel VBA, it's important to take the time to build a solid foundation.  In this lesson, we cover some of the basic concepts in Excel VBA that are essential for efficient and robust coding.  These ideas make coding more efficient which means faster execution and less code to read for the programmer.  Result!

A loop is one of computer programming's most powerful concepts.  By asking a programme to repeat (or 'loop') a set of instructions until a certain condition is met, we can get HUGE amounts of work done.  In this lesson, we explore the 3 loop options in Excel VBA.  Do you absolutely need all 3?  Probably not, but we do use them in the workthrough video and, besides, learning Excel VBA is fun!

A conditional statement is another powerful coding concept. By asking Excel VBA if something is happening in the spreadsheet (ie. if a 'condition' is met,) we can send the code in one of two directions.  In fact, we can send it in many directions!  In this lesson, we explore how to get conditional statements working both in the spreadsheet, and in the Excel VBA editor.

What, there's even more to Excel VBA?  Unbelievably, there is!  In this lesson, we go deep into the Excel VBA toolkit and explore some of its most powerful features.  Application.WorksheetFunction allows us to harness the power of worksheet formulae without having to input formulae into the spreadsheet.  Mind-bending, I know!  It's a difficult one, so take your time.  And, remember to punch the air if (when?) it works!

Ready to be thrown into the deep end?  We've held nothing (yes, nothing!) back for this course: we're throwing the most powerful techniques Excel VBA has to offer you in this lesson.  We will explore two techniques that can have a 'game-changing' effect on your Excel VBA coding, and that facilitate awesome Excel VBA simulations.  Sink or swim?  Let's see how it goes, and don't forget to record what happens in your learning journal!

In the 1st walkthrough video, we consider some typical scenarios that we would wish to 'simulate', or generate random data for.  Then, we choose the most interesting one (about dogs, naturally,) and begin to model it by creating the basic simulation mechanism in Excel.

Most Relevant Lesson(s):
2

In walkthrough video 2, we look at how to transfer the basic simulation mechanism into VBA.  This might be preferable because, for example, we might not want random numbers visible in the worksheets.

Most Relevant Lessons:
6,7,9

Things get much more difficult in video 3.  We look at how to 'scale up' a simulation model by creating a mechanism to add additional columns of random data at the click of the button.  This kind of functionality requires some sophisticated dynamic functionality to support it.

Most Relevant Lessons:
4,9,10

In this walkthrough video, we continue the process of scaling up the model and incorporating more dynamic functionality.  We also take the time to validate generate data to check it is in line with the custom probability distribution inputted.

Most Relevant Lessons:
4,7,8

In video 5, we build multiple columns into the model and incorporate flexible options such as choosing the type of probability distribution for each column.

Most Relevant Lessons:
6,8,10

In the sixth walkthrough video, we explore how to sample the normal distribution in an Excel VBA simulation model.  As usual, we validate the outputs to ensure they conform to the expected distribution.

Most Relevant Lesson:
5

In this walkthrough video, we deal with the tricky issue of Excel formatting.  Chris gets into a tangle and works his way out with the help of an internet search or two. 

By this stage of the model build, content from almost all the lessons is relevant.

Ah, dates in Excel!  They can be problematic.  In walkthrough video 8, Chris shows how to create a random set of dates using Excel VBA, and also provides some practical tips on working with dates in Excel.

By this stage of the model build, content from almost all the lessons is relevant.

Excel VBA simulation models tend to be built for particular business situations.  Is a 'general' simulation model even possible?  In these videos, Chris attempts to apply the model to a different scenario, uncovering some of the implementations strengths, and a few weaknesses.

By this stage of the model build, content from almost all the lessons is relevant.

In the final walkthrough video, we apply the same model to generate numbers for a fictional lottery.  

By this stage of the model build, content from almost all the lessons is relevant.

This section includes:

1) Interview with an industry professional, academic and former senior data consultant Giles Hindle.  Chris and Giles discuss the application of statistical analysis in Excel and how Excel VBA simulations can deliver value in the real world

2) Building A Simulation Model For The Monty Hall Problem.  This famous problem has confused people for years.  We can solve it using an Excel VBA simulation model.  Taken from our Members' Monday live sessions - click JOIN below any YouTube video for more information about Members' Monday.

It's been a pleasure to be your guide through the Excel VBA Simulation programme.  Now, whose guide will you be?