Teach Excel to Do Your Work FOR YOU. . .
Microsoft Office is everywhere, installed on over 750 million computers, but most users only know how to set up a basic table or maybe even do a few formulas here and there.
In my course, I teach you how to take Excel by the horns and make it do whatever you want, whenever you want. It can go through loads of information and create a printable report for you. You can make custom forms so that you can access, analyze, edit, or add new information quickly to your data tables/ worksheets.
Excel programming utilizes a simple but effective tool called "VBA" - the hidden programming language that runs quietly in the background while you work. It’s very easy and straight-forward to use.
I'll show you the easiest tricks to learn this basic language in a fun, progressive method. Learn at your own pace. With each of my short, info-packed lectures, you'll learn another essential skill that you can immediately use. You'll find yourself handling these Automation tools instantly and in any spreadsheet you already use every day. If there's one thing I'm good at - and my students are good at - it's AUTOMATION.
My motto is, "If I'm not making everybody's job easier, quicker and more enjoyable, I don't deserve to have this job" - and that's what I live by.
Take this course and access your true potential.
Oh, and I want to be the first to hear about your New Raise you get once you're making Excel Programs and running everything on autopilot for your co-workers!
To get the very important Developer Tab, (in excel 2010) go to File>Options>Customize Ribbon and check the box that says Developer on the right.
In this lesson, will learn about the range object and how to use it as if you're typing into cells yourself.
In this lesson, we'll make our first macro from scratch. It's super easy!
Now let's add a little spice to our macro!
How to go line-by-line when you want to analyze your code more thoroughly.
In this lesson was show you how to save your workbook as a macro enabled workbook.
There's more than one way to skin that cat! Macros are designed to be triggered a number of ways. In this video we'll discuss this.
You don't only have to use the cells names, you can also use named ranges. Check it out!
Value returns the actual value of a cell, not it's formatting, eg: 12.5 instead of $12.50
Text returns the full formatting of something so if a cell had $12.50, it would return the full $12.50, not 12.5 value.
Gleaming the row or column of a range you're working with can be very valuable. Learn how to wield this tool now!
If you click on a cell or highlight several cells, you've just selected them. Turns out, Excel can automatically select things - like a ghost clicking around for you!!
This isn't the same as the worksheet function COUNT, this simply counts how many cells you have in the range, not how many aren't blank.
Address brings the exact location of the cell or range you're referring to, such as $A$2 or A1:B3.
You can automatically put a worksheet formula into a range within your macro
UPDATE: Please note that when I say "Absolute value" I mean the "Actual value", I don't mean that this value cannot be negative numbers. Thanks and program on!!
Change the formatting of your ranges on the fly with this handy dandy tool.
Everybody needs to know how to automatically make ranges Bold, Italic or Underline!!
A Quick List of questions to make sure you've got it so far - on the Range Properties.
Pinpointing which cells you want using rows and columns, almost like latitude and longitude for coordinates. Very useful when we use loops in the lessons to come!
You don't have to use column 2, there's a way to use B instead when using the cells object.
More on the cells object. . .
Quick tip on making changes to all cells in a sheet - easy.
Sandwich these two concepts, the range object used with the cells object. Cool!
Using variables is important and fun! They make calculations way easy!
Sometimes you have to let Excel know what to expect when using variables.
Here, let me give you some cool examples.
You can open one macro from within another!
Share your variables with other modules or userforms or keep them private. . up to you.
These special variables shouldn't change, so lets tell excel what they are, and we can use them anytime, anywhere!!
Sometimes you want to use your current variables in another procedure. Sometimes you just need to throw the value in there but don't want it affected in the original procedure, other times you want to manipulate the original so it's different when you get back to the originating procedure. Here's how to do all of that!
All the cool things you can do from the file menu, including importing and exporting modules or userforms. neat
Now the Edit menu.
View Menu has some neat things and perspectives for your macro and/or variables. . .
really useful things. Watch this now!
More on the watch window.
All about the insert and format menus
Learn how to debug like a BOSS! Mainly ways to save you time and headaches.
Last but not least on the Menus. . .
You'll need to memorize this one-liner, but it's easy once you know what it means.
Very similar to last row.
Really really easy to grab the Next row in your set. Great for auto-data entry.
How to record a macro and use it to LEARN!!
How to Sort dynamically, even when more rows are added. No more hard-coding!
More on the recording tool.
Want to save a lot of keystrokes? Listen to this lesson.
Equal to, less than, IS NOT Equal to, etc. . . this is good to know!
Here's where the fun begins. . . If Then statements are super fun and help you arrive at the logical conclusions you need to pretty easily.
Another logical tool.
Using the word NOT to negate a statement.
Adding the "Else" aka otherwise part of the IF THEN statement.
What in the world is ELSE IF??
Don't let text mess you up when comparing to numbers.
Save space and use a one-liner of code for your basic If Then statements! Easy!
Jump to different areas of code like a teleportation device!
Try this on for size to save keystrokes when using If Then statements. It's nice once you get used to it.
"Do you like Excel VBA? Click Yes or No"
In this lecture, you'll learn how to let the user select yes or no and navigate the macro depending on their choices. Very powerful and yet simple to utilize.
Offset allows you to take your current position and roll with it - no matter where the macro takes you, you can always affect a cell 3 rows down and 5 columns right, for example.
Learn how to create your own functions that work within cells with the equals sign just like =SUM, or use them within macros to simplify code!
This is stuff you really need to understand so you can program effectively. It's fun to know, don't worry you can retake this quiz for life and the course is yours for life!
Loops help us analyze data, as well as write data. Every VLOOKUP you've every used has a built in loop to go from one row to the next and find you the answer. We're going to make our own loops and tell it when to start and stop. It's super fun and easy once you learn the basics. Check it out!
Now let's make it interesting. . . add some spice to our first loop.
We'll start with a basic loop to analyze all the rows in our data set.
An InputBox is a tool to get insight or data entry from a user.
An InputBox helps the user feel involved and can make this report dynamic based on what they want to see.
Add a button . . . it's what all the cool kids are doing. . .
Let's make a button that looks much neater using Shapes!
Everyone wants reports that can go to the printer and look good on paper. Learn the basics to this valuable skill.
Get the data you need on the correct sheet in the correct row and column!
You need to clear out the last report you wrote onto the report sheet.
This will show you how to make it unhidden, just in case, as well as bringing the report sheet we want to see to the forefront without the user needing to hunt for it.
Add an Auto-Print Preview. It's fun and useful!
Instead of Print Preview, why not just have it go straight to the printer. Do not pass go, do not collect $200!!!
You can predetermine how many copies to print or prompt the user perhaps with another inputbox?! This normally is sent to your default printer.
If you hit cancel or "X" out of your InputBox, here's how to handle errors that may pop up. It's so simple.
Rather than always starting at the beginning, you may sometimes want to start at a larger number and head backwards, counting downwards or descending. It's super easy!
This will loop through each item in a group, like objects or cells in a named range or shapes/buttons on a page or userform!
Here's another example of how to use a FOR EACH loop. More to come!
DO Loops are a bit different as you don't define the start and end points so clearly. Very interesting though!
Do Until loops and loops until certain conditions are met.
Loop until is very similar to Do until, except it ALWAYS does the loop at least once, and then it decides whether to continue or not.
Do While loops are kind of the opposite as UNTIL loops, because they only run during ideal conditions.
Very similar again to the do while, except, once again - it goes through the loop at least once and only continues if conditions are ideal.
Here's how to exit a DO loop anywhere you want, even in the middle of a loop and move on past the DO loop portion of your macro. You can set up these multiple times in a loop if you need.
Trigger a macro on a worksheet through various means. Change event is when anything changes on a sheet, your macro is triggered. Cool!
When the worksheet is opened, this macro can be triggered.
This is triggered when de-selecting a sheet.
Only used with sheet deletion, but interesting. .
Trigger a macro by double-clicking either on a specific cell or cells OR anywhere on the sheet.
Triggered whenever calculations occur.
A very useful tool if you don't want to find yourself battling off endless loops when cells keep re-triggering themselves. Sometimes, you need to turn Event listening off for a sec.
This is pretty neat. The worksheet listens until something changes on that particular sheet, then you can customize where it's listening and what it DOES!!
Another practical example. . .
Using this trick, you can customize very specific areas you want to trigger the macro and others for it to ignore.
a Hyperlink Trigger.
A few lesser known, lesser used Events and my final thoughts on the matter.
This is a practice exercise sheet that allows you to use what you've been learning. Many of the exercises deal with worksheet events. If you have trouble with the question on Userforms (which you may not have reviewed yet), please skip ahead to Userforms section videos or ask us in the forum.
There are 2 workbooks that accompany this exercise video, a Practice Sheet that you need to code yourself and a fully coded version for reference. Yes, that means you can peek at the answers from time to time if you're struggling! Enjoy and please don't hesitate to ask questions in the forum!
This event occurs when the workbook is opened.
When any chart, worksheet or the workbook itself is activated/selected.
Right before the actual Save occurs, you get a chance to run this macro automatically.
After Save event occurs, you get to recap with this macro. Even can tell you is save was successful or not!
Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.
Occurs before the workbook (or anything in it) is printed. You can cancel the printing automatically if things aren't as the programmer wants. . .
Occurs when the chart, worksheet, or workbook is deactivated.
Occurs when a new chart is created in the workbook.
UPDATE: In Excel 2010 or earlier, you need to Use the Ch.HasTitle = True in order to create a Title that can then be edited. In 2013 the title came with it automatically. Sorry bout that. Here's the sample code:
Private Sub Workbook_NewChart(ByVal Ch As Chart)
Ch.HasTitle = True
Ch.ChartTitle.Text = "My Custom Chart"
Occurs when a new sheet is created in the workbook.
Occurs when any sheet is activated.
These are all similar to their WorkSheet counterparts, except with a Workbook Event, you don't have to put them on each worksheet, you can set it globally, no matter what sheet to trigger.
Occurs when any sheet is deactivated.
Occurs when you click any hyperlink in Microsoft Excel.
Occurs when the selection changes on any worksheet (doesn't occur if the selection is on a chart sheet).
Occurs when any workbook window is activated.
Occurs when any workbook window is deactivated.
Occurs when any workbook window is resized.
A brief talk on Activex controls and why I like them better than Forms controls.
Finally - how to use a simple Button on a worksheet!
KeyDown is triggered when someone has pressed a keyboard key. Find out how to harness this toll in this video.
When using Keydown event, you can check to see whether Ctrl, Alt, Shift or any combination of them was pressed in addition to using the Keycode checker. This is awesome!
KeyPress is similar to Keydown. check it out.
When you hover the mouse over something. . . fun control.
This is a useful trick - to use the status bar for info to the user. Try this out! so easy.
Learn all the fun subtleties with MouseUp
Learn about these events, triggered based on when they are in focus, such as tab, enter or clicked.
More on the Checkbox
LinkedCell joins a cell on a worksheet to the value of a control.
Option button, either this or that but not both!
Tips on alignment tools and other such fun.
More on option buttons with this stimulating quiz!
Make sure people make a choice - or the macro will let them know they need to still do some things.
Let's just make the button invisible until they fill out everything they need to. One fun method for compliance!
Use cell A1 with a spinbutton
Spinbuttons with textboxes and what to watch out for when using numerals as opposed to text.
A little more fun before we leave the SpinButton. . .
Using dates with a textbox is tricky. Watch this for some tips that are sure to save you time!
How to use the control called "Label". fun!
One popular way to fill a combobox, but with this method you can't be choosey. You need to know this, but later we'll address a more customizable method.
YOU NEED TO LEARN THIS! Now!! :)
Comboboxes don't have to just be one single column of information!
Get what you actually need in the list, not everything. This acts as a custom filter you can manipulate.
Learn all the good stuff with a combobox
Did you know you can have multiple columns of data in a dropdown/combobox? Fun!!!
Learn what a ListBox is and how to kick butt with one.
Here's how to use a Listbox on a worksheet
How to handle a multiselect listbox, where users can select more than one item!
List arrays in excel don't start with row 1 or column 1, they start with row 0, then 1,2 and so on, same for columns. Learn all the subtleties in this one!
Now we get really choosey with dynamic filtering and manipulation using .ADDITEM. Awesome!!
Did you know you can have data that's invisible, but you can still use it in your listbox? This is an awesome trick
Add a picture and click event to lead to a website.
Toggle buttons and the joy therein
There are other controls you can download and mess with. May be glitchy, may be awesome. Use at own risk, read up by googling them
Hi! My name is Daniel and i'm 29. I've lived most my life in Scott City, Missouri, United States. I've been using Excel since my early teens, and very early on realized that I absolutely love it. After working on various projects in my spare time for years, improving the lives of those that used my programs, I knew this was more than just a hobby for me; it's my passion. A few years back, I got my first opportunity to program in Excel, but when I landed the job I was asked to re-write my predecessor's coded programs with fancy databases and automated Crystal reports! I found my Excel skills somewhat lacking. I HAD TO LEARN VBA! I studied and studied what I could online and in books, but I wasn't able to learn much that way. I had to dive into my first project for months and months - basically just teaching myself and "figuring it out". I realized that if only someone was out there teaching Excel VBA like Mike Gervin (ExcelIsFun) taught Excel, making everything simple and visual - then Anyone could learn VBA! I started a YouTube channel, mostly for myself as a reference tool, but people started to bite. People flooded my inbox with questions and comments that I was literally changing their lives for the better. My passion is and always has been teaching, and I've turned thousands of people into Excel programmers. Now, with Udemy, I'm going to teach the good stuff - the stuff I kept secret; the Advanced and "not-so-Advanced-but-Awesome" Excel VBA. The things that make people hire you - that make you irresistable and irreplacable. Building REAL Programs, Advanced Reporting and Data Analysis. Dashboards and live data. Automation. My main focus is to create an online community of professional Excel VBA developers and beginners who create Real Programs, solve Real Problems and change Real Lives.