Principles of Financial Modelling

Model Design and Best Practices Using Excel and VBA

Inbunden, Engelska, 2018

AvMichael Rees,Michael (Independent Consultant) Rees

1 109 kr

Beställningsvara. Skickas inom 7-10 vardagar. Fri frakt för medlemmar vid köp för minst 249 kr.

The comprehensive, broadly-applicable, real-world guide to financial modellingPrinciples of Financial Modelling – Model Design and Best Practices Using Excel and VBAcovers the full spectrum of financial modelling tools and techniques in order to provide practical skills that are grounded in real-world applications. Based on rigorously-tested materials created for consulting projects and for training courses, this book demonstrates how to plan, design and build financial models that are flexible, robust, transparent, and highly applicable to a wide range of planning, forecasting and decision-support contexts. This book integrates theory and practice to provide a high-value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. Highlights of its content include extensive coverage of: Model design and best practices, including the optimisation of data structures and layout, maximising transparency, balancing complexity with flexibility, dealing with circularity, model audit and error-checkingSensitivity and scenario analysis, simulation, and optimisationData manipulation and analysisThe use and choice of Excel functions and functionality, including advanced functions and those from all categories, as well as of VBA and its key areas of application within financial modellingThe companion website provides approximately 235 Excel files (screen-clips of most of which are shown in the text), which demonstrate key principles in modelling, as well as providing many examples of the use of Excel functions and VBA macros. These facilitate learning and have a strong emphasis on practical solutions and direct real-world application.For practical instruction, robust technique and clear presentation, Principles of Financial Modelling is the premier guide to real-world financial modelling from the ground up. It provides clear instruction applicable across sectors, settings and countries, and is presented in a well-structured and highly-developed format that is accessible to people with different backgrounds.

Produktinformation

Utgivningsdatum2018-05-11
Mått178 x 252 x 36 mm
Vikt1 089 g
FormatInbunden
SpråkEngelska
SerieWiley Finance Series
Antal sidor544
FörlagJohn Wiley & Sons Inc
ISBN9781118904015

Tillhör följande kategorier

Finansiering inom Ekonomi och Ledarskap

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 xxvAbout the Author xxviiAbout the Website xxixPart One Introduction to Modelling, Core Themes and Best Practices 1Chapter 1 Models of Models 3Introduction 3Context and Objectives 3The Stages of Modelling 3Backward Thinking and Forward Calculation Processes 4Chapter 2 Using Models in Decision Support 7Introduction 7Benefits of Using Models 7Providing Numerical Information 7Capturing Influencing Factors and Relationships 7Generating Insight and Forming Hypotheses 8Decision Levers, Scenarios, Uncertainties, Optimisation, Risk Mitigation and Project Design 8Improving Working Processes, Enhanced Communications and Precise Data Requirements 9Challenges in Using Models 9The Nature of Model Error 9Inherent Ambiguity and Circularity of Reasoning 10Inconsistent Scope or Alignment of Decision and Model 10The Presence on Biases, Imperfect Testing, False Positives and Negatives 11Balancing Intuition with Rationality 11Lack of Data or Insufficient Understanding of a Situation 12Overcoming Challenges: Awareness, Actions and Best Practices 13Chapter 3 Core Competencies and Best Practices: Meta-themes 15Introduction 15Key Themes 15Decision-support Role, Objectives, Outputs and Communication 16Application Knowledge and Understanding 17Skills with Implementation Platform 17Defining Sensitivity and Flexibility Requirements 18Designing Appropriate Layout, Input Data Structures and Flow 20Ensuring Transparency and Creating a User-friendly Model 20Integrated Problem-solving Skills 21Part Two Model Design and Planning 23Chapter 4 Defining Sensitivity and Flexibility Requirements 25Introduction 25Key Issues for Consideration 25Creating a Focus on Objectives and Their Implications 26Sensitivity Concepts in the Backward Thought and Forward CalculationProcesses 26Time Granularity 30Level of Detail on Input Variables 30Sensitising Absolute Values or Variations from Base Cases 31Scenarios Versus Sensitivities 32Uncertain Versus Decision Variables 33Increasing Model Validity Using Formulae 34Chapter 5 Database Versus Formulae-driven Approaches 37Introduction 37Key Issues for Consideration 37Separating the Data, Analysis and Presentation (Reporting) Layers 37The Nature of Changes to Data Sets and Structures 39Focus on Data or Formulae? 40Practical Example 42Chapter 6 Designing the Workbook Structure 47Introduction 47Designing Workbook Models with Multiple Worksheets 47Linked Workbooks 47Multiple Worksheets: Advantages and Disadvantages 48Generic Best Practice Structures 49The Role of Multiple Worksheets in Best Practice Structures 49Type I: Single Worksheet Models 50Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51Further Comparative Comments 51Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52Multi-sheet or “Three Dimensional” Formulae 53Using Excel’s Data/Consolidation Functionality 54Consolidating from Several Sheets into a Database Using a Macro 55User-defined Functions 56Part Three Model Building, Testing and Auditing 57Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59Introduction 59Approaches to Identifying the Drivers of Complexity 59Taking the Place of a Model Auditor 59Example: Creating Complexity in a Simple Model 60Core Elements of Transparent Models 61Optimising Audit Paths 62Creating Short Audit Paths Using Modular Approaches 63Creating Short Audit Paths Using Formulae Structure and Placement 67Optimising Logical Flow and the Direction of the Audit Paths 68Identifying Inputs, Calculations and Outputs: Structure and Formatting 69The Role of Formatting 70Colour-coding of Inputs and Outputs 70Basic Formatting Operations 73Conditional Formatting 73Custom Formatting 75Creating Documentation, Comments and Hyperlinks 76Chapter 8 Building Robust and Transparent Formulae 79Introduction 79General Causes of Mistakes 79Insufficient Use of General Best Practices Relating to Flow, Formatting,Audit Paths 79Insufficient Consideration Given to Auditability and Other Potential Users 79Overconfidence, Lack of Checking and Time Constraints 80Sub-optimal Choice of Functions 80Inappropriate Use or Poor Implementation of Named Ranges, CircularReferences or Macros 80Examples of Common Mistakes 80Referring to Incorrect Ranges or To Blank Cells 80Non-transparent Assumptions, Hidden Inputs and Labels 82Overlooking the Nature of Some Excel Function Values 82Using Formulae Which are Inconsistent Within a Range 83Overriding Unforeseen Errors with IFERROR 84Models Which are Correct in Base Case but Not in Others 85Incorrect Modifications when Working with Poor Models 85The Use of Named Ranges 85Mechanics and Implementation 86Disadvantages of Using Named Ranges 86Advantages and Key Uses of Named Ranges 90Approaches to Building Formulae, to Testing, Error Detection and Management 91Checking Behaviour and Detecting Errors Using Sensitivity Testing 91Using Individual Logic Steps 93Building and Splitting Compound Formulae 94Using Absolute Cell Referencing Only Where Necessary 96Limiting Repeated or Unused Logic 96Using Breaks to Test Calculation Paths 97Using Excel Error Checking Rules 97Building Error-checking Formulae 98Handling Calculation Errors Robustly 100Restricting Input Values Using Data Validation 100Protecting Ranges 101Dealing with Structural Limitations: Formulae and Documentation 102Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105Introduction 105Key Considerations 105Direct Arithmetic or Functions, and Individual Cells or Ranges? 105IF Versus MIN/MAX 107Embedded IF Statements 109Short Forms of Functions 111Text Versus Numerical Fields 112SUMIFS with One Criterion 112Including Only Specific Items in a Summation 113AGGREGATE and SUBTOTAL Versus Individual Functions 114Array Functions or VBA User-defined Functions? 115Volatile Functions 115Effective Choice of Lookup Functions 116Chapter 10 Dealing with Circularity 117Introduction 117The Drivers and Nature of Circularities 117Circular (Equilibrium or Self-regulating) Inherent Logic 117Circular Formulae (Circular References) 118Generic Types of Circularities 119Resolving Circular Formulae 119Correcting Mistakes that Result in Circular Formulae 120Avoiding a Logical Circularity by Modifying the Model Specification 120Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121Resolving a Circularity Using Iterative Methods 122Iterative Methods in Practice 123Excel’s Iterative Method 123Creating a Broken Circular Path: Key Steps 125Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126Practical Example 128Using Excel Iterations to Resolve Circular References 129Using a Macro to Resolve a Broken Circular Path 129Algebraic Manipulation: Elimination of Circular References 130Altered Model 1: No Circularity in Logic or in Formulae 130Altered Model 2: No Circularity in Logic in Formulae 131Selection of Approach to Dealing with Circularities: Key Criteria 131Model Accuracy and Validity 132Complexity and Transparency 133Non-convergent Circularities 134Potential for Broken Formulae 138Calculation Speed 140Ease of Sensitivity Analysis 140Conclusions 141Chapter 11 Model Review, Auditing and Validation 143Introduction 143Objectives 143(Pure) Audit 143Validation 144Improvement, Restructuring or Rebuild 145Processes, Tools and Techniques 146Avoiding Unintentional Changes 146Developing a General Overview and Then Understanding the Details 147Testing and Checking the Formulae 151Using a Watch Window and Other Ways to Track Values 151Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155Introduction 155Overview of Sensitivity-related Techniques 155DataTables 156Overview 156Implementation 157Limitations and Tips 157Practical Applications 160Example: Sensitivity of Net Present Value to Growth Rates 160Example: Implementing Scenario Analysis 160Chapter 13 Using GoalSeek and Solver 163Introduction 163Overview of GoalSeek and Solver 163Links to Sensitivity Analysis 163Tips, Tricks and Limitations 163Practical Applications 164Example: Breakeven Analysis of a Business 165Example: Threshold Investment Amounts 166Example: Implied Volatility of an Option 167Example: Minimising Capital Gains Tax Liability 167Example: Non-linear Curve Fitting 169Chapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171Introduction 171Practical Applications 172Example: Running Sensitivity Analysis Using a Macro 172Example: Running Scenarios Using a Macro 173Example: Using a Macro to Run Breakeven Analysis with GoalSeek 173Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175Chapter 15 Introduction to Simulation and Optimisation 177Introduction 177The Links Between Sensitivity and Scenario Analysis,Simulation and Optimisation 177The Combinatorial Effects of Multiple Possible Input Values 177Controllable Versus Non-controllable: Choice VersusUncertainty of Input Values 178Practical Example: A Portfolio of Projects 179Description 179Optimisation Context 180Risk or Uncertainty Context Using Simulation 180Further Aspects of Optimisation Modelling 182Structural Choices 182Uncertainty 183Integrated Approaches to Optimisation 183Modelling Issues and Tools 184Chapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187Introduction 187The Meaning, Origins and Uses of Monte Carlo Simulation 187Definition and Origin 187Limitations of Sensitivity and Scenario Approaches 188Key Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189The Nature of Model Outputs 190The Applicability of Simulation Methods 190Key Process and Modelling Steps in Risk Modelling 191Risk Identification 191Risk Mapping and the Role of the Distribution of Input Values 191The Modelling Context and the Meaning of Input Distributions 192The Effect of Dependencies Between Inputs 192Random Numbers and the Required Number of Recalculations or Iterations 193Using Excel and VBA to Implement Risk and Simulation Models 194Generation of Random Samples 194Repeated Recalculations and Results Storage 195Example: Cost Estimation with Uncertainty and Event Risks Using Excel/VBA 196Using Add-ins to Implement Risk and Simulation Models 196Benefits of Add-ins 196Example: Cost Estimation with Uncertainty and Event Risks Using @RISK 197Part Five Excel Functions and Functionality 199Chapter 17 Core Arithmetic and Logical Functions 201Introduction 201Practical Applications 201Example: IF, AND, OR, NOT 202Example: MIN, MAX, MINA, MAXA 204Example: MINIFS and MAXIFS 204Example: COUNT, COUNTA, COUNTIF and Similar Functions 205Example: SUM, AVERAGE, AVERAGEA 206Example: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206Example: PRODUCT 207Example: SUMPRODUCT 209Example: SUBTOTAL 209Example: AGGREGATE 210Example: IFERROR 212Example: SWITCH 215Chapter 18 Array Functions and Formulae 217Introduction 217Functions and Formulae: Definitions 217Implementation 217Advantages and Disadvantages 218Practical Applications: Array Functions 218Example: Capex and Depreciation Schedules Using TRANSPOSE 218Example: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218Example: Cost Allocation Using Matrix Multiplication Using MMULT 219Example: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220Example: Summing Powers of Integers from 1 Onwards 222Practical Applications: Array Formulae 225Example: Finding First Positive Item in a List 225Example: Find a Conditional Maximum 226Example: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227Chapter 19 Mathematical Functions 229Introduction 229Practical Applications 229Example: EXP and LN 229Example: ABS and SIGN 232Example: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233Example: MROUND, CEILING.MATH and FLOOR.MATH 235Example: MOD 236Example: SQRT and POWER 236Example: FACT and COMBIN 237Example: RAND() 238Example: SINE, ASIN, DEGREES and PI() 239Example: BASE and DECIMAL 241Chapter 20 Financial Functions 243Introduction 243Practical Applications 243Example: FVSCHEDULE 244Example: FV and PV 244Example: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246Example: NPV and IRR for a Buy or Lease Decision 248Example: SLN, DDB and VDB 250Example: YIELD 252Example: Duration of Cash Flows 252Example: DURATION and MDURATION 253Example: PDURATION and RRI 254Other Financial Functions 255Chapter 21 Statistical Functions 257Introduction 257Practical Applications: Position, Ranking and Central Values 258Example: Calculating Mean and Mode 258Example: Dynamic Sorting of Data Using LARGE 260Example: RANK.EQ 261Example: RANK.AVG 262Example: Calculating Percentiles 262Example: PERCENTRANK-type Functions 263Practical Applications: Spread and Shape 264Example: Generating a Histogram of Returns Using FREQUENCY 265Example: Variance, Standard Deviation and Volatility 267Example: Skewness and Kurtosis 271Example: One-sided Volatility (Semi-deviation) 272Practical Applications: Co-relationships and Dependencies 273Example: Scatter Plots (X–Y Charts) and Measuring Correlation 274Example: More on Correlation Coefficients and Rank Correlation 275Example: Measuring Co-variances 277Example: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277Practical Applications: Probability Distributions 280Example: Likelihood of a Given Number of Successes of an Oil Exploration Process 282Example: Frequency of Outcomes Within One or Two Standard Deviations 283Example: Creating Random Samples from Probability Distributions 283Example: User-defined Inverse Functions for Random Sampling 284Example: Values Associated with Probabilities for a Binomial Process 285Example: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285Example: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287Example: Confidence Intervals for the Standard Deviation Using Chi-squared 289Example: Confidence Interval for the Slope of Regression Line (or Beta) 289Practical Applications: More on Regression Analysis and Forecasting 291Example: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291Example: Using LINEST to Perform Multiple Regression 292Example: Using LOGEST to Find Exponential Fits 293Example: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294Example: Linear Forecasting Using FORECAST.LINEAR 295Example: Forecasting Using the FORECAST.ETS Set of Functions 296Chapter 22 Information Functions 299Introduction 299Practical Applications 300Example: In-formula Comments Using ISTEXT, ISNUMBER or N 300Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300Example: Detecting Consistency of Data in a Database 301Example: Consistent use of “N/A” in Models 301Example: Applications of the INFO and CELL Functions: An Overview 303Example: Creating Updating Labels that Refer to Data or Formulae 303Example: Showing the User Which Recalculation Mode the File Is On 305Example: Finding the Excel Version Used and Creating Backward Compatible Formulae 305Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306Chapter 23 Date and Time Functions 307Introduction 307Practical Applications 308Example: Task Durations, Resource and Cost Estimation 308Example: Keeping Track of Bookings, Reservations or Other Activities 308Example: Creating Precise Time Axes 309Example: Calculating the Year and Month of a Date 309Example: Calculating the Quarter in Which a Date Occurs 310Example: Creating Time-based Reports and Models from Data Sets 311Example: Finding Out on What Day of the Week You Were Born 311Example: Calculating the Date of the Last Friday of Every Month 311Example: the DATEDIF Function and Completed Time Periods 312Chapter 24 Text Functions and Functionality 313Introduction 313Practical Applications 314Example: Joining Text Using CONCAT and TEXTJOIN 314Example: Splitting Data Using the Text-to-columns Wizard 315Example: Converting Numerical Text to Numbers 316Example: Dynamic Splitting Text into Components I 316Example: Dynamic Splitting Text into Components II 317Example: Comparing LEFT, RIGHT, MID and LEN 317Example: Dynamic Splitting Text into Components III 318Example: Comparing FIND and SEARCH 319Example: the UPPER and LOWER Functions 319Example: the PROPER Function 319Example: the EXACT Function 320Example: Comparing REPLACE with SUBSTITUTE 320Example: the REPT Function 320Example: the CLEAN and TRIM Functions 321Example: Updating Model Labels and Graph Titles 322Example: Creating Unique Identifiers or Keys for Data Matching 323Chapter 25 Lookup and Reference Functions 325Introduction 325Practical Applications: Basic Referencing Processes 326Example: the ROW and COLUMN Functions 326Example: the ROWS and COLUMNS Functions 327Example: Use of the ADDRESS Function and the Comparison with CELL 327Practical Applications: Further Referencing Processes 328Example: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328Example: Charts that Can Use Multiple or Flexible Data Sources 330Example: Reversing and Transposing Data Using INDEX or OFFSET 331Example: Shifting Cash Flows or Other Items over Time 334Example: Depreciation Schedules with Triangle Calculations 334Practical Applications: Combining Matching and Reference Processes 335Example: Finding the Period in Which a Condition is Met Using MATCH 335Example: Finding Non-contiguous Scenario Data Using Matching Keys 336Example: Creating and Finding Matching Text Fields or Keys 336Example: Combining INDEX with MATCH 337Example: Comparing INDEX-MATCH with V- and HLOOKUP 338Example: Comparing INDEX-MATCH with LOOKUP 343Example: Finding the Closest Matching Value Using Array and Other Function Combinations 344Practical Applications: More on the OFFSET Function and Dynamic Ranges 345Example: Flexible Ranges Using OFFSET (I) 345Example: Flexible Ranges Using OFFSET (II) 346Example: Flexible Ranges Using OFFSET (III) 347Example: Flexible Ranges Using OFFSET (IV) 347Practical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349Example: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349Example: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351Example: Other Uses of INDIRECT – Cascading Drop-down Lists 352Practical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352Example: Model Navigation Using Named Ranges and Hyperlinks 353Chapter 26 Filters, Database Functions and PivotTables 355Introduction 355Issues Common to Working with Sets of Data 356Cleaning and Manipulating Source Data 356Static or Dynamic Queries 356Creation of New Fields or Complex Filters? 357Excel Databases and Tables 357Automation Using Macros 359Practical Applications: Filters 359Example: Applying Filters and Inspecting Data for Errors or Possible Corrections 359Example: Identification of Unique Items and Unique Combinations 362Example: Using Filters to Remove Blanks or Other Specified Items 363Example: Extraction of Data Using Filters 365Example: Adding Criteria Calculations to the Data Set 365Example: Use of Tables 366Example: Extraction of Data Using Advanced Filters 369Practical Applications: Database Functions 370Example: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370Example: Implementing a Between Query 371Example: Implementing Multiple Queries 371Practical Applications: PivotTables 373Example: Exploring Summary Values of Data Sets 373Example: Exploring Underlying Elements of the Summary Items 376Example: Adding Slicers 376Example: Timeline Slicers 378Example: Generating Reports Which Ignore Errors or Other Specified Items 380Example: Using the GETPIVOTDATA Functions 380Example: Creating PivotCharts 382Example: Using the Excel Data Model to Link Tables 383Chapter 27 Selected Short-cuts and Other Features 387Introduction 387Key Short-cuts and Their Uses 387Entering and Modifying Data and Formulae 388Formatting 390Auditing, Navigation and Other Items 391Excel KeyTips 393Other Useful Excel Tools and Features 393Sparklines 393The Camera Tool 393Part Six Foundations of VBA and Macros 395Chapter 28 Getting Started 397Introduction 397Main Uses of VBA 397Task Automation 398Creating User-defined Functions 398Detecting and Reacting to Model Events 398Enhancing or Managing the User Interface 399Application Development 399Core Operations 399Adding the Developer Tab to Excel’s Toolbar 399The Visual Basic Editor 399Recording Macros 401Typical Adaptations Required When Using Recorded Code 402Writing Code 403Running Code 404Debugging Techniques 405Simple Examples 406Example: Using Excel Cell Values in VBA 406Example: Using Named Excel Ranges for Robustness and Flexibility 407Example: Placing a Value from VBA Code into an Excel Range 408Example: Replacing Copy/Paste with an Assignment 409Example: A Simple User-defined Function 409Example: Displaying a Message when a Workbook is Opened 410Chapter 29 Working with Objects and Ranges 413Introduction 413Overview of the Object Model 413Objects, Properties, Methods and Events 413Object Hierarchies and Collections 414Using Set. . .=. . . . 415Using the With. . .End With Construct 415Finding Alternatives to the Selection or Activation of Ranges and Objects 416Working with Range Objects: Some Key Elements 416Basic Syntax Possibilities and Using Named Ranges 416Named Ranges and Named Variables 417The CurrentRegion Property 417The xlCellTypeLastCell Property 418Worksheet Names and Code Names 419The UsedRange Property 419The Cells Property 420The Offset Property 421The Union Method 421InputBox and MsgBox 421Application.InputBox 422Defining Multi-cell Ranges 422Using Target to React to Worksheet Events 422Using Target to React to Workbook Events 423Chapter 30 Controlling Execution 425Introduction 425Core Topics in Overview 425Input Boxes and Message Boxes 425For. . .Next Loops 425For Each. . . In. . .Next 426If. . .Then 427Select Case. . .End Select 427GoTo 428Do. . .While/Until. . .Loop 428Calculation and Calculate 429Screen Updating 432Measuring Run Time 432Displaying Alerts 433Accessing Excel Worksheet Functions 433Executing Procedures Within Procedures 434Accessing Add-ins 435Practical Applications 435Example: Numerical Looping 435Example: Listing the Names of All Worksheets in a Workbook 436Example: Adding a New Worksheet to a Workbook 437Example: Deleting Specific Worksheets from a Workbook 437Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438Chapter 31 Writing Robust Code 441Introduction 441Key Principles 441From the Specific to the General 441Adapting Recorded Code for Robustness 442Event Code 442Comments and Indented Text 442Modular Code 443Passing Arguments ByVal or ByRef 443Full Referencing 445Using Worksheet Code Numbers 447Assignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447Working with Ranges Instead of Individual Cells 448Data Types and Variable Declaration 448Choice of Names 449Working with Arrays in VBA 450Understanding Error Codes: An Introduction 451Further Approaches to Testing, Debugging and Error-handling 452General Techniques 452Debugging Functions 453Implementing Error-handling Procedures 454Chapter 32 Manipulation and Analysis of Data Sets with VBA 455Introduction 455Practical Applications 455Example: Working Out the Size of a Range 455Example: Defining the Data Set at Run Time Based on User Input 457Example: Working Out the Position of a Data Set Automatically 457Example: Reversing Rows (or Columns) of Data I: Placement in a New Range 459Example: Reversing Rows (or Columns) of Data II: In Place 460Example: Automation of Other Data-related Excel Procedures 461Example: Deleting Rows Containing Blank Cells 462Example: Deleting Blank Rows 463Example: Automating the Use of Filters to Remove Blanks or Other Specified Items 464Example: Performing Multiple Database Queries 468Example: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469Chapter 33 User-defined Functions 473Introduction 473Benefits of Creating User-defined Functions 473Syntax and Implementation 474Practical Applications 475Example: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476Example: A Wrapper to Access the Latest Excel Function Version 477Example: Replication of IFERROR for Compatibility with Excel 2003 478Example: Sum of Absolute Errors 479Example: Replacing General Excel Calculation Tables or Ranges 480Example: Using Application.Caller to Generate a Time Axis as an Array Function 480Example: User-defined Array Functions in Rows and Columns 482Example: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484Example: Sheet Reference Functions 485Example: Statistical Moments when Frequencies Are Known 487Example: Rank Order Correlation 489Example: Semi-deviation of a Data Set 491Index 493