Vba Introduction
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 read and 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.1
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 read and 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.1