Excel VBA 1 - the basics

We will start with a three week crash course in Excel VBA (2 weeks) and spreadsheet application development (1 week). Make sure you have the VBA for Modelers textbook.

A quote from Enterprise Data Analysis and Visualization: An Interview Study on the growing need for technically adept analysts:

When discussing recruitment, one Chief Scientist said "analysts that can't program are disenfranchised here”

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

Screencasts and other activities

Excel VBA 1 - Intro to VBA programming

Read Chapters 1-3 in VBA for Modelers. In Section 3.5 of VBA for Modelers, follow along and do "A First Program" on pages 24-29 (page numbers may vary by edition). Name your file First_Program-YourName.xlsm. Depending on which edition of the VBA book you bought, use the appropriate file.

Before we really get into programming, let's do some Macro Recording to get a preview of VBA and the Excel Object model.

Now, let's become familiar with the VB Editor and get some understanding of basic logic flow in computer programs.

Ok, now let's dive in and learn about variables, Dim statements, and some basic concepts regarding subroutine and function procedures. We'll also learn basic debugging using stepping and the Immediate Window.

Exercise 5.1 - Displaying a Message.

You should try to do this now yourself. You can find the solution in the \Exercise5_1and2 folder inside of the Downloads folder.

And we will end this first session on VBA by learning about built in VBA functions via a little string parsing exercise.

Exercise 5.2 - Displaying a Message.

You should try to do this now yourself. You can find the solution in the \Exercise5_1and2 folder inside of the Downloads folder.

Next week we will dive into the Excel Object Model with a focus on Range Objects (Chapter 6).

Explore (OPTIONAL)

Learn about Grace Hopper, a true software pioneer. You'll also find out where the programming term bug came from.

Nice example of how VBA can be used to generate custom spreadsheets for non-technical end-users.

Good example of using VBA to encapsulate complex, model specific, formulas in UDF's (user defined functions)