Excel@ for Scientists and Engineers Numerical Methods Joseph Bill0 E. BICENTENNIAL BICENTENNIAL WILEY-INTERSCIENCE A John Wiley & Sons, Inc., Publication This Page Intentionally Left Blank Excel@ for Scientists and Engineers Numerical Methods THE WILEY BICENTENNIAL-KNOWLEDGE FOR GENERATIONS G a c hg eneration has its unique needs and aspirations. When Charles Wiley first opened his small printing shop in lower Manhattan in 1807, it was a generation of boundless potential searching for an identity. And we were there, helping to define a new American literary tradition. Over half a century later, in the midst of the Second Industrial Revolution, it was a generation focused on building the future. Once again, we were there, supplying the critical scientific, technical, and engineering knowledge that helped frame the world. Throughout the 20th Century, and into the new millennium, nations began to reach out beyond their own borders and a new international community was born. Wiley was there, expanding its operations around the world to enable a global exchange of ideas, opinions, and know-how. For 200 years, Wiley has been an integral part of each generation’s journey, enabling the flow of information and understanding necessary to meet their needs and filfill their aspirations. Today, bold new technologies are changing the way we live and learn. Wiley will be there, providing you the must-have knowledge you need to imagine new worlds, new possibilities, and new opportunities. Generations come and go, but you can always count on Wiley to provide you the knowledge you need, when and where you need it! Ek-&Ti% L 4 4 ! ! - . f . @- WILLIAM J. PESCE PETER BOOTH WlLEV PRESIDENT AND CHIEF EXECUTIVE PmCER CHAIRMAN OF THE BOARD Excel@ for Scientists and Engineers Numerical Methods Joseph Bill0 E. BICENTENNIAL BICENTENNIAL WILEY-INTERSCIENCE A John Wiley & Sons, Inc., Publication Copyright 02 007 by John Wiley & Sons, Inc. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 11 1 River Street, Hoboken, NJ 07030, (201) 748-601 1, fax (201) 748-6008, or online at http://www.wiley.comgo/permission. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic format. For information about Wiley products, visit our web site at www.wiley.com. Wiley Bicentennial Logo: Richard J. Pacific0 Library of Congress Cataloging-in-Publication Data is available. ISBN: 978-0-47 1-38734-3 Printed in the United States of America. 109 8 7 6 5 4 3 2 1 Summary of Contents .. Detailed Table of Contents .................................................................................. v11 Preface ................................................................................................................ xv Acknowledgments ............................................................................................... xix About the Author ................................................................................................. xix Chapter 1 Introducing Visual Basic for Applications .................................... 1 Chapter 2 Fundamentals of Programming with VBA .................................. 15 Chapter 3 Worksheet Functions for Working with Matrices ....................... 57 Chapter 4 Number Series ............................................................................. 69 Chapter 5 Interpolation ................................................................................ 77 Chapter 6 Differentiation ............................................................................. 99 Chapter 7 Integration ................................................................................. 127 Chapter 8 Roots of Equations .................................................................... 147 Chapter 9 Systems of Simultaneous Equations .......................................... 189 Chapter 10 Numerical Integration of Ordinary Differential Equations Part I: Initial Conditions ............................................................ 217 Chapter 11 Numerical Integration of Ordinary Differential Equations Part 11: Boundary Conditions ..................................................... 245 Chapter 12 Partial Differential Equations .................................................... 263 Chapter 13 Linear Regression and Curve Fitting ......................................... 287 Chapter 14 Nonlinear Regression Using the Solver ..................................... 313 Chapter 15 Random Numbers and the Monte Carlo Method ....................... 341 APPENDICES Appendix 1 Selected VBA Keywords ........................................................... 365 Appendix 2 Shortcut Keys for VBA ............................................................. 387 Appendix 3 Custom Functions Help File ...................................................... 389 Appendix Some Equations for Curve Fitting ............................................. 409 4 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 V This Page Intentionally Left Blank Contents 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 vii