Microsoft Excel Essentials: Level 3 - VBA Programming+Macros
4.6 (151 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,508 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 (151 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,508 students enrolled
Created by Alan Jarvis
Last updated 8/2017
English
Current price: $10 Original price: $150 Discount: 93% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 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
Requirements
  • 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
Description

The No.1 VBA Course on Udemy!

If you really want to harness the full power of Excel, then learning how to program in VBA is ESSENTIAL!

_____________________________________________________________________________

  • Over 500 Students enrolled in the first 2 weeks...find out why!
  • Over 14,500 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 hours of HD video lectures
  • Tons of downloadable resources
  • Quizes
  • 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
173 Lectures
13:28:04
+
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"...you can download the .pdf from this lecture. Print it out, keep it near your computer and learn them well...it will save you countless hours of frustration.

The 15 Golden Rules Of Coding
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.

Introducing The Visual Basic Editor, & Recording Our First Macro
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)

Saving Macro-Enabled Workbooks, And Security Settings
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...

Moving Code Around
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!

Stepping Out. Well, In Actually! - Debugging Made Easy :)
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
12:34

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
07:53

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
06:17

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
03:18

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
02:03

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
10:52

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!

Introduction To The Coding Section
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
06:15

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
02:47

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
02:10

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
06:27

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
03:36

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
03:03

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
02:42

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
02:15

Ooh, you will be using this one a LOT!

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

The Offset Property
02:18

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
02:49

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
02:33

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
02:05

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

ACTIVATE vs. SELECT
01:43

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
02:40

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
02:54

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
02:02

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
03:10

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
05:26

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

The Value Property - Reading And Writing Data
02:35

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
02:58

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
02:52

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!

CODING EXERCISE: The Rainbow
02:20

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
04:04

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
01:34

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
02:01

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
03:05

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
02:55

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
06:20

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
03:41

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

CODING EXERCISE: Open/Write/Close
03:41

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
06:46

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
03:40

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

Variables - Module Level Variables
03:43

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
03:02

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
01:09

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
09:30

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
01:07

Okay, loop #1:

Do...Loop

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

Looping With Do...Loop
05:01

Loop #2:

For...Next

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

Looping With For...Next
02:33

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
02:02

Loop #3:

While...Wend

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

Looping With While...Wend
07:10

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
01:41

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
06:43

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
05:12

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
03:17

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
03:41

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
03:33

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
02:19

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
03:21

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
06:16

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

Maths - Doing Simple Maths In Code
02:42

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

.Formula

.FormulaR1C1

And discuss how each is structured.

Maths - Writing Formulas To Single Cells
06:56

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
05:19

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
03:55

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 functions...it's so easy!

Maths - Built-in Functions With Defined Ranges
03:25

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
05:29

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
04:20

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
05:54

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
04:06

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
04:47

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

Arrays - An Introduction
02:37

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
05:56

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
04:44

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
05:33

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
04:55

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 useful...in 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
07:41

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
01:20

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
04:25

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"
06:12

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
02:09

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
07:53

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
03:47

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
03:04

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
03:17

In this lecture, we will explore why I recommend (with pivot charts at least) recording a bunch of individual macros...one 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)
02:37

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)
07:36

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
04:46

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
04:33

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
07:31

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
03:56

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
04:36

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
05:03

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
02:54

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

Tidy Up The Title
03:01

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)
05:53

Another 3 in 1 lecture! Here we will:

Change the title

Add data labels

Remove the field buttons.

Prettying Up Our Pie Chart
02:50

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
04:50
+
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
04:18

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
05:59

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
04:11

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
02:07

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
03:51

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
04:16

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)
03:06

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
01:55
+
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
04:09

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
02:47

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

WorkBook SheetChange
01:14

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
05:17

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
03:55

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
05:41

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
03:09

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!
01:44

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!
02:44

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
02:32

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
04:57

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!
03:31
+
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
05:39

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

Using A UDF To Return Information
01:41

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
08:30

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
04:07

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
01:47
+
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
00:55

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
05:27

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
05:36
+
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
04:57

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
10:14

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
05:06
6 More Sections
About the Instructor
Alan Jarvis
4.7 Average rating
1,075 Reviews
14,962 Students
6 Courses
Everything Is Easy, Once You Know How

Over 14,500 Happy Students, in 162 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 Excel...you'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.

Alan