Excel VBA 2 - the Excel object model

Now that we know the basics, let’s learn about the Excel object model. This is what makes it “Excel VBA”.

Objectives

In this module you will learn the basics of Excel VBA programming. By the end of it, you should understand and/or be able to use/do the following:

  • Understand the role of VBA in spreadsheet application development
  • Use the VB Editor effectively
  • Be able to create and modify basic programs using variables, conditional logic, loops, input boxes, message boxes, Excel objects (worksheets, ranges, etc.), and VB functions.

Readings

  • In this 2-week crash course we'll be mostly drawing on material from Chapters 1-10 in VBA for Modelers.
  • Chapter 5 (Getting started with VBA) is VERY IMPORTANT.
  • Chapter 6 (Range object) is ALSO VERY IMPORTANT in terms of learning the most important object in Excel programming.
  • Later in the term we'll be using other chapters when we learn more advanced spreadsheet application development skills.

Downloads

  • Same download as VBA 1 session

Screencasts and other activities

Excel VBA 2 - The Excel Object Model

Now that we've covered the very basics of programming, let's take our first real dive into the Excel Object model.

The first video ended with a challenge to solve Exercise 5.3. Let's see how it's done.

In this next video I'll show how to do the extensions to Exercise 5.3 mentioned in the slides.

After Exercise 5.3, we see the need to learn about control logic - things like loops and if-then statements.

Now we will revisit the First Program you did earlier and pick up a few more debugging techniques such as Watches and Breakpoints.

We'll use a simple averaging program to see loops and if-then in action.

We'll conclude this part of the VBA crash course with an in-depth look at the Range object.

Several people in the past have asked questions related to copying a range in a spreadsheet to other places within the workbook or into another workbook. So, I worked up a quick set of examples. Download and extract range_copy.zip and then open the range_copy.xlsm file and you'll find buttons on the first sheet that are associated with different examples. The code is heavily commented.

Putting it all together - the Break Even problem

Now, let's put it all together with a little exercise in which we'll use VBA to mimic the functionality of a one-way data table. I've broken this up into four short screencasts. There's also links below to the files uses in this exercise. I HIGHLY recommend that you work through this before attempting the VBA homework as it will give you a chance to make sure you understand how to use basic range referencing, looping, and conditional logic to accomplish a typical Excel task. All the files you'll need are in the \BreakEven subfolder of the Downloads file.