Excel VBA Complete Guide

What’s included

$14.99 / $24.99

Get ready for your exam by enrolling in our comprehensive training course. This course includes a full set of instructional videos designed to equip you with in-depth knowledge essential for passing the certification exam with flying colors.

4.59

Pay once, own it forever

Video Courses

The Range Object

Lectures	Duration
1. Intro and Course Workbooks download	4m
2. Get the Developer Tab and Intro to Developer Tab	1m
3. Getting started with Range object	1m
4. Quick Tip - Project Explorer and Properties Window!	1m
5. Creating Your First Macro From Scratch	2m
6. Adding a Secondary Command	1m
7. Debugging in Excel VBA	1m
8. Saving a Macro-Enabled Workbook (.xlsm)	1m
9. Before You Begin The Exercises...	3m
10. Several Ways to trigger your Macro	1m
11. Affecting Multiple Cells with One Range Command - 2 Methods	2m
12. Adding Strings of Text to a Cell	2m
13. Using a Named Range with the Range Object	2m

1. Intro and Course Workbooks download

2. Get the Developer Tab and Intro to Developer Tab

3. Getting started with Range object

4. Quick Tip - Project Explorer and Properties Window!

5. Creating Your First Macro From Scratch

6. Adding a Secondary Command

7. Debugging in Excel VBA

8. Saving a Macro-Enabled Workbook (.xlsm)

9. Before You Begin The Exercises...

10. Several Ways to trigger your Macro

11. Affecting Multiple Cells with One Range Command - 2 Methods

12. Adding Strings of Text to a Cell

13. Using a Named Range with the Range Object

Range Properties

Lectures	Duration
1. .VALUE Property	2m
2. .TEXT Property	1m
3. .ROW and .COLUMN Properties	1m
4. .SELECT Property	1m
5. .COUNT Property	2m
6. .ADDRESS Property	2m
7. .FORMULA Property	4m
8. .NUMBERFORMAT Property	2m
9. FONT.BOLD, UNDERLINE or ITALIC Properties	3m

1. .VALUE Property

2. .TEXT Property

3. .ROW and .COLUMN Properties

4. .SELECT Property

5. .COUNT Property

6. .ADDRESS Property

7. .FORMULA Property

8. .NUMBERFORMAT Property

9. FONT.BOLD, UNDERLINE or ITALIC Properties

The Cells Object

Lectures	Duration
1. The Cells Object	2m
2. Using Column Letters instead of Numbers	1m
3. Using Cells Positionally within a Range	2m
4. Affecting ALL Cells in a Worksheet	2m
5. Using Range Object with Cells Object	3m

1. The Cells Object

2. Using Column Letters instead of Numbers

3. Using Cells Positionally within a Range

4. Affecting ALL Cells in a Worksheet

5. Using Range Object with Cells Object

Variables

Lectures	Duration
1. Intro to Variables	2m
2. Declaring Variables - Variable Types	3m
3. Declaring Variables - Types Demo	4m
4. Calling a Procedure from Another	2m
5. Public and Private Variables	3m
6. Using Constants	2m
7. BONUS! Passing Variables to Another Procedure - ByVal and ByRef	7m

1. Intro to Variables

2. Declaring Variables - Variable Types

3. Declaring Variables - Types Demo

4. Calling a Procedure from Another

5. Public and Private Variables

6. Using Constants

7. BONUS! Passing Variables to Another Procedure - ByVal and ByRef

Toolbars and Menus

Lectures	Duration
1. File Menu walkthrough	2m
2. Edit Menu walkthrough	5m
3. View Menu walkthrough	2m
4. View Menu2 - Watch Window, Locals Window and Immediate Window	8m
5. Watch Window Wonders!	3m
6. Insert and Format Menus	1m
7. Debug Menu (Important!)	5m
8. Run and Tools Menus	2m

1. File Menu walkthrough

2. Edit Menu walkthrough

3. View Menu walkthrough

4. View Menu2 - Watch Window, Locals Window and Immediate Window

5. Watch Window Wonders!

6. Insert and Format Menus

7. Debug Menu (Important!)

8. Run and Tools Menus

Super Important Tools and Excel Logic

Lectures	Duration
1. Determining the Last Row in your Data Set	3m
2. Determining the Last Column in your Data Set	2m
3. Determining the Next Row in your Data Set	1m
4. Recording a Macro	4m
5. BONUS! - Dynamic Sorting!	6m
6. Absolute vs Relative References	4m
7. Using With and End With	4m
8. Comparison Operators in Excel VBA	1m
9. If Then Statement	2m
10. Using Is Not Equal To (<>)	1m
11. Logical Operator, NOT	1m
12. If, Then, Else Statement	1m
13. If, Then, ElseIF Statement	2m
14. Comparitive Operators with Text and Numbers!	3m
15. Use If Then Statements in a One-Liner of code	2m
16. Using GOTO and Labels	2m
17. Select Case as Alternative to IF THEN Statements	2m
18. Message Box with Yes and No Buttons	6m
19. Relative Positioning using Offset	4m
20. BONUS - User Defined Functions 101	5m
21. BONUS - UDF Lesson 2	4m

1. Determining the Last Row in your Data Set

2. Determining the Last Column in your Data Set

3. Determining the Next Row in your Data Set

4. Recording a Macro

5. BONUS! - Dynamic Sorting!

6. Absolute vs Relative References

7. Using With and End With

8. Comparison Operators in Excel VBA

9. If Then Statement

10. Using Is Not Equal To (<>)

11. Logical Operator, NOT

12. If, Then, Else Statement

13. If, Then, ElseIF Statement

14. Comparitive Operators with Text and Numbers!

15. Use If Then Statements in a One-Liner of code

16. Using GOTO and Labels

17. Select Case as Alternative to IF THEN Statements

18. Message Box with Yes and No Buttons

19. Relative Positioning using Offset

20. BONUS - User Defined Functions 101

21. BONUS - UDF Lesson 2

Loops and Report Writing Basics

Lectures	Duration
1. Intro to Loops; the For Next Loop	3m
2. Fun with our For Next Loop	4m
3. Beginning our first Report using Loops	6m
4. Using an InputBox	3m
5. Adding InputBox to Our First Report to make it Dynamic	3m
6. Add a Button to Open Our Report	2m
7. Add a Cool Looking Button or Image to Open Report	2m
8. Our First Printable Report part 1 - Declaring and Setting the Sheets	5m
9. Printable Report Part 2 - Getting Items On Report Sheet	7m
10. Printable Report Part 3 - Clearing Last Report	3m
11. Printable Report Part 4 - Ensuring Visibility and Autoselect Report Sheet	4m
12. Exercise 07a - Question	1m
13. Exercise 07a - Answer	11m
14. Using PrintPreview Automatically	1m
15. Using PrintOut to Send Directly to Default Printer	2m
16. Handling Debug Error When Cancelling InputBox	2m
17. For Loop Going Backwards using STEP	3m
18. The FOR EACH Loop	5m
19. FOR EACH Loop - Practical Examples	4m
20. Exercise 07b - Question	1m
21. Exercise 07b - Answer	8m
22. Intro to DO LOOPS	3m
23. DO Until	2m

1. Intro to Loops; the For Next Loop

2. Fun with our For Next Loop

3. Beginning our first Report using Loops

4. Using an InputBox

5. Adding InputBox to Our First Report to make it Dynamic

6. Add a Button to Open Our Report

7. Add a Cool Looking Button or Image to Open Report

8. Our First Printable Report part 1 - Declaring and Setting the Sheets

9. Printable Report Part 2 - Getting Items On Report Sheet

10. Printable Report Part 3 - Clearing Last Report

11. Printable Report Part 4 - Ensuring Visibility and Autoselect Report Sheet

12. Exercise 07a - Question

13. Exercise 07a - Answer

11m

14. Using PrintPreview Automatically

15. Using PrintOut to Send Directly to Default Printer

16. Handling Debug Error When Cancelling InputBox

17. For Loop Going Backwards using STEP

18. The FOR EACH Loop

19. FOR EACH Loop - Practical Examples

20. Exercise 07b - Question

21. Exercise 07b - Answer

22. Intro to DO LOOPS

23. DO Until

EVENTS: Worksheet Events made Easy

Lectures	Duration
1. Intro to Worksheet Events and Selection_Change	4m
2. Worksheet Activate Event	1m
3. Worksheet Deactivate Event	2m
4. BeforeDelete Event - for Worksheet Deletion	1m
5. BeforeDoubleClick Event - Trigger a Macro when Double Clicking	3m
6. BeforeRightClick Event - Trigger a Macro when Right-Clicking	2m
7. Calculate Event	3m
8. How to Disable Events on the Workbook	2m
9. Intro to Change Event	1m
10. Change Event with EnableEvents toggle - Avoid endless loop!	5m
11. Change Event Triggered with Custom Range Using Intersect	3m
12. FollowHyperlinks Event	3m
13. Final Thoughts on Worksheet Events and Review Other Events	2m

1. Intro to Worksheet Events and Selection_Change

2. Worksheet Activate Event

3. Worksheet Deactivate Event

4. BeforeDelete Event - for Worksheet Deletion

5. BeforeDoubleClick Event - Trigger a Macro when Double Clicking

6. BeforeRightClick Event - Trigger a Macro when Right-Clicking

7. Calculate Event

8. How to Disable Events on the Workbook

9. Intro to Change Event

10. Change Event with EnableEvents toggle - Avoid endless loop!

11. Change Event Triggered with Custom Range Using Intersect

12. FollowHyperlinks Event

13. Final Thoughts on Worksheet Events and Review Other Events

WorkBook Events

Lectures	Duration
1. Workbook Open	5m
2. Workbook Activate	2m
3. Before Save	2m
4. After Save	2m
5. Before Close	4m
6. Before Print	1m
7. Deactivate	1m
8. NewChart	3m
9. NewSheet	2m
10. Exercise 09 - Question	1m
11. Exercise 09 - Answer	5m
12. Sheet Activate	2m
13. Delete, DoubleClick, RightClick, Calculate, Change	5m
14. SheetDeactivate	3m
15. Follow Hyperlink	1m
16. SheetSelectionChange	1m
17. WindowActivate	1m

1. Workbook Open

2. Workbook Activate

3. Before Save

4. After Save

5. Before Close

6. Before Print

7. Deactivate

8. NewChart

9. NewSheet

10. Exercise 09 - Question

11. Exercise 09 - Answer

12. Sheet Activate

13. Delete, DoubleClick, RightClick, Calculate, Change

14. SheetDeactivate

15. Follow Hyperlink

16. SheetSelectionChange

17. WindowActivate

Activex Controls on Worksheets

Lectures	Duration
1. Activex Controls vs Forms Controls	4m
2. Button Click - Wage Calculator	4m
3. Keydown Event	4m
4. BONUS: Capturing Shift, Alt, Ctrl or some combination using the Shift variable	3m
5. KeyPress Event	3m
6. MouseMove Event	2m
7. MouseMove Event with Application.Statusbar - fun and useful	4m
8. MouseUp and MouseDown Events - Left, Middle, & Rightclick, Shift, Alt & Ctrl	6m
9. GotFocus and LostFocus Events	1m
10. Use a Checkbox for its VALUE	3m
11. Checkbox Event Without a Button	2m
12. LinkedCell Property with a Checkbox	2m
13. Option Buttons Intro - What is an Option Button and Grouping	3m
14. Grouping, Alignment and Aesthetics for Controls	3m
15. Homemade Quiz using Option Buttons and a Command Button	5m
16. Compliance Checker for your Forms or Quizzes Part 1	4m
17. Compliance check part 2	10m
18. SpinButton with Cell A1	4m
19. SpinButtons with Textboxes and Numbers	6m
20. SpinButton BONUS - Move Textbox Position with SpinButton	4m
21. SpinButton with Dates in a Textbox	4m
22. Activex "Label" Control - Basics	4m
23. ComboBox - Fill with ListFillRange	4m
24. Creating a Dynamic Range	5m
25. ComboBox - How to use Multiple Columns	3m
26. Using .ADDITEM to Get Specific items in your Combobox List	9m
27. Noteworthy Properties of a Combobox	6m
28. Multiple Columns in Combobox with.ADDITEM and List, ListCount etc	7m
29. ListBox Basics using Dynamic Range	8m
30. Listbox Click event and Dynamic Options	3m
31. BONUS - Manipulating and Using Multiselect Items in a Listbox	6m
32. ListBox - Referencing Column 0 and 1 in a cell Dynamically!	4m
33. Dynamic ListBoxes Using .ADDITEM and More	10m
34. Make and Utilize INVISIBLE Columns - Special Trick!	4m
35. Using a Scroll Bar on a worksheet	3m
36. Pictures on a Worksheet, plus using FollowHyperlink Method	6m
37. Toggle Button	2m
38. A Brief Note on Alternate, 3rd Party Controls	2m

1. Activex Controls vs Forms Controls

2. Button Click - Wage Calculator

3. Keydown Event

4. BONUS: Capturing Shift, Alt, Ctrl or some combination using the Shift variable

5. KeyPress Event

6. MouseMove Event

7. MouseMove Event with Application.Statusbar - fun and useful

8. MouseUp and MouseDown Events - Left, Middle, & Rightclick, Shift, Alt & Ctrl

9. GotFocus and LostFocus Events

10. Use a Checkbox for its VALUE

11. Checkbox Event Without a Button

12. LinkedCell Property with a Checkbox

13. Option Buttons Intro - What is an Option Button and Grouping

14. Grouping, Alignment and Aesthetics for Controls

15. Homemade Quiz using Option Buttons and a Command Button

16. Compliance Checker for your Forms or Quizzes Part 1

17. Compliance check part 2

10m

18. SpinButton with Cell A1

19. SpinButtons with Textboxes and Numbers

20. SpinButton BONUS - Move Textbox Position with SpinButton

21. SpinButton with Dates in a Textbox

22. Activex "Label" Control - Basics

23. ComboBox - Fill with ListFillRange

24. Creating a Dynamic Range

25. ComboBox - How to use Multiple Columns

26. Using .ADDITEM to Get Specific items in your Combobox List

27. Noteworthy Properties of a Combobox

28. Multiple Columns in Combobox with.ADDITEM and List, ListCount etc

29. ListBox Basics using Dynamic Range

30. Listbox Click event and Dynamic Options

31. BONUS - Manipulating and Using Multiselect Items in a Listbox

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

33. Dynamic ListBoxes Using .ADDITEM and More

10m

34. Make and Utilize INVISIBLE Columns - Special Trick!

35. Using a Scroll Bar on a worksheet

36. Pictures on a Worksheet, plus using FollowHyperlink Method

37. Toggle Button

38. A Brief Note on Alternate, 3rd Party Controls

Userforms - the best thing. EVER.

Lectures	Duration
1. Userform - Hide, Show and Unload	5m
2. Sample Database and Creating a Data Entry Form	6m
3. Fill Status combobox with Rowsource when Userform starts - 2 methods	4m
4. New Employee Data Entry Form!	9m
5. Format Textbox Numbers as Numerals, Clearing out Form upon Submission	3m
6. Set the Tab Order for Userform Controls	2m
7. Set Default Values to Make Data Entry Easier	2m
8. Avoid Duplicate Employee ID and other Fun Automation Tricks	13m
9. Edit Mode for Employee Database - Part 1	11m
10. Edit Mode for Employee Database - Part 2	8m
11. Edit Mode Pt 3 - Adding Automation and Save Button	16m
12. Userform Report with Filters - Part 1	11m
13. Userform Report with Filters - Part 2	6m
14. Applying the Dynamic Filters to Reports	3m
15. Clear out Last Report	3m
16. Report Generation and Aesthetics, Headers, Footers, Repeat Rows etc.	6m
17. Concluding Thoughts	3m

1. Userform - Hide, Show and Unload

2. Sample Database and Creating a Data Entry Form

3. Fill Status combobox with Rowsource when Userform starts - 2 methods

4. New Employee Data Entry Form!

5. Format Textbox Numbers as Numerals, Clearing out Form upon Submission

6. Set the Tab Order for Userform Controls

7. Set Default Values to Make Data Entry Easier

8. Avoid Duplicate Employee ID and other Fun Automation Tricks

13m

9. Edit Mode for Employee Database - Part 1

11m

10. Edit Mode for Employee Database - Part 2

11. Edit Mode Pt 3 - Adding Automation and Save Button

16m

12. Userform Report with Filters - Part 1

11m

13. Userform Report with Filters - Part 2

14. Applying the Dynamic Filters to Reports

15. Clear out Last Report

16. Report Generation and Aesthetics, Headers, Footers, Repeat Rows etc.

17. Concluding Thoughts

BONUS Section - VBA Functions

Lectures	Duration
1. InStr Function - Search for Text Within Other Text	7m
2. The EVALUATE Function - Mac-Daddy of all functions	6m
3. Sum Function in Excel VBA - EASY!	3m
4. Count Function in VBA	2m

1. InStr Function - Search for Text Within Other Text

2. The EVALUATE Function - Mac-Daddy of all functions

3. Sum Function in Excel VBA - EASY!

4. Count Function in VBA