The Ultimate Excel
VBA Course

• Intro and course workbooks download
• Get the developer tab and intro to developer tab
• Getting started with range object
• Quick tip – project explorer and properties window!
• Creating your first macro from scratch
• Adding a secondary command. . .
• Debugging in Excel VBA
• Saving a macro-enabled workbook (.xlsm)
• Before you begin the exercises
• Exercise 01 – Question
• Exercise 01 – Answer
• Several ways to trigger your macro
• Affecting multiple cells with one range command – 2 methods
• Adding strings of text to a cell
• Using a named range with the range object
• Range object quiz

Section three explores the differences and similarities between accessing a cell through the Range object and the new Cells object, which can now access cells using row, column coordinates! This is useful when utilizing numeric loops and variables in the next sections.

Topics covered:

• The cells object
• Using column letters instead of numbers
• Using cells positionally within a range
• Affecting all cells in a worksheet
• Exercise 03 – Question
• Exercise 03 – Answer
• Using range object with cells object
• Cells quiz

Variables open up the possibilities in any programming language. You don’t have to store information in worksheet cells every time. Sometimes it’s better to store a date, string of text or even numbers inside of ‘nicknames’ called variables to contain or manipulate temporary data. They’re super fun and useful!

Topics covered:

• Intro to variables
• Declaring variables – variable types
• Declaring variables – types of demo
• Concatenation – joining two or more strings!
• Bonus – date math fun!
• Exercise 04 – Question
• Exercise 04 – Answer
• Calling a procedure from another
• Public and private variables
• Using constants
• Bonus! Passing variables to another procedure – ByVal and ByRef
• Variables quiz

You’re no better than your tools, so let’s take a look at the Visual Basic Editor and all the wonderful things you can do once you know how to use it!

Topics covered:

• File menu walkthrough
• Edit menu walkthrough
• View menu walkthrough
• View menu2 – watch window, locals window and immediate window
• Watch window wonders!
• Insert and format menus
• Debug menu (important!)
• Run and tools menus
• Toolbars quiz

This section covers all the tricks you’ll need to begin automating and really setting your programs in motion. Enjoy deliciously exciting experiments into IF/THEN statements, to create a fork in your procedures based on custom conditions. Learn how to determine the last row or column in order to set a cell section OR even to know which is the next row to automatically add new data to. That and much more!

Topics covered:

• Determining the last row in your data set
• Determining the last column in your data set
• Determining the next row in your data set
• Recording a macro
• Bonus! – dynamic sorting!
• Absolute vs relative references
• Using with and end with
• Comparison operators in excel vba
• If then statement
• Using is not equal to (<>)
• Logical operator, not
• Exercise 06a – Question
• Exercise 06a – Answer
• If, then, else statement
• If, then, elseif statement. . .
• Comparative operators with text and numbers!
• Use if then statements in a one-liner of code
• Using go to and labels
• Select case as alternative to if then statements
• Message box with yes and no buttons
• Exercise 06b – Question
• Exercise 06b – Answer
• Relative positioning using offset
• Bonus – User defined functions 101
• Bonus – UDF lesson 2
• Super important info quiz

Adding CommandButtons, ComboBoxes (dropdown menus), ListBoxes, and many other ActiveX controls can really make your program POP! There’s a lot you can do with these, and each one has many, many triggers you can manipulate. Want to select ALL the text in a textbox when you click into it? Want to automatically Disable and gray out certain controls when conditions are met? Want to set up data entry and calculation on button click (but first to check a few conditions?).

Meet your new best friend. ActiveX Controls.

Topics covered:

• Activex Controls vs Forms Controls
• Button Click – Wage Calculator
• Keydown Event
• BONUS: Capturing Shift, Alt, Ctrl or some combination using the Shift variable
• KeyPress event
• MouseMove event
• MouseMove event with Application.Statusbar – fun and useful
• MouseUp and MouseDown Events – Left, Middle, & Rightclick, Shift, Alt & Ctrl
• GotFocus and LostFocus events
• Use a Checkbox for its VALUE
• Checkbox event without a button
• LinkedCell property with a checkbox
• Option buttons intro – What is an option button and grouping
• Grouping, Alignment and Aesthetics for Controls
• Homemade quiz using option buttons and a command button
• Compliance checker for your Forms or Quizzes Part 1
• Compliance check part 2
• SpinButton with Cell A1
• SpinButtons with Textboxes and Numbers
• SpinButton BONUS – Move Textbox Position with SpinButton
• SpinButton with Dates in a Textbox
• Activex “Label” Control – Basics
• ComboBox – Fill with ListFillRange
• Creating a Dynamic Range
• ComboBox – How to use Multiple Columns
• Using .ADDITEM to Get Specific items in your Combobox List
• Noteworthy Properties of a Combobox
• Multiple Columns in Combobox with .ADDITEM and List, ListCount etc
• ListBox Basics using Dynamic Range
• Listbox Click event and Dynamic Options
• BONUS – Manipulating and using multiselect items in a Listbox
• ListBox – Referencing Column 0 and 1 in a cell Dynamically!
• Dynamic ListBoxes Using .ADDITEM and More
• Make and Utilize INVISIBLE Columns – Special Trick!
• Using a Scroll Bar on a worksheet
• Pictures on a Worksheet, plus using FollowHyperlink Method
• Toggle button
• A brief note on Alternate, 3rd Party Controls

If it sounds like I’m enthusiastic, that’s because I AM! Userforms can take your program to the next level, giving you a new profound amount of control.

Did you know you can make a form popup that can call up other forms, control all procedures and bring up reports, all the while hosting lots of ActiveX controls that help you manage, manipulate, update or append new data. Keeping in mind, you can actually edit or add to sheets that are completely hidden to the end user! WHAT?! Yep. It’s amazing and so much fun.

Topics covered:

• Userform – Hide, show and unload
• Sample database and creating a data entry form
• Fill Status combobox with Rowsource when Userform starts – 2 methods
• New employee data entry form!
• Format textbox numbers as numerals, clearing out form upon submission
• Set the tab order for userform controls
• Set default values to make data entry easier
• Avoid duplicate employee ID and other fun automation tricks
• Edit Mode for Employee Database – Part 1
• Edit Mode for Employee Database – Part 2
• Edit Mode Pt 3 – Adding Automation and Save Button
• BONUS – Refreshing the RowSource After Updating Data!
• BONUS – Combobox by Last Name Instead of EmpID
• BONUS – Search Name and ID
• Userform Report with Filters – Part 1
• Userform Report with Filters – Part 2
• Applying the Dynamic Filters to Reports
• Clear out Last Report
• Report generation and aesthetics, headers, footers, repeat rows etc.
• Advanced exercise – Review of Objectives
• Advanced exercise 01 – Solution
• Advanced exercise 02 – Solution
• Advanced exercise 03 and 04 – Solutions
• Advanced exercise BONUS A and B – Solutions
• Advanced exercise BONUS C automate the workflow for efficiency – Solutions
• Concluding thoughts. . .

Here’s where I show you some additional features that are just amazing, but don’t always have a nice place to file away. Or I got asked a random question and it merited a video in the Bonus section. Fun!

Topics covered:

• InStr Function – Search for Text Within Other Text
• The EVALUATE Function – Mac-Daddy of all functions
• Sum Function in Excel VBA – EASY!
• Count Function in VBA
• BONUS LECTURE