Excel VBA

Sooner or later, every Excel user runs into a problem that can’t be solved with a built-in function or feature. What if, for example, you had a worksheet with 20 cities and you wanted to put their current temperatures in each adjacent cell. There’s no feature for that in Excel! That’s where Visual Basic for Applications (“VBA”) comes in. VBA is a programming language built right into Excel and other MS Office applications. You can write VBA code to grab parts of Excel (cells, rows, menus, etc.) — and even data from the web — and programmatically change their values. You can also change their properties: sizes, colors, fonts, etc. Automating repetitive tasks is another popular use of VBA.

The Coding Environment. Microsoft provides a separate free app to help you write code, the “Visual Basic Code Editor.” You can launch it right from Excel. It has features for organizing your code, giving you hints about what the functions should look like, and debugging your program while watching it run, step-by-step.

Programming Basics. Don’t worry if you haven’t written code before. Microsoft has gone out of its way to make VBA as simple as possible. We’ll cover the basics (no pun intended) of coding: grabbing onto Excel cells and changing their properties, getting input from the user, tracking changeable values in “variables,” executing different chunks of code depending on if-this-then-that decisions, and repeating a chunk of code multiple times.

Interacting with your User. The power of VBA becomes clear when you can ask the person looking at your spreadsheet to click on a button, have a nice-looking form pop up, and let them type in some data or choose options from a dropdown box — and then have your custom VBA code process that data and make intelligent updates to the underlying spreadsheet. We’ll show you how to build those forms, visually, with a drag-and-drop editor.

Error Handling. Coding errors are about as inevitable as death and taxes. Fortunately both the code editor and the VBA language itself provide a number of ways to ease the burden of debugging, whether it’s stepping through your code and executing it line-by-line or by anticipating errors in advance and providing alternate chunks of code to execute. Having good debugging skills and mastery of the tools is critical.

Learning Outcomes:

1. Understanding How Excel, VBA, and the Coding Editor Fit Together
2. Getting Around the Visual Basic Coding Editor
3. Accessing and Manipulating Excel Objects
4. Using Variables, Control Structures, Loops and Subroutines
5. Getting User Input and Creating Custom Dialog Boxes
6. Debugging and Error Handling

Day 1 Topics:

Introduction to VBA Usage and Terminology

• Defining VBA and What It Can Do
• Comparing Macros vs. Programming
• Exploring the Visual Basic Editor Tool
• Activating and Using the Developer Tab

Programming Basics

• Understanding VBA Syntax and Help Resources
• Creating VBA Sub Procedures
• Getting Information with Input Boxes
• Delivering Information with Message Boxes
• Using Variables for Flexibility
• Understanding Scope of Variables and Procedures
• Defining and Using Functions and Sub Procedures

Creating Decision Structures

• Understanding How a Decision Structure Interprets Conditions
• Using IF…Then Statements
• Creating IF… Then… ElseIF Statements
• Applying Select Case Statements

Day 2 Topics:

Creating Looping Structures

• Using Loops to Repeat Processes
• Implementing a Fixed Iteration Loop
• Creating Infinite Loops
• Using Loops with Workbook Objects

Designing Custom Dialog Boxes

• Creating User Forms to Collect Data
• Using the Form Object Designer Tool
• Connecting Forms to Events in the Workbook
• Processing Collected Data with VBA

Debugging and Error Handling

• Examining Programming Errors
• Using VBA Debugging Tools
• Applying Error Handling within VBA Code