An introduction to Excel VBA programming
Visual Basic for Applications (VBA) is a programming language created by Microsoft® that can be used to automate operations in Microsoft Excel, which is perhaps the most frequently used computer software program for manipulating data and building models in banks and insurance companies.
One advantage of VBA is that it enables you to do anything that you can do manually in Excel and do many things that Excel does not allow you to do manually. As a powerful tool, VBA has been used by actuaries and financial analysts to build actuarial and financial models.
In the spring of 2016, I was assigned to teach the course “Math3550: Programming for Actuaries,” which is taken primarily by junior and senior undergraduate students majoring in actuarial science at the University of Connecticut. This course explores how an actuary uses computers to solve common actuarial problems and teaches students how to design, develop, test, and implement programs using Microsoft Excel with VBA.
Since existing books on Excel VBA do not have exercises or applications related to actuarial science, I started to write lecture notes for this course. This textbook has grown out of those lecture notes. This textbook has been written for undergraduate students majoring in actuarial science who wish to learn the basic fundamentals and applications of Excel VBA. In doing so, this book does not assume that readers have any prior programming experience. This book will also be of use to actuaries and financial analysts working in insurance companies and banks who wish to learn Excel VBA.
This textbook is divided into two parts: preliminaries of Excel VBA programming and some applications of VBA in finance and insurance. The preliminaries covered in the first part include how to run VBA programs, modules, best practices of VBA coding, the Excel object model, variables, control
statements, functions, and error handling, among many other things. The applications of VBA introduced in the second part include generating regular payment schedules, bootstrapping yield curves, creating risk-neutral scenarios, pricing a guarantee embedded in a variable annuity contract,
how to connect to databases, and object-oriented programming in VBA.
The best way to learn programming is by doing. I encourage readers to practice the VBA code presented in the book. The book also contains many exercises. Sample solutions of some exercises are given in the appendix of this book. Readers should explore the exercises before looking at the
Table of contents
Introduction to VBA
VBA refers to Visual Basic for Applications and is a programming language created byMicrosoft® to automate operations inMicrosoft Office® applications such as Excel®, Access®,Word®, PowerPoint®, and Outlook®. VBA is a powerful tool that enables you to do whatever you need to do in your job.
With VBA, you can do anything that you can do manually and do many things that Excel® does not allow you to do manually. In particular, VBA allows you to
• automate a recurring task.
• automate a repetitive task.
• run a macro automatically if an event occurs.
• build custom functions (i.e., user-defined functions).
• build a customized interface.
• manipulate files and folders.
• manipulateMicrosoft Office® applications.
• work withWindows® by calling its Application Programming Interfaces
• work with other applications through Dynamic-Link Libraries (DLLs).
In this chapter, we introduce some basic concepts of VBA programming
in Excel®. After studying this chapter, readers will be able to
• run VBAmacros or programs.
• understand regular and class modules.
• use the Excel® macro record to create VBAmacros.