Preface ........................................................ xv
Acknowledgments ............................................... xix
About the Author .............................................. xix
Chapter 1 Introducing Visual Basic for Applications ............. 1
The Visual Basic Editor ......................................... 1
Visual Basic Procedures ......................................... 4
There Are Two Kinds of Macros ................................ 4
The Structure of a Sub Procedure ............................. 4
The Structure of a Function Procedure ........................ 5
Using the Recorder to Create a Sub Procedure ................. 5
The Personal Macro Workbook .................................. 7
Running a Sub Procedure ...................................... 8
Assigning a Shortcut Key to a Sub Procedure .................. 8
Entering VBA Code ............................................... 9
Creating a Simple Custom Function ........................... 10
Using a Function Macro ...................................... 10
A Shortcut to Enter a Function .............................. 12
Some FAQs ...................................................... 13
Chapter 2 Fundamentals of Programming with VBA ................. 15
Components of Visual Basic Statements .......................... 15
Operators ................................................... 16
Variables ................................................... 16
Objects, Properties, and Methods ............................ 17
Objects ..................................................... 17
Properties .................................................. 17
Using Properties ............................................ 19
Functions ................................................... 20
Using Worksheet Functions with VBA .......................... 22
Some Useful Methods ......................................... 22
Other Keywords .............................................. 23
Program Control ................................................ 23
Branching ................................................... 23
Logical Operators ........................................... 24
Select Case ................................................. 24
Looping ..................................................... 24
For...Next Loop ............................................. 25
Do While... Loop ............................................ 25
For Each...Next Loop ........................................ 25
Nested Loops ................................................ 26
Exiting from a Loop or from a Procedure ..................... 26
VBA Data Types ................................................. 27
The Variant Data Type ....................................... 28
Subroutines .................................................... 28
Scoping a Subroutine ........................................ 29
VBA Code for Command Macros .................................... 29
Objects and Collections of Objects .......................... 29
"Objects" That Are Really Properties ........................ 30
You Can Define Your Own Objects ............................. 30
Methods ..................................................... 31
Some Useful Methods ......................................... 31
Two Ways to Specify Arguments of Methods .................... 32
Arguments with or without Parentheses ....................... 33
Making a Reference to a Cell or a Range ........................ 33
A Reference to the Active Cell or a Selected Range .......... 33
A Reference to a Cell Other than the Active Cell ............ 34
References Using the Union or Intersect Method .............. 35
Examples of Expressions to Refer to a Cell or Range ......... 35
Getting Values from a Worksheet ............................. 36
Sending Values to a Worksheet ............................... 37
Interacting with the User ...................................... 37
MsgBox ...................................................... 37
MsgBox Return Values ........................................ 39
InputBox .................................................... 39
Visual Basic Arrays ............................................ 41
Dimensioning an Array ....................................... 41
Use the Name of the Array Variable to Specify the Whole
Array .................................................... 42
Multidimensional Arrays ..................................... 42
Declaring the Variable Type of an Array...................... 42
Returning the Size of an Array .............................. 42
Dynamic Arrays .............................................. 43
Preserving Values in Dynamic Arrays ......................... 43
Working with Arrays in Sub Procedures:
Passing Values from Worksheet to VBA Module .............. 44
A Range Specified in a Sub Procedure Can Be Used as
an Array ................................................. 44
Some Worksheet Functions Used Within VBA Create an Array
Automatically ............................................ 45
An Array of Object Variables ................................ 45
Working with Arrays in Sub Procedures:
Passing Values from a VBA Module to a Worksheet .......... 45
A One-Dimensional Array Assigned to a Worksheet Range Can
Cause Problems ........................................... 46
Custom Functions ............................................... 47
Specifying the Data Type of an Argument ..................... 47
Specifying the Data Type Returned by a Function Procedure ... 47
Returning an Error Value from a Function Procedure .......... 48
A Custom Function that Takes an Optional Argument ........... 48
Arrays in Function Procedures .................................. 48
A Range Passed to a Function Procedure Can Be Used as
an Array ................................................. 48
Passing an Indefinite Number of Arguments:
Using the ParamArray Keyword ............................. 49
Returning an Array of Values as a Result .................... 49
Creating Add-In Function Macros ................................ 50
How to Create an Add-In Macro ............................... 51
Testing and Debugging .......................................... 51
Tracing Execution ........................................... 52
Stepping Through Code ....................................... 52
Adding a Breakpoint ......................................... 52
Examining the Values of Variables While in Break Mode ....... 53
Examining the Values of Variables During Execution .......... 54
Chapter 3 Worksheet Functions for Working with Matrices ........ 57
Arrays, Matrices and Determinants .............................. 57
Some Types of Matrices ...................................... 57
An Introduction to Matrix Mathematics .......................... 58
Excel's Built-in Matrix Functions .............................. 60
Some Additional Matrix Functions ............................... 63
Problems ....................................................... 66
Chapter 4 Number Series ........................................ 69
Evaluating Series Formulas ..................................... 70
Using Array Constants to Create Series Formulas ............. 70
Using the ROW Worksheet Function to Create Series
Formulas ................................................. 71
The INDIRECT Worksheet Function ............................. 71
Using the INDIRECT Worksheet Function with the ROW
Worksheet Function to Create Series Formulas ............. 72
The Taylor Series .............................................. 72
The Taylor Series: An Example ............................... 73
Problems ....................................................... 75
Chapter 5 Interpolation ........................................ 77
Obtaining Values from a Table .................................. 77
Using Excel's Lookup Functions to Obtain Values from
a Table .................................................. 77
Using VLOOKUP to Obtain Values from a Table ................. 78
Using the LOOKUP Function to Obtain Values from a Table ..... 79
Creating a Custom Lookup Formula to Obtain Values from
a Table .................................................. 80
Using Excel's Lookup Functions to Obtain Values from
a Two-Way Table .......................................... 81
Interpolation .................................................. 83
Linear Interpolation in a Table by Means of Worksheet
Formulas ................................................. 83
Linear Interpolation in a Table by Using the TREND
Worksheet Function ....................................... 85
Linear Interpolation in a Table by Means of a Custom
Function ................................................. 86
Cubic Interpolation ......................................... 87
Cubic Interpolation in a Table by Using the TREND
Worksheet Function ....................................... 89
Linear Interpolation in a Two-Way Table by Means of
Worksheet Formulas ....................................... 90
Cubic Interpolation in a Two-Way Table by Means of
Worksheet Formulas ....................................... 91
Cubic Interpolation in a Two-Way Table by Means of
a Custom Function ........................................ 93
Problems ....................................................... 96
Chapter 6 Differentiation ...................................... 99
First and Second Derivatives of Data in a Table ................ 99
Calculating First and Second Derivatives ...................... 100
Using LINEST as a Fitting Function ......................... 105
Derivatives of a Worksheet Formula ............................ 109
Derivatives of a Worksheet Formula Calculated by Using
a VBA Function Procedure ................................ 109
First Derivative of a Worksheet Formula Calculated by
Using the Finite-Difference Method ...................... 110
The Newton Quotient ........................................ 110
Derivative of a Worksheet Formula Calculated by Using
the Finite-Difference Method ............................ 111
First Derivative of a Worksheet Formula Calculated by
Using a VBA Sub Procedure Using the Finite-Difference
Method .................................................. 112
First Derivative of a Worksheet Formula Calculated by
Using a VBA Function Procedure Using
the Finite-Difference Method ............................ 115
Improving the VBA Function Procedure ....................... 118
Second Derivative of a Worksheet Formula ................... 120
Concerning the Choice of Δx for the Finite-Difference
Method .................................................. 123
Problems ...................................................... 124
Chapter 7 Integration ......................................... 127
Area under a Curve ............................................ 127
Calculating the Area under a Curve Defined by a Table of
Data Points ............................................. 129
Calculating the Area under a Curve Defined by a Table of
Data Points by Means of a VBA Function Procedure ........ 130
Calculating the Area under a Curve Defined by a Formula .... 131
Area between Two Curves .................................... 132
Integrating a Function ........................................ 133
Integrating a Function Defined by a Worksheet Formula by
Means of a VBA Function Procedure ....................... 133
Gaussian Quadrature ........................................ 137
Integration with an Upper or Lower Limit of Infinity ....... 140
Distance Traveled Along a Curved Path ......................... 141
Problems ...................................................... 143
Chapter 8 Roots of Equations .................................. 147
A Graphical Method ......................................... 147
The Interval-Halving or Bisection Method ................... 149
The Interval Method with Linear Interpolation
(the Regula Falsi Method) ............................... 151
The Regula Falsi Method with Correction for Slow
Convergence ............................................. 153
The Newton-Raphson Method .................................. 154
Using Goal Seek... ......................................... 156
The Secant Method .......................................... 160
The Newton-Raphson Method Using Circular Reference and
Iteration ............................................... 161
A Newton-Raphson Custom Function ........................... 163
Bairstow's Method to Find All Roots of a Regular
Polynomial .............................................. 166
Finding Values Other than Zeroes of a Function ................ 174
Using Goal Seek... to Find the Point of Intersection of
Two Curves .............................................. 174
Using the Newton-Raphson Method to Find the Point of
Intersection of Two Lines ............................... 176
Using the Newton-Raphson Method to Find Multiple
Intersections of a Straight Line and a Curve ............ 178
A Goal Seek Custom Function ................................ 180
Problems ...................................................... 185
Chapter 9 Systems of Simultaneous Equations ................... 189
Cramer's Rule .............................................. 190
Solving Simultaneous Equations by Matrix Inversion ......... 191
Solving Simultaneous Equations by Gaussian Elimination ..... 191
The Gauss-Jordan Method .................................... 196
Solving Linear Systems by Iteration ........................... 200
The Jacobi Method Implemented on a Worksheet ............... 200
The Gauss-Seidel Method Implemented on a Worksheet ......... 203
The Gauss-Seidel Method Implemented on a Worksheet Using
Circular References ..................................... 204
A Custom Function Procedure for the Gauss-Seidel Method .... 205
Solving Nonlinear Systems by Iteration ........................ 207
Newton's Iteration Method .................................. 207
Problems ...................................................... 213
Chapter 10 Numerical Integration of Ordinary Differential
Equations
Part I: Initial Conditions ................................. 217
Solving a Single First-Order Differential Equation ............ 218
Euler's Method ............................................. 218
The Fourth-Order Runge-Kutta Method ........................ 220
Fourth-Order Runge-Kutta Method Implemented on
a Worksheet ............................................. 220
Runge-Kutta Method Applied to a Differential Equation
Involving Both x and y .................................. 223
Fourth-Order Runge-Kutta Custom Function for a Single
Differential Equation with the Derivative Expression
Coded in the Procedure .................................. 224
Fourth-Order Runge-Kutta Custom Function for a Single
Differential Equation with the Derivative Expression
Passed as an Argument ................................... 225
Systems of First-Order Differential Equations ................. 228
Fourth-Order Runge-Kutta Custom Function for Systems of
Differential Equations .................................. 229
Predictor-Corrector Methods ................................... 235
A Simple Predictor-Corrector Method ........................ 235
A Simple Predictor-Corrector Method Utilizing
an Intentional Circular Reference ....................... 236
Higher-Order Differential Equations ........................... 238
Problems ...................................................... 241
Chapter 11 Numerical Integration of Ordinary Differential
Equations
Part II: Boundary Conditions ............................... 245
The Shooting Method ........................................... 245
An Example: Deflection of a Simply Supported Beam .......... 246
Solving a Second-Order Ordinary Differential Equation
by the Shooting Method and Euler's Method ............... 249
Solving a Second-Order Ordinary Differential Equation
by the Shooting Method and the RK Method ................ 251
Finite-Difference Methods ..................................... 254
Solving a Second-Order Ordinary Differential Equation
by the Finite-Difference Method ......................... 254
Another Example ............................................ 258
A Limitation on the Finite-Difference Method ............... 261
Problems ...................................................... 262
Chapter 12 Partial Differential Equations ..................... 263
Elliptic, Parabolic and Hyperbolic Partial Differential
Equations .................................................. 263
Elliptic Partial Differential Equations ....................... 264
Solving Elliptic Partial Differential Equations:
Replacing Derivatives with Finite Differences ........... 265
An Example: Temperature Distribution in a Heated Metal
Plate ................................................... 267
Parabolic Partial Differential Equations ...................... 269
Solving Parabolic Partial Differential Equations:
The Explicit Method ..................................... 270
An Example: Heat Conduction in a Brass Rod ................. 272
Solving Parabolic Partial Differential Equations:
The Crank-Nicholson or Implicit Method .................. 274
An Example: Vapor Diffusion in a Tube ...................... 275
Vapor Diffusion in a Tube Revisited ........................ 277
Vapor Diffusion in a Tube (Again) .......................... 279
A Crank-Nicholson Custom Function .......................... 280
Vapor Diffusion in a Tube Solved by Using a Custom
Function ................................................ 282
Hyperbolic Partial Differential Equations ..................... 282
Solving Hyperbolic Partial Differential Equations:
Replacing Derivatives with Finite Differences ........... 282
An Example: Vibration of a String .......................... 283
Problems ...................................................... 286
Chapter 13 Linear Regression and Curve Fitting ................ 287
Linear Regression ............................................. 287
Least-Squares Fit to a Straight Line ....................... 288
Least-Squares Fit to a Straight Line Using the Worksheet
Functions SLOPE, INTERCEPT and RSQ ...................... 289
Multiple Linear Regression .................................... 291
Least-Squares Fit to a Straight Line Using LINEST .......... 292
Multiple Linear Regression Using LINEST .................... 293
Handling Noncontiguous Ranges of known_x's in LINEST ....... 297
A LINEST Shortcut .......................................... 297
LINESTs Regression Statistics .............................. 297
Linear Regression Using Trendline .......................... 298
Limitations of Trendline ................................... 301
Importing Trendline Coefficients into a Spreadsheet by
Using Worksheet Formulas ................................ 302
Using the Regression Tool in Analysis Tools ................ 303
Limitations of the Regression Tool ......................... 305
Importing the Trendhne Equation from a Chart into
a Worksheet ............................................. 305
Problems ...................................................... 309
Chapter 14 Nonlinear Regression Using the Solver .............. 313
Nonlinear Least-Squares Curve Fitting ......................... 314
Introducing the Solver ..................................... 316
How the Solver Works ....................................... 316
Loading the Solver Add-In .................................. 317
Why Use the Solver for Nonlinear Regression? ............... 317
Nonlinear Regression Using the Solver: An Example .......... 318
Some Notes on Using the Solver ............................. 323
Some Notes on the Solver Parameters Dialog Box ............. 323
Some Notes on the Solver Options Dialog Box ................ 324
When to Use Manual Scaling ................................. 326
Statistics of Nonlinear Regression ............................ 327
The Solver Statistics Macro ................................ 328
Be Cautious When Using Linearized Forms of Nonlinear
Equations ............................................... 329
Problems ...................................................... 332
Chapter 15 Random Numbers and the Monte Carlo Method .......... 341
Random Numbers in Excel ....................................... 341
How Excel Generates Random Numbers ......................... 341
Using Random Numbers in Excel .............................. 342
Adding "Noise" to a Signal Generated by a Formula .......... 344
Selecting Items Randomly from a List ....................... 345
Random Sampling by Using Analysis Tools .................... 347
Simulating a Normal Random Distribution of a Variable ...... 349
Monte Carlo Simulation ........................................ 350
Monte Carlo Integration ....................................... 354
The Area of an Irregular Polygon ........................... 354
Problems ...................................................... 362
APPENDICES .................................................... 363
Appendix 1 Selected VBA Keywords .............................. 365
Appendix 2 Shortcut Keys for VBA .............................. 387
Appendix 3 Custom Functions Help File ......................... 389
Appendix 4 Some Equations for Curve Fitting ................... 409
Appendix 5 Engineering and Other Functions .................... 423
Appendix 6 ASCII Codes ........................................ 427
Appendix 7 Bibliography ....................................... 429
Appendix 8 Answers and Comments for End-of-Chapter Problems ... 431
INDEX ......................................................... 443
|
