An Introduction to Excel VBA Programming

Guojun Gan is an Assistant Professor in the Department of Mathematics at the University of Connecticut, where he has been since August 2014. Prior to that, he worked at Manulife Financial, a large life insurance company in Toronto, Canada, for six years. His research focuses on data mining and actuarial science. He has published four books and several papers related to data mining, actuarial science, and mathematical finance.

I VBA Preliminaries

Introduction to VBA

Getting Started

Modules

VBA Classes

The ExcelMacro Recorder

Excel Objects

The Excel ObjectModel

The Application Object

TheWorkbook Objects

TheWorksheet Objects

TheWorksheetFunction Object

Variables, Data Types, and Scopes

Variable Declaration and Data Types

Arrays

Constants

Strings

Dates

Scopes

Operators and Control Structures

Operators

Flow Control

Loops

Functions, Events and File IO

User-Defined Functions

Events

File IO

Error Handling and Debugging 109

Error Handling

Debugging VBA Code

Best Practices of VBA Coding

II Applications

Generating Payment Schedules

Introduction

Public Holidays in the United States

The Julian and Gregorian Calendars

Day Count Conventions

Business Day Conventions

Implementation

TheMDateModule

TheMHolidayModule

TheMScheduleModule

TheMInterfaceModule

Bootstrapping Yield Curves

Introduction

Interpolation

Bootstrapping Yield Curves

Finding Roots of an Equation

Implementation

TheMCurveModule

TheMInterfaceModule

Generating Risk-Neutral Scenarios

Introduction

The Black-ScholesModel

Generating Random Normal Numbers

Implementation

TheMGeneratorModule

TheMInterfaceModule

Valuing a GMDB

Introduction

Life Table Construction

GMDB Valuation

Greek Calculation

Implementation

TheMLifeTableModule

TheMGMDBModule

TheMInterfaceModule

Connecting to Databases

ActiveX Data Objects

SQL

Implementation

TheMDatabaseModule

TheMInterfaceModule

Object-Oriented Programming 233

Introduction

Objects

Implementation

The CZeroCurveModule

The CSwapModule

The CBootstrapperModule

An OO Bootstrapping Tool

Solutions to Selected Exercises