Become an Excel VBA Troubleshooter: Save Time and Earn More
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,922 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Become an Excel VBA Troubleshooter: Save Time and Earn More to your Wishlist.

Add to Wishlist

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,922 students enrolled
Created by Paul Morrissey
Last updated 9/2016
English
Learn Fest Sale
Current price: $10 Original price: $50 Discount: 80% off
3 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 13 Articles
  • 9 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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;
View Curriculum
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);
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!

Who 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);
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 54 Lectures Collapse All 54 Lectures 03:58:36
+
Introduction
5 Lectures 18:49

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

Preview 09:13

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.

Welcome
03:29

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.  

Preview 02:16

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.  

How to get the most out of the course
02:12

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!!

Using the exercises to build and enforce your understanding
01:39

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.

Understanding your own starting point and knowledge
7 questions
+
Your First Simple Test Harness
6 Lectures 13:22

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.

A common scenario
03:33

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.  

What is a test harness?
01:27

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.  

Preview 03:35

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. 

Building a test harness in VBA code
04:06

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.

Background on quadratic Formula
00:24

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

Basic test harness
8 questions

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?

Basic test harness coding exercise
00:17
+
Making your variables justify their existence
3 Lectures 19:46

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.  

Preview 06:29

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;
Correcting variables types and names
12:57

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

Variables
8 questions

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?

Variables coding exercise
00:20
+
A brief tour of the various VBA Debugging tools
6 Lectures 19:29

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. 

Using the immediate window
03:53

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. 

Preview 03:03

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. 

The locals window
01:36

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. 

Keeping an eye on specific variables with the watch window
04:07

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. 

Tracking variables through function calls using the call stack
03:26

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.  

The main debugging menu toolbar buttons
03:24
+
Stepping through and debugging your code
2 Lectures 08:46

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.  

Stepping through your code
08:21

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

Stepping through your code
8 questions

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.  

Debugging coding exercise
00:25
+
Using a debug mode
3 Lectures 09:44

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!

Preview 01:45

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

How to build a debug mode
07:38

A short quiz to test your understanding of debug modes.  

Debug mode
8 questions

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.  

Debug mode coding exercise
00:21
+
Make your debugging job easier with some simple refactoring
8 Lectures 29: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.  


What we mean by refactoring
01:47

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. 

Some common techniques I use
10:32

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. 

Preview 05:59

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. 

Simplify select case
02:07

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).  

Replace variable with query
04:05

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!

Preview 04:46

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

Refactoring
7 questions

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.  

Refactoring exercise
00:19

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. 

How to create a debug mode using conditional compile
00:11
+
Error handling that helps you catch bugs
5 Lectures 32:41

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.  

Preview 04:35

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!

Basic try-catch-finally in VBA
16:07

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.  

Input validation
05:27

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.

Using error handling to debug
05:59

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.  

Error handling to help debugging
8 questions

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.   

Error handling exercise
00:33
+
Enhancing Your Test Harness
4 Lectures 12:29

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. 

Testing boundary conditions
05:08

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. 

Testing error handling is working
04:59

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. 

Preview 02:09

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.  

Enhancing Your Test Harness
7 questions

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. 

Enhancing your test harness exercise
00:13
+
Questions and Answers
4 Lectures 10:27

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.  

How to I edit an existing course review?
02:58

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.  

How to I change the viewing speed of lectures?
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.  

A question about improving lecture quality
02:39

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.  

What are a good set of resources for learning MS Excel and Basic Level VBA?
02:11
3 More Sections
About the Instructor
Paul Morrissey
4.8 Average rating
8 Reviews
2,922 Students
1 Course
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.