Entering VBA Code
Of course, most of the VBA code you create will not be recorded, but instead entered at the keyboard. As you type your VBA code, the Visual Basic Editor checks each line for syntax errors. A line that contains one or more errors will be displayed in red, the default color for errors. Variables usually appear in black. Other colors are also used comments see later are usually green and some VBA keywords Function, Range, etc. usually appear in blue. These default colors can be changed if you...
Stepping Through Code
There are two ways to begin the process of stepping through the code of a Sub procedure Select the name of the procedure in the Macro Name list box and press the Step Into button. This will display the code module containing the procedure the first line of the procedure will be highlighted in yellow, as in Figure 2-22 . Add a breakpoint as described in the following section, then run the Sub procedure in the usual way. When the code window is displayed, with a line of code highlighted, you can...
An Example Vapor Diffusion in a Tube
An air-filled tube 20 cm long allows water vapor to diffuse from a source liquid water to a drying chamber, where the vapors are dissipated. Initially the tube is capped so that the vapor cannot escape. The temperature of the tube is held at 30 C. The equilibrium vapor pressure of water at this temperature is 31.8 mm Hg thus the vapor pressure inside the tube is 31.8 mm Hg. When the cap is removed, the vapor will diffuse toward the drying chamber, where the water vapor pressure is zero. We wish...
Two Ways to Specify Arguments of Methods
VBA methods usually take one or more arguments. The Sort method, for example, takes 10 arguments. The syntax of the Sort method is object.Sort key1, orderl, key2, order2, key3, order3, header, orderCustom, The object argument is required all other arguments are optional. You can specify the arguments of a method in two ways. One way is to list the arguments in order as they are specified in the preceding syntax, i.e., Range A1 E150 .Sort Last Name, xIAscending which sorts the data contained in...
An Example Vibration of a String
A string 50 cm long and weighing 0.5 g is under a tension of 33 kg. Initially the mid-point of the string is displaced 0.5 cm from its equilibrium position and released. We want to calculate the displacement as a function of time at 5 cm intervals along the length of the string, using equation 12-34. From equation 1235 the At must be 8.8 x 10 5 seconds. The spreadsheet shown in Figure 12-14 illustrates the solution of the vibrating string problem. Column B contains time in increments of At from...
Linear Regression
Linear regression is not limited to the case of finding the least-squares slope and intercept of a straight line. Linear regression methods can be applied to any function that is linear in the coefficients . Many functions that produce curved x-y plots are linear in the coefficients, including power series, for example, and some functions containing exponentials, such as Mathematically, a function that is linear in the coefficients is one for which the partial derivatives of the function with...
Derivatives of a Worksheet Formula
Instead of calculating the first or second derivative of a curve represented by data points, we may wish to find the derivative of a function a worksheet formula . In the following, two different methods are illustrated to calculate the first or second derivative of a worksheet formula by using a user-defined function. The calculation of the first derivative of the function y 3x3 5x2 - 5x 11 is used as the example for each method Derivatives of a Worksheet Formula Calculated by Using a VBA...
An Example Heat Conduction in a Brass Rod
Consider an insulated 10-cm brass rod, initially at a temperature of 0 C. One end of the rod is heated to 100 C. Equation 12-20 describes the heat flow in the rod as a function of time. For simplicity, we assume that there is no heat loss through the sides of the rod. For brass, the coefficient of thermal conductivity k is 0.26 cal s cm-1 deg ', the heat capacity c is 0.094 cal g_1 deg-1 and the density p is 8.4 g cm-3. From these values, the coefficient k in equation 12-22 is 3.04 s cm2....
Nonlinear Regression Using the Solver
If you have read the preceding chapter on linear regression and are familiar with the use of LIN EST, you should have no trouble recognizing a function that is linear in the coefficients. Some examples of functions that are linear in the coefficients arsy a bx cx2 dx ory aex. However, if the function is one such as it is not linear in the coefficients. It should be obvious that it's not possible to apply LI NEST to this equation given a column of x values, you can't create a column of ea bx...
Some Additional Matrix Functions
Some additional functions useful for working with arrays or matrices are provided on the CD that accompanies this book. The additional functions are as follows Identity Matrix. The function MIDENT s 'ze returns an identity matrix of a specified size. The size argument is optional. Use size when you want to use an identity matrix in a formula. Omit size when you want to fill a range of cells on a worksheet with an identity matrix the size of the matrix is then determined by the size of the...
The GaussSeidel Method Implemented on a Worksheet Using Circular References
The worksheet in the preceding section can be easily modified to use intentional circular references, as follows. After entering the starting values in row 13 and the formulas in row 14 as before Figure 9-11 , change the cell references in the formulas in cells B14 and C14 from references to row 13 to references to row 14. The formulas in cells B14, C14 and D14 are now, respectively, E 8- C 8 C14- D 8 D14 B 8 E 9- B 9 B14- D 9 D14 C 9 E 10- B 10 B14- C 10 C14 D 10 This produces the Cannot...
Problems Kzw
Answers to the following problems are found in the folder Ch. 11 BVP in the Problems amp Solutions folder on the CD. 1. Repeat the beam deflection example at the beginning of this chapter, using the Runge-Kutta method instead of Euler's method. Use Goal Seek to solve the problem. What is the maximum beam deflection 2. Modify the beam deflection example at the beginning of this chapter, so that 200 rows of calculation are performed, and the length of the beam L is a variable. Use Goal Seek to...
Integrating a Function
Instead of finding the area under a curve defined by a set of data points, you may wish to integrate a function F x . You could simply create a table of function values and use one of the methods described in earlier sections to calculate the area. But a more convenient solution would be to create a custom function that uses the Formula property of the cell to get the worksheet formula to be integrated, in the same way that was used in the preceding chapter, and uses the formula to find the...
Integrates
Returns the integral the area under the curve of an expression between specified limits. The area is calculated by using Simpson's 1 3 method. IntegrateS expressro gt , variable, from_lower, to_upper expression reference to a cell containing a formula the integrand, the function F x to be integrated variable cell reference corresponding to x, the variable of integration. fromjower the lower limit of integration to__upper the upper limit of integration The argument expression can be either a...
LeastSquares Fit to a Straight Line Using the Worksheet Functions SLOPE
Figure 13-1 shows the phase diagram of methane hydrate, one of a class of compounds known as clathrate hydrates. Methane hydrate, an ice-like solid, consists of methane molecules trapped in a crystalline lattice of water molecules each unit cell of the crystal lattice contains 46 water molecules and up to 8 gas molecules. The figure shows that the solid phase forms under conditions of high pressure and relatively low temperature. Previously, information about the formation of methane hydrate...
Problems Duk
Answers to the following problems are found in the folder Ch. 10 ODE in the Problems amp Solutions folder on the CD. 1. A function is described by the differential equation dyjdt 1 -t y . Calculate y for t 0 to t 5, in increments of 0.1. 2. A function is described by the differential equation 3. A function is described by the differential equation Calculate y for x 0 to x 2.5. Adjust the magnitude of Ax for different parts of the calculation, as appropriate. 4. Trajectory I. Consider the motion...
Logarithmic
Figure A4-9. Logarithmic function. The curve follows equation A4-7 with a - 2, b 1. Data with the behavior shown in Figure A4-9 can be fitted by the logarithmic equation A4-7. 'Plateau Curve. A relationship of the form exhibits the behavior shown in Figure A4-10. 1 Figure A4-10. Plateau curve. The curve follows equation A4-8 with a ,b . In biochemistry, this type of curve is encountered in a plot of reaction rate of an enzyme-catalyzed reaction of a substrate as a function of the concentration...
Area under a Curve
By area under a curve we mean the area bounded by a curve and the x-axis the line y 0 , between specified limits. The area can be positive if the curve lies above the x-axis or negative if it is below. Calculation of the area under a curve is sometimes referred to as quadrature, since it involves subdividing the area under the curve into a number of panels whose areas can be calculated. The sum of the areas of the panels will be an approximation to the area under the curve. The three most...
Linear Interpolation in a TwoWay Table by Means of Worksheet Formulas
To perform linear interpolation in a two-way table a table with two ranges of independent variables, x and y and a two-dimensional array of z values forming the body of the table , we can use the same linear interpolation formula that was employed earlier. Consider the example shown in Figure 5-15 we want to find the viscosity value in the table for x 76 F, y 56.3 wt ethylene glycol. The shaded cells are the values that bracket the desired x and y values. Viscosity of Heat Transfer Fluid cps...
Cubic Interpolation in a TwoWay Table by Means of Worksheet Formulas
To perform cubic interpolation between data points in a two-way table, we use a procedure similar to the one for linear interpolation. Figure 5-18 shows the table of viscosities that was used earlier. In this example we want to obtain the viscosity of a 63 solution at 55 F. The shaded cells are the values that bracket the desired x and y values. Viscosity of Heat Transfer Fluid cps Figure 5-18. Cubic interpolation in a two-way table. The shaded cells are the ones used in the interpolation,...
Importing the Trendline Equation from a Chart into a Worksheet
Scientists and engineers often use Excel's Trendline feature to obtain a least-squares fit to data in a chart. Trendline provides a limited gallery of mathematical fitting functions, including regular polynomials up to order six. The disadvantage of Trendline is that the trendline equation is merely a caption in the chart to use it in the worksheet, the coefficients must be transferred manually by typing, or copying and pasting. The utility TrendlineToCell provided on the CD-ROM converts the...
Chapter Integration
The solution of scientific and engineering problems sometimes requires integration of an expression. Symbolic integration involves the use of the methods of calculus to yield a closed-form analytical expression the indefinite integral, or mathematical function F x whose derivative dy dx is given. We will not attempt to find the indefinite integral Excel is not equipped to do symbolic algebra but instead find the area under the curve bounded by a function F x and the x-axis. This area is the...
Solving Parabolic Partial Differential Equations The CrankNicholson or Implicit
In the explicit method, we used a central difference formula for the second derivative and a forward difference formula for the first derivative equations 1224 and 12-25 . A variant of equation 12-26 that makes the approximations to both derivatives central differences is known as the Crank-Nicholson formula - rF,_hj 2 2r FiJ l - rFMJ x rF j 2 - 2r Fu rFMJ or, if i represents distance x and j represents time t, rFx_u x 2 2r Fx l l - rFx J l rFx_u 2 - 2r Fxt rFx X t where r Ay k Ax 2 . Choosing...
Branching
VBA supports If Then statements very similar to the Excel worksheet function IF. The syntax of lf Then is If LogicalExpression Then statementl Else statement2 The lf Then statement can be a Simple If statement, for example If x gt 0 Then numerator 10 A x If LogicalExpression in this example x gt 0 is True, statementl is carried out if LogicalExpression is False, nothing is done program execution moves to the next line . lf Then Else structures are also possible. For example If Err.Number 13...
FirstOrder Differential Equation
This section describes methods for solving first-order differential equations with initial conditions the order of a differential equation is determined by the order of the highest derivative in the equation . Two methods will be described Euler's method and the Runge-Kutta method. Euler's method is simple in concept, but not of sufficient accuracy to be useful it is included here because it illustrates the basic method of calculation and can be modified to yield methods of higher accuracy. The...
A Simple PredictorCorrector Method Utilizing an Intentional Circular Reference
An intentional circular reference can be used in the corrector formula to eliminate the need to Fill Right the corrector formula in order to perform the iterations. The corrector formula in cell C6 is changed from the formula shown above to which creates a circular reference, since cell C6 refers to itself. A circular reference is usually an error Excel displays the Cannot resolve circular references error message and puts a zero in the cell. In this case, however, the circular reference is...
Some Notes on the Solver Options Dialog Box
The Options button in the Solver Parameters dialog box displays the Solver Options dialog box Figure 14-7 and allows you to control the way Solver attempts to reach a solution. The default values of the options are shown in Figure 14-7. Max Time and Iterations. The Max Time and Iterations parameters determine when the Solver will return a solution or halt. If either Max Time or Iterations is exceeded before a solution has been reached, the Solver will pause and ask if you want to continue. For...
Evaluating Series Formulas
The obvious way to evaluate a series formula is to evaluate individual terms in the series formula in separate rows of the spreadsheet, and then sum the terms. Figure 4-1 illustrates the evaluation of e by using equation 4-1, summing terms until the contribution from the next term in the series is less than 1E-15. Figure 4-1. Evaluation of the terms of a series row-by-row. The spreadsheet calculates the value of e by using equation 4-1 . Note that some rows of calculation have been hidden....
Logistic Curve with Offset on the Axis The logistic equation
Figure A4-16. Logistic curve with offset on they-axis. The curve follows equation A4-13 with a 1, b -2, c 1 and d -0.2. This equation takes into account the value of the plateau maximum and minimum coefficients a and d, respectively , the offset on the -axis, and the Hill slope. Gaussian Curve. The Gaussian or normal error curve equation A4-14 y _ exp - x - m 2 j2 A4_H cr Jln can be used to model UV-visible band shapes, usually in order to deconvolute a spectrum consisting of two or more...
A Simple PredictorCorrector Method
To illustrate the method we will modify the simple Euler method, equation 10-6, as follows. The predictor equation is yn i jvi 2hF x ,y 10-28 which requires values at x _i and x to calculate _y i- Once we have an approximate value for y i, we use the corrector equation to get an improved value of y i. The corrector equation is used iteratively the value of y i is used to obtain an improved value of y i and the process is continued until a specified level of convergence is obtained. Two starting...
Chapter Number Series
are important in many areas of mathematics, such as the evaluation of transcendental functions, integrals or differential equations. Often, the sum of a number series is used as an approximation to a function that can't be evaluated directly. The approximation becomes more and more accurate as more terms are added to the sum for example, the value of e, the base of natural logarithms, can be evaluated by means of the sum of an infinite series If the sum of a series approaches a finite value as...
GaussJordan
Solves a set of N linear equations in N unknowns by the Gaussian-Jordan method. Returns the array of N unknowns, in column format only. G a u ss J o rda n 2 coeff_ma trix,const_ vector coeff_matrix a reference to an N row x N column array of coefficients const_vector a reference to an N row x 1 column array of constants The coeff_matrix and the const_vector tables can contain values or formulas. The GaussJordan2 function is an array function. You must select an N row x l column vertical range...
Assigning a Shortcut Key to a Sub Procedure
If you didn't assign a shortcut key to the macro when you recorded it, but would like to do so after the fact, choose Macro from the Tools menu and Macros from the submenu. Highlight the name of the macro in the Macro Name list box, and press the Options button. You can now enter a letter for the shortcut key CONTROL lt key gt or SHIFT CONTROL lt key gt in Excel for Macro recorded 6 22 2004 by Boston Coflege Windows, OPTION COMMAND lt key gt or SHIFT OPTION COMMAND lt key gt in Excel for the...
Problems Voi
Answers to the following problems are found in the folder Ch. 09 Simultaneous Equations in the Problems amp Solutions folder on the CD. 1. Solve the following system of four simultaneous equations 3 1 1. 1 2 - 2 3 - 1.8 4 11 3.2 1 2.1 2 3.2 3 2.2 4 0 1.6 1 1.1 2-3.2 3 2.4 4 -5 2. Current flow in a circuit is described by Kirchhoffs laws. A particular circuit network yielded the following three simultaneous linear equations Find the currents I I2 and 3 in the circuit network. 3. Solve the...
Differential Equations
For a function F x,y that depends on more than one independent variable, the partial derivative of the function with respect to a particular variable is the derivative of the function with respect to that variable while holding the other variables constant. For a function of two independent variables x and y, the partial derivatives are dF x,y dx y held constant and dF x,y idy x held constant . There are three second-order partial derivatives for the function F x,y amp F x,y ldx, amp F x,y...
Multiple Linear Regression Using LINEST
Now that we've gained some familiarity with LINEST, let's apply it to an example of multiple linear regression. The data table in Figure 13-7 lists the freezing points of solutions of ethylene glycol. We want to be able to obtain the freezing point of a solution of ethylene glycol with wt that is intermediate between the data values given in the table. Figure 13-7. Freezing point of ethylene glycol-water solutions, folder 'Chapter 13 Examples', workbook 'Dowtherm data', sheet 'Using Trendline'...
FourthOrder RungeKutta Method Implemented on a Worksheet
The spreadsheet in Figure 10-2 illustrates the use of the RK method to simulate the first-order kinetic process A gt B, again using initial concentration A 0 0.2000 and rate constant k 5 x 10 3. The differential equation is, again, equation 10-4. This equation is of the simple form dy dx F y , and thus only the yi terms of T to T4 need to be evaluated. The RK terms note that T is the Euler method term are shown in equations 10-12 through 10-15. Figure 10-2. Simulation of first-order kinetics by...
PredictorCorrector Methods
The methods in the preceding sections are one-step methods. They need only the value of the preceding point to calculate the value of the new point. Thus they are self-starting methods. Predictor-corrector methods, on the other hand, use the values of two or more previous points to calculate the value of the new point. They are not self-starting two or more known initial values are needed. Often a Runge-Kutta calculation is used to provide the needed values. Predictor-corrector methods use two...
The Shooting Method
The shooting method is a trial-and-error method. To solve a problem where the values of y are known at x0 and x , the boundaries of the interval of interest, we set up the problem as though it were an initial-value problem, with two knowns given at the same boundary for example, at x0- See Figure 10-17 for an example of an initial-value problem of this type the two knowns, shown in bold, are the value ofy at jc0 and a trial value of y' at jc0. Using the trial value of y we calculate y for a...
GaussSeidel
Solves a set of N linear equations in N unknowns by the Gaussian-Seidel method. Returns the array of N unknowns, in column format only. init_values coeff_matrix a reference to an N row x N column array of coefficients const_vector a reference to an N row x 1 column array of constants init_values a reference to an N row x 1 column array of initial values The coeff_matrix, const_vector and init_values tables can contain values or formulas. The optional init_values may be helpful for large arrays....
A Custom Function Procedure for the GaussSeidel Method
The Gauss-Jacobi and the Gauss-Seidel methods can easily be implemented as a custom function. Since the Gauss-Seidel method is more efficient, only the Gauss-Seidel custom function is presented here. The VBA code is shown in Figure 9-13. If any of the diagonal elements of the coefficients matrix are zero, a divide-by-zero error will be produced. Thus it is necessary either to ensure that the coefficients matrix does not contain any zero diagonal terms before beginning the solution, or to...
InterpC 1
Performs cubic interpolation in a 2-way table of x-, - and z-values. x and are the independent variable, z is the dependent variable. Returns the interpolated z-value corresponding to a specified x-value. InterpC2 xjookup, y_lookup, known_x 's, known_y's, known_z's xjookup the x-value for which you want to find the interpolated z-value yjookup the -value for which you want to find the interpolated z-value known_x's the set of x-values in the table independent values known_y's the set of -values...
HigherOrder Differential Equations
Differential equations of higher order can also be solved using the methods described in this chapter, since a differential equation of order n can be converted into a set of n first-order differential equations. For example, consider the following second-order differential equation equation 10-30 that describes the damped vibration of a mass m connected to a rigid support by a linear spring with coefficient ks and a vibration damper with coefficient kd, illustrated in Figure 1015. Figure...
The Personal Macro Workbook
The Record Macro dialog box allows you to choose where the recorded macro will be stored. There are three possibilities in the Store Macro In list box This Workbook, New Workbook and Personal Macro Workbook. The Personal Macro Workbook PERSONAL.XLS in Excel for Windows, or Personal Macro Workbook in Excel for the Macintosh is a workbook that is automatically opened when you start Excel. Since only macros in open workbooks are available for use, the Personal Macro Workbook is the ideal location...
Numerical Integration of Ordinary Differential Equations Part I Initial
A differential equation is an equation that involves one or more derivatives. Many physical problems, when formulated mathematically, lead to differential equations. For example, the equation k gt 0 describing the decrease in y as a function of time, occurs in the fields of reaction kinetics, radiochemistry or electrical engineering where y represents concentration of a chemical species, or atoms of a radioactive element, or electrical charge, respectively as well as in many other fields. Of...
Problems
Answers to the following problems are found in the folder Ch. 03 Matrices in the Problems amp Solutions folder on the CD. 1. Find the inverse and the determinant of the following matrices 0.75 0.5 0.25 0.5 1 0.5 0.25 0.5 0.75 2. Find the value of the determinant of each of the following.
SimultEqNL
Solves a set of N non-linear equations in N unknowns by Newton's iteration method. Returns the array of N unknowns, in column format only. SimultEqNL e lt 7 at o gt s, variables, constants equations a reference to an N row x N column array of coefficients variables a reference to an N row x 1 column array of constants constants a reference to an N row x l column array of initial values The coeff__matrix, const_vector and init__vaiues tables can contain values or formulas. The optional...
Dimensioning an Array
The Dim short for Dimension statement is used to declare the size of an array. Unless specified otherwise, VBA arrays begin with an index of 0. Thus the statement establishes array storage for 11 elements, Sample O through Sample 10 . However, you can specify that the arrays in your procedure begin with an array index of 1. Since worksheet ranges, worksheet functions and worksheet arrays use or assume a lower array index of 1, always specifying VBA arrays with lower array index of 1 can...
The Contents of the CD
The CD-ROM that accompanies this book contains a number of folders or other documents an Examples folder. The Examples folder contains a folder for each chapter, e.g., 'Ch. 05 Interpolation Examples.' The examples folder for each chapter contains all of the examples discussed in that chapter spreadsheets, charts and VBA code. The location of the Excel file pertinent to each example is specified in the chapter text, usually in the caption of a figure, e.g., Figure 5-5. Using VLOOKUP and MATCH to...
The GaussSeidel Method Implemented on a Worksheet
In the Gauss-Seidel method, an improved value of one of the variables is used in the iteration cycle as soon as it has been calculated. The Gauss-Seidel method is sometimes called the method of successive replacement. To illustrate, consider the same system of order 3 that was used previously to illustrate the Jacobi method. Again, begin with initial estimates of zero for x , x2 and x3. Now solve for each unknown value in turn, using the latest values of the variables as they are calculated...