The Ultimate Excel Programmer Course

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "02 Range Properties\Exercises" folder. Please open "Exercise 02a - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "02 Range Properties\Exercises" folder. Please open "Exercise 02b - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "03 The Cells Object\Exercises" folder. Please open "Exercise 03 - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Concatenation is just a fancy word - it's easy.

In this short lecture, we learn about Date and Time built in variables that assist us in making calculations based on the time or date the user interacts with our program! Fun!!!

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "04 Variables\Exercises" folder. Please open "Exercise 04 - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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

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

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "06 Super Important Tools and Excel Logic\Exercises" folder. Please open "Exercise 06a - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "06 Super Important Tools and Excel Logic\Exercises" folder. Please open "Exercise 06b - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "07 Loops and Report Writing Basics\Exercises" folder. Please open "Exercise 07a - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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!

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "07 Loops and Report Writing Basics\Exercises" folder. Please open "Exercise 07b - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Fun BONUS Lecture! - Learn to scrape data from files in a folder and plug them into a central 'Master' workbook.

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.

Right-click triggers!

Triggered whenever calculations occur.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "08 Worksheet Events\Exercises" folder. Please open "Exercise 08a - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "08 Worksheet Events\Exercises" folder. Please open "Exercise 08b - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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"
End Sub

Occurs when a new sheet is created in the workbook.

Please download this exercise from the "Course Files Download" zip file, or Download the single Question file here.

This will be located in the "09 Workbook Events\Exercises" folder. Please open "Exercise 09 - Question.xlsm" and click on the "Objective" tab to view your tasks. The following video will show the solution step-by-step. If you need help, you may refer to the "Answer" version of this workbook with full source code to look at.

Happy Coding!

Dan

Solution video for this Exercise. Solution workbook is also attached to this Lecture as well as found in the "Course Files Download" folder!

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.

Checkbox learning!

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

Scrollbar examples.

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