Become an Excel VBA Troubleshooter: Save Time and Earn More

Being an Excel VBA troubleshooter opens new career doors, enhances your CV and makes you a highly valuable employee
4.7 (8 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
2,633 students enrolled
Instructed by Paul Morrissey IT & Software / Other
$19
$50
62% off
Take This Course
  • Lectures 54
  • Length 4 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 5/2016 English

Course Description

*** Course last updated on 1st September 2016 ***

This course is designed to take your basic VBA skills and quickly move you into the intermediate category.  You can probably already put together “most of the way there” programs.  These will usually have some form of problem (even if you don’t realise it).  They might have calculation errors, be a bit slow or break under specific use cases.  This course will teach you to track down and fix these problems … and then how to restructure your code to prevent such problems and handle run time errors in an effective way.    

A VBA Troubleshooter is a valuable team member.  You will quickly find people coming to you for help and advice.  Your CV will look much better and it will put in a great position to negotiate a pay rise or even use this as the first step in a new career. 


Some assumptions about you

Let’s agree that you are highly motivated, want to learn new skills and want to get ahead in life.  Maybe you want to earn more in your current role, maybe you want to enhance your CV or maybe you want to switch careers.  Let’s also agree you are willing to put in the time and effort to learn new skills … yet want to do so in an efficient and effective way.  Finally, let’s agree you believe in getting what you pay for.

Still with me? Keep reading and see exactly why over 1,000 students enrolled in just the first 10 weeks!!!


How this course is different

There are lots of great VBA courses both on the internet and on Udemy.  Many however try to cover a bit of everything related to VBA.  Before building my own course I looked at what was available and made sure I was providing something new and different.  Instead of teaching “a little bit of everything” I listened to my existing students, took the most useful VBA coding skill set and turned it into a full blown course all of its own.

I don’t aim for the complete beginner (you won’t learn about the basics such recording macros, CELL objects and RANGE objects).  Instead I assume you have some VBA knowledge and want to quickly improve on this by learning some higher level skills.  If you are a complete beginner however, just ask me for a list of resources to get you to the starting point. 

Rather than the usual passive “tell you how to do stuff” method of delivery, my teaching style is very much active – if you are lazy you will still get something out of it … but nowhere near as much as if you put in a reasonable amount of work to do the quizzes and exercises. In addition to all of this my goal is for the course to become a living entity with a “community of students”.  I have a long list of additional material, techniques and examples I plan to add.  I want it to become a reference point for future where you can dip in and out as you encounter new problems,  where you can request a specific topic to be covered and where you can provide the feedback needed to fine tune the course.


Who will benefit most from the course?

Almost everyone will gain something but some will gain more than others:      

  • Do you get a nice warm fuzzy feeling from those “Aha!!” moments when you understand something new?
  • Are you willing to put in some time and effort to learn new skills?
  • Are you willing to stick up your hand and ask a question … even if you think it sounds silly?

If you answered “Yes” to at least 2 of these then you have the right attitude and I want you as part of my student community.  I would much rather have a small set of the right students that complete the course and get involved actively. You should enrol on this course:     

  • BECAUSE you answered “Yes” to 2 questions from above – you fit my ideal student description;
  • BECAUSE you are driven, motivated, want to get ahead in life; 
  • BECAUSE you are willing to work to get what you want from life;


Frequently Asked Questions

Q: Why should I pay for a course when there is lot's online for free?

A: Very good point.  There are two problems with the free online content.  Firstly, it will take a long time to find what you need.  Even if you do find something it probably won't be exactly what you want and need modification.  This takes time and time is the only real scarce resource - it's valuable and you shouldn't go wasting it.  Secondly, although there is a lot of  good content online there is also a lot of complete and utter s*it£.  If you willing to spend the time finding content, are able to tell the difference between good and bad and able to modify what you find confidently then you don't need this course anyway.  Otherwise  you can save yourself some time and stress by enrolling.    


Q: Why do you put so much emphasis on exercises and quizzes? 

A: Past experience tells me the best way to lean coding is by doing.  I have taken this principle and used it to create this course.  You will find material in the exercises and quizzes that is not covered in the main body of the course and this is deliberate!


Q: Why do you not provide solutions to exercises?

A: There are two reasons.  Firstly, there is often more than one answer to a given exercise.  Secondly, and more importantly, if I do put up solutions then many students will become lazy and simply look at the solutions before attempting exercises.  I don't want this and instead want to encourage you to have a go yourself first.  


Still unsure? – try it for free!

I am giving away approx 30% of my course free as preview lectures.  I am doing this because I believe in the quality of my course, I am confident the previews will prove the course is well worth the price and that once you look at them you will want to buy the full course. 

If that isn’t enough my course is backed by the Udemy 30 day 100% money back guarantee – if you enrol and change your mind you can get a full refund with no questions asked.  Why would I highlight? Because I’m confident that once you enrol you won’t want to give it back!

What are the requirements?

  • A working copy of Microsoft Excel (2010 or above preferred but earlier versions are still okay);
  • Basic high school mathematics (knowing how to solve a quadratic formula helps);
  • A very basic knowledge of VBA (e.g. how to open up the Visual Basic Editor and create a "Hello World" program);
  • If you are a complete beginner you should still enrol - send me an IM to let me know and I can point you in the right direction for some introductory resources (free or paid - up to you);

What am I going to get from this course?

  • Learn a wide variety of techniques to quickly identify and fix problems in VBA code - even if you have no experience of debugging;
  • Improve the robustness of your code - we can never completely prevent un-handled runtime errors but you will learn make them a lot more unlikely;
  • Build your code in a way that even a complete beginner can quickly read and understand what it is doing - with an added benefit of making your code maintainable;
  • Gain confidence that your code is doing what is should be by learning how to build test harnesses;
  • Raise custom errors to highlight user input problems and prevent run time errors;
  • Build error handling mechanisms that allow you to quickly pinpoint what is causing an error and help you fix it;
  • Impress your work friends and your boss with your new coding skills ... in a very short space of time;
  • Move yourself from the "Beginner" to "Intermediate" VBA coder category;
  • Increase productivity by spending less time debugging code and preventing errors in the first place;

What is the target audience?

  • You get a warm fuzzy feeling when you understand new concepts (the "Aha!" moments);
  • You are willing to put in some time and effort to learn;
  • When you don't understand something you put your hand up and ask a question ... even if it seems like a silly question. I much prefer students that engage with me (everyone benefits);
  • You are driven, motivated and want to get ahead in life;
  • You plan to complete the WHOLE course;
  • You want to learn high level skills as well as a set of best practise methods for building robust VBA code;
  • You enjoy doing exercises and learning in a practical way in general - this course is very much geared to active learning;
  • You don't mind having a go, getting it wrong and learning from your mistakes (this really is the best way to learn and I encourage it);

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
09:13

This short lecture aims to give an overview of the entire course and show you what you will be learning and how.  

03:29

Thank you for enrolling on my course! :)   

This is mostly a house-keeping lecture ... yet a very important house-keeping lecture.  Understanding how this course came into being and a bit of my background will give you a much better feel for why I do things a certain way.  I describe my vision for the course as a living and growing learning tool and reference source.

02:16

My teaching style is very different to many others I have seen (both on Udemy and every other place of learning).  I believe in learning by doing and by trying without being afraid of getting it wrong.  I have designed this course to allow that as much as possible using examples, quizzes and exercises.

Here I give a brief overview of how the content of my course differs and some details of my teaching style - how it differs and why I think it's the best way of learning.  

02:12

This course is designed with an active approach to learning in mind.  Each section has examples and exercises and various quizzes are scattered throughout.  

In this lecture I explain to you my suggested learning approach to help you get the most out of the course.  

01:39

The exercises are very important. I have designed them so that I don't quite teach you absolutely everything when you attempt the exercise (the material might be covered later). You are supposed to make mistakes and have areas to improve on.

By making mistakes and correcting these you will learn a huge amount. The key reason many find it difficult to learn new skills is that they are afraid to get things wrong - don't let this happen to you!!

7 questions

The aim of this is to find out where you are now before you start working through the course. We will have a similar quiz at the end and comparing the two should clearly highlight what you have learnt.

Section 2: Your First Simple Test Harness
03:33

As soon as people figure out you know what you are doing with VBA they start asking you for help. Usually this is in the form of "Can you fix this macro for me?". 

In this lecture I will introduce you to a an example spreadsheet for a request just like this.  In the lectures that follow we will gradually fix and improve this spreadsheet and turn it into an awesome piece of coding.

01:27

You will learn what a test harness is, what they are used for and why they are important.  We leave the building of our first simple test harness to the next lecture.  

03:35

Now you know what a test harness is and what it does you will actually build a test harness for our example code.  

We start with a simple test harness for a single function (which we call a user defined function) in a worksheet.  In later lectures we will expand and improve the test harness but for now this quick and simple harness will do what we need.  

04:06

In addition to our worksheet test harness we are also going to build a test harness in VBA code.  When we do we will see the VBA test harness won't even run!! ... the spreadsheet and code is so broken that it throws a type mismatch error.  We won't go about fixing it just yet but will do so bit by bit as we progress through the course. 

Article

Throughout this course we focus on a single example of solving a quadratic equation using the quadratic formula and work through this together.  

I appreciate that it may have been a number of years since you did any high school maths so the file you can download from the resources section contains some background on the quadratic formula and how to solve it.  

It is meant as a quick refresher and details of the theory are not important: this is just an example and we are more concerned with learning a coding skill set.

8 questions

A brief quick to make sure you understand the concepts covered in this section. 

Article

In the resources to this lecture there is:

  1. A spreadsheet with a code module and a function;
  2. A worksheet in this spreadsheet explaining what the function should be doing and the exercise requirements;


After downloading the resources you should attempt the exercise.  This provides an additional example of building a very basic test harness. 

Extra credits: the function is currently not behaving correctly - are you able to fix it?

Section 3: Making your variables justify their existence
06:29

Option Explicit is the single fastest and easiest way to improve your code.  It (still) baffles me how many people miss this simple change.  

In this lecture you will learn to quickly correct improve code by adding Option Explicit to existing code.  You also learn how to make sure the VBE automatically adds it to all new modules and class modules.  

12:57

All variables are not created equally ... and some are much better suited to certain tasks than others.  In this lecture you will learn:

  1. Variable naming conventions that will make your code much easier to understand;
  2. How to choose the correct variable type to minimise unexpected behaviour;
  3. Which variables to avoid and why;
8 questions

This quiz tests your knowledge of the section on variables ... and has some refresher questions from the test harness section. 

Article

In the resources to this lecture there is:

  1. A spreadsheet with a code module and a function;
  2. A worksheet in this spreadsheet explaining what the function should be doing and the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of working with variables. 

Extra credits: the function is currently not behaving correctly and the code layout is horrible - are you able to fix it and use the code layout to improve readability?

Section 4: A brief tour of the various VBA Debugging tools
03:53

The immediate window can give us information on variables and objects while the code is running.  Our main usage of it will be with Debug.Print statements within our test harnesses.  

However, there are a few other less well known things the immediate window can do that we cover here as well. 

03:03

Breakpoints allow us to pause code execution and inspect the values of our variables and states of our objects.  There are lots of ways we can add breakpoints and even different types of breakpoints.  

We quickly cover some of these in this lecture and you will see breakpoints pop up a lot in the lectures that follow. 

01:36

If we open the locals window while in break mode we can see all variables currently in scope, inspect their values and see their level of scope.  We can change the level of scope displayed by using the locals window along with the call stack (explained later).  For now we take a quick tour around the locals window using our Newton Raphson function. 

04:07

The watch window is very similar to the locals window - in this case however we see the details of a set of variables we specify (rather than all variable in the current level of scope).  To do this we set up a watch and watches can be used to set up "break points" ... where the code moves into break mode under certain conditions during code execution. 

03:26

The call stack allows us to trace backwards during break mode to the point of code where a given function or subroutine was called.  I find it most useful when used together with the locals window and when we have a lot of functions or subroutines with function calls to other subroutines or functions. 

03:24

The last lecture in our tour of the VBA Debugging tools shows you a few of the most common VBA debugging menu toolbar buttons I tend to use and where to find them.  

Section 5: Stepping through and debugging your code
08:21

Up to now we have been slowing making changes to our example spreadsheet but we have yet to get correct answers from our function.  

In this lecture you will step through the code, identify exactly where the bugs are and then fix them.  This is a very important lecture but comes after the test harness ... since we need some way of being able to tell when we get the correct answers.  

8 questions

A few questions to test your knowledge of this section and review the two earlier sections.  

Article

In the resources to this lecture there is:

  1. A spreadsheet with a code module and a function;
  2. On this spreadsheet will be a worksheet explaining what the function should be doing and the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of stepping through your code and using this technique to weed out bugs. 

Note: The code is already laid out in an easy to read way, the variables make sense and we are using Option Explicit.  There is still something wrong and this is the time when we start stepping through code.  

Section 6: Using a debug mode
01:45

At the heart of a debug mode is the Assert statement.  The problem with the Assert statement is how it leaves your code execution in break mode. 

In this lecture you will learn how to use a debug mode that allows you to use Assert statements to help you catch problems, ensure your code is correct and helps you debug ... without your code dropping into break mode when it is released to users!

07:38

Following on from the previous lecture we will work through our example and actually build in a debug mode with some sensible Assert statements.  

8 questions

A short quiz to test your understanding of debug modes.  

Article

In the resources to this lecture there is:

  1. A spreadsheet with a code module and a function;
  2. A worksheet on this spreadsheet explaining the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of building a debug mode. 

Note: This exercise uses the same example of one of our earlier exercises (after I have worked on the variables and fixed it by stepping through).  The code is correct and your job now is to improve what we have.  

Section 7: Make your debugging job easier with some simple refactoring
01:47

At a very high level refactoring is the process of making changes to improve the internal shape, structure and maintainability of your code ... without changing the functionality of your code.  The end result of a refactoring exercise should be cleaner code that does exactly the same thing as before.  

In this lecture you will learn what refactoring is and why we bother doing it at all.  


10:32

There are a lot of possible refactoring techniques out there (and whole books have been written on them!).  When coding in VBA I find myself using the same ones over and over again and most of these are simple enough that we can cover them in this course.  

In this lecture you will learn about 3 common refactoring techniques I tend to use, how they work and the process for actually making the changes. 

05:59

Extract method is essentially taking a block of code (or even just one line) and using this to create a new method all of it's own.  

In this lecture we use the extract method technique to improve our solveQuadratic function by extracting a number of methods and creating separate test harnesses for these. 

02:07

Eventually we will completely remove the select case from our solveQuadratic function.  However, for now we can do some simplifying of it and that's what we cover in this lecture. 

04:05

We now apply the replace variable with query refactoring technique to our solveQuadratic function.  In doing so we see an example of the balancing act we have to maintain ... it would be very easy to go too far with this refactoring technique and I decide to stop the refactoring when we reach the arrays of solutions (since it is more convenient to use these).  

04:46

Comments are a great way to improve code readability and many of you will be wondering why I have left it so late to cover code commenting.  The reasons are simple: well written code has less of a need for comments.  After performing our various refactorings we see our code becomes self commenting and so we only need a few comments here and there.  

Another important point to bear in mind is the more comments we have the more work we have later when we update our code ... since we have to update our comments as well.  One of the worst coding mistakes you can make is to fail to update your comments since they can then become actively misleading!

7 questions

I had a spare hour on a Friday afternoon and decided to build this quiz ... don't tell my boss I was skiiving off!

Article

In the resources to this lecture there is:

  1. A spreadsheet with a code module and a function;
  2. A worksheet on this spreadsheet explaining the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of the various refactoring techniques. 

Note: There are many ways to perform the refactoring in this exercise and no single right or wrong answer ... have a go and see what you can come up with.  

Article

This free YouTube video describes how to create a debug mode using conditional compilation and some of the pitfalls to watch our for when you do it. 

Section 8: Error handling that helps you catch bugs
04:35

To handle our errors in an elegant way we first need some understanding of the built in error handling objects in VBA.  In this lecture we learn about the Err object, some of it's main properties and methods and how the Err object is cleared.  

16:07

A common (general) programming structure for error handling is the try-catch-finally structure.  When using this we:

  1. Try to execute a given block of code;
  2. When an error occurs we "catch" it and redirect code execution to a separate "catch block" of code;
  3. In both cases we always execute a final block of code (the finally block);

Such a try-catch-finally block is not available in VBA by default.  However, we can use the the built in error object and some carefully structured GoTo statements and locations to replicate this behaviour.  In this lecture we learn exactly how to do that!

05:27

The first task in any subroutine or function is to perform input validation on the input arguments.  This involves making sure they are sensible when taken on their own and taken together.  Since there are only limited checks we can perform in the method signature we perform the input validation just after the signature and when something looks wrong we raise a custom error. 

In this lecture I take you through how to perform all of the above and ensure your methods are effectively validating all input arguments.  

05:59

Here you will learn an additional error handling technique that I absolutely love!! ... how to structure your error handling code to help you track down bugs.

8 questions

By this point you should be feeling much more confident about your VBA knowledge ... so I'm going to really start pushing you and see what happens.  

Article

In the resources to this lecture there is:

  1. A spreadsheet with codes module and some VBA code;
  2. A worksheet on this spreadsheet explaining the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of adding error handling to your spreadsheets. 

Note: There are many ways to structure your error handling and the technique I have shown you here is simply a common approach that I have used in the past.  There is no right or wrong way of building your error handling - so long as it does it's job!  In some of my bonus material I plan to build on this by showing you how to construct a central error handling and some of the other techniques/structures that you might want to use.   

Section 9: Enhancing Your Test Harness
05:08

Boundary conditions are not the easiest things to get your head around.  I tend to think of them as a set of inputs that give known simple or special results.  

Examples might be a set of minimum or maximum inputs.  Or a set of inputs that mean the code essentially "does nothing".  

They are specific to a given problem and in this lecture you will learn the basic idea behind them and how to use them to make sure your code is behaving as it should be. 

04:59

The last thing you want is for your custom error handling to either:

  1. Miss an error it is supposed to catch;
  2. Flag an error where there is none

In this lecture we learn how to make sure our custom error raising and handling is working correctly. 

02:09

So far we have used only the one test case.  In reality you probably want to have a range of test cases which are essentially frequently used examples.  Here we learn how to extend our test harness to include these. 

7 questions

The final quiz.  The questions in this cover the whole course and are more difficult than the others.  We will really test your understanding here.  

Article

In the resources to this lecture there is:

  1. A spreadsheet with codes module and some VBA code;
  2. A worksheet on this spreadsheet explaining the exercise requirements;

After downloading the resources you should attempt the exercise.  This provides an additional example of enhancing a test harness to include all of the things covered in this section. 

Section 10: Questions and Answers
02:58

One of my students left a course review and then wanted to update the review - it's not always obvious how you can do this and so in this lecture I give detailed steps and provide a link to the Udemy support page with further instructions.  

02:39

Recently a student asked me whether they could view my lectures at different speeds.  I deliberately try to make my lectures accessible to non-native English speakers by talking a little slower (and clearer) than I might normally do.  For native English speakers you may find this a bit slow but there is a way to speed up lectures and in this video I show you how.  

02:39

Another question I have had from students was related to how I can improve my lectures.  I had already made some changes based on this and the video explains what the question was about and how I have gone about improving new lectures.  

Article

Recently a student asked me for a set of resources to help a complete beginner to learn about Excel and VBA.  The article in this lecture gives a list of online resources of varying levels and some comments on them.  I have tried as much as possible to find free resources and will add/modify this over time.  

Section 11: Troubleshooting library
23:38

Type Mismatch errors are an annoying and common error that can occur.  At a high level they are quite simple but there are a number of special cases that can trigger (or not trigger) these errors that it's worth being aware of.  In this lecture you will learn all about them.  

01:47

This is for those situations when a user is trying to step through code themselves and wants to inspect the reference libraries being used.  Problem is that you can't do it in certain circumstances.  It's a really short video with one of these example situations.  

11:10

A subscript out of range error can occur when you have a collection and you try to access elements of this collection using an invalid keyname.  A very common example is when we have a macro that requires a worksheet tabname - the user then goes and changes this tabname and the macro breaks with a subscript out of range error.  In this lecture you will learn to fix this type of error using worksheet codenames. 

08:59

We continue with the subscript out of range due to an invalid keyname ... but this time prevent the problem using a hidden named range.

4 questions

Some questions to test your knowledge of the library contents and keep your troubleshooting skills sharp. 

Section 12: Thank you for taking the course and keep in touch
Article

Mainly just a bit thank you from me!

Article

My plan for a final section of this course is to have a library of common errors/problems and techniques for solving these.  To build this I need your help though: please send me details of problems you have encountered and maybe we can include them here.  

Section 13: Bonus material and retired lectures
16:53

This lecture shows you how to construct a debug mode using conditional compile in VBA.  The lecture is recorded in MS Access and 99% of the techniques used are the same for MS Excel.  I have uploaded it to the bonus material section since the main course is on MS Excel and it is the same video that is linked to in the lecture "how to create a debug mode using conditional compile".  

The course bonus lecture
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Paul Morrissey, Udemy learning junkie, instructor and total Geek!

Hi Everyone! 

My name is Paul and I'm a Udemy learning junkie.  I found Udemy in late 2015 and by mid 2016 created my very first Udemy course and officially became a Udemy instructor.  I'm a total Geek and find it hard to sit around and do nothing - building Udemy courses is just one of the various projects I find myself doing in my spare time.  It takes a lot more work than I expected to build a course but I have found I really do enjoy it.   

My goal on Udemy is to rank among the top instructors within the first 3 years.  This is ambitious and will take a lot of work.  It's also something I can't do on my own: only with the feedback and support of my students will I stand any chance of making this happen.  

Academic Background

My undergrad degree was in Aerospace Engineering (MEng).  During this I found I was far more interested in the mathematics and programming than Engineering so I did a masters degree in Pure Mathematics (MSc) and a PhD in Computer Science (Cryptography).  After finishing my PhD I started working as an Actuary and I have just finished the UK Institute and Faculty of Actuaries (IFoA) fellowship qualification (FIA). 

Coding and Teaching Related Experience

My first encounter with coding was when I learnt Fortran as an engineering undergrad.  Later when working on undergrad and MSc projects I learnt to code in Matlab (using m-files) and then in C.  

My coding skills only really took off though when I working on my PhD.  The computer science department I was in only used Linux - I quickly had to learn use Linux operating systems, shell scripting and teach myself C++.  It was here where I learn object orientated coding techniques and built some very complex and fast code (mostly for integer factorization).     

When working in Actuarial related roles I have had to master Microsoft Excel, Visual Basic for Applications (VBA) and C#.  In my current role as Actuarial Systems Technical Lead for a major UK Insurer I mainly deal in VBA and C# code.  In addition I take care of the Actuarial Infrastructure and various modelling platforms.  

I started out teaching when some work friends noticed me coding in VBA and asked me to teach them.  I said okay and my first attempt was a series of informal emails with links, a list of topics to cover and exercises to enforce the concepts.  This gradually grew into a website of it's own and later into full blown Udemy courses.  In addition I provide actuarial system training internally as part of my day job (it's wide and varied!).   

Outside of Work

For a number of years I have spent my free time studying for the IFoA fellowship exams.  Now that these are done I find myself spending more time learning other things (coding, foreign languages - learning German at the moment).  I keep myself in shape with regular trips to the gym to lift weights and running.  

Ready to start learning?
Take This Course