Microsoft Excel Essentials: Level 3 - VBA Programming+Macros
4.6 (191 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.
1,741 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel Essentials: Level 3 - VBA Programming+Macros to your Wishlist.

Add to Wishlist

Microsoft Excel Essentials: Level 3 - VBA Programming+Macros

Turbocharge Excel with VBA! From Helper Programs to Complete Solutions With Excel VBA - Excel 2010 Excel 2013 Excel 2016
4.6 (191 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.
1,741 students enrolled
Created by Alan Jarvis
Last updated 9/2017
Price: $150
30-Day Money-Back Guarantee
  • 13.5 hours on-demand video
  • 61 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn to program in VBA from scratch
  • Complete routine tasks thousands of times faster! (Yes, see the demo!)
  • Confidently tackle any programming project
  • Understand coding, and the thought process behind it
  • Fully automate Excel using Visual Basic for Applications
  • Fully automate report generation
  • Program simple "helper" solutions
  • Develop complex full blown applications from scratch
  • Control other Microsoft applications like Outlook, Word, PowerPoint and even Windows itself!
  • Create emails and send them automatically!
  • Create Word documents using VBA code right from Excel!
  • Create PowerPoint presentations in seconds automatically
  • Create entire folder directories in Windows automatically!
View Curriculum
  • All you need is a PC, a copy of Excel 2007, 2010, 2013 or 2016, and a willingness to learn!
  • Ideally you will need to have completed Level 1 & 2 of this series
  • You will have a good understanding of the basics of Excel

Over 18,000 students in 167 countries are becoming Excel masters with THIS series! Check out the FREE PREVIEW LECTURES and SEE WHY!

The No.1 Excel VBA Programming Course on Udemy!

If you really want to harness the full power of Excel, then learning how to program in VBA (Visual Basic for Applications) is ESSENTIAL!


  • Over 500 Students enrolled in the first 2 weeks...find out why!
  • Over 15,000 Students in 162 countries are mastering Excel with THIS series
  • Average minutes viewed over triple that of similar similar courses on Udemy!


My guess is you're reading this because:

  • You want to join the Excel Elite, and program your own solutions?
  • Maybe you want to automate most of your work, do more in less time with less effort?
  • Could be you want to earn more money?
  • Perhaps you may even fancy a career change?

If you answered yes to any of the above, I want you in my student community!

In this course you will learn everything you need to know about coding in VBA, even if you've never looked at a line of code before!


"This is a great course! ...I can't wait to start using it. I will certainly be using the emailing program! And reports. My mind is reeling!"  - Nickie Student!

"Great course overall, by far the best on Udemy..." - Antsta


Introducing Visual Basic For Applications

Visual Basic for Applications, or VBA, is included with Excel, so there's nothing more to buy. If you have Excel, you already have access to VBA.

VBA is the powerhouse. It's the code that lives under the hood where the magic happens.

A lot of people can record a Macro, but being able to program in VBA is quite a different thing. It separates the good from the truly amazing, and I want you to be amazing!

Now, one of those words in "Visual Basic for Applications" carries more weight than the others. And no, it's not "Basic" (though coding the way I will teach you is simple!).

No, it's "Applications". In fact, it's the "s" at the very end. The significance is that VBA works across ALL MICROSOFT APPLICATIONS! Word, Outlook, Access, PowerPoint, Internet Explorer, and even WINDOWS ITSELF!

In this course I will not only teach you how to code for Excel, there are even THREE, no...FOUR Bonus Sections:

  • Contolling Word
  • Contolling Outlook
  • Yes, even controlling Windows
  • and now...Automating PowerPoint!


"If you are serious about learning how to make excel work for you, you should take this course" - Beatrice

"I first encountered Alan by doing his level 1 and level 2 courses here on Udemy. I wish I had come across him many years earlier. I'm learning so much about VBA than I could have ever imagined. This is an excellent course..." - Jonathan Balmforth


What Makes Me Qualified To Teach You?

Well, I'm a regular guy. I don't have a University degree, but what I do have is the drive to learn new things and never be beaten by a problem...a lot like you, I guess.

What I also have is over 20 years of experience in using Excel, and programming solutions with VBA.

I have built simple helper systems to make life easier for my colleagues. I've programmed complete MRP systems to run the procurement division of a £1.3b company, and everything in between.

So, I can do it, does that mean I can teach you?

You bet! I have taught hundreds of students one on one, and even more in groups. Many of my students have gone on to better paying jobs, start their own businesses, or at the very least made their work lives less stressful! One of my most recent students got a promotion and a 15% pay rise just 16 weeks after learning how to code! Not bad, hey?

Why Is Your Course Different, Alan?

I'm glad you asked!

If you got this far, you already know you get:

  • Over 13.5 hours of HD video lectures
  • Tons of downloadable resources
  • Quizes
  • All the source code for the course + more!
  • Programming projects

There are lots of great courses on coding in VBA. There are even lots of free videos on YouTube. Many of them show you all the basics in kind of a "little bit of everything" approach. I looked at what was available, and I knew I had to create something different. Of course I still cover all the basics, just like the other courses, but we will go beyond the basics, and cover in depth the "missing bits".

In addition, I will teach you my step-by-step process, the actual methods I use to build powerful solutions easily and quickly. I'm sure programming can be difficult, but not the way I do it!

You will learn how streamline your code for efficiency (and make it execute faster!) from the beginning. After we've covered all the basics, we will go on to create 6 separate projects using those very tools and techniques, where we will pull together all of your earlier learning, and give you the confidence to program your own solutions as soon as you finish the course!

With So Much Power, What Will You Create?

With the skills you will learn in this course you will be limited only by your imagination.

If you can describe it, you can most likely code it. What will YOU create?

Let's see. Enrol now, and let's get started!

Who is the target audience?
  • No programming or coding experience needed!
  • This course for those who want to harness the full power of Excel
  • Anyone who wants to develop their career
Compare to Other Excel VBA Courses
Curriculum For This Course
173 Lectures
What's It All About, Alan?
2 Lectures 16:52

Why should I learn how to code? This is a question I get asked all the time. In this lecture we cover the 3 main reasons: Speed, Accuracy & Repeatability!

You will also see a live demonstration of the time difference between doing a task manually vs. automating with VBA code...the result is staggering!!!

Preview 10:28

This lecture covers all 16 of the 15 Golden Rules of Coding :)

As a beginner, I had to learn through trial and error, but you don't have to! In this lecture we will cover the "Golden Rules" can download the .pdf from this lecture. Print it out, keep it near your computer and learn them will save you countless hours of frustration.

Preview 06:24
Introducing Your Personal Built In Translator...the Macro Recorder
10 Lectures 01:07:51

In this lecture we will take a look at the VBE, that is the Visual Basic Editor. This is the environment where we will write our VBA, our Visual Basic for Applications code.

We will also record a macro, and take our first peek under the hood, and see how Excel's built in translator can help us in writing code of our own.

You can download the file used in this example to follow along.

Preview 11:10

Now we have managed to get some code into our workbook, we're going to take a look at how we save the file so we can keep it!

We will also take a look at Macro Security, and the difference between opening a regular Workbook (.xlsx) and a Macro-Enabled Workbook (.xlsm)

Preview 03:16

Okay, moving on...we're now going to add our second piece of code (still using the built in "translator" (hint: the Macro Recorder), and then we're going to learn how to move code around to suit our needs...

Preview 04:38

Code runs pretty fast, huh? Too fast to see in fact!

In this lecture you will learn about stepping through your code a single line at a time (MUCH easier!). The benefits of doing this are that you can see exactly what is happening live, and if there are any errors, you will know where to look!

Preview 05:50

In this lecture we will dissect the With...End With statement. We will test what each of the elements does, and decide if we actually need all of them...and if we don't, what to do about it. 

With And End With

In this lecture you will begin to learn the dark art of making your code more efficient!

We will look at a simple trick to dramatically reduce (in some cases) the number of lines of executable code needed to perform the same job. Common sense will tell you 5 lines of code will run faster than 10, but 2 will run faster still!!

Streamlining You Code, Or, Get Rid Of What You Don't Need

In this lecture you will learn how combine the code from the two macros we have recorded so far, and better yet, learn how to execute them one after the other automatically, leaving them in their original modular form!

Combining Your Code

Now you know a bunch of different ways to execute, or run, your code from Excel. Well, there is a chance that others who may use your workbook also know how to do that! So what happens if you don't want them to see the Macro names in the "Run Macro" window?

It's just a question of privacy...

A Little Privacy Please

You will have possibly noticed that I do not use keyboard shortcuts when recording macros, and with good reason!

In this short lecture, we will look at the keyboard shortcuts already assigned (and you'll see there aren't many left!), and why it's generally not a great idea to use the ones that are free.

Keyboard Shortcuts, And Why I Don't Use Them

And it was all going so well!

In this lecture, you will see why recording Macros will only get you so far.

They are useful for generating chunks of code that we can play with and adapt, but sometimes you're just going to have to roll your sleeves up and write some code proper, and from scratch (don't worry, it's easy :))

Why You Can't Get By With Just Recording Macros

Okay, we've covered quite a lot of ground so far, time for a pop quiz...

Section 2 Quiz
5 questions
The Building Blocks Of Coding...Your Dictionary & Phrase Book Of Success!
31 Lectures 01:40:36

This is a brief intro to the most IMPORTANT section of the entire course!

Preview 05:35

In this lecture, we will cover everything you need to download for this section, and take a look at how to import all the code for your very own "my Macro Book".

We'll also cover how to add a button, and assigning a Macro to it!

Getting All The Code For This Section

In this lecture we need to make sure that your VBE settings are the same as mine. We'll cover how you do this, and also cover some options to change the look of the text in the coding window...

Changing Your VBE Settings

Okay, so you've written your coding masterpiece, how do you protect it?

In this lecture we will cover how you lock your code from prying eyes!

Protecting Your Code

In this lecture we will discuss the object oriented language that is VBA. We will cover what objects, methods and properties are.

Now, it's not important you know the names of these things, I programmed for 18 years without knowing them, BUT it IS critical you know what they do.

After this lecture, you certainly will!

You will also learn a really quick way to find which properties and methods are available to an object, and how to tell the difference between the two :)

Objects, Methods And Properties

Up to this point, I haven't mentioned the hierarchy. I did this deliberately to keep everything as simple as possible. That said, you will soon learn that whatever code you run, it always runs on the sheet you are looking at! That's fine sometimes, but perhaps not always!

That's where the hierarchy comes in.

By the end of this lecture, you will have a complete understanding of this vital principle.

Understanding The Hierarchy

In this lecture, we will cover the object you will be using more than any other...the Range object. This is the first of two ways to reference a cell, and without doubt the simplest to follow (and you'll see why!)

The Range Object

In this lecture, we will discover other way to reference cells...using the Cells object.

You may wonder why there is more than one way...well, the simplest is the Range, but Cells does have it's uses...particularly when we get to looping (which we cover in the next section :))

The Cells Object

Now, we don't always need to hard code the cell we want to use. Sometimes we just want to use whatever is already selected. That's what the ActiveCell does.

Though it sounds like an object, technically it's a property that returns the object of the currently selected cell...Phew!

The ActiveCell Property

Ooh, you will be using this one a LOT!

In this lecture we cover one of my favourite property...the Offset.

The Offset Property

Here's another one that you will be using a lot. The End property.

The End property will allow you to make your code completely dynamic. So when you get to manipulating data sets, or importing them, your code will never miss a thing...very cool indeed!

The End Property

Now we will look at extending the functionality of the End function, to dynamically select (for now) ranges of data.

Yep, you will be seeing a lot of this one too!

Dynamic Range Selection

We're really getting in to my favourite snippets of code now! Another personal favourite of mine, the CurrentRegion. Very useful for grabbing a whole data set!

The CurrentRegion Property

You may have noticed he very first method in the method/propery list is Activate. How does that differ from Select? You'll know in about 2 minutes :)


Right, we've exhausted everything we will ever need to know about selecting cells, now we're going to do the same with the next level of the hierarchy...the worksheet!

We will learn how to flip right and left using the Next and Previous properties, and we will also cover navigating to a sheet by its TAB name (and discover why that's not always a great idea!)

Between The Sheets

In this lecture we will learn how to get around the issue of calling a sheet by its TAB name, if the user happens to change it in Excel! Enter, the VB Name.

This is my favourite method, and for any project that will be used by someone else, it is the ONLY method I use. I think you will too :)

Calling A Sheet By Its VB Name

At some point, maybe even in this course, you will sheets referenced by using Sheets or Worksheets. There is a difference, and in around 2 minutes, you will know exactly what that is!

Sheets Vs. Worksheets

The final level of the hierarch (until we get to controlling the other applications like Outlook and Windows), is the Workbook. By the end of this lecture, you know how to navigate to anywhere in the Excel application...

Getting Around The Workbooks

When you code, you will mostly be doing one of two things: reading data, or writing data.

In this lecture we will take a quick look at a few ways to write data using the value property. We will cover how to write to the sheet you can see, write to a cell on a different sheet, and even how to write to another workbook!

The Value Property - Writing Data

In this lecture we will cover how to read (and write again!) data from cells and ranges.

The Value Property - Reading And Writing Data

In this lecture we will take a quick look at the ModCopyAndPaste file you can download. On the surface of it, it looks to do what we did with .Value in the last lecture, but with one important difference!

Copy and Paste

We've covered a lot of ground on methods so far, now we'll take a look at some of the more commonly used properties.

We will go through two or three here, but I strongly encourage you to have a play around with these, as doing is the best way of learning!

Commonly Used Properties

Okay, time for a coding exercise!

All of the instructions are in the video, and in the file you can download from this lecture...Good Luck!


From time to time, you will need to find out what the cell address of a cell is. Luckily, VBA has a built in property just for this...Address!

So, finding the address of the cell you're in is a little useful, but finding the address of your last populated cell in a range, or the first empty cell after your data is enormously useful!

We will also cover here how to perform both of these feats, and why you would want to :) by combining some of the other methods and functions we've already discussed...Are you starting to feel like a programmer yet?

The Address Property

If you prefer to work with the cells object, rather than the range object, it would be very useful to find out which row or column number the currently selected cell is in. In this lecture, we'll cover the properties that allow you to do this.

The Row and Column Properties

Okay, so finding the row number is useful, but getting a column letter is much easier to understand, right? After all, we're familiar with calling cells A1, B2 and so on.

In this lecture you will find a really useful snippet on code to capture the letter (not the number) :) 

Capturing The Column Letter

Your coding arsenal is really starting to build up now.

In this lecture we will cover some of the really useful properties you can capture, like the user name of who is accessing your workbook, the names of the active workbook, and the name of the workbook where your code lives (they may be different). We'll also capture the path to the workbook(s), and finally, we'll capture the name of the sheet we're in.

More Useful Properties

This lecture covers a few more bits of info we can get from the file we're in.

It took me so long to work out how to do this, I just had to keep the code in my macro book so I could use it again and again!

We'll also learn about the count property, which we'll use to count how many sheets we have in the workbook, and even how many workbooks we have open.

Even More Useful Properties

In this lecture, we will look at how to open a different workbook simply in code!

Not only that, but we'll look at the different parameters we can use, so we can open as read only, and even how to open a password protected file!

If that's not enough, we'll also learn how to open the "file open" dialogue box, so we can click the file we want, and then open that!

Opening Another Workbook Programmatically

In this lecture, we will cover how to close a workbook programmatically, and the options available to us as to whether we want the changes to be saved or not. Nice and easy.

Closing Workbooks Programmatically

Time for another coding exercise. A little more complex, but well within your abilities!


We've covered a lot in the last 90 minutes...let's see how you're doing! 

Let's See How You're Doing...
8 questions
The Programmers Toolbox...The Techie Stuff, Made Easy (Honest!)
37 Lectures 02:40:55

Right, a Geeky section. In here you'll learn everything you'll ever need to know the key programming tools!

Preview 00:50

Okay, this is quite the techie section, but by the end of this course, you will know this stuff inside and out!

In this lecture, we'll take a look at the:

Local Variable

We will also take a look at another useful feature of the VBE: the Watches window.

There is an example file you can download, along with a pdf "cheat sheet"...I highly recommend you print this out and keep it handy.

Variables - Local Variables

In this lecture we're going top look at a slight twist on the locally declared variable, and see how we can adapt our code to enable it to pass its value from one sub to another...

Variables - Local Variables With A Twist

Okay, in this lecture we will learn about Module Level to declare them, where (notice the bold type) to declare them, and how they differ from local variables.

Variables - Module Level Variables

In this lecture, we'll have a look at the top level variable; the Project Level Variable.

These are useful if you want to pass the value in your variable to a Sub in a different module!

Variables - Project Level Variables

In this short lecture we will cover a really neat trick to force you to declare all of the variables in your code...and that's a good thing!

A Neat Trick To Force Variable Declaration

Right, we've learned an awful lot about variable in the last few lectures, so this one is a wrap up covering some of the more technical aspects...well worth 9 and a half minutes.

Variables - All The Techie Bits

One of the most fundamental tools in any coders toolbox is the Loop. In the next few lectures, you will learn about all of them!

An Introduction To Looping

Okay, loop #1:


The simplest of all the loops, but one that comes with a warning...if you use them, you must provide a way to exit closely :)

Looping With Do...Loop

Loop #2:


A very old loop, and one that is easy to use, and is automatically prevented from going on forever!

Looping With For...Next

Loop #2.5:

For...Next "Stepped"

A slight variation of the vanilla For...Next loop. One that increases it's iteration by a number that isn't 1!

Looping With A Stepped For...Next

Loop #3:


Our final loop. It's been around for ages, and I bet you will use it more than once!

Looping With While...Wend

Being able to test your data to see if certain criteria are met is an essential part of the programmers toolkit. In the next few lectures, we'll take a look at all the tests you'll ever need!

An Introduction To Logical Testing

Let's start with the basics. In this lecture you will learn the simplest of all tests...the single line If test.

Logical Testing - A Simple If Test

In this lecture we will take another look at the same code, the single line If test, but we will learn how to adapt it to use Cells().

Cells is by far the most efficient way to code, particularly with anything loop related, and can save thousands of unnecessary lines of code being executed...and that's a really good thing! 

Logical Testing - A Simple If Test Using Cells

On to the next...what if we wanted to do one thing if our criterion was met, but a different thing if it wasn't? That's when you need the If Then Else...

Logical Testing - If Then Else

In this lecture we will take another look at the same code, the If Then Else test, but we will learn how to adapt it to use Cells(). And as we know, that's a lot more efficient!

Logical Testing - If Then Else Using Cells

So far, so good, Alan...but what if we want to test more than one thing? Luckily, that's easy too!

Logical Testing - Testing Multiple Criteria

In this lecture, we'll take a look at testing if one value is true, but the other one is false...

Logical Testing - Testing If One Is True, And One Is False

Here you'll learn how to evaluate if either one or the other values is true. Nice and easy.

Logical Testing - Testing If Either Value Is True

The powerhouse of all logical tests: Select Case!

With this, you can not just do this or that, you can do this or that, or that, or that, or that, or that...

Logical Testing - Select Case

In this lecture, you'll learn how easy it is to do math in code...nice and simple!

Maths - Doing Simple Maths In Code

In this lecture, you'll learn about a few different ways to write a formula in to a cell in Excel using:



And discuss how each is structured.

Maths - Writing Formulas To Single Cells

Funnily enough, the simpler format of writing formulas as we do in Excel works perfectly fine providing we write them to a range...strange but true!

There's also a quick demo of how long it takes to write 100001 formulas to a range...hint: It's quite quick :)

Maths - Writing Formulas To Ranges Of Cells

In this lecture you will learn how to use Excel's built in functions within your VBA code.

Maths - Using Excel's Built-in Functions

Okay, nearly there. In this last lecture on mathematics, we look at setting up a virtual named range in VBA, and using it with one of the Excel's so easy!

Maths - Built-in Functions With Defined Ranges

In this lecture we're going to look at using the InputBox Function to get data from a user...

InputBox - Getting User Input Using The InputBox Function

In this lecture we will cover the various ways to change the casing of the users input using:

UCase - To convert everything to UPPERCASE

LCase - To convert everything to lowercase

and because VBA has no built in function for Proper case, we will call on one of Excel's built in functions:

Application.WorksheetFunction.Proper - To Make Everything Proper Case :)

Manipulating The User Input With Casing

In this lecture we will look at the InputBox Method...this differs slightly from the InputBox Function, as it allows users to click cells in Excel itself, and from there we can pull data from the cell they just selected...a very useful trick to have in your toolbox!

InputBox - Getting User Input Using The InputBox Method

In this lecture we will look at Message Boxes. They come in two flavours, one where we don't return a value to a variable...a simple message box (that's the subject of this lecture), and one where we do (which is the next lecture).

Message Boxes - Simple Message Boxes

In this lecture we will look at capturing to a variable which of the buttons was pressed in response to our message box...and then doing something depending on the response!

Message Boxes - Testing Which Button Was Pressed

In this lecture we will learn what an array is, and how it can help us significantly streamline our code!

Arrays - An Introduction

Arrays can either have one dimension, or multiple dimensions. A single dimension array would hold a list of data (like a column in Excel), with each of the cells in the list being a single element of that array.

Arrays can also be static (that is their size is hard coded), or dynamic (can change at runtime once we know how big they should be).

In this lecture, we'll take a look at a single dimensioned static array.

Arrays - A Simple One Dimensional Static Array

Okay, we know the limitations of the static array, now it's time to get dynamic!

Dynamic arrays have two big benefits: they can expand or contract as our code executes depending on the data set we're looking at, and we can also erase them when we're done to free up system memory. Win win!

Arrays - A Simple One Dimensional Dynamic Array

In this lecture we will take a look at a two dimensional array...all that means is that we will have two dimensions in our array, just like Excel has rows and columns...very easy, but very useful!

Arrays - A Simple Two Dimensional Static Array

Okay, now to the best news of all. You can read an entire set of data in to an array with ONE line of code! I really wish this was around when I began to code!!!

Arrays - The Most Efficient Way To Capture An Array

So, let's imagine we don't want to write the entire data set we just captured, but instead use it to tell the user something this lecture, we'll combine a few of the other elements we've covered in this section to do just that!

Arrays - Extracting Useful Data Based On User Input

Okay, you have learned a lot...let's see what you can recall so far...

Time For A Quick Quiz...
7 questions
Automating All Your Reports!
22 Lectures 01:34:41

This short lecture is an introduction to the section. We cover what the downloadable resources are, how to port the finished code in to a module, and finally a quick demo of what you'll be able to do in about 90 from now!

Preview 02:28

If you are a user of Office 2010, one of the subs in this section needs a slight modification to the version you will see here. It's a simple swap, and happened as Excel 2010 users do not have the "Recommended Charts" option. You will find 2 sets of code in the downloadable file, so whichever version you have, your code will still work. Unfortunately, my laptop with 2010 just doesn't have the juice to record video!

A Special Note For Office 2010 Users

In this lecture, we will record the bones of the code we will use into individual macros. All straight forward stuff...the clever stuff comes next...

Recording The Bones Of The Code

In this lecture we will begin the deconstruction process, dealing firstly with the "Add New Sheet" subroutine.

We'll also cover a great trick to give yourself a "safety net" while you're working on your masterpiece!

Streamlining The "Add New Sheet Code"

Okay, in this lecture we will take a look at the individual elements of the code we generated for the "Profit By Day" chart...nice and simple.

Deconstructing The "Profit By Day" Code

We pack quite a bit in to the next 7 minutes! We will begin the process of capturing all of our "data strings" dynamically at runtime...that is, our code will workout what it should be looking at as the code executes!

We will also do a quick Masterclass in string building that will enable you to make any of your code fully dynamic!

Pretty neat, huh?

Building Source Data Strings Dynamically At Runtime

In this lecture, we'll create a "Run Order"...that is a subroutine that will call each of our other subroutines in the correct order.

We will also add a sub that will capture all of our data ranges in one go, so we can use them throughout all the other subs in the module.

Creating The Run Order and Data Capture Subs

One thing causes more problems when coding pivot tables and charts than any other...getting around the naming issue! 3 minutes from now, you will know how to solve it!

Solving That Naming Problem

In this lecture we cove a REALLY neat trick. I have watched people add dozens of lines of code to scale, re-scale, and re-scale to get their charts to be just the right size...luckily, there is a really clever way to do this precisely in a single line of code (well, two actually).

POWER USER - Sizing Your Charts Precisely

In this lecture, we will explore why I recommend (with pivot charts at least) recording a bunch of individual for each element you want to address. Changing the title alone generates around 30 lines of code!!!

Using this method makes isolating the pertinent code an awful lot simpler!

Changing The Chart Title (And Why We Do It Separately)

In this lecture we'll take a look at the code we generated for the two pivot tables in our report...and just how alike they really are!

This is really good news for us, as it means we can polish the first one, and then steal that code for the second!

Deconstructing The Pivot Tables (It's Slightly Different)

This is three lectures in one! Here we will cover adding the (three) finishing touches to our first pivot table:

Changing the title

Changing the format to currency

Changing the sort order (this will come in useful a little later :))

Titles, Money And Sorting

In this lecture, we will look at saving ourselves some time, by taking the fully polished code from our other pivot table, an using again here...just a couple of modifications required.

Butchering One Table, To Create Another

Okay, in this lecture we will take a look at the commentary. You will remember that our code needs to be completely dynamic, so we will need to interrogate our data set (whichever one it happens to be) to extract at runtime what the variable parts will be. Quite a lot in here, so pay close attention!

Adding The Commentary - Building Strings Dynamically At Runtime

A short lecture this one. We'll just take a look at the finished code for building the commentary...we will also take a close look at any modifications we may need to make.

Adding The Comentary Using Data From The Sheet We're On

In this lecture we will cover which parts of the colossal amount of code our recorded Marco produced as the bits we are looking for...from there, it's just a short step to being able to manipulate string formatting like a professional!

POWER USER - How DO You Make Specific Words Bold

This lecture is a masterclass in INSTR (called "in string"). We will cover the two main flavours, and what they do differently, and more importantly, we will learn how to apply this to our current project.

POWER USER - INSTR...A Very Useful Function

Okay, time to use what we did in the last lecture. We will utilise the INSTR to complete all of the elements we want to appear in bold...we DO need to pay close attention, as we must be explicit...

INSTR And Paying Attention To Detail

In this lecture we will complete the prettying up of our title...nice an simple after the last few lectures!

Tidy Up The Title

In this lecture we will look at adding a final pie chart to our report using data we already have on our report sheet!

Easy As Pie (Chart)

Another 3 in 1 lecture! Here we will:

Change the title

Add data labels

Remove the field buttons.

Prettying Up Our Pie Chart

This is it...the final lecture in this section!

In here, we will port the code we have created to a new workbook, so we can run it over any of the 4 data sets we have. We will also add a really clever bit of code to check that we have actually selected the correct sheet (simple, but effective!).

Putting It All Together
The Data Is Out There...On The Internet, That Is
9 Lectures 31:21

This is just a quick demo of the project we'll be building together in this section. The specific application may not be useful to you, but the principle behind almost certainly will be! Enjoy, and just think of the possibilities...

Preview 01:38

In this lecture we will look at building the code to extract data from the internet. Though the recorded code is relatively simple, there are a couple of key things that you will need to know...which we will cover here :)

Pulling Data From The Internet - Capturing The Data For Rome

In this lecture, we will take the "Rome" code, and make a few minor changes to make it work for Cancun and London...this is way faster than recording the Macros again!

To make our table uniform, we will also add in a few extra lines to make everything just as it should be!

Getting To Cancun And London From Rome

In this lecture, we will write a really useful loop to make our (somewhat untidy) table, perfectly tabulated...and that's going to make it a whole lot easier to read data from!

Data Clean Up

In this short lecture, we will cover using the find and replace function to remove the "degrees centigrade" form our data, which will allow us to populate data in our chart.

A Simple Find And Replace

Another fairly straight forward lecture, where we will look at writing the formulas to pull data from our web import to our very pretty table in the main sheet.

Getting Our Formulas Right

In this lecture we will halve the amount of code taken to populate our formulas, so this section of our code will run twice as fast!

Streamlining The Formulas Code

I get asked this a lot! You know those really helpful messages you sometimes see in the bottom left hand corner of Excel? Well in this lecture you are going to learn how to take full control of them!

POWER USER - Displaying Messages In The Status Bar (Cool)

In this wrap up lecture, we will tie it all together by assigning our "Run Order" code to the chart. That way, when the chart is clicked, our code executes. Not only that, we'll add a feature to prevent the user seeing all the flipping between pages that happens when our code executes...and it will make it run even faster still!

Putting It All Together
Workbook Events: You Don't Have To Run Code To Have Code Run!
13 Lectures 43:46

In this short introductory lecture, we will take a sneak peak at what we're going to be covering in the next 40 minutes.

There's fun stuff, there's cool stuff and there's powerful stuff...enjoy!

Preview 02:06

In this lecture we will look at the Workbook Sheet Activate function. We will discuss the syntax, which looks complicated, but the VBE does all the work for us!

We will also find out where to find all of the various Workbook Events (easy peasy)!

WorkBook SheetActivate

In this lecture we'll take a look at the Workbook Event BeforePrint.

In this demo, we will get some feedback from the user, and then decide what to do based on their response.

Nice and easy!

WorkBook BeforePrint

Now we'll take a look at the Workbook Sheet Change. Very useful for monitoring any changes in your workbook.

WorkBook SheetChange

In this lecture, we will look at the Workbook Open Event. This is possibly the most widely used, and for good reason.

We're going to exploit it's full power, by automatically creating a back up of our file each time the workbook is opened...without doing a thing!

UPDATE: Something strange is going on in the world! In this lecture, I use a line of code:

MyTime = Left(Time(), 2) & "." & Mid(Time(), 4, 2) & " - "

For some reason, it now seems to fail in the mornings! So I have replaced the source file code with this:

MyTime = Format(Hour(Now()), "00") & "." & Format(Minute(Now()), "00") & " - "

...this should be good anytime of day :)

WorkBook Open - Creating An Auto-Back Up

Another use for the Workbook Open Event...displaying a Splashscreen.

I get asked this so often, I just had to include it in this course :)

For the splash screen we will use a UserForm, and have that trigger when the workbook opens!

WorkBook Open - Creating A Splash Screen

In this lecture we will expand on the functionality of the last demo. You will learn how to have the Workbook Open Event trigger the Splashscreen, and then have the Splashscreen trigger another piece of code stored in a regular module!!!

We will get the users name (automatically), and capture and store both pieces of information in a log (which we can hide from the user :))

WorkBook Open - Calling Other Code

In this lecture we'll have a look at the Workbook Before Close Event.

You can write any code you like in the event handlers, but I've used this particular piece of code a LOT! 

WorkBook BeforeClose

Just a bit of fun, this one! We're now moving on to WorkSHEET Events.

We'll take a look at the Activate Event, and have the code send the user back to the front sheet whenever they try to click the tab!

WorkSheet Activate - You Can't Pick This!

Another bit of fun, this one!

We'll take a look at the Activate Event again, but this time we're going to send the user to a randomly selected sheet...quite possibly NOT the one they clicked!

WorkSheet Activate - You Might Pick This!

The Worksheet Change Event is the worksheet equivalent of the WorkBOOK Change Event, but its scope is limited to the sheet in which you write it :)

WorkSheet Change

In this lecture, we honour those great folks over at Microsoft, by utilising a cool piece of code they wrote.

You will learn the iterative process I use when I come across a new vexing problem, and some code I can find on the internet kinda works, but doesn't do exactly what I want to do!

My guess: you'll be doing the same :)

WorkSheet Change - A More Useful Use

To finish this section, we have another fun demo.

This is another "Activate" Event, but we'll use input boxes, message boxes and probably more importantly, learn how to disable events, why you may need to, and how to turn them on again!

WorkSheet Activate - Top Secret Classified Information!
User Defined Functions...What To Do If The Function You Need Isn't In Excel!
5 Lectures 21:44

As you already know, Excel has literally hundreds of built in functions, and you can usually do everything you want to do by using those. But, occasionally you may have a need for a function that just doesn't exist...enter User Defined Functions (UDF's).

In this lecture we will take a look at UDF's, what they are and how you create them. Nice and easy!

User Defined Functions...What They Are, And How You Make Them

So far we've looked at creating simple UDF's to perform a specific calculation, but we can also use them to return (and simple)!

Using A UDF To Return Information

In this lecture we're going to create something with a few more moving parts!

We will build a UDF to return a string with a textual countdown to any date in the future...what's more, we'll even cover how we make the UDF volatile (sounds ominous!)

Creating A Countdown Timer With A UDF

In this lecture we will take a look at how to build a UDF where we have multiple arguments. Sounds tricky, but it's soo simple :)

A Custom UDF For Calculating Volume Discount

I almost forgot this! In this lecture, we will look at the slight modification you need to make if you're calling your UDF from another workbook (not the one it lives in).

Calling A UDF From A Different Workbook
Bonus Section: Controlling Windows - Folder Creation Gizmo
4 Lectures 14:22

In this lecture you will see a quick demo of what we're going to be creating in this section...this can save you HOURS!

Preview 02:24

In this lecture we will look at the single line of code that works the magic! A simple as that!

Creating A New Folder With A Single Line Of Code

In this lecture we will go through the code line by line. By now you will already know most of the lines of code, we're just putting them in a specific order.

Really simple, but very fast and very useful!

A Single Level Folder Structure

Now the last routine was useful, but being able to create folders in folders would even more useful.

In this lecture, we will butcher the code we just used, and nest one loop inside another to add the subfolders.

Folders Within Folders
Bonus Section: eMail Automation...Why WRITE emails!
4 Lectures 23:27

In this introduction to the section, you will see a live demo of the project and we'll also go over the two different approaches to controlling Outlook with VBA via Excel. They are:

  • Early binding
  • Late binding

We will use late binding, and it's far easier for you to too! I tell you why inside :) 

Preview 03:10

In this lecture I will take you through my email sub.

We will go through it line by line, and at the end, you will know all the elements.

Of course, you don't HAVE to know how it works, you could just use it!

Understanding The eMail Routine

In this lecture we will deconstruct the "gathering the data" routine.

You are already familiar with almost all of the code we will be using, but we will be putting them together to produce something really useful!

Deconstructing How We Capture All The Data

Okay, in this lecture we will take a quick run through the code so far live and watch exactly what it's doing. Then we'll do a quick step through the (slightly modified) email sub.

Then we'll hit the button, and see the routine in action!

The eMail Loop
6 More Sections
About the Instructor
Alan Jarvis
4.6 Average rating
1,389 Reviews
21,087 Students
7 Courses
Everything Is Easy, Once You Know How - 20,000+ Students!

Over 19,500 Happy Students, in 167 Countries Are Enrolled In My Courses!

Hi, my name is Alan, and I have almost thirty years of experience in teaching various subjects and have held senior management positions at several blue chip and Times Top 100 companies. I now specialise in consultancy and interim work.

My love for Excel began soon after the program was released. Back then, we really only used it for typing tables in our production planning department. If you can believe it, I was taught to then calculate with a calculator and TYPE the answers into'd be surprised how many people I meet who still do this!

There were two turning points for me in my journey. The first was a chance conversation with one of our accountants when I was complaining how slow it was to keep having to flip between worksheets to see different bits of data. He introduced me to VLookups and a new world of possibilities opened up. I found that I need never have huge paper accountancy pads (remember those?) and a calculator to produce a production plan again! Suddenly, I could produce my plan in 15 minutes, rather than the day and a half it used to take. I began delving into other features of Excel, and finding ways to incorporate them into the planning "system" I had created, and remember this was back in the day when the internet hadn't been invented! Through trial and error, I refined my Workbook until I could chomp through days of work in seconds, and what's more, using these powerful features of Excel, I could optimise our production runs to get more output from the same working week, something that had never been done before. Promotion soon followed, and I became the "go to guy" for anything Excel related. This was the 90's.

Then, in manufacturing, came the MRP revolution. We had an army of programmers developing a system for us. This was my second turning point. I was awestruck with coders. The skill they had, and how clever they were, and that they were smart enough to go to university in the first place. I wasn't like that. I had no formal education beyond primary and secondary school, but a really cool programmer named Mark let me into a secret...

He said "If you can describe, step by step, how to cross the road without getting killed, you can be a programmer". I recorded my first macro, and took a peek under the hood. I deconstructed the VBA code, and played with it until I understood what everything did. Then I changed it to make it do what I needed it to do.

Since then, I have developed accountancy systems, simple planning systems, full blown MRP systems and scores of stand alone "helper" systems to simplify and speed up work in almost every department in every company I have worked in. Some of the solutions I created 20 years ago are still being used today! If you doubt the power of Excel, it may surprise you to learn that you can run the procurement of a £1.3b (yes, billion) company with an Excel file with a bit of coding behind it. This is a skill that people will pay highly for!

Not everyone wants to go that far, so over the years I have taught people to create efficient solutions for themselves, either with or without coding. Those who took the time to learn have since had multiple promotions, or have started their own businesses! At the very least, they are getting more work done in less time with less headaches :)

If you are a complete beginner, just want to polish up your skills, or want to create sophisticated super efficient solutions for your business or personal life, my series of courses will help you achieve that. All you need is a little time, and a willingness to learn...I'm looking forward to working with you.