Principles of Financial Modelling

MICHAEL REES, D.PHIL., MBA, operates globally to help senior executives to solve their most complex problems in the areas of decision support, business strategy, value-creation, risk assessment, and optimisation. He combines practical experience from top firms with an exceptional analytic record, and is among the world’s leading authors and instructors in the field of financial and risk modelling. His special interest is in cases where issues in strategy, business economics, and valuation are best addressed using practical advanced quantitative approaches. He has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark. He has approximately 30 years’ business and finance experience, in many sectors, including oil, gas, energy and resources, private equity, health care, biotechnology, chemicals, construction, engineering, and insurance.

Preface xxv

About the Author xxvii

About the Website xxix

Part One Introduction to Modelling, Core Themes and Best Practices 1

Chapter 1 Models of Models 3

Introduction 3

Context and Objectives 3

The Stages of Modelling 3

Backward Thinking and Forward Calculation Processes 4

Chapter 2 Using Models in Decision Support 7

Introduction 7

Benefits of Using Models 7

Providing Numerical Information 7

Capturing Influencing Factors and Relationships 7

Generating Insight and Forming Hypotheses 8

Decision Levers, Scenarios, Uncertainties, Optimisation,  Risk Mitigation and Project Design 8

Improving Working Processes, Enhanced Communications and Precise Data Requirements 9

Challenges in Using Models 9

The Nature of Model Error 9

Inherent Ambiguity and Circularity of Reasoning 10

Inconsistent Scope or Alignment of Decision and Model 10

The Presence on Biases, Imperfect Testing, False Positives and Negatives 11

Balancing Intuition with Rationality 11

Lack of Data or Insufficient Understanding of a Situation 12

Overcoming Challenges: Awareness, Actions and Best Practices 13

Chapter 3 Core Competencies and Best Practices: Meta-themes 15

Introduction 15

Key Themes 15

Decision-support Role, Objectives, Outputs and Communication 16

Application Knowledge and Understanding 17

Skills with Implementation Platform 17

Defining Sensitivity and Flexibility Requirements 18

Designing Appropriate Layout, Input Data Structures and Flow 20

Ensuring Transparency and Creating a User-friendly Model 20

Integrated Problem-solving Skills 21

Part Two Model Design and Planning 23

Chapter 4 Defining Sensitivity and Flexibility Requirements 25

Introduction 25

Key Issues for Consideration 25

Creating a Focus on Objectives and Their Implications 26

Sensitivity Concepts in the Backward Thought and Forward Calculation

Processes 26

Time Granularity 30

Level of Detail on Input Variables 30

Sensitising Absolute Values or Variations from Base Cases 31

Scenarios Versus Sensitivities 32

Uncertain Versus Decision Variables 33

Increasing Model Validity Using Formulae 34

Chapter 5 Database Versus Formulae-driven Approaches 37

Introduction 37

Key Issues for Consideration 37

Separating the Data, Analysis and Presentation (Reporting) Layers 37

The Nature of Changes to Data Sets and Structures 39

Focus on Data or Formulae? 40

Practical Example 42

Chapter 6 Designing the Workbook Structure 47

Introduction 47

Designing Workbook Models with Multiple Worksheets 47

Linked Workbooks 47

Multiple Worksheets: Advantages and Disadvantages 48

Generic Best Practice Structures 49

The Role of Multiple Worksheets in Best Practice Structures 49

Type I: Single Worksheet Models 50

Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50

Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51

Further Comparative Comments 51

Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52

Multi-sheet or “Three Dimensional” Formulae 53

Using Excel’s Data/Consolidation Functionality 54

Consolidating from Several Sheets into a Database Using a Macro 55

User-defined Functions 56

Part Three Model Building, Testing and Auditing 57

Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59

Introduction 59

Approaches to Identifying the Drivers of Complexity 59

Taking the Place of a Model Auditor 59

Example: Creating Complexity in a Simple Model 60

Core Elements of Transparent Models 61

Optimising Audit Paths 62

Creating Short Audit Paths Using Modular Approaches 63

Creating Short Audit Paths Using Formulae Structure and Placement 67

Optimising Logical Flow and the Direction of the Audit Paths 68

Identifying Inputs, Calculations and Outputs: Structure and Formatting 69

The Role of Formatting 70

Colour-coding of Inputs and Outputs 70

Basic Formatting Operations 73

Conditional Formatting 73

Custom Formatting 75

Creating Documentation, Comments and Hyperlinks 76

Chapter 8 Building Robust and Transparent Formulae 79

Introduction 79

General Causes of Mistakes 79

Insufficient Use of General Best Practices Relating to Flow, Formatting,

Audit Paths 79

Insufficient Consideration Given to Auditability and Other Potential Users 79

Overconfidence, Lack of Checking and Time Constraints 80

Sub-optimal Choice of Functions 80

Inappropriate Use or Poor Implementation of Named Ranges, Circular

References or Macros 80

Examples of Common Mistakes 80

Referring to Incorrect Ranges or To Blank Cells 80

Non-transparent Assumptions, Hidden Inputs and Labels 82

Overlooking the Nature of Some Excel Function Values 82

Using Formulae Which are Inconsistent Within a Range 83

Overriding Unforeseen Errors with IFERROR 84

Models Which are Correct in Base Case but Not in Others 85

Incorrect Modifications when Working with Poor Models 85

The Use of Named Ranges 85

Mechanics and Implementation 86

Disadvantages of Using Named Ranges 86

Advantages and Key Uses of Named Ranges 90

Approaches to Building Formulae, to Testing, Error Detection and Management 91

Checking Behaviour and Detecting Errors Using Sensitivity Testing 91

Using Individual Logic Steps 93

Building and Splitting Compound Formulae 94

Using Absolute Cell Referencing Only Where Necessary 96

Limiting Repeated or Unused Logic 96

Using Breaks to Test Calculation Paths 97

Using Excel Error Checking Rules 97

Building Error-checking Formulae 98

Handling Calculation Errors Robustly 100

Restricting Input Values Using Data Validation 100

Protecting Ranges 101

Dealing with Structural Limitations: Formulae and Documentation 102

Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105

Introduction 105

Key Considerations 105

Direct Arithmetic or Functions, and Individual Cells or Ranges? 105

IF Versus MIN/MAX 107

Embedded IF Statements 109

Short Forms of Functions 111

Text Versus Numerical Fields 112

SUMIFS with One Criterion 112

Including Only Specific Items in a Summation 113

AGGREGATE and SUBTOTAL Versus Individual Functions 114

Array Functions or VBA User-defined Functions? 115

Volatile Functions 115

Effective Choice of Lookup Functions 116

Chapter 10 Dealing with Circularity 117

Introduction 117

The Drivers and Nature of Circularities 117

Circular (Equilibrium or Self-regulating) Inherent Logic 117

Circular Formulae (Circular References) 118

Generic Types of Circularities 119

Resolving Circular Formulae 119

Correcting Mistakes that Result in Circular Formulae 120

Avoiding a Logical Circularity by Modifying the Model Specification 120

Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121

Resolving a Circularity Using Iterative Methods 122

Iterative Methods in Practice 123

Excel’s Iterative Method 123

Creating a Broken Circular Path: Key Steps 125

Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126

Practical Example 128

Using Excel Iterations to Resolve Circular References 129

Using a Macro to Resolve a Broken Circular Path 129

Algebraic Manipulation: Elimination of Circular References 130

Altered Model 1: No Circularity in Logic or in Formulae 130

Altered Model 2: No Circularity in Logic in Formulae 131

Selection of Approach to Dealing with Circularities: Key Criteria 131

Model Accuracy and Validity 132

Complexity and Transparency 133

Non-convergent Circularities 134

Potential for Broken Formulae 138

Calculation Speed 140

Ease of Sensitivity Analysis 140

Conclusions 141

Chapter 11 Model Review, Auditing and Validation 143

Introduction 143

Objectives 143

(Pure) Audit 143

Validation 144

Improvement, Restructuring or Rebuild 145

Processes, Tools and Techniques 146

Avoiding Unintentional Changes 146

Developing a General Overview and Then Understanding the Details 147

Testing and Checking the Formulae 151

Using a Watch Window and Other Ways to Track Values 151

Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153

Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155

Introduction 155

Overview of Sensitivity-related Techniques 155

DataTables 156

Overview 156

Implementation 157

Limitations and Tips 157

Practical Applications 160

Example: Sensitivity of Net Present Value to Growth Rates 160

Example: Implementing Scenario Analysis 160

Chapter 13 Using GoalSeek and Solver 163

Introduction 163

Overview of GoalSeek and Solver 163

Links to Sensitivity Analysis 163

Tips, Tricks and Limitations 163

Practical Applications 164

Example: Breakeven Analysis of a Business 165

Example: Threshold Investment Amounts 166

Example: Implied Volatility of an Option 167

Example: Minimising Capital Gains Tax Liability 167

Example: Non-linear Curve Fitting 169

Chapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171

Introduction 171

Practical Applications 172

Example: Running Sensitivity Analysis Using a Macro 172

Example: Running Scenarios Using a Macro 173

Example: Using a Macro to Run Breakeven Analysis with GoalSeek 173

Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175

Chapter 15 Introduction to Simulation and Optimisation 177

Introduction 177

The Links Between Sensitivity and Scenario Analysis,

Simulation and Optimisation 177

The Combinatorial Effects of Multiple Possible Input Values 177

Controllable Versus Non-controllable: Choice Versus

Uncertainty of Input Values 178

Practical Example: A Portfolio of Projects 179

Description 179

Optimisation Context 180

Risk or Uncertainty Context Using Simulation 180

Further Aspects of Optimisation Modelling 182

Structural Choices 182

Uncertainty 183

Integrated Approaches to Optimisation 183

Modelling Issues and Tools 184

Chapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187

Introduction 187

The Meaning, Origins and Uses of Monte Carlo Simulation 187

Definition and Origin 187

Limitations of Sensitivity and Scenario Approaches 188

Key Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189

The Nature of Model Outputs 190

The Applicability of Simulation Methods 190

Key Process and Modelling Steps in Risk Modelling 191

Risk Identification 191

Risk Mapping and the Role of the Distribution of Input Values 191

The Modelling Context and the Meaning of Input Distributions 192

The Effect of Dependencies Between Inputs 192

Random Numbers and the Required Number of Recalculations or Iterations 193

Using Excel and VBA to Implement Risk and Simulation Models 194

Generation of Random Samples 194

Repeated Recalculations and Results Storage 195

Example: Cost Estimation with Uncertainty and Event Risks Using Excel/VBA 196

Using Add-ins to Implement Risk and Simulation Models 196

Benefits of Add-ins 196

Example: Cost Estimation with Uncertainty and Event Risks Using @RISK 197

Part Five Excel Functions and Functionality 199

Chapter 17 Core Arithmetic and Logical Functions 201

Introduction 201

Practical Applications 201

Example: IF, AND, OR, NOT 202

Example: MIN, MAX, MINA, MAXA 204

Example: MINIFS and MAXIFS 204

Example: COUNT, COUNTA, COUNTIF and Similar Functions 205

Example: SUM, AVERAGE, AVERAGEA 206

Example: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206

Example: PRODUCT 207

Example: SUMPRODUCT 209

Example: SUBTOTAL 209

Example: AGGREGATE 210

Example: IFERROR 212

Example: SWITCH 215

Chapter 18 Array Functions and Formulae 217

Introduction 217

Functions and Formulae: Definitions 217

Implementation 217

Advantages and Disadvantages 218

Practical Applications: Array Functions 218

Example: Capex and Depreciation Schedules Using TRANSPOSE 218

Example: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218

Example: Cost Allocation Using Matrix Multiplication Using MMULT 219

Example: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220

Example: Summing Powers of Integers from 1 Onwards 222

Practical Applications: Array Formulae 225

Example: Finding First Positive Item in a List 225

Example: Find a Conditional Maximum 226

Example: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227

Chapter 19 Mathematical Functions 229

Introduction 229

Practical Applications 229

Example: EXP and LN 229

Example: ABS and SIGN 232

Example: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233

Example: MROUND, CEILING.MATH and FLOOR.MATH 235

Example: MOD 236

Example: SQRT and POWER 236

Example: FACT and COMBIN 237

Example: RAND() 238

Example: SINE, ASIN, DEGREES and PI() 239

Example: BASE and DECIMAL 241

Chapter 20 Financial Functions 243

Introduction 243

Practical Applications 243

Example: FVSCHEDULE 244

Example: FV and PV 244

Example: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246

Example: NPV and IRR for a Buy or Lease Decision 248

Example: SLN, DDB and VDB 250

Example: YIELD 252

Example: Duration of Cash Flows 252

Example: DURATION and MDURATION 253

Example: PDURATION and RRI 254

Other Financial Functions 255

Chapter 21 Statistical Functions 257

Introduction 257

Practical Applications: Position, Ranking and Central Values 258

Example: Calculating Mean and Mode 258

Example: Dynamic Sorting of Data Using LARGE 260

Example: RANK.EQ 261

Example: RANK.AVG 262

Example: Calculating Percentiles 262

Example: PERCENTRANK-type Functions 263

Practical Applications: Spread and Shape 264

Example: Generating a Histogram of Returns Using FREQUENCY 265

Example: Variance, Standard Deviation and Volatility 267

Example: Skewness and Kurtosis 271

Example: One-sided Volatility (Semi-deviation) 272

Practical Applications: Co-relationships and Dependencies 273

Example: Scatter Plots (X–Y Charts) and Measuring Correlation 274

Example: More on Correlation Coefficients and Rank Correlation 275

Example: Measuring Co-variances 277

Example: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277

Practical Applications: Probability Distributions 280

Example: Likelihood of a Given Number of Successes of an Oil Exploration Process 282

Example: Frequency of Outcomes Within One or Two Standard Deviations 283

Example: Creating Random Samples from Probability Distributions 283

Example: User-defined Inverse Functions for Random Sampling 284

Example: Values Associated with Probabilities for a Binomial Process 285

Example: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285

Example: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287

Example: Confidence Intervals for the Standard Deviation Using Chi-squared 289

Example: Confidence Interval for the Slope of Regression Line (or Beta) 289

Practical Applications: More on Regression Analysis and Forecasting 291

Example: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291

Example: Using LINEST to Perform Multiple Regression 292

Example: Using LOGEST to Find Exponential Fits 293

Example: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294

Example: Linear Forecasting Using FORECAST.LINEAR 295

Example: Forecasting Using the FORECAST.ETS Set of Functions 296

Chapter 22 Information Functions 299

Introduction 299

Practical Applications 300

Example: In-formula Comments Using ISTEXT, ISNUMBER or N 300

Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300

Example: Detecting Consistency of Data in a Database 301

Example: Consistent use of “N/A” in Models 301

Example: Applications of the INFO and CELL Functions: An Overview 303

Example: Creating Updating Labels that Refer to Data or Formulae 303

Example: Showing the User Which Recalculation Mode the File Is On 305

Example: Finding the Excel Version Used and Creating Backward Compatible Formulae 305

Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306

Chapter 23 Date and Time Functions 307

Introduction 307

Practical Applications 308

Example: Task Durations, Resource and Cost Estimation 308

Example: Keeping Track of Bookings, Reservations or Other Activities 308

Example: Creating Precise Time Axes 309

Example: Calculating the Year and Month of a Date 309

Example: Calculating the Quarter in Which a Date Occurs 310

Example: Creating Time-based Reports and Models from Data Sets 311

Example: Finding Out on What Day of the Week You Were Born 311

Example: Calculating the Date of the Last Friday of Every Month 311

Example: the DATEDIF Function and Completed Time Periods 312

Chapter 24 Text Functions and Functionality 313

Introduction 313

Practical Applications 314

Example: Joining Text Using CONCAT and TEXTJOIN 314

Example: Splitting Data Using the Text-to-columns Wizard 315

Example: Converting Numerical Text to Numbers 316

Example: Dynamic Splitting Text into Components I 316

Example: Dynamic Splitting Text into Components II 317

Example: Comparing LEFT, RIGHT, MID and LEN 317

Example: Dynamic Splitting Text into Components III 318

Example: Comparing FIND and SEARCH 319

Example: the UPPER and LOWER Functions 319

Example: the PROPER Function 319

Example: the EXACT Function 320

Example: Comparing REPLACE with SUBSTITUTE 320

Example: the REPT Function 320

Example: the CLEAN and TRIM Functions 321

Example: Updating Model Labels and Graph Titles 322

Example: Creating Unique Identifiers or Keys for Data Matching 323

Chapter 25 Lookup and Reference Functions 325

Introduction 325

Practical Applications: Basic Referencing Processes 326

Example: the ROW and COLUMN Functions 326

Example: the ROWS and COLUMNS Functions 327

Example: Use of the ADDRESS Function and the Comparison with CELL 327

Practical Applications: Further Referencing Processes 328

Example: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328

Example: Charts that Can Use Multiple or Flexible Data Sources 330

Example: Reversing and Transposing Data Using INDEX or OFFSET 331

Example: Shifting Cash Flows or Other Items over Time 334

Example: Depreciation Schedules with Triangle Calculations 334

Practical Applications: Combining Matching and Reference Processes 335

Example: Finding the Period in Which a Condition is Met Using MATCH 335

Example: Finding Non-contiguous Scenario Data Using Matching Keys 336

Example: Creating and Finding Matching Text Fields or Keys 336

Example: Combining INDEX with MATCH 337

Example: Comparing INDEX-MATCH with V- and HLOOKUP 338

Example: Comparing INDEX-MATCH with LOOKUP 343

Example: Finding the Closest Matching Value Using Array and Other Function Combinations 344

Practical Applications: More on the OFFSET Function and Dynamic Ranges 345

Example: Flexible Ranges Using OFFSET (I) 345

Example: Flexible Ranges Using OFFSET (II) 346

Example: Flexible Ranges Using OFFSET (III) 347

Example: Flexible Ranges Using OFFSET (IV) 347

Practical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349

Example: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349

Example: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351

Example: Other Uses of INDIRECT – Cascading Drop-down Lists 352

Practical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352

Example: Model Navigation Using Named Ranges and Hyperlinks 353

Chapter 26 Filters, Database Functions and PivotTables 355

Introduction 355

Issues Common to Working with Sets of Data 356

Cleaning and Manipulating Source Data 356

Static or Dynamic Queries 356

Creation of New Fields or Complex Filters? 357

Excel Databases and Tables 357

Automation Using Macros 359

Practical Applications: Filters 359

Example: Applying Filters and Inspecting Data for Errors or Possible Corrections 359

Example: Identification of Unique Items and Unique Combinations 362

Example: Using Filters to Remove Blanks or Other Specified Items 363

Example: Extraction of Data Using Filters 365

Example: Adding Criteria Calculations to the Data Set 365

Example: Use of Tables 366

Example: Extraction of Data Using Advanced Filters 369

Practical Applications: Database Functions 370

Example: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370

Example: Implementing a Between Query 371

Example: Implementing Multiple Queries 371

Practical Applications: PivotTables 373

Example: Exploring Summary Values of Data Sets 373

Example: Exploring Underlying Elements of the Summary Items 376

Example: Adding Slicers 376

Example: Timeline Slicers 378

Example: Generating Reports Which Ignore Errors or Other Specified Items 380

Example: Using the GETPIVOTDATA Functions 380

Example: Creating PivotCharts 382

Example: Using the Excel Data Model to Link Tables 383

Chapter 27 Selected Short-cuts and Other Features 387

Introduction 387

Key Short-cuts and Their Uses 387

Entering and Modifying Data and Formulae 388

Formatting 390

Auditing, Navigation and Other Items 391

Excel KeyTips 393

Other Useful Excel Tools and Features 393

Sparklines 393

The Camera Tool 393

Part Six Foundations of VBA and Macros 395

Chapter 28 Getting Started 397

Introduction 397

Main Uses of VBA 397

Task Automation 398

Creating User-defined Functions 398

Detecting and Reacting to Model Events 398

Enhancing or Managing the User Interface 399

Application Development 399

Core Operations 399

Adding the Developer Tab to Excel’s Toolbar 399

The Visual Basic Editor 399

Recording Macros 401

Typical Adaptations Required When Using Recorded Code 402

Writing Code 403

Running Code 404

Debugging Techniques 405

Simple Examples 406

Example: Using Excel Cell Values in VBA 406

Example: Using Named Excel Ranges for Robustness and Flexibility 407

Example: Placing a Value from VBA Code into an Excel Range 408

Example: Replacing Copy/Paste with an Assignment 409

Example: A Simple User-defined Function 409

Example: Displaying a Message when a Workbook is Opened 410

Chapter 29 Working with Objects and Ranges 413

Introduction 413

Overview of the Object Model 413

Objects, Properties, Methods and Events 413

Object Hierarchies and Collections 414

Using Set. . .=. . . . 415

Using the With. . .End With Construct 415

Finding Alternatives to the Selection or Activation of Ranges and Objects 416

Working with Range Objects: Some Key Elements 416

Basic Syntax Possibilities and Using Named Ranges 416

Named Ranges and Named Variables 417

The CurrentRegion Property 417

The xlCellTypeLastCell Property 418

Worksheet Names and Code Names 419

The UsedRange Property 419

The Cells Property 420

The Offset Property 421

The Union Method 421

InputBox and MsgBox 421

Application.InputBox 422

Defining Multi-cell Ranges 422

Using Target to React to Worksheet Events 422

Using Target to React to Workbook Events 423

Chapter 30 Controlling Execution 425

Introduction 425

Core Topics in Overview 425

Input Boxes and Message Boxes 425

For. . .Next Loops 425

For Each. . . In. . .Next 426

If. . .Then 427

Select Case. . .End Select 427

GoTo 428

Do. . .While/Until. . .Loop 428

Calculation and Calculate 429

Screen Updating 432

Measuring Run Time 432

Displaying Alerts 433

Accessing Excel Worksheet Functions 433

Executing Procedures Within Procedures 434

Accessing Add-ins 435

Practical Applications 435

Example: Numerical Looping 435

Example: Listing the Names of All Worksheets in a Workbook 436

Example: Adding a New Worksheet to a Workbook 437

Example: Deleting Specific Worksheets from a Workbook 437

Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438

Chapter 31 Writing Robust Code 441

Introduction 441

Key Principles 441

From the Specific to the General 441

Adapting Recorded Code for Robustness 442

Event Code 442

Comments and Indented Text 442

Modular Code 443

Passing Arguments ByVal or ByRef 443

Full Referencing 445

Using Worksheet Code Numbers 447

Assignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447

Working with Ranges Instead of Individual Cells 448

Data Types and Variable Declaration 448

Choice of Names 449

Working with Arrays in VBA 450

Understanding Error Codes: An Introduction 451

Further Approaches to Testing, Debugging and Error-handling 452

General Techniques 452

Debugging Functions 453

Implementing Error-handling Procedures 454

Chapter 32 Manipulation and Analysis of Data Sets with VBA 455

Introduction 455

Practical Applications 455

Example: Working Out the Size of a Range 455

Example: Defining the Data Set at Run Time Based on User Input 457

Example: Working Out the Position of a Data Set Automatically 457

Example: Reversing Rows (or Columns) of Data I: Placement in a New Range 459

Example: Reversing Rows (or Columns) of Data II: In Place 460

Example: Automation of Other Data-related Excel Procedures 461

Example: Deleting Rows Containing Blank Cells 462

Example: Deleting Blank Rows 463

Example: Automating the Use of Filters to Remove Blanks or Other Specified Items 464

Example: Performing Multiple Database Queries 468

Example: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469

Chapter 33 User-defined Functions 473

Introduction 473

Benefits of Creating User-defined Functions 473

Syntax and Implementation 474

Practical Applications 475

Example: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476

Example: A Wrapper to Access the Latest Excel Function Version 477

Example: Replication of IFERROR for Compatibility with Excel 2003 478

Example: Sum of Absolute Errors 479

Example: Replacing General Excel Calculation Tables or Ranges 480

Example: Using Application.Caller to Generate a Time Axis as an Array Function 480

Example: User-defined Array Functions in Rows and Columns 482

Example: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484

Example: Sheet Reference Functions 485

Example: Statistical Moments when Frequencies Are Known 487

Example: Rank Order Correlation 489

Example: Semi-deviation of a Data Set 491

Index 493