Autoplay
Autocomplete
Previous Lesson
Complete and Continue
VBA Fundamentals (Part II) [Premium]
Course Description and Requirements
Course Description and Requirements
Welcome! Please Start Here
What is the Course structure? (12:10)
Call Procedures and Pass Data Between Procedures
Module Overview: Call Procedures and Pass Data Between Procedures (1:24)
Section Overview: The basics of procedure calling (0:39)
EMB Review: Introduction to procedure calling (2:19)
EMB Review: How do you call procedures? (2:48)
Section Overview: Procedure scope (0:46)
Introduction to procedure scope (2:35)
Bonus: How do you call ambiguously-named procedures? (2:27)
Bonus: How do you call procedures in different workbooks? (6:55)
Section Overview: Work with procedure arguments (1:15)
Introduction to passing arguments to procedures (3:37)
What procedures can pass or receive data? (1:25)
How do you declare a procedure that receives arguments? (1:26)
How do you work with the argument list of a procedure? (3:30)
How do you pass arguments to a procedure? (1:06)
How do you use arguments in a procedure? (1:21)
Bonus: How do you declare a procedure that accepts an indefinite number of arguments? (2:20)
Bonus: How do you pass arguments by reference or by value? (3:17)
Module Wrap-Up (1:33)
Work with Functions in VBA
Module Overview: Work with Functions in VBA (1:39)
Section: Introduction to functions in VBA (3:27)
Section Overview: How do you work with VBA built-in functions? (0:37)
Introduction to VBA built-in functions (1:49)
How do you work with the arguments of a VBA built-in function? (6:31)
How do you use the values returned by VBA built-in functions? (1:01)
Section Overview: Work with VBA built-in functions (0:18)
Practice: Gather user input with a message box (3:33)
Practice: Obtain a number's square root (1:21)
Practice: Obtain the current system time (1:08)
Practice: Obtain the current system date (1:08)
Practice: Obtain the current month as a value (1:28)
Practice: Obtain the current month's name (1:37)
Practice: Obtain a date's serial number (1:36)
Practice: Check if a cell is empty (1:45)
Practice: Count the number of characters in a string (1:31)
Practice: Obtain a file's size (2:06)
Practice: Obtain information about a variable or an object (2:30)
Section: How do you work with worksheet functions in VBA? (4:35)
Section Overview: Work with worksheet functions in VBA (0:17)
Practice: Add numbers (1:39)
Practice: Identify the largest value in a set of values (1:33)
Practice: Identify the smallest value in a set of values (1:31)
Practice: Calculate a loan payment (2:14)
Practice: Carry out a VLookup (3:03)
Practice: Transpose an array (3:21)
Module Wrap-Up (1:00)
Create User-Defined Functions
Module Overview: Create User-Defined Functions (1:52)
Section Overview: What are User-Defined Functions? (0:29)
Introduction to User-Defined Functions (6:12)
Don't use UDFs to do the work of a Sub procedure (2:41)
Section Overview: How do you create User-Defined Functions? (0:35)
How do you create UDFs? (5:32)
How should you name UDFs? (3:05)
How do the concepts of scope and lifetime apply when working with UDFs? (3:09)
Section Overview: How do you call User-Defined Functions? (0:32)
How do you call UDFs? (2:58)
How do you work with UDF arguments? (1:26)
How do you use UDFs in formulas? (4:33)
Section Overview: Create User-Defined Functions (0:16)
Practice: Calculate the square of a number (1:09)
Practice: Obtain Excel's directory (1:11)
Practice: Obtain the user's name (0:58)
Practice: Check if a cell contains a formula (1:50)
Practice: Check if a cell's font is bold (1:55)
Practice: Check if a cell's font is in italics (1:56)
Practice: Obtain a cell's number format (1:44)
Practice: Count the number of sheets in a workbook (2:51)
Practice: Obtain a workbook's name (2:41)
Practice: Obtain a workbook's full name (2:51)
Practice: Obtain a worksheet's name (2:24)
Practice: Extract an item from a multiple-item string (3:16)
Practice: Make Excel speak an argument (1:25)
Practice: Obtain date and time a workbook was last saved (2:07)
Practice: Create a non-volatile time stamp (1:24)
Practice: Generate non-volatile random numbers (1:48)
Practice: Use Like operator in worksheet formulas (2:23)
Practice: Specify and format a date (2:31)
Practice: Modify a comment (1:48)
Practice: Obtain a hyperlink's address (1:53)
Practice: Obtain a column's letter (2:55)
Section Overview: Work with more advanced User-Defined Functions (0:39)
Bonus: How do you use UDFs stored in other workbooks? (2:00)
Bonus: How do you create volatile UDFs? (2:05)
Bonus: How do you create UDFs that return errors? (0:53)
Section Overview: Customize User-Defined Functions (0:48)
Bonus: Introduction to customizing the Insert Function dialog box (2:32)
Bonus: How can you manually add a UDF description to the Insert Function dialog box? (2:23)
Bonus: How do you customize UDFs with the MacroOptions method? (4:43)
Module Wrap-Up (1:30)
Control Code Execution: Introduction
Module Overview: Control Code Execution: Introduction (2:30)
How do you work with labels? (1:29)
Introduction to structured programming and the GoTo statement (4:04)
How do you use the GoTo statement? (1:26)
Module Wrap-Up (0:39)
Control Code Execution: Conditionally Execute Statements
Module Overview: Control Code Execution: Conditionally Execute Statements (1:48)
Section Overview: Work with conditions and conditional expressions (0:35)
Introduction to conditions and conditional expressions (3:35)
How does VBA evaluate expressions with multiple conditions? (3:38)
Section Overview: Work with the If… Then… Else statement (1:02)
Introduction to If… Then… Else (2:01)
How do you work with If… Then? (6:52)
How do you work with If… Then… Else? (4:43)
How do you work with If… Then… ElseIf… Else? (6:01)
How do you nest If… Then… Else statements? (2:24)
How should you organize conditional tests when working with If… Then… Else? (5:28)
Section Overview: Work with If… Then… Else (0:18)
Practice: Obtain the user's first name (3:28)
Practice: Gather user input with an input box (3:53)
Bonus: Work with IIf (3:13)
Section Overview: Work with the Select Case statement (0:34)
Introduction to Select Case (2:52)
How do you work with Select Case? (5:10)
How do you nest Select Case statements? (1:15)
Module Wrap-Up (2:05)
Control Code Execution: Handle Errors
Module Overview: Control Code Execution: Handle Errors (1:43)
Section Overview: Introduction to errors (0:44)
What are errors? (1:55)
Bonus: How do the Interpreter and Compiler flag errors? (3:59)
Introduction to runtime errors and bugs (2:48)
Section Overview: How do you handle errors? (0:42)
Introduction to error handling (4:39)
Should you handle errors by deleting code? (3:16)
Should you handle errors by conditionally executing statements? (4:51)
Section Overview: Introduction to working with error handlers (0:40)
Specify the appropriate error-trapping settings (3:32)
Introduction to the On Error statement (2:48)
Section Overview: Work with On Error GoTo Label (0:57)
How do you work with the On Error GoTo Label statement? (2:24)
How do you ensure error handlers aren't executed when no error occurs? (2:15)
How do you resume execution after an error handler? (3:22)
How do you work with several error handlers? (1:59)
Section Overview: Work with On Error Resume Next (0:35)
How do you work with the On Error Resume Next statement? (1:30)
How do you analyze and handle errors with On Error Resume Next? (4:34)
Section: Restore default error handling behavior with On Error GoTo 0 (1:46)
Module Wrap-Up (1:50)
Control Code Execution: Repeat a Set of Statements
Module Overview: Control Code Execution: Repeat a Set of Statements (2:16)
Section Overview: Introduction to loops (0:30)
What are loops? (4:44)
Introduction to VBA loops (3:55)
Section Overview: Fixed-iteration loops (0:53)
Introduction to For… Next (1:38)
What is the structure of the For… Next loop? (8:29)
How does VBA process a For… Next loop? (4:01)
How do you work with For Each… Next? (5:11)
Section Overview: Work with fixed-iteration loops (0:20)
Practice: Delete even rows (2:36)
Practice: Fill cells in rows where the first cell is empty (3:52)
Practice: Fill array with worksheet names (4:31)
Practice: Create a table of contents (4:22)
Practice: Extract characters before first number in string (3:15)
Practice: Remove vowels from a string (3:17)
Practice: Delete empty rows (8:07)
Practice: Delete empty worksheets (2:56)
Practice: Hide all worksheets except the active worksheet (2:12)
Practice: Unhide all worksheets (1:22)
Practice: Save previously saved workbooks (3:02)
Practice: Close all workbooks and save changes (2:43)
Practice: Add cells based on their fill color (3:45)
Section Overview: Indefinite loops (0:58)
Introduction to indefinite loops (8:15)
How do you work with Do While? (3:08)
How do you work with Do… Loop While? (2:26)
How do you work with Do Until? (2:12)
How do you work with Do… Loop Until? (2:56)
How do you work with While… Wend? (2:54)
Practice: Loop through all days of the current month (7:51)
Section Overview: Advanced looping topics (0:32)
How do you nest loops? (4:06)
How do you prevent infinite loops? (4:49)
Module Wrap-Up (2:31)
Create Automatic Procedures
Module Overview: Create Automatic Procedures (2:10)
Section Overview: Introduction to events and automatic procedures (0:30)
What are events? (4:41)
What event types can you work with? (4:15)
Section Overview: Create and store event-handler procedures (1:00)
Introduction to event-handler procedures and event-handler procedure storage (6:25)
How do you display an object's Code Window? (1:11)
How do you declare and name event-handler procedures? (3:33)
How do you work with event arguments? (1:20)
How do you create a Class module to monitor Application-level events? (4:54)
How do you create a Class module to monitor embedded-chart-level events (4:04)
Section Overview: Advanced event topics (0:42)
Bonus: Introduction to event sequences (2:26)
How do you enable or disable events? (6:12)
Section Overview: Work with events (0:17)
Practice: Save a copy before closing a workbook (2:11)
Practice: Block changes to the workbook's name (2:01)
Practice: Maximize a workbook's window upon activation (1:23)
Practice: Copy the current selection when deactivating a workbook (1:44)
Practice: Activate a specific cell upon worksheet activation (1:51)
Practice: Add time stamp to newly-added worksheet (2:16)
Practice: Update page footers prior to printing (2:22)
Practice: Prevent deactivation of a worksheet (1:30)
Practice: Toggle bold formatting by double-clicking on a cell (1:58)
Practice: Monitor a cell range for changes (3:24)
Practice: Specify fill color for row and column containing active cell (2:38)
Practice: Display the current time in a cell (5:14)
Practice: Disable a keyboard shortcut (2:35)
Module Wrap-Up (1:31)
Course Wrap-Up
Course Wrap-Up (8:05)
Practice: Check if a cell's font is in italics
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock