Autoplay
Autocomplete
Previous Lesson
Complete and Continue
VBA Fundamentals (Part I) [Premium]
Course Description and Requirements
Course Description and Requirements
Welcome! Please Start Here
Welcome to the Course (2:10)
EMB Review: How do you use the Course software? (5:27)
What is the main purpose of VBA Fundamentals? (2:49)
How are the Course materials organized? (5:38)
What is the Course structure? (11:38)
Procedures and Sub procedures
Module Overview: Procedures and Sub procedures (1:25)
Section Overview: Introduction to procedures and Sub procedures (1:23)
What is a procedure? (1:58)
How do you work with procedures within the VBE? (3:02)
What is the structure of a Sub procedure? (2:03)
How are procedures executed? (1:02)
How long should procedures be? (1:44)
Section Overview: How should you name procedures? (0:51)
What are the rules you must follow when choosing procedure names? (2:16)
Additional suggestions for choosing procedure names (2:18)
Module Wrap-Up (2:21)
The structure of a VBA statement
Module Overview: The structure of a VBA statement (2:49)
Section Overview: Introduction to the basic elements of VBA (1:30)
EMB Review: Introduction to objects (1:38)
EMB Review: Introduction to collections (3:11)
EMB Review: Introduction to variables (1:13)
EMB Review: Introduction to properties (0:55)
EMB Review: Introduction to methods (1:10)
EMB Review: Introduction to parameters (1:29)
EMB Review: How do you connect VBA constructs? (0:59)
Section Overview: Introduction to object-oriented programming (1:27)
What is the basic structure of a VBA statement? (3:17)
How do you look at the Excel Application from an object-oriented perspective? (2:26)
Module Wrap-Up (1:10)
Objects
Module Overview: Objects (2:38)
Section Overview: The Excel Object Model (0:54)
What is the Excel Object Model? (0:44)
What sections of the Excel Object Model should you focus on? (1:37)
What is the structure of the Excel Object Model? (3:21)
Section Overview: Collections (0:44)
What are collections? (3:12)
How do you distinguish individual objects from collections? (2:16)
Why should you work with collections? (2:55)
Section Overview: How do you refer to objects? (1:49)
How do you build an object reference? (0:51)
How do you navigate the Excel Object Model? (3:35)
What are the main advantages and disadvantages of fully-qualified object references? (0:40)
Introduction to simplifying fully-qualified object references (1:35)
How do you simplify fully-qualified object references? (5:22)
How do you identify an object within a collection? (6:52)
Section Overview: Practice creating object references (2:05)
Practice: Refer to a worksheet (2:22)
Practice: Refer to a single cell (3:33)
Practice: Refer to a cell range (3:47)
Practice: Refer to a named cell range (1:41)
Practice: Refer to an entire row (2:42)
Practice: Refer to an entire column (2:41)
Practice: Refer to the union of several cell ranges (3:51)
Practice: Refer to the intersection of several cell ranges (3:29)
Practice: Refer to a cell by using another cell range as source reference (4:00)
Practice: Refer to a cell a number of rows and columns away from another cell (2:31)
Practice: Refer to the current data region (1:54)
Practice: Refer to the cell at the border of the current data region (3:38)
Practice: Refer to a cell range until the end of the current data region (3:49)
Practice: Resize a cell range (2:06)
Practice: Refer to an Excel table (2:24)
Practice: Refer to the data in an Excel table column (1:51)
Module Wrap-Up (2:32)
Properties and methods
Module Overview: Properties and methods (2:28)
Section Overview: Introduction to properties and methods (0:50)
What can you do with objects? (1:02)
What are properties? (1:21)
What are methods? (1:10)
Where do you find properties and methods within the Excel Object Model? (2:55)
How do you work with properties? (2:18)
How do you work with methods? (0:54)
Section Overview: How do you work with parameters and set property values? (0:52)
What are parameters? (0:57)
Does the structure of a VBA statement change when working with parameters? (4:27)
What is the structure of a VBA statement that works with method parameters? (4:27)
What is the structure of a VBA statement that works with property parameters? (1:24)
How do you set a property's value? (1:02)
Section Overview: Introduction to the Properties Window (1:09)
Bonus: Where do you find the Properties Window? (0:49)
Bonus: Why is the Properties Window useful? (2:53)
Bonus: How do you modify a property from the Properties Window? (1:10)
Bonus: How do you refer to objects using the CodeName property? (1:10)
Section Overview: Work with properties and methods (1:08)
Practice: Obtain a workbook's full name (1:02)
Practice: Obtain the number of open workbooks (1:07)
Practice: Obtain a cell's value (1:26)
Practice: Obtain the text displayed in a cell (1:23)
Practice: Obtain the number of cells within a cell range (1:23)
Practice: Obtain the number of cells within a large cell range (1:49)
Practice: Obtain the row or column number of a cell range (2:09)
Practice: Obtain a cell range's address (2:12)
Practice: Identify whether a cell range contains formulas (2:15)
Practice: Set the name of a worksheet (0:59)
Practice: Set a cell range's value (1:57)
Practice: Set a cell range's formula (2:57)
Practice: Specify a font's bold formatting (1:07)
Practice: Set the fill color of a cell range (1:33)
Practice: Set a cell range's number format (1:29)
Practice: Activate a worksheet and select a cell range (1:22)
Practice: Clear a cell range (1:03)
Practice: Clear a cell range's formulas and values (1:13)
Practice: Clear a cell range's formatting (1:06)
Practice: Copy a cell range (1:23)
Practice: Cut a cell range (1:27)
Practice: Activate a cell range in any workbook (1:56)
Practice: Delete a cell range (2:57)
Practice: Create a new workbook (0:53)
Practice: Protect a workbook (2:47)
Module Wrap-Up (1:54)
The With… End With Statement
Module Overview: The With… End With Statement (1:30)
Section Overview: The basics of the With… End With statement (1:03)
EMB Review: What is the With… End With statement? (1:12)
EMB Review: What is the structure of the With… End With statement? (1:55)
EMB Review: What is the logic behind the structure of a With… End With statement? (0:52)
EMB Review: Why should you work with the With… End With statement? (2:08)
EMB Review: When should you use the With… End With statement? (1:40)
Section Overview: Nesting With… End With statements (0:35)
EMB Review: How do you nest With… End With statements? (4:03)
EMB Review: How many With… End With statements should you nest? (1:14)
Section Overview: Work with the With… End With statement (0:51)
Practice: Specify cell and font characteristics (3:46)
Practice: Review the procedures you created in previous Practice Lessons (1:27)
Module Wrap-Up (1:26)
Formulas and Cell References
Module Overview: Formulas and cell references (1:50)
Section Overview: Formulas and cell references in VBA (0:56)
How do you refer to cells in Excel? (3:45)
Why should you use R1C1-style references? (2:13)
Section Overview: How do you create R1C1-style references? (1:11)
What is the basic structure of an R1C1-style reference? (1:56)
How do you refer to the current cell, row or column using R1C1-style references? (1:26)
How do you create absolute R1C1-style cell references? (1:07)
How do you create relative R1C1-style references? (1:14)
How do you create mixed R1C1-style references? (1:14)
Be careful with R1C1-style references wrapping around the worksheet (1:04)
How do you identify a column's number? (1:41)
Practice: Work with R1C1-style references (3:47)
Module Wrap-Up (1:24)
Expressions and operators
Module Overview: Expressions and operators (1:46)
Section Overview: How do you work with expressions and operators? (0:53)
What are expressions? (1:35)
What are operators? (1:03)
What are arithmetic operators? (1:02)
What are comparison operators? (1:49)
What are logical operators? (2:54)
What are concatenation and assignment operators? (1:07)
In which order are operators applied? (4:19)
Section Overview: Work with operators and expressions (0:19)
Practice: Toggle Boolean settings (3:08)
Practice: Create a conditional expression with the And operator (2:37)
Practice: Create a conditional expression with the Or operator (2:41)
Module Wrap-Up (1:06)
Variables and data types
Module Overview: Variables and data types (1:42)
Section Overview: Introduction to variables (0:32)
Why should you work with variables? (2:09)
What are variables? (2:45)
Section Overview: How do you declare variables? (1:38)
Introduction to variable declaration (1:23)
Why should you declare variables explicitly? (3:24)
What are data types? (3:18)
Why should you declare data types explicitly? (3:24)
What are the basic rules you must follow when declaring a variable? (3:02)
What are the rules you must follow when choosing variable names? (3:26)
Additional suggestions for choosing variable names (2:39)
Bonus: How do you declare several variables in 1 line of code? (2:10)
Bonus: How do you force explicit variable declaration in a single module? (3:04)
Bonus: How do you force explicit variable declaration in future modules? (1:34)
Section Overview: How do you work with data types? (1:02)
Introduction to data types (2:08)
The Variant data type (4:36)
The String data type (3:37)
Numeric data types (6:53)
The Date data type (4:41)
The Boolean data type (0:41)
What is the basic rule of thumb for choosing data types? (2:32)
What are some exceptions to the basic rule of thumb for choosing data types? (3:10)
Suggestions for choosing data types (3:00)
Section: How do you assign data to variables? (1:46)
Section Overview: Work with variables (0:19)
Practice: Apply bold formatting to a dynamic cell range (5:18)
Practice: Enter data in the next empty row (4:10)
Module Wrap-Up (2:06)
Variable scope and lifetime
Module Overview: Variable scope and lifetime (2:03)
Section Overview: Introduction to variable scope and lifetime (0:46)
What is variable scope? What is variable lifetime? (1:08)
What is the default scope and lifetime of a variable? (2:04)
What can you do when the default variable scope or lifetime isn't appropriate? (1:37)
Section Overview: How do you work with module-level variables? (0:34)
Bonus: What is the scope and lifetime of module-level variables? (1:59)
Bonus: How do you declare module-level variables? (1:59)
Section Overview: How do you work with global variables? (0:32)
Bonus: What is the scope and lifetime of global variables? (0:41)
Bonus: How do you declare global variables? (1:33)
Section Overview: How do you work with static variables? (0:29)
Bonus: What are static variables? (3:11)
Bonus: How do you declare static variables? (1:55)
Section Overview: Additional considerations regarding variable scope and lifetime (0:36)
Bonus: How do you reset variables? (1:32)
How do you choose the scope and lifetime of a variable? (4:17)
Practice: Modify the default scope or lifetime of a variable (1:54)
Module Wrap-Up (1:11)
Object variables
Module Overview: Object variables (1:32)
Section Overview: Introduction to object variables (0:35)
What are object variables? (1:58)
Why should you work with object variables? (5:03)
Section Overview: How do you work with object variables? (0:38)
How do you declare object variables? (2:17)
Why should you declare your object variables as of a specific data type? (3:32)
How do you assign an object to an object variable? (1:54)
Module Wrap-Up (0:52)
Constants
Module Overview: Constants (1:50)
Section: Introduction to Constants (1:17)
Section Overview: How do you work with user-defined constants? (0:42)
Why should you work with user-defined constants? (3:04)
How do you declare and assign data to constants? (2:10)
Bonus: Introduction to constant scope and lifetime (2:00)
Section Overview: How do you work with built-in constants? (0:30)
What are built-in constants? (1:36)
Why should you work with built-in constants? (2:23)
Section Overview: Work with constants (0:18)
Practice: Set workbook calculation to manual (1:26)
Practice: Set the page orientation to landscape (1:27)
Module Wrap-Up (1:12)
Arrays
Module Overview: Arrays (2:11)
Section Overview: Introduction to arrays (0:26)
What are arrays? (1:50)
Why should you work with arrays? (2:34)
Section Overview: How do you declare arrays? (0:45)
How do you declare arrays? (1:29)
How do you set an array's default lower bound? (2:10)
Bonus: How can you hold different data types in a single array? (1:16)
Array scope and lifetime (0:58)
Section Overview: Multidimensional arrays (0:38)
What are multidimensional arrays? (2:08)
How do you work with multidimensional arrays? (1:51)
Section Overview: Dynamic arrays (0:33)
What are dynamic arrays? (1:00)
How do you declare and resize a dynamic array? (2:28)
Section Overview: Fill and work with the elements of an array (0:38)
How do you fill an array? (1:53)
How do you get information from an array? (0:34)
How do you erase the data within an array? (0:43)
Section Overview: Work with arrays (0:18)
Practice: Transfer a 1-dimensional array to a worksheet (1:53)
Practice: Transfer a cell range's data to an array (2:24)
Practice: Fill an array with the Array function (2:30)
Module Wrap-Up (1:37)
Course Wrap-Up
Course Wrap-Up (12:18)
Practice: Enter data in the next empty row
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock