Learning VBA in Excel (2019)

Course details

The Visual Basic for Applications (VBA) programming language allows you to automate routine tasks in Excel—and it's not as hard to learn as most people think. In this course, Excel expert Curt Frye offers a crash course on VBA, sharing techniques that can help users of Excel 2007 or later get more out of this powerful program. Curt covers creating subroutines and functions to hold code, and provides a solid grounding in the Excel object model. Programming techniques are demonstrated through real-world examples. Exercise files accompany the course.

Learning objectives
Recall the limitations of a subroutine.
List the steps taken to record a macro with a PivotTable.
Identify the keyword used to resize an array.
Explain what a VBA watch does.
Name the Excel function that uses the distance from one cell to another.
Determine how to delete a worksheet.
Identify the type of variables that can be used in more than one subroutine.
Summarize the effects of assigning a macro to a function key.

Concepts

0. Introduction

01 - Dive into using VBA in Excel
02 - What you should know

1. Visual Basic for Applications

03 - Introduce object-oriented programming
04 - Examine the Excel object model
05 - Work in the Visual Basic Editor
06 - Set VBA project properties
07 - Create, export, and delete code modules
08 - Create a subroutine
09 - Create a function
10 - Add comments to your code
11 - Run a VBA routine
12 - Add code to a recorded macro

2. Defining Variables, Constants, and Calculations

13 - Introduce Excel VBA data types
14 - Declare variables and require declaration before use
15 - Manage variable scope
16 - Define static variables and constants
17 - Create a calculation using mathematical operators
18 - Define arrays
19 - Define and use object variables
20 - Streamline code references using With End With statements

3. Adding Logic to Your VBA Code

21 - Repeat a task using a ForNext loop
22 - Step through all items of a collection using a ForEach loop
23 - Repeat a task using a Do loop
24 - Perform a task when conditions are met using an IfThen statement
25 - Select which action to take using a Case statement

4. Debugging Your VBA Code

26 - Manage errors using On Error statements
27 - Step through a subroutine or function
28 - Set breakpoints in your code
29 - Verify output using the Immediate window
30 - Watch a value in a routine

5. Managing Workbook Elements and Data in VBA

31 - Write a value to a cell
32 - Cut, copy, and paste cell data
33 - Find values in cells
34 - Refer to cells using the OFFSET function
35 - Concatenate text strings
36 - Return part of a string
37 - Manage worksheets with VBA
38 - Manage workbooks with VBA

6. Adding Advanced Elements to Your Workbook

39 - Turn off screen updating when you run a macro
40 - Use worksheet functions in a macro
41 - Acquire values using an input box or message box
42 - Call a subroutine from another subroutine

7. Using Excel Events in Your VBA Code

43 - Run a procedure when you open, close, or save a workbook
44 - Run a procedure when a cell range changes
45 - Trigger a procedure using a specific key sequence

8. Putting It All Together

46 - Apply techniques in a capstone project

Conclusion

47 - Further information