Vba 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 2 Calculations without VBA 3 How to Learn VBA 4 Calculations with VBA 4.1 Creating a simple function . . . . . .. . . . . . 4.2 A Simple Example of a Subroutine . . . . . . . 4.3 Creating a Button to Invoke a Subroutine . . . 4.4 Functions can call functions . . . . . . . . . . . 4.5 Illegal Function Names . . . . . . . . . . . . . . 4.6 Differences Between Functions and Subroutines 3 3 4 5 5 7 7 8 9 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 5.1 Using a named range to readand write numbers from spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . 5.2 Reading and Writing to Cells Which are not Named. . . 5.3 Using the “Cells” Function to Read and Write to Cells.

10 the . . . . . . . . . 11 12 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 10.1Arrays as Output . . . . . . . . . 10.2 Arrays as Inputs . . . . . . . . . 10.2.1 The Array as a Collection 10.2.2 The Array as an Array . . 22 23 24 24 25 26 26 27 27 28 28

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

11 Miscellany 11.1 Getting Excel to generate yourmacros 11.2 Using multiple modules . . . . . . . . 11.3 Recalculation speed . . . . . . . . . . 11.4 Debugging . . . . . . . . . . . . . . . . 11.5 Creating an Add-in . . . . . . . . . . .

for . . . . . . . .

you . . . . . . . . . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

12 A Simulation Example 29 12.1 What is thealgorithm? . . . . . . . . . . . . . . . . . . . . . 29 12.2 VBA code for this example. . . . . . . . . . . . . . . . . . . . 30 12.3 A trick to speed up the calculations . . . . . . . . . . . . . . 32

Copyright c 1995-2000, Robert L. McDonald. November 3, 2000

2 CALCULATIONS WITHOUT VBA

3

1

Introduction

Visual Basic for Applications, Excel’s powerful built-in programming language,permits you to easily incorporate user-written functions into a spreadsheet.1 You can easily calculate Black-Scholes and binomial option prices, for example. Lest you think VBA is something esoteric which you will never otherwise need to know, VBA is now the core macro language for all Microsoft’s office products, including Word. It has also been incorporated into software from other vendors. You neednot write complicated programs using VBA in order for it to be useful to you. At the very least, knowing VBA will make it easier for you to analyze relatively complex problems for yourself. This document presumes that you have a basic knowledge of Excel, including the use of built-in functions and named ranges. I do not presume that you know anything about writing macros or programming. The...