Książki

The comprehensive, broadly-applicable, real-world guide to financial modelling Financial Modelling in Practice, Second Edition covers the full spectrum of financial modelling tools and techniques to provide practical skills grounded in real-world scenarios. Based on rigorously-tested training materials, this book demonstrates the construction and operation of a range of financial models with clear instruction on model design, Excel functionality, VBA coding, risk analysis, real options modelling and more. This second edition has been significantly updated to align with the latest version of Excel and the current financial modelling climate, including new guidance on sensitivity analysis as a model design tool and risk analysis as an extension of sensitivity analysis. The companion website provides access to worked examples and pre-formatted spreadsheets to facilitate learning and implementation, and the strong emphasis on practical solutions allows direct real-world application. The number of people tasked with financial modelling duties has increased significantly in recent years, yet comprehensive, broadly-applicable references are rare.
This book brings together concept and practice to provide a high value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. * Utilize Excel at a more advanced level, including VBA modelling * Learn unique tips and hints for data manipulation and analysis * Understand financial statement modelling for valuation * Master simulation, risk modelling and optimization Financial modelling is invaluable to a broad range of financial decisions, including corporate finance valuation, portfolio composition, cash flow analysis, budgeting, forecasting and more. This book provides clear instruction applicable across sectors, settings and countries, presented in a well-structured and highly-developed format accessible to people with very different backgrounds. For practical instruction, robust technique and clear presentation, Financial Modelling in Practice is the premier guide to real-world financial modelling from the ground up.

Principles of Financial Modelling - Model Design and Best Practices Using Excel and VBA

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