Lynda.com Excel: VBA in Depth

Author: Curt Frye
Duration (hr:min): 03:01
Released on: 6/23/2010
Exercise files: Yes
Software works on: Windows only

In Excel: VBA in Depth, Excel and VBA expert Curt Frye introduces object-oriented programming and shows how to automate routine tasks and provide custom functionality to enhance Excel performance and efficiency. This course introduces the Visual Basic for Applications programming language, covers creating subroutines and functions to hold code, and provides a solid grounding in the Excel 2007 object model. Programming techniques are demonstrated through real-world examples. Exercise files accompany the course.

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

Content:

Introduction - 01:27

    Welcome - 1:09 - 4.7 MB
    Using the exercise files - 0:18 - 0.3 MB

1. Introducing Visual Basic for Applications (VBA) - 39:48

    Introducing object-oriented programming - 1:53 - 1.6 MB
    Examining the Excel object model - 4:32 - 3.6 MB
    Working in the Visual Basic editor - 4:34 - 5.0 MB
    Setting VBA project properties - 2:33 - 2.4 MB
    Creating, exporting, and deleting code modules - 6:48 - 6.6 MB
    Creating a subroutine or function - 6:02 - 5.7 MB
    Controlling procedure scope and visibility - 3:12 - 3.3 MB
    Adding comments to the code - 3:43 - 3.5 MB
    Running a VBA routine - 3:50 - 4.6 MB
    Adding code to a recorded macro - 2:41 - 3.2 MB

2. Defining Variables, Constants, and Calculations - 35:53

    Introducing Excel VBA data types - 6:07 - 6.7 MB
    Declaring variables and requiring declaration before use - 4:22 - 4.2 MB
    Managing variable scope - 3:33 - 3.7 MB
    Defining static variables and constants - 4:52 - 5.1 MB
    Creating a calculation using mathematical operators - 3:33 - 3.6 MB
    Defining arrays - 7:16 - 7.1 MB
    Defining and using object variables - 2:58 - 2.8 MB
    Streamlining code references using With...End With statements - 3:12 - 3.6 MB

3. Adding Logic to Your VBA Code - 20:43

    Repeating a task using a For...Next loop - 2:35 - 2.5 MB
    Stepping through all items of a collection using a For...Each loop - 3:11 - 3.1 MB
    Repeating a task with a Do loop - 5:24 - 5.3 MB
    Performing a task when conditions are met using If...Then - 7:40 - 7.2 MB
    Selecting which action to take using a Case statement - 1:53 - 1.8 MB

4. Debugging Your VBA Code - 19:53

    Managing errors using On Error statements - 5:02 - 4.7 MB
    Stepping through a subroutine or function - 5:23 - 5.6 MB
    Setting breakpoints in the code - 2:36 - 2.6 MB
    Verifying output using the Immediate window - 3:52 - 3.8 MB
    Watching a value in a routine - 3:00 - 2.9 MB

5. Managing Workbook Elements and Data in VBA - 32:51

    Writing a value to a cell - 3:40 - 3.6 MB
    Cutting, copying, and pasting cell data - 5:01 - 5.1 MB
    Finding values in cells - 4:19 - 4.2 MB
    Referring to cells using the Offset function - 2:57 - 3.0 MB
    Concatenating text strings - 4:16 - 4.0 MB
    Returning part of a string - 2:55 - 2.8 MB
    Managing worksheets with VBA - 5:52 - 5.6 MB
    Managing workbooks with VBA - 3:51 - 3.9 MB

6. Adding Advanced Elements to Your Workbook - 12:07

    Turning off screen updating within a macro - 2:26 - 2.5 MB
    Using worksheet functions in a macro - 2:26 - 2.4 MB
    Acquiring values using an input box or message box - 5:05 - 5.1 MB
    Calling a subroutine from another subroutine - 2:10 - 2.0 MB

7. Using Excel Events in Your VBA Code - 12:29

    Running a procedure when you open, close, or save a workbook - 4:21 - 4.3 MB
    Running a procedure when a cell range changes - 4:35 - 4.6 MB
    Triggering a procedure using a specific key sequence - 3:33 - 3.7 MB

8. Putting It All Together - 06:13

    Applying techniques in a Capstone project - 6:13 - 6.1 MB

Conclusion - 00:17

    Goodbye - 0:17 - 0.2 MB