Excel: Learning VBA

Course details

VBA allows you to automate routine tasks in Excel, and it's easier to learn than you might think. In this course, Excel expert Curt Frye presents a thorough introduction to the Visual Basic for Applications programming language. Learn how to create subroutines and functions to hold code, and gain a solid grounding in the Excel object model. Explore programming techniques through real-world examples and the exercise files that accompany the course. Find out how to work with the Visual Basic Editor, add code to a macro, and create, export, and delete code modules. Go over declaring and using variables, managing variable scope, and defining arrays. Discover ways to use VBA to manage your workbooks and spreadsheets. Learn how to simplify repeating tasks with loops and debug VBA code. Practice cutting, copying, and pasting cell data, as well as running and triggering event procedures. Plus, use practical challenges to apply what you’ve learned.

Concepts

0. Introduction

01 - Extend the capabilities of Excel using VBA
02 - What you should know before starting

1. Introducing 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 procedure
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
21 - Challenge - Define variables, constants, and calculations
22 - Solution - Define variables, constants, and calculations

3. Adding Logic to Your VBA Code

23 - Repeat a task using a For Next loop
24 - Step through all items of a collection using a For Each loop
25 - Repeat a task using a Do loop
26 - Execute code conditionally using an If Then statement
27 - Select which action to take using a Case statement
28 - Challenge - Add logic to your code
29 - Solution - Add logic to your code

4. Debugging Your VBA Code

30 - Manage errors using On Error statements
31 - Step through a subroutine or function
32 - Set breakpoints in your code
33 - Verify output using the Immediate window
34 - Watch a value in a routine
35 - Challenge - Debug your VBA code
36 - Solution - Debug your VBA code

5. Manage Workbook Elements and Data in VBA

37 - Write a value to a cell
38 - Cut, copy, and paste cell data
39 - Find values in cells
40 - Refer to cells using the OFFSET property
41 - Concatenate text strings
42 - Return part of a string
43 - Manage worksheets with VBA
44 - Manage workbooks with VBA
45 - Challenge - Manage workbook elements and data in VBA
46 - Solution - Manage workbook elements and data in VBA

6. Adding Advanced Elements to Your VBA Code

47 - Turn off screen updating when you run a macro
48 - Use worksheet functions in a macro
49 - Acquire values using an input box or message box
50 - Call a subroutine from another subroutine
51 - Challenge - Add advanced elements to your workbook
52 - Solution - Add advanced elements to your workbook

7. Using Excel Events in Your VBA Code

53 - Run a procedure when you open, close, or save a workbook
54 - Run a procedure when a cell range changes
55 - Trigger a procedure using a specific key sequence
56 - Challenge - Use Excel events in your VBA code
57 - Solution - Use Excel events in your VBA code

Conclusion

58 - Further resources