An introduction to Excel VBA programming : with applications in finance and insurance

301 Pages·2017·31.03 MB·English

by  Gan

AN INTRODUCTION TO
EXCEL VBA PROGRAMMING
with Applications in Finance and Insurance

A N I N T R O D U C T I O N T O
EXCEL VBA PROGRAMMING
with Applications in Finance and Insurance
GUOJUN GAN
University of Connecticut
Storrs
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2017 by Taylor & Francis Group, LLC
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Printed on acid-free paper
Version Date: 20170217
International Standard Book Number-13: 978-1-1381-9715-2 (Hardback)
This book contains information obtained from authentic and highly regarded sources. Reasonable
efforts have been made to publish reliable data and information, but the author and publisher cannot
assume responsibility for the validity of all materials or the consequences of their use. The authors and
publishers have attempted to trace the copyright holders of all material reproduced in this publication
and apologize to copyright holders if permission to publish in this form has not been obtained. If any
copyright material has not been acknowledged please write and let us know so we may rectify in any
future reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced,
transmitted, or utilized in any form by any electronic, mechanical, or other means, now known or
hereafter invented, including photocopying, microfilming, and recording, or in any information
storage or retrieval system, without written permission from the publishers.
For permission to photocopy or use material electronically from this work, please access
www.copyright.com (http://www.copyright.com/) or contact the Copyright Clearance Center, Inc.
(CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization
that provides licenses and registration for a variety of users. For organizations that have been granted
a photocopy license by the CCC, a separate system of payment has been arranged.
Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and
are used only for identification and explanation without intent to infringe.
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
Toallmystudents.

Contents
ListofFigures xi
ListofTables xiii
Preface xv
I VBAPreliminaries 1
1 IntroductiontoVBA 3
1.1 GettingStarted . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.3 VBAClasses . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.4 TheExcelMacroRecorder . . . . . . . . . . . . . . . . . . . . 13
1.5 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 ExcelObjects 17
2.1 ExcelObjectModel . . . . . . . . . . . . . . . . . . . . . . . . 17
2.2 ApplicationObject . . . . . . . . . . . . . . . . . . . . . . . . 21
2.3 WorkbookObjects . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.4 WorksheetObjects . . . . . . . . . . . . . . . . . . . . . . . . 28
2.5 RangeObject . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2.6 WorksheetFunctionObject . . . . . . . . . . . . . . . . . . . 37
2.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3 Variables,DataTypes,andScopes 43
3.1 VariableDeclarationandDataTypes . . . . . . . . . . . . . . 43
3.2 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.3 Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.4 Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.5 Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
3.6 Scopes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
3.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
vii
viii Contents
4 OperatorsandControlStructures 75
4.1 Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
4.2 FlowControl . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
4.3 Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
4.4 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
5 Functions,Events,andFileIO 95
5.1 User-DefinedFunctions . . . . . . . . . . . . . . . . . . . . . 95
5.2 Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
5.3 FileIO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
5.4 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
6 ErrorHandlingandDebugging 109
6.1 ErrorHandling . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
6.2 DebuggingVBACode . . . . . . . . . . . . . . . . . . . . . . . 115
6.3 BestPracticesofVBACoding . . . . . . . . . . . . . . . . . . 120
6.4 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
II Applications 127
7 GeneratingPaymentSchedules 129
7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
7.2 PublicHolidaysintheUnitedStates . . . . . . . . . . . . . . 130
7.3 JulianandGregorianCalendars . . . . . . . . . . . . . . . . . 131
7.4 DayCountConventions . . . . . . . . . . . . . . . . . . . . . 133
7.5 BusinessDayConventions . . . . . . . . . . . . . . . . . . . . 134
7.6 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 135
7.6.1 MDateModule . . . . . . . . . . . . . . . . . . . . . . 135
7.6.2 MHolidayModule . . . . . . . . . . . . . . . . . . . . 147
7.6.3 MScheduleModule . . . . . . . . . . . . . . . . . . . 151
7.6.4 MInterfaceModule . . . . . . . . . . . . . . . . . . . . 156
7.7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
8 BootstrappingYieldCurves 161
8.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
8.2 Interpolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
8.3 BootstrappingYieldCurves . . . . . . . . . . . . . . . . . . . 164
8.4 FindingRootsofanEquation . . . . . . . . . . . . . . . . . . 167
8.5 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 168
8.5.1 MCurveModule . . . . . . . . . . . . . . . . . . . . . 168
8.5.2 MInterfaceModule . . . . . . . . . . . . . . . . . . . . 179
Contents ix
8.6 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
9 GeneratingRisk-NeutralScenarios 183
9.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
9.2 Black–ScholesModel . . . . . . . . . . . . . . . . . . . . . . . 184
9.3 GeneratingRandomNormalNumbers . . . . . . . . . . . . 186
9.4 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 187
9.4.1 MGeneratorModule . . . . . . . . . . . . . . . . . . . 187
9.4.2 MInterfaceModule . . . . . . . . . . . . . . . . . . . . 195
9.5 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
10 ValuingaGMDB 199
10.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
10.2 LifeTableConstruction . . . . . . . . . . . . . . . . . . . . . 201
10.3 GMDBValuation . . . . . . . . . . . . . . . . . . . . . . . . . 202
10.4 GreekCalculation . . . . . . . . . . . . . . . . . . . . . . . . . 205
10.5 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 205
10.5.1 MLifeTableModule . . . . . . . . . . . . . . . . . . . 206
10.5.2 MGMDBModule . . . . . . . . . . . . . . . . . . . . . 209
10.5.3 MInterfaceModule . . . . . . . . . . . . . . . . . . . . 214
10.6 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
11 ConnectingtoDatabases 217
11.1 ActiveX®DataObjects . . . . . . . . . . . . . . . . . . . . . . 218
11.2 SQLQueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
11.3 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 221
11.3.1 MDatabaseModule . . . . . . . . . . . . . . . . . . . 221
11.3.2 MInterfaceModule . . . . . . . . . . . . . . . . . . . . 229
11.4 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
12 Object-OrientedProgramming 233
12.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
12.2 Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
12.3 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 234
12.3.1 CZeroCurveModule . . . . . . . . . . . . . . . . . . . 235
12.3.2 CSwapModule . . . . . . . . . . . . . . . . . . . . . . 238
12.3.3 CBootstrapperModule . . . . . . . . . . . . . . . . . 241
12.3.4 OOBootstrappingTool . . . . . . . . . . . . . . . . . 246
12.4 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249