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!
My guess is you're reading this because:
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:
"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:
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!
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!!!
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.
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.
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)
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...
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!
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.
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!!
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!
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...
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.
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 :))
Okay, we've covered quite a lot of ground so far, time for a pop quiz...
This is a brief intro to the most IMPORTANT section of the entire course!
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!
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...
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!
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 :)
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.
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!)
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 :))
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!
Ooh, you will be using this one a LOT!
In this lecture we cover one of my favourite property...the Offset.
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!
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!
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!
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!)
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 :)
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!
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...
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!
In this lecture we will cover how to read (and write again!) data from cells and ranges.
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!
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!
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?
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.
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) :)
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.
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.
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!
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.
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!
Right, a Geeky section. In here you'll learn everything you'll ever need to know the key programming tools!
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:
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.
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...
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.
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!
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!
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.
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!
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 them...watch closely :)
A very old loop, and one that is easy to use, and is automatically prevented from going on forever!
A slight variation of the vanilla For...Next loop. One that increases it's iteration by a number that isn't 1!
Our final loop. It's been around for ages, and I bet you will use it more than once!
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!
Let's start with the basics. In this lecture you will learn the simplest of all tests...the single line 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!
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...
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!
So far, so good, Alan...but what if we want to test more than one thing? Luckily, that's easy too!
In this lecture, we'll take a look at testing if one value is true, but the other one is false...
Here you'll learn how to evaluate if either one or the other values is true. Nice and easy.
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...
In this lecture, you'll learn how easy it is to do math in code...nice and simple!
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.
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 :)
In this lecture you will learn how to use Excel's built in functions within your VBA code.
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!
In this lecture we're going to look at using the InputBox Function to get data from a user...
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 :)
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!
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).
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!
In this lecture we will learn what an array is, and how it can help us significantly streamline our code!
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.
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!
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!
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!!!
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!
Okay, you have learned a lot...let's see what you can recall so far...
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!
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!
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...
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!
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.
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?
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.
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!
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).
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!
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!
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 :))
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.
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!
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.
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!
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.
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...
In this lecture we will complete the prettying up of our title...nice an simple after the last few lectures!
In this lecture we will look at adding a final pie chart to our report using data we already have on our report sheet!
Another 3 in 1 lecture! Here we will:
Change the title
Add data labels
Remove the field buttons.
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!).
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...
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 :)
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!
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!
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.
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.
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!
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!
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!
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!
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)!
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!
Now we'll take a look at the Workbook Sheet Change. Very useful for monitoring any changes in your workbook.
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 :)
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!
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 :))
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!
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!
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!
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 :)
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 :)
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!
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!
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)!
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!)
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 :)
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).
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!
In this lecture we will look at the single line of code that works the magic! A simple as that!
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!
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.
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:
We will use late binding, and it's far easier for you to too! I tell you why inside :)
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!
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!
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!
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 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.