The Ultimate Excel Programmer Course
4.3 (2,921 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.
49,525 students enrolled
Wishlisted Wishlist

Please confirm that you want to add The Ultimate Excel Programmer Course to your Wishlist.

Add to Wishlist

The Ultimate Excel Programmer Course

Learn Excel VBA from scratch with Dan Strong! I've trained over 40K students on YouTube, check it out!
Bestselling
4.3 (2,921 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.
49,525 students enrolled
Created by Daniel Strong
Last updated 6/2016
English
English
Price: $200
30-Day Money-Back Guarantee
Includes:
  • 10 hours on-demand video
  • 3 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
Automate and Customize data entry forms
Choose the right Loop for each task
Master the CELLS and RANGE objects in multiple scenarios
Create multiple Variable styles to match your need
Customize your VBA Editor and Understand all the Toolbars and options
Debug and Troubleshoot code like a boss!
Record, Modify or Write Macros from scratch
Make Custom Formulas/Functions on the fly
Breeze through IF THEN statements and conquer all the Logical Operators
Batch out inter-active MessageBoxes, InputBoxes and give users CHOICES!
Generate Basic Reports that can be printed
Add filters to report menus to narrow the records
Take control of forms, Buttons, Drop-down menus, Checkboxes and option buttons AND so much more. . .
Trigger code from a number of different methods - from Clicking on a cell - to De-Selecting a worksheet.
Set up Special Commands when a workbook: Opens or closes, is selected, any cell or certain cells are selected, right before printing, etc. . .
Manipulate Userforms for data entry, report generation, editing tables/databases - ALL within your control. Restrict the flow of data OR make the Userform(s) responsive, calculating, INTUITIVE.
Streamline your work and the work of others.
Put Excel ON AUTOPILOT. . .
View Curriculum
Requirements
  • You should already have Microsoft Excel (version 2003, 2007, 2010, 2013 or 2016). Obviously the newer the better, but VBA works the same in all versions. It's just that newer versions have more options, thus more VBA Commands to control those options.
  • Very very basic Excel skills needed for this course (like opening the program, how to click on cells and type things.) You really don't need any Excel experience, because I'll take you from Start to Finish in this course. I was just trying to be funny.
Description

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!

-Dan

Who is the target audience?
  • This Excel course is for anyone who wants to learn Automation secrets in Excel VBA. It’s for complete newbies and/or students looking for a refresher or Reference tool, to pick and choose relevant lessons for their projects. No prior programming knowledge is needed.
  • This course is probably not for you if you’re looking to learn super advanced report authoring, which will be covered in a future course specifically on this topic. We will, however, make sure you know how to make good solid reports from your data and many tricks to make them look good.
  • I don't think Office 365 has programming abilities with VBA as of yet, so be wary of this if you only use Office 365. They may release that as a feature in the future, but we're not there yet.
  • If you're using Excel for Mac, you will find that this course won't cover all the various differences in Excel Mac, as this course was filmed using Excel for PC. Some Mac users have taken this course, but it's best with Excel for PC.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 173 Lectures Collapse All 173 Lectures 09:57:36
+
The Range Object
11 Lectures 26:38

An introduction to the course

Preview 11:37

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.

Preview 01:30

In this lesson, will learn about the range object and how to use it as if you're typing into cells yourself.

Preview 01:20

In this lesson, we'll make our first macro from scratch. It's super easy!

Creating Your First Macro From Scratch
02:29

Now let's add a little spice to our macro!

Adding a Secondary Command. . .
00:52

How to go line-by-line when you want to analyze your code more thoroughly.

Debugging in Excel VBA
01:23

In this lesson was show you how to save your workbook as a macro enabled workbook.

Saving a Macro-Enabled Workbook (.xlsm)
01:11

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.

Several Ways to trigger your Macro
01:24

Affecting Multiple Cells with One Range Command - 2 Methods
01:35

Adding Strings of Text to a Cell
01:40

You don't only have to use the cells names, you can also use named ranges. Check it out!

Using a Named Range with the Range Object
01:37

Range Object Quiz
4 questions
+
Range Properties
9 Lectures 17:29

Value returns the actual value of a cell, not it's formatting, eg: 12.5 instead of $12.50

.VALUE Property
01:54

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.

.TEXT Property
01:00

Gleaming the row or column of a range you're working with can be very valuable. Learn how to wield this tool now!

.ROW and .COLUMN Properties
01:07

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

.SELECT Property
00:55

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.

.COUNT Property
01:47

Address brings the exact location of the cell or range you're referring to, such as $A$2 or A1:B3.

.ADDRESS Property
01:48

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

.FORMULA Property
03:47

Change the formatting of your ranges on the fly with this handy dandy tool.

.NUMBERFORMAT Property
02:27

Everybody needs to know how to automatically make ranges Bold, Italic or Underline!!

FONT.BOLD, UNDERLINE or ITALIC Properties
02:44

A Quick List of questions to make sure you've got it so far - on the Range Properties.

Properties Quiz
4 questions
+
The Cells Object
5 Lectures 10:35

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!

The Cells Object
02:24

You don't have to use column 2, there's a way to use B instead when using the cells object.

Using Column Letters instead of Numbers
00:55

More on the cells object. . .

Using Cells Positionally within a Range
02:10

Quick tip on making changes to all cells in a sheet - easy.

Affecting ALL Cells in a Worksheet
02:05

Sandwich these two concepts, the range object used with the cells object. Cool!

Using Range Object with Cells Object
03:01

Cells Quiz
3 questions
+
Variables
7 Lectures 23:36

Using variables is important and fun! They make calculations way easy!

Intro to Variables
02:24

Sometimes you have to let Excel know what to expect when using variables.

Declaring Variables - Variable Types
02:37

Here, let me give you some cool examples.

Declaring Variables - Types Demo
04:25

You can open one macro from within another!

Calling a Procedure from Another
01:57

Share your variables with other modules or userforms or keep them private. . up to you.

Public and Private Variables
03:06

These special variables shouldn't change, so lets tell excel what they are, and we can use them anytime, anywhere!!

Using Constants
02:02

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!

Preview 07:05

Variables Quiz
4 questions
+
Toolbars and Menus
8 Lectures 28:25

All the cool things you can do from the file menu, including importing and exporting modules or userforms. neat

File Menu walkthrough
01:52

Now the Edit menu.

Edit Menu walkthrough
05:08

View Menu has some neat things and perspectives for your macro and/or variables. . .

View Menu walkthrough
02:12

really useful things. Watch this now!

View Menu2 - Watch Window, Locals Window and Immediate Window
07:43

More on the watch window.

Watch Window Wonders!
03:25

All about the insert and format menus

Insert and Format Menus
00:40

Learn how to debug like a BOSS! Mainly ways to save you time and headaches.

Debug Menu (Important!)
05:12

Last but not least on the Menus. . .

Run and Tools Menus
02:13

Toolbars Quiz
4 questions
+
Super Important Tools and Excel Logic
21 Lectures 01:01:09

You'll need to memorize this one-liner, but it's easy once you know what it means.

Determining the Last Row in your Data Set
03:17

Very similar to last row.

Determining the Last Column in your Data Set
02:23

Really really easy to grab the Next row in your set. Great for auto-data entry.

Determining the Next Row in your Data Set
01:06

How to record a macro and use it to LEARN!!

Recording a Macro
03:39

How to Sort dynamically, even when more rows are added. No more hard-coding!

Preview 05:54

More on the recording tool.

Absolute vs Relative References
04:07

Want to save a lot of keystrokes? Listen to this lesson.

Using With and End With
03:32

Equal to, less than, IS NOT Equal to, etc. . . this is good to know!

Comparison Operators in Excel VBA
01:01

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.

If Then Statement
02:25

Another logical tool.

Using Is Not Equal To (<>)
00:41

Using the word NOT to negate a statement.

Logical Operator, NOT
00:56

Adding the "Else" aka otherwise part of the IF THEN statement.

If, Then, Else Statement
01:22

What in the world is ELSE IF??

If, Then, ElseIF Statement. . .
02:02

Don't let text mess you up when comparing to numbers.

Comparitive Operators with Text and Numbers!
02:35

Save space and use a one-liner of code for your basic If Then statements! Easy!

Use If Then Statements in a One-Liner of code
01:42

Jump to different areas of code like a teleportation device!

Using GOTO and Labels
01:52

Try this on for size to save keystrokes when using If Then statements. It's nice once you get used to it.

Select Case as Alternative to IF THEN Statements
02:21

"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.

Message Box with Yes and No Buttons
06:27

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.

Relative Positioning using Offset
04:13

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!

Preview 05:03


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!

Super Important Info Quiz
5 questions
+
Loops and Report Writing Basics
23 Lectures 01:10:02

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!

Intro to Loops; the For Next Loop
03:07

Now let's make it interesting. . . add some spice to our first loop.

Fun with our For Next Loop
03:46

We'll start with a basic loop to analyze all the rows in our data set.

Beginning our first Report using Loops
05:46

An InputBox is a tool to get insight or data entry from a user.

Using an InputBox
02:53

An InputBox helps the user feel involved and can make this report dynamic based on what they want to see.

Adding InputBox to Our First Report to make it Dynamic
03:12

Add a button . . . it's what all the cool kids are doing. . .

Add a Button to Open Our Report
02:23

Let's make a button that looks much neater using Shapes!

Add a Cool Looking Button or Image to Open Report
02:19

Everyone wants reports that can go to the printer and look good on paper. Learn the basics to this valuable skill.

Our First Printable Report part 1 - Declaring and Setting the Sheets
05:00

Get the data you need on the correct sheet in the correct row and column!

Printable Report Part 2 - Getting Items On Report Sheet
06:41

You need to clear out the last report you wrote onto the report sheet.

Printable Report Part 3 - Clearing Last Report
03:19

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.

Printable Report Part 4 - Ensuring Visibility and Autoselect Report Sheet
03:34

Add an Auto-Print Preview. It's fun and useful!

Using PrintPreview Automatically
00:50

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.

Using PrintOut to Send Directly to Default Printer
01:59

If you hit cancel or "X" out of your InputBox, here's how to handle errors that may pop up. It's so simple.

Handling Debug Error When Cancelling InputBox
02:18

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!

For Loop Going Backwards using STEP
02:49

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!

The FOR EACH Loop
05:17

Here's another example of how to use a FOR EACH loop. More to come!

FOR EACH Loop - Practical Examples
03:30

DO Loops are a bit different as you don't define the start and end points so clearly. Very interesting though!

Intro to DO LOOPS
03:26

Do Until loops and loops until certain conditions are met.

DO Until
01:53

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.

Loop Until
01:31

Do While loops are kind of the opposite as UNTIL loops, because they only run during ideal conditions.

DO While
01:43

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.

Loop While
00:58

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.

EXIT DO - Multiple Exits using your own Criteria along the Way!
01:48

Loops and Reports Quiz
4 questions
+
EVENTS: Worksheet Events made Easy
13 Lectures 32:08

Trigger a macro on a worksheet through various means. Change event is when anything changes on a sheet, your macro is triggered. Cool!

Intro to Worksheet Events and Selection_Change
04:03

When the worksheet is opened, this macro can be triggered.

Worksheet Activate Event
01:14

This is triggered when de-selecting a sheet.

Worksheet Deactivate Event
02:29

Only used with sheet deletion, but interesting. .

BeforeDelete Event - for Worksheet Deletion
01:05

Trigger a macro by double-clicking either on a specific cell or cells OR anywhere on the sheet.

BeforeDoubleClick Event - Trigger a Macro when Double Clicking
02:59

Right-click triggers!

BeforeRightClick Event - Trigger a Macro when Right-Clicking
01:43

Triggered whenever calculations occur.

Calculate Event
02:33

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.

How to Disable Events on the Workbook
01:32

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

Intro to Change Event
01:14

Another practical example. . .

Change Event with EnableEvents toggle - Avoid endless loop!
04:51

Using this trick, you can customize very specific areas you want to trigger the macro and others for it to ignore.

Change Event Triggered with Custom Range Using Intersect
03:19

a Hyperlink Trigger.

FollowHyperlinks Event
02:41

A few lesser known, lesser used Events and my final thoughts on the matter.

Final Thoughts on Worksheet Events and Review Other Events
02:25

Worksheet Events Quiz
4 questions
+
WorkBook Events
17 Lectures 37:50

This event occurs when the workbook is opened.

Workbook Open
05:13

When any chart, worksheet or the workbook itself is activated/selected.

Workbook Activate
01:53

Right before the actual Save occurs, you get a chance to run this macro automatically.

Before Save
02:25

After Save event occurs, you get to recap with this macro. Even can tell you is save was successful or not!

After Save
02:13

Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.

Before Close
03:33

Occurs before the workbook (or anything in it) is printed. You can cancel the printing automatically if things aren't as the programmer wants. . .

Before Print
01:21

Occurs when the chart, worksheet, or workbook is deactivated.

Deactivate
01:06

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

NewChart
03:13

Occurs when a new sheet is created in the workbook.

NewSheet
01:51

Occurs when any sheet is activated.

Sheet Activate
02:19

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.

Delete, DoubleClick, RightClick, Calculate, Change
04:51

Occurs when any sheet is deactivated.

SheetDeactivate
02:49

Occurs when you click any hyperlink in Microsoft Excel.

Follow Hyperlink
00:44

Occurs when the selection changes on any worksheet (doesn't occur if the selection is on a chart sheet).

SheetSelectionChange
00:42

Occurs when any workbook window is activated.

WindowActivate
01:09

Occurs when any workbook window is deactivated.

WindowDeactivate
00:25

Occurs when any workbook window is resized.

WindowResize
02:03
+
Activex Controls on Worksheets
38 Lectures 02:45:09

A brief talk on Activex controls and why I like them better than Forms controls.

Activex Controls vs Forms Controls
03:36

Finally - how to use a simple Button on a worksheet!

Button Click - Wage Calculator
04:10

KeyDown is triggered when someone has pressed a keyboard key. Find out how to harness this toll in this video.

Keydown Event
04:18

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!

BONUS: Capturing Shift, Alt, Ctrl or some combination using the Shift variable
03:19

KeyPress is similar to Keydown. check it out.

KeyPress Event
02:53

When you hover the mouse over something. . . fun control.

MouseMove Event
02:08

This is a useful trick - to use the status bar for info to the user. Try this out! so easy.

MouseMove Event with Application.Statusbar - fun and useful
03:44

Learn all the fun subtleties with MouseUp

MouseUp and MouseDown Events - Left, Middle, & Rightclick, Shift, Alt & Ctrl
06:28

Learn about these events, triggered based on when they are in focus, such as tab, enter or clicked.

GotFocus and LostFocus Events
01:09

Checkbox learning!

Use a Checkbox for its VALUE
02:41

More on the Checkbox

Checkbox Event Without a Button
01:58

LinkedCell joins a cell on a worksheet to the value of a control.

LinkedCell Property with a Checkbox
01:59

Option button, either this or that but not both!

Option Buttons Intro - What is an Option Button and Grouping
03:08

Tips on alignment tools and other such fun.

Grouping, Alignment and Aesthetics for Controls
02:45

More on option buttons with this stimulating quiz!

Homemade Quiz using Option Buttons and a Command Button
04:37

Make sure people make a choice - or the macro will let them know they need to still do some things.

Compliance Checker for your Forms or Quizzes Part 1
03:36

Let's just make the button invisible until they fill out everything they need to. One fun method for compliance!

Compliance check part 2
09:50

Use cell A1 with a spinbutton

SpinButton with Cell A1
04:21

Spinbuttons with textboxes and what to watch out for when using numerals as opposed to text.

SpinButtons with Textboxes and Numbers
06:04

A little more fun before we leave the SpinButton. . .

SpinButton BONUS - Move Textbox Position with SpinButton
03:55

Using dates with a textbox is tricky. Watch this for some tips that are sure to save you time!

SpinButton with Dates in a Textbox
03:31

How to use the control called "Label". fun!

Activex "Label" Control - Basics
03:50

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.

ComboBox - Fill with ListFillRange
03:34

YOU NEED TO LEARN THIS! Now!! :)

Creating a Dynamic Range
04:47

Comboboxes don't have to just be one single column of information!

ComboBox - How to use Multiple Columns
03:09

Get what you actually need in the list, not everything. This acts as a custom filter you can manipulate.

Using .ADDITEM to Get Specific items in your Combobox List
09:25

Learn all the good stuff with a combobox

Noteworthy Properties of a Combobox
05:38

Did you know you can have multiple columns of data in a dropdown/combobox? Fun!!!

Multiple Columns in Combobox with .ADDITEM and List, ListCount etc
07:16

Learn what a ListBox is and how to kick butt with one.

ListBox Basics using Dynamic Range
07:47

Here's how to use a Listbox on a worksheet

Listbox Click event and Dynamic Options
03:26

How to handle a multiselect listbox, where users can select more than one item!

BONUS - Manipulating and Using Multiselect Items in a Listbox
06:04

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!

ListBox - Referencing Column 0 and 1 in a cell Dynamically!
03:32

Now we get really choosey with dynamic filtering and manipulation using .ADDITEM. Awesome!!

Dynamic ListBoxes Using .ADDITEM and More
09:37

Did you know you can have data that's invisible, but you can still use it in your listbox? This is an awesome trick

Make and Utilize INVISIBLE Columns - Special Trick!
03:35

Scrollbar examples.

Using a Scroll Bar on a worksheet
03:29

Add a picture and click event to lead to a website.

Pictures on a Worksheet, plus using FollowHyperlink Method
05:52

Toggle buttons and the joy therein

Toggle Button
02:24

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

A Brief Note on Alternate, 3rd Party Controls
01:34
2 More Sections
About the Instructor
Daniel Strong
4.3 Average rating
2,952 Reviews
55,225 Students
3 Courses
Excel Instructor/Programmer

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.