Lynda.COM Excel: VBA in Depth

Curt Frye

Software works on:

Windows only

Topics include:

• Working in the Visual Basic Editor
• Adding code to a macro
• Creating, exporting, and deleting code modules
• Declaring and using variables
• Managing variable scope
• Defining arrays
• Managing workbooks and worksheets with VBA
• Repeating tasks with loops
• Debugging VBA code
• Cutting, copying, and pasting cell data
• Running and triggering event procedures

Introduction
Welcome           01:09
Using the exercise files           00:18
39:48 1. Introducing Visual Basic for Applications (VBA)
Introducing object-oriented programming           01:53
Examining the Excel object model           04:32
Working in the Visual Basic editor           04:34
Setting VBA project properties           02:33
Creating, exporting, and deleting code modules           06:48
Creating a subroutine or function           06:02
Controlling procedure scope and visibility           03:12
Adding comments to the code           03:43
Running a VBA routine           03:50
Adding code to a recorded macro           02:41
35:53 2. Defining Variables, Constants, and Calculations
Introducing Excel VBA data types           06:07
Declaring variables and requiring declaration before use           04:22
Managing variable scope           03:33
Defining static variables and constants           04:52
Creating a calculation using mathematical operators           03:33
Defining arrays           07:16
Defining and using object variables           02:58
Streamlining code references using With...End With statements           03:12
20:43 3. Adding Logic to Your VBA Code
Repeating a task using a For...Next loop           02:35
Stepping through all items of a collection using a For...Each loop           03:11
Repeating a task with a Do loop           05:24
Performing a task when conditions are met using If...Then           07:40
Selecting which action to take using a Case statement           01:53
19:53 4. Debugging Your VBA Code
Managing errors using On Error statements           05:02
Stepping through a subroutine or function           05:23
Setting breakpoints in the code           02:36
Verifying output using the Immediate window           03:52
Watching a value in a routine           03:00
32:51 5. Managing Workbook Elements and Data in VBA
Writing a value to a cell           03:40
Cutting, copying, and pasting cell data           05:01
Finding values in cells           04:19
Referring to cells using the Offset function           02:57
Concatenating text strings           04:16
Returning part of a string           02:55
Managing worksheets with VBA           05:52
Managing workbooks with VBA           03:51
12:07 6. Adding Advanced Elements to Your Workbook
Turning off screen updating within a macro           02:26
Using worksheet functions in a macro           02:26
Acquiring values using an input box or message box           05:05
Calling a subroutine from another subroutine           02:10
12:29 7. Using Excel Events in Your VBA Code
Running a procedure when you open, close, or save a workbook           04:21
Running a procedure when a cell range changes           04:35
Triggering a procedure using a specific key sequence           03:33
06:13 8. Putting It All Together
Applying techniques in a Capstone project           06:13
00:17 Conclusion
Goodbye           00:17