Who should attend
This class is for Excel users who need to add interactive features and automations to their workbooks and spreadsheets.
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.
Day 1 Topics:
Introduction to VBA Usage and Terminology
Creating Decision Structures
Day 2 Topics:
Creating Looping Structures
Designing Custom Dialog Boxes
Debugging and Error Handling
“The 2-day session far exceeded my expectations and Josh did a great job presenting the material that was digestible.”
“Great class, great foundation to build on for using VBA.”
“The instructor was great and really went above and beyond to ensure that the class took away a greater understanding of the material and the application in general.”
“Great learning experience.”
“I am no longer intimidated by VBA, The options are endless if you know VBA, VBA can be fun. I absolutely LOVED this course!”