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

Introduction

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

Introducing Visual Basic for Applications

Introduce object-oriented programming
Examine the Excel object model
Work in the Visual Basic Editor
Set VBA project properties
Create, export, and delete code modules
Create a subroutine
Create a function
Add comments to your code
Run a VBA procedure
Add code to a recorded macro

Defining Variables, Constants, and Calculations

Introduce Excel VBA data types
Declare variables and require declaration before use
Manage variable scope
Define static variables and constants
Create a calculation using mathematical operators
Define arrays
Define and use object variables
Streamline code references using With End With statements
Challenge - Define variables, constants, and calculations
Solution - Define variables, constants, and calculations

Adding Logic to Your VBA Code

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

Debugging Your VBA Code

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

Manage Workbook Elements and Data in VBA

Write a value to a cell
Cut, copy, and paste cell data
Find values in cells
Refer to cells using the OFFSET property
Concatenate text strings
Return part of a string
Manage worksheets with VBA
Manage workbooks with VBA
Challenge - Manage workbook elements and data in VBA
Solution - Manage workbook elements and data in VBA

Adding Advanced Elements to Your VBA Code

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

Using Excel Events in Your VBA Code

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

Conclusion

Further resources