Vba in Excel

An Introduction to VBA in Excel 

Robert L. McDonald†

First draft: November, 1995

November 3, 2000

Abstract

This is a tutorial showing how to use the macro facility in Microsoft

Office—Visual Basic for Applications—to simplify analytical tasks in

Excel.

Contents

1 Introduction 3

2 Calculations without VBA 3

3 How to Learn VBA 4

4 Calculations with VBA 5

4.1 Creating a simple function . . . . . . . . . . . . . . . . . . . . 5

4.2 A Simple Example of a Subroutine . . . . . . . . . . . . . . . 7

4.3 Creating a Button to Invoke a Subroutine . . . . . . . . . . . 7

4.4 Functions can call functions . . . . . . . . . . . . . . . . . . . 8

4.5 Illegal Function Names . . . . . . . . . . . . . . . . . . . . . . 9

4.6 Differences Between Functions and Subroutines . . . . . . . . 9

Copyright

c 1995-2000 Robert L. McDonald. Thanks to Jim Dana for asking stimulating

questions about VBA.

†Finance Dept, Kellogg School, Northwestern University, 2001 Sheridan Rd., Evanston,

IL 60208, tel: 847-491-8344, fax: 847-491-5719, E-mail: r-mcdonald@northwestern.edu.

CONTENTS 2

5 Storing and Retrieving Variables in a Worksheet 10

5.1 Using a named range to read and write numbers from the

spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

5.2 Reading and Writing to Cells Which are not Named. . . . . . 12

5.3 Using the "Cells" Function to Read and Write to Cells. . . . 13

6 Using Excel Functions 13

6.1 Using VBA to compute the Black-Scholes formula . . . . . . 13

6.2 The Object Browser . . . . . . . . . . . . . . . . . . . . . . . 15

7 Checking for Conditions 16

8 Arrays 17

8.1 Defining Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . 18

9 Iterating 19

9.1 A simple for loop . . . . . . . . . . . . . . . . . . . . . . . . . 20

9.2 Creating a binomial tree . . . . . . . . . . . . . . . . . . . . . 20

9.3 Other kinds of loops . . . . . . . . . . . . . . . . . . . . . . . 22

10 Reading and Writing Arrays 22

10.1 Arrays as Output . . . . . . . . . . . . . . . . . . . . . . . . . 23

10.2 Arrays as Inputs . . . . . . . . . . . . . . . . . . . . . . . . . 24

10.2.1 The Array as a Collection . . . . . . . . . . . . . . . . 24

10.2.2 The Array as an Array . . . . . . . . . . . . . . . . . . 25

11 Miscellany 26

11.1 Getting Excel to generate your macros for you . . . . . . . . 26

11.2 Using multiple modules . . . . . . . . . . . . . . . . . . . . . 27

11.3 Recalculation speed . . . . . . . . . . . . . . . . . . . . . . . 27

11.4 Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

11.5 Creating an Add-in . . . . . . . . . . . . . . . . . . . . . . . . 28

12 A Simulation Example 29

12.1 What is the algorithm? . . . . . . . . . . . . . . . . . . . . . 29

12.2 VBA code for this example. . . . . . . . . . . . . . . . . . . . 30

12.3 A trick to speed up the calculations . . . . . . . . . . . . . . 32

Copyright

c 1995-2000,