Excel scientific and engineering cookbook pdf download

Excel scientific and engineering cookbook pdf download

Excel scientific and engineering cookbook pdf download,Book description

The book shows how to perform these useful tasks and others:Use Excel and VBA in general Import data from a variety of sources Analyze data Perform calculations Visualize the results Chances are you already use Excel to perform some fairly routine calculations. Now the Excel Scientific and Engineering Cookbook shows you how to leverage Excel to perform more It’s easy how to download this Excel Scientific And Engineering Cookbook PDF, for free. Excel Scientific And Engineering Cookbook PDF – This is probably one of the most About the Author of Excel Scientific And Engineering Cookbook PDF Free Download Book. Editorial Reviews About the Author As a naval architect and marine engineer, David M. Bourg Download As PDF: Excel Scientific And Engineering Cookbook Cookbooks Oreilly Books [PHB]≡ PDF Free Excel Scientific And Engineering Cookbook Cookbooks. Start reading ... read more

For example, if you type 1. In general, Excel is pretty smart about interpreting the data type you intend; however, sometimes it does need a little help. Also, sometimes you may want to change the format of the data to give it an appearance other than the default appearance assigned to it by Excel. In these cases you can manually specify the type and format of data contained in cells by accessing the Format Cells dialog box. Figure shows the Format Cells dialog box. Notice here I had a cell selected that contained the entry 1. This allows you to preview the results while making format changes. I find that one of the most common uses of formatting tasks is specifying the number of decimal places to show for numbers. Changing this setting is a format change only; it does not change the data itself. For example, if you enter You can also select one of the categories shown in the Format Cells dialog box to set the type of data. If you click each category in the list, a short description and other formatting options will appear in the Format Cells dialog box.

For example, if you select the Number category, the Format Cells dialog box will appear see Figure Format changes affect the currently selected cell and remain in effect until you change the format. If you set a cell to display Currency and then enter a new value in that cell, it will also be interpreted as currency. Format Cells dialog box Figure Format Cells with the Number category selected You need not always use the Format Cells dialog box to change cell formatting. Some of these formatting operations have been assigned to toolbar buttons for convenience. Take a look at Figure and notice the toolbar buttons that I highlighted.

These specific buttons allow you to change some cell format properties with the click of a button. The two other highlighted buttons respectively increase and decrease the number of decimal places to show. I should also point out that formatting changes take effect for all of the cells currently selected. The next recipe explains how to select more than one cell at a time. See Also I find the Number, Percentage, and Scientific format categories more than adequate for most scientific and engineering computing tasks; however, there may be occasions when you want to set your own specific data format for a unique application. In this case, you can actually specify your own format template in the Format Cells dialog box by setting up format codes using the Custom format category. Solution The easiest way to select a contiguous group of cells is to click and drag with the mouse.

Specifically, press the left mouse button to select a cell at one corner of the group and then hold down the mouse button while dragging to an opposite corner of the group of cells. The whole block of cells will be selected. You can click and drag to select a column of cells, a row of cells, or a block of cells, as described here. Discussion As with most things in Excel, there are number of alternative methods for selecting a group of cells. Clicking and dragging with the mouse is probably the most common way, but you can also perform the same operation using the keyboard. With a cell selected, hold down the Shift key and press one of the arrow keys to select a range of cells. You can also use the Shift key in conjunction with the mouse.

For example, select a cell and then hold down the Shift key and select another cell. This selects the range of cells between the two corner cells. If you want to select a group of cells that are not contiguous, you can do so by clicking each desired cell while holding down the Ctrl key. To select all of the cells in the spreadsheet, click the small rectangle in the grid heading bar just above the first row heading and to the left of the first column heading. To select an entire row, click the row heading. Likewise, to select an entire column, click the column heading.

You can use the Shift and Control keys to select groups of rows or columns from their headings, analagously to how you select groups of cells. When a group of cells is selected, all of the selected cells are highlighted as shown in Figure The selection methods described here are fairly common selection tasks that I use all the time. In some cases, even more control over selection is required. In this case, Excel has a feature that allows you to select cells based on some specific criterion for example, cells that contain text or cells that contain numbers. Group of selected cells The Go To Special dialog box allows you to select or go to cells that correspond to the selected criterion. For the text selected in Figure , I selected the Constants criterion, which enabled the checkboxes below Formulas, at which point I made sure only Text was checked.

You can do the same with numbers; for example, this is a convenient way to select all cells that contain only numbers, so that in a single shot you can change the number of decimal places shown for all numeric cells. Go To Special dialog box Solution You need to enter cell formulas in order to perform calculations on data contained in other cells. Entering a formula is simple enough: simply select a cell to hold the formula and then type the equals sign followed by your formula, which can refer to other cells that contain data. You can enter a formula in the same way you enter text, as discussed in Recipe 1. You can either enter the formula directly in a cell pressing Enter when done , or you can use the formula bar, as discussed earlier. The cell containing the formula will display the result of the formula and not the formula itself. To see the formula, just look at the formula bar when the cell is selected.

Or press the F2 shortcut key to edit the formula directly in the cell, as shown in Figure Entering formulas The cell in column C row 4 contains a formula to divide the number contained in column A row 1 by that in column A row 2. I pressed F2 to display the formula for editing directly in the cell. I should mention here that spreadsheets are a bit different from procedural programs with which you may be used to working. However, once you write a formula in a spreadsheet cell, it executes and remains up-to-date automatically. Therefore, if you change the data in cells referred to by a formula, the results of the formula are updated immediately. Excel takes care of making sure your spreadsheet calculations are updated whenever you change anything. Excel performs calculations on the stored values of cells not the displayed values , using 15 digits of precision.

To really leverage formulas, you must understand cell references. In Excel such references are called A1-style references. The letter refers to the column; the number refers to the row number. References to cells such as A1 are by default relative references. When a relative reference to a cell appears in a formula, that cell is referred to in terms of its relative position to the cell containing the formula. If you cut or copy the formula and then paste it someplace else, the cell references in the formula will automatically change so that they refer to cells in the same relative position to the formula as before.

This is better explained by way of example. I moved the formula one row down, so the relative cell references were also adjusted by one row. If I moved the formula to column B, then the cell references would have changed from A to B. This behavior may seem a bit unusual at first, but the advantage of it is clear when you are setting up tables of data and formulas to manipulate the data. For example, say I had two columns of data, B and C, as shown in Figure For all the remaining rows, I can simply copy and paste the formula in cell D2 into cells from D3 to D7, and Excel will automatically adjust the row numbers in the formulas. This saves you the time of having to rewrite each formula directly. To force Excel to refer to a specific cell you need to use an absolute cell reference. This essentially fixes the cell reference to always refer to a specific cell no matter how the formula is moved or copied.

In this case we fixed both the column and the row in the example cell reference. You can also press F4 to cycle through the reference styles for the cell being referred to. I use absolute references most often when performing calculations on datasets using formulas that refer to common scientific or engineering constants. Formulas cannot refer to themselves. For example, if you put a formula in cell A3, it cannot contain a reference to cell A3. Solution Yes, there are other ways to refer cells, such as the R1C1 style. Discussion Besides using the A1 style of cell reference, you can also use the so-called R1C1 style of cell reference.

The R1C1 style uses numbers to identify both rows and columns in a spreadsheet. For example, R1C1 refers to the cell in row 1 column 1. To use the R1C1 style you must first activate it. R1C1 reference style Also notice that Excel automatically changed the formulas. When using the R1C1 style, if you enter a cell reference like R3C5 i. Using brackets around the number following either R or C indicates relative cell references. For example, R[1]C[2] refers to the cell one row down and two rows to the right of the cell containing that reference. The cell two rows up and one row to the left would be referred to as R[-2]C[-1]. An R or C not followed by a number or a number in brackets refers to the same row or column as the cell containing the reference. See Also To learn more about cell references, see Recipe 1. For example, some formulas discussed in Recipe 1. Thus you need to know the syntax for referring to more than one cell.

You could use a number contained in another cell as an exponent; e. In this recipe I want to make you aware of the wide variety of built-in functions and how to access them in your spreadsheets. Excel has many built-in functions, which can be organized in the following categories: Database functions Database functions include functions that allow you to get information from database entries and perform some statistical analyses of data contained in databases. Date and time functions Date and time functions include functions that allow you work with and perform calculations using dates and times. There are many others, including functions to get the current date and time. Engineering functions Engineering functions include functions that allow you to work with complex numbers, convert between number systems, and convert between systems of measurement. There are also many other specialized functions for working with Bessel and Delta functions, among others.

Financial functions Financial functions include many functions that help you analyze things like accrued interest, depreciation, annuities, and investments, among many others. Logical functions Logical functions include a small set of useful logical functions. For example, the IF function allows you to evaluate a logical expression and return one value if the expression is true and another value if it is false. There are other functions including AND, OR, and NOT, among others, that help you construct logical expressions. Lookup functions Lookup functions are useful for looking up data in tables, matching values, returning rows or columns for cell references, and other tasks.

Math functions Math functions help you perform calculations, and include operations such as taking the absolute value of a number, finding square roots, working with logarithms and exponential functions, summing values of data contained in rows or columns, and working with trigonometric functions. This is perhaps the most used set of functions for the sorts of calculations discussed in this book. Statistical Functions Statistical functions are useful for calculating averages, standard deviations, variances, among many other calculations. There is also a set of functions useful for working with probability and distributions, including binomial, Weibull, and normal distributions, among others. Text and data functions Text and data functions include a set of functions for manipulating text and converting numeric data to text.

For example, there are functions to compare text strings, search for a substring within a larger text string, convert a string to all upper- or lowercase, and replace characters within a string. These functions are rather specialized and are useful for working with external programs and databases and collecting information from the host operating system, among other tasks. The recipes throughout the remainder of this book will use many built-in functions, most notably, but not limited to, the math, statistical, engineering, and logical functions. I do want to show you how to access these built-in functions now so you can be familiar with the process when you read the more specific recipes to follow in later chapters of this book.

The easiest way to use a function in a formula is to simply type the function. In this case, SUM is the function and the cell range is the argument of the function. At this point, you can type the arguments directly or, if the argument calls for a cell range, you can actually point and click to select the cell range, and Excel will automatically complete the cell reference argument for you. This reduces working with cell references in formulas to a point-andclick operation rather than typing everything out manually. The Function Arguments dialog box displays controls and notes to help you construct the arguments for the function. To use the Insert Function dialog, select the formula bar to begin writing a formula and then press the fx icon when you need to look up a function. This will bring up the Insert Function dialog box see Figure Or you can select a category from the drop-down list to view a list of functions within that category.

From there you can select the specific function you need and press OK. When you press OK, the Function Argument dialog box will open to assist you with supplying function arguments. You can nest functions. I usually have the task pane opened up with the Excel Help page displayed and open to the function reference section of the help guide. To open the function reference, open the task pane Ctrl-F1 and select Excel Help from the drop-down list at the top of the pane. It contains a concise reference to all of the menu options and built-in functions in Excel.

Solution Format your spreadsheet to make it more presentable and better organized. Discussion In Recipe 1. Take a look at the spreadsheet in Figure It also calculates the cumulative area under the curve from 0 to x. The results are plotted on a chart adjacent to the calculation table. Instead I want to focus on the calculation table. I could have simply filled in a column of x values, entered formulas for the y and area values, and left it at that. However, that would look messy. Formatting also serves as a form of documentation so that you can come back to a spreadsheet weeks or years later and quickly see what you did. Formatted spreadsheet There are many ways to format your spreadsheet. In this example, I added a text label, in italics, in the cell C2 to indicate the purpose of the value in cell D2. I also added some borders around the table of calculations and delineated the column headings with a filled background and bold text. I also centered the column labels above the data instead of using the default left justification for text.

These are some of the most common formatting tasks I make on virtually every spreadsheet I write. They are simple and effective. I personally use the cell formatting options the most. You can use the main menu bar or the shortcut key combination, Ctrl-1, to open the Format Cells dialog box. The other tabs give you control of other cell aspects: Alignment Set the position, orientation, and justification of text within a cell. You can also specify whether long text should wrap around, creating multiple lines of text in a single cell. Font Specify the font type, style, and size of text to appear in a cell. For example, you could specify Arial font type with a bold style and a size of 10 points. Border Add borders to any edge of a cell. You can select from a variety of line styles, including solid, dashed, and dotted, among others, and even set the line thickness.

Patterns Set the background color or pattern for a cell. While the Format Cells dialog box presents you with many cell formatting options, I prefer to use the toolbars for quick formatting tasks. You can customize this toolbar as discussed in Recipe 1. There may be times when you want to clear all the formats applied to a cell. See Also The formatting options discussed here are common and useful. There are other formatting options that may come in handy from time to time, although you probably will not use them too often. These options include the row, column, and spreadsheet formatting options mentioned earlier, as well as others such as conditional formatting, which allows you to set formatting for a cell contingent on the value of the cell relative to some criterion you specify. Solution Define your own custom style and apply it to cells, taking care of many format settings all in one step.

Some of these are familiar, including the normal cell style along with currency and percent styles. You can add your own style to this list. Click the combo box, type in a unique name for your new style, and then press the Add button. Now you can check off the format options you want to include in your style and modify the format settings by pressing the Modify button. Pressing the Modify button opens the Format Cells dialog box see Figure in Recipe 1. You can make any format selection you desire and close this dialog box when done. The settings you specified will be applied to your custom style. Once your style is defined, you have a few options for actually using it to format cells.

from the main menu bar to open the Style dialog box again. There you can select your desired style and press OK to close the dialog box. Your style will then be applied to the selected cell or cells. from the main menu bar to open the Customize dialog box. Select the Commands tab shown in Figure Customize dialog box Now select Format from the Categories list to show the format commands. From the Commands list look for the Style drop-down listbox shown in Figure After you close the Customize dialog box, that listbox will be available to you on the toolbar. To use the Style listbox, select the cells to which you want to apply a style and then select the desired style from the Style listbox that you added to the toolbar.

Solution Read the following discussion. For the most part, these work just as they do in any other Windows program. To move the contents of a cell from one place to another, use the Cut and Paste operations. To copy a cell into other cells, use the Copy and Paste operations. This means things like font, alignment, borders, and patterns will be copied as well. Copying a cell to another location has no effect on formulas that refer to the original cell. Sometimes you may want to cut or copy and then paste all of the format settings and data from one cell to another. When you select the Paste Special option, a dialog box like that shown in Figure appears. For example, if you want to copy and paste only cell format settings, you can select the Formats option. The Operation options allow you to specify some basic mathematical operations to perform on the data being copied and the contents of the destination cell. For example, say you copy a number from one cell and Paste Special it to a cell that already contains another number.

If you select the Add option, the sum of the two cell values will be entered in the destination cell. The Transpose option is similar to a matrix transpose operation. For example, if you copy a column of numbers and paste it with the Transpose option, it will be converted to a row of numbers. Here are some handy keyboard shortcuts for the basic cut-and-paste operations: Cut Ctrl-X Copy Ctrl-C Paste Ctrl-V There is no shortcut for the Paste Special operation; however, you can right-click anywhere in your spreadsheet to bring up a pop-up menu containing the Paste Special option. I find this quicker than accessing the main menu bar. Cut, Copy, Paste, and other options e. Solution Use cell names. Discussion Select the cell or cell range for which you want to define a name. from the main menu bar to open the Define Name dialog box see in Figure If you had the range selected when you opened this dialog box, then the cell range should already be correct.

Once your name is entered and the cell reference is correct, press the Add button. This format, using the sheet name followed by! Thus, you can use the name in a formula on any other sheet and it will still point to the cell on Sheet1. I find names quite useful. Then I name these so I can refer to them throughout the workbook. I find descriptive names more intuitive and easier to remember than cryptic cell references that span sheets in a workbook. Discussion Excel allows you to specify what constitutes valid data for any given cell. For example, say you had a spreadsheet that performed some calculations given a value that was input in a particular cell by a user other than yourself.

Such a situation could arise if, say, you write a spreadsheet that allows you to interpolate data based on a regression equation. In such a case, you would want to restrict the independent value input by the user to within the allowable bounds of the regression analysis. from the main menu bar. Figure shows the Data Validation dialog box. The Allow drop-down listbox allows you to select the type of data to allow for example, a decimal number, a whole number, a date, or text. Once you select the Allow type, the other controls will present additional qualifiers. In the example shown in Figure , I set 0 to as the valid data range. You can also specify ranges greater than some value, less than some value, and so on. Once you press OK, these changes take effect. If the user attempts to enter data that does not fit the valid data criteria, a message box will appear indicating bad data entry. You can specify exactly what you want the error message to display by entering your message under the Error Alert tab of the Data Validation dialog box.

Further, clicking the Input Message tab allows you to specify an information message to be displayed to the user whenever a cell using data validation is selected. This is useful for giving your users a clue as to what constitutes valid data for any particular cell. You could define a custom style reflecting these format settings and use the style as discussed in Recipe 1. In Chapter 2, I discuss macros and other programming tasks in much greater detail in the context of using Visual Basic for Applications. That said, you can record simple macros to automate common tasks as discussed here, without using Visual Basic. from the main menu bar to open the Record Macro Dialog box. In the Macro Dialog box, enter a descriptive name for your new macro in the Macro Name field.

Also enter a shortcut letter for the shortcut key combination Ctrl plus the letter you specify , which will be used to execute the macro on demand. In the Store Macro In field, select where you want the macro stored. The default is This Workbook, which stores the macro in the current workbook. However, if you want to be able to use the macro in any workbook, you should select Personal Macro Workbook. Execute the actions you want recorded. Use the shortcut key you specified in the Macro Dialog box to execute your macro. If you want the macro to execute relative to the position of the active cell when you start recording, make sure the Relative Reference button is selected on the Record Macro toolbar that appears when you start recording a macro. The Relative Reference button is shown in Figure Stop Recording Relative Reference Figure Record Macro toolbar You may also press the Stop Recording button to stop recording a macro instead of using the Stop Recording menu item as mentioned earlier.

menu or the Alt-F8 shortcut. The Macro dialog box allows you to run selected macros, delete macros, or redefine their shortcut keys. Solution Aside from simply inserting text in cells adjacent to cells containing data or formulas, you can use comments or equation objects to document your spreadsheets. Discussion Figure shows a sample spreadsheet that includes a comment and an equation object documenting parts of the spreadsheet. The equation object is the rectangle containing the mathematical expression for the area calculation. Both comments and equations are useful devices for documenting your spreadsheets. I often use comments to leave myself notes or reminders or to-do lists within my spreadsheets.

I use equations to document formulas used in my spreadsheets that will be used by others or included in reports or other presentations. Equations in standard mathematical form are much clearer and far easier to comprehend than cell formulas containing a bunch of cell references and operators all strung together. A comment box will appear with input focus, so you can immediately begin typing your comment. This will give input focus to the comment, allowing you to edit its message. To delete a comment, select the cell containing the comment and right-click to reveal a pop-up menu where you can select Delete Comment to actually delete the comment.

To add an equation, you need to use the Microsoft Equation Editor to insert an equation object into your spreadsheet. to show the Object Dialog box. From the list of Object Types in this dialog box, choose Microsoft Equation 3. When you press OK, the dialog will close, returning you to the spreadsheet in equation-editing mode see Figure The equation rectangle will appear, allowing you to enter an equation using the equation toolbar shown in Figure You can type text in the equation using the keyboard. To insert mathematical symbols, select them from the equation toolbar. Use the arrow keys on the keyboard to navigate an equation while editing. The Equation Editor is a bit cumbersome at first; I suggest you try it and practice until you get the hang of it. You can click and drag the resulting equation to place it anywhere in your spreadsheet.

To edit an existing equation, select it and then right-click on it to reveal a pop-up menu. To delete an equation, simply select it and press the Delete key on your keyboard. This will open a new Equation Editor window showing your equation along with the Equation Editor toolbar. The Equation Editor also contains a help menu option. This is useful for preparing technical reports in Word. Chapter 2 CHAPTER 2 Getting Acquainted with Visual Basic for Applications 2. At the time, I was not all that impressed with Visual Basic and preferred Visual C and a few Borland products over Visual Basic. When I first used Visual Basic, I saw no advantage over the other languages I was using at the time. All that changed, however, when Microsoft integrated Visual Basic with its Office suite of applications.

This opened the door to greater flexibility, more control, and the possibility of developing very powerful applications very quickly using VBA while leveraging the interface, features, and functionality of the host Office application Excel in our case. VBA allows you to do this sort of thing too. But before we do, you should be familiar with the VBA development tools, language, and interface to Excel. Just like the overview of Excel presented in Chapter 1, the material covered in this chapter is necessarily at an introductory level. If you are already familiar with VBA, you can skip this chapter without loss of continuity. Start exploring the IDE as described in the following discussion. Discussion The VBA IDE is basically the VBA editor. The editor allows you to perform all these development tasks in a single, integrated application.

You can access the IDE using the menu as mentioned earlier or you can use the shortcut Alt-F You can also use the Visual Basic toolbar, shown in Figure Visual Basic editor button Figure Visual Basic toolbar The IDE looks very much like any other Windows application, with a menu at the top, toolbars, and child windows in the client area of the window, as shown in Figure The VBA IDE is a multiple-document interface style of window. The area below the toolbars and menu is called the client area, which acts as a container for all the other child windows to be displayed within the IDE. Within the client area of the VBA IDE shown in Figure , there are three panels or windows.

The panel in the upper left is called the project panel, or project explorer window. It shows an Explorer-like view of all the currently open VBA projects. Each Excel workbook has a corresponding VBA project with a default name of the form VBAProject spreadsheet name. You can change this name by editing the Name property in the properties panel more on that panel in a moment. Each project consists of multiple objects and code modules. Every sheet in a workbook has an associated object shown in the project window, as does the workbook itself.

This has the advantage of giving our custom code global scope so that our custom functions will be accessible by any sheet in the workbook. Figure shows an example project with an open code window containing some VBA code. When you select any object or module in the project panel, its properties are displayed in the properties panel. In object-oriented programming lingo, an object is a construct that contains properties and methods. These properties describe what the object looks like i. Further, we could give it methods such as move forward, reverse, and stop. These are actions i. A more realistic programming example would be an object that represents, say, a text file. The properties could be things like size and number of words, while methods would be things like save and spellcheck. Take another look at the code window shown in Figure At the top of the window you should see two drop-down listboxes.

The listbox on the left is the object list and the one on the right is the procedure list. The object list shows a list of objects in the selected module, while the procedure list shows a list of procedures associated with the selected object. Procedures in VBA context refer to functions and subroutines. You can jump to any procedure in the code window by selecting it from the procedure list. This is a handy way to get around your code window when it fills up with a lot of code. You may have also noticed two little icons in the lower-left corner of the code window shown in Figure These icons consist of a few horizontal lines.

The icon on the left with fewer lines puts the code window in Procedure View when clicked, while the one on the right puts the code window in Full Module View. Procedure View shows only one procedure at a time in the code window, which is convenient if you find your code window too cluttered. In Procedure View, you have to use the procedure list to navigate from procedure to procedure. On the other hand, Full Module View shows the entire contents of the module. I usually leave the window in full view and navigate using both normal window scrolling and the procedure list. You write VBA statements instructions in code windows, as illustrated in Figure As you can see, the code illustrated in Figure is indented in blocks.

details in this recipe. For example, if you type a function declaration, VBA will automatically complete an End Function statement for you. These are designed to save you time and help reduce errors. Saving in either the VBA IDE or Excel saves both your VBA project and its associated workbook. To return to Excel from the VBA IDE, press the Excel icon in the VBA IDE toolbar. Or you can switch to Excel via the normal application switching methods in Windows; e. See Also This recipe is merely an overview of the VBA IDE. Press F1 in the VBA IDE to open the VBA help document and then press the Contents tab. The first topic is the user interface reference. As I mentioned in Chapter 1, I think the best way to learn the VBA IDE is to explore it and try things out. Solution Open the VBA IDE, create a code module for your workbook, and then start writing your custom procedures functions and subroutines.

Discussion When working with Excel and VBA, you write custom code in functions and subroutines. Essentially, the VBA procedures you write are extensions of Excel. This extensibility is what makes Excel, in my opinion, such a powerful computation tool. You can actually write a subroutine and call it main, using it as a starting point for subsequent code and calls to other procedures. Subroutines VBA subroutines have the basic form shown in Example Example VBA Subroutine Public Sub MySubroutineName Param1 As Integer, Param2 As Double ' Your code goes here End Sub Subroutines start with a scope qualifier, Public or Private, followed by the Sub keyword, followed by the subroutine name, which is then followed by an optional parameter list.

After the declaration comes the body of the subroutine, which is a collection of code statements that you supply. A subroutine definition is closed with End Sub. The scope qualifier specifies whether the subroutine has global or local scope. If you specify Public, then the subroutine has global scope and can be called from any code module. On the other hand, if you specify Private, then the subroutine has local scope and can only be called from the code module within which it is defined. The Sub keyword is mandatory and lets VBA know you are indeed declaring a subroutine procedure. Note that VBA will show keywords in a different color from that used for your code statements. I have my version set to display keywords in blue and code statements in black. from the main menu bar and select the Editor Format tab to customize the editor. After the Sub keyword, you must supply a subroutine name. the programming days of trying to compress names down to a meager eight characters! By the way, these naming rules apply to all names in VBA, including function names, variable names, constants, and parameters.

I should also point out that VBA is not case sensitive. It is, however, considered good style to use both upper- and lowercase characters to make your code more readable. For example, MySubroutineName is more readable than mysubroutinename. To make things even clearer, you could use the underscore character e. Parameters are optional for subroutines. Parameters may be passed into the subroutine and used in your code. A parameter list must include the parameter name followed by a type specifier in the form ParamName As DataType. You must separate the parameter name from the data type specifier using the As keyword. Separate multiple parameters in the list with commas as illustrated in Example Data types are types such as Integer, Double, Boolean, and Byte. See Recipe 2. By default, arguments passed into procedures as parameters are passed by reference. This means the actual value of the argument is not passed to the function; rather, a pointer to the argument is passed.

A pointer is a data type that stores the memory address of the object being pointed to. This gives the procedure direct access to the argument, which means the procedure can change the value of the argument. This may be what you want, but you need to be careful not to inadvertently change the value of an argument. To pass an argument by value, use the ByVal keyword in the parameter list. For example, to force Param1 to be passed by value in Example , you would write ByVal Param1 As Integer. Doing so ensures the argument passed in as Param1 will be preserved irrespective of how its value is used in the procedure. The drawback of passing parameters by value is performance degradation, since the actual value has to be copied in memory when the procedure is called. Alternatively, you may use the Call keyword. Both approaches are illustrated in Example Calling subroutines MySubroutineName 4, 2. Note that you cannot call subroutines from cell formulas. You can do so with functions, as discussed in a moment.

To actually add a new subroutine to a code module, you can either use the Add Procedure dialog box or simply start typing the subroutine in a code window. Personally, I find it more efficient to just type the subroutine directly. The Add Procedure dialog box, shown in Figure , allows you to specify what type of procedure you want to add, along with a few options. Functions VBA functions are similar to subroutines except for two key differences. First, functions are declared using the Function keyword instead of Sub. actually return a value and can therefore be used within expressions. Example shows an example function declaration, along with how the function may be called as part of an expression. Since this is a function that returns a value, you need to specify the data type of the value returned by the function.

Thus, you have to include the As DataType qualifier at the end of the function declaration. In this example, the return type is a Double. You can, of course, include other statements in the body of a function, in addition to the single statement illustrated in Example The important thing to remember is that the function exits, returning a value, on a code line where the function name is set to some value. The naming rules that apply to subroutines also apply to functions. Adding a function is similar to adding a subroutine. You can either type it in directly as I prefer to do, or you can use the Add Procedure dialog. Table summarizes some of the VBA data types that I use most often for the sorts of calculations covered in this book. VBA data types Data type Storage Values Boolean 2 bytes True or False Byte 1 byte 0 to Integer 2 bytes —32, to 32, Double 8 bytes —1.

For the most part, the types shown in Table are sufficient for the sorts of programming tasks discussed throughout this book. I mentioned in Chapter 1 that Excel performs calculations using double precision by default. To ensure double-precision calculations in Visual Basic, be sure to use the Double data type. When writing expressions using literal numeric values, you can use Visual Basic type-declaration characters to let Visual Basic know what data type you want it to consider when evaluating your expression. The character is the type-declaration character for Double data types. In fact, the VBA IDE will help you out here. If you type a number like 3. There are other type-declaration characters recognized by Visual Basic. Speaking of strings, while this book focuses mainly on scientific and engineering calculations where doubles and integers are more prevalent, strings are sometimes useful for formatting output.

Such output may go to spreadsheet cells in the form of text or datafiles written directly to disk. Solution Use the Dim statement to declare a variable, as illustrated in Example Declaring variables using the Dim statement Dim a As Double Dim n As Integer Dim statements are of the general form Dim VariableName As DataType, where Dim and As are VBA keywords, DataType is one of the VBA data types as discussed in Recipe 2. Discussion You can use Dim statements to declare local variables within procedures by including the Dim statements in the body of the procedure. Such local variables can be used only by the procedures within which they are declared. To declare global variables that have module-level scope, place the Dim statements at the beginning of the code module before any procedure declarations. Such global variables can be used by any procedure in the module within which the variables are declared.

To declare global variables that can be used by any code module within a project, use a Public statement like this Public VariableName As DataType. You can also declare module-level variables using the Private statement, but this is the same as using the Dim statement. You can declare multiple variables on a single line by separating the variable declarations with commas, as shown in Example Multiple declarations Dim a As Double, Dim b As Double, Dim c As Double Dim d, e, f As Double The first line in Example declares three Double type variables, a, b, and c. The fact is, only variable f in the second line is a Double; d and e are of Variant type. So what gives? VBA requires an explicit data type to follow each variable name; otherwise, it assumes a Variant type. A Variant is a generic data type that can represent any of the other explicit data types. I recommend using explicit data types as much as possible and using Variants only when absolutely necessary for example, when a built-in VBA procedure or function requires Variant parameters.

The VBA IDE provides a useful way to quickly find the declaration of any variable in your code. You can select the variable name in your code and use the shortcut Shift-F2 to automatically jump to the statement that declares the variable. Constants declared within procedures have local scope; that is, they are available only within the procedure. Constants declared outside of any procedures at the beginning of a code module have module-level scope and can be used by any procedure within the module. As with variables, you can use the Public keyword, as illustrated in Example , when declaring constants at the beginning of a code module to make the constant available to all procedures in the project. A constant is just that, constant.

Once a constant is declared with its value set, you cannot change its value. Constants are useful for defining descriptive names to commonly used values such as scientific or engineering coefficients. For example, you could define a constant to represent π and use the constant Pi throughout your calculations rather than write out the numerical value. Excel includes a built-in worksheet function PI that returns the value of π. You can actually use that function in your VBA code by accessing the function through the WorksheetFunction object, as in WorksheetFunction. Solution You declare arrays just as you do variables, except you specify the size of the array in the declaration, as shown in Example Declaring arrays Dim y 1 To 4 As Double Dim x 4 As Double Dim M 1 To 8, 1 To 8 As Double Dim N 8, 8 As Double Arrays are declared with their size specified in parentheses following the array name.

Discussion When you use the To keyword in an array size specification, the first number is the lower bound and the second number the one following To is the upper bound of the array. You can skip the To keyword and simply put the desired size i. In this latter approach, the first array index, the base, is 0 unless you use the Option Base statement. Use the Option Base statement at the beginning of a code module before your procedures to specify the default base for arrays. For example, Option Base 1 sets the base to 1, while Option Base 0 sets the base to 0. PDF is a hugely popular format for documents simply because it is independent of the hardware or application used to create that file.

This means it can be viewed across multiple devices, regardless of the underlying operating system. You can rely on simple Budget Templates to create a simple yet an effective budget. You can use the budget for daily, monthly or weekly purchase plans. The act of building a simple budget generally entails listing your incomes on one side a. An oversized pdf file can be hard to send through email and may not upload onto certain file managers. Luckily, there are lots of free and paid tools that can compress a PDF file in just a few easy steps. RAW Paste Data Copied. Public Pastes. Python 52 min ago 4. Lua 1 hour ago JSON 1 hour ago 7.

Nancy Greene's Ownd. Excel scientific and engineering cookbook pdf download. Author: David M Bourg. Excel Scientific and Engineering Cookbook. Read more. Excel Financials Cookbook. Advanced Excel for Scientific Data Analysis. Global optimization: scientific and engineering case studies. Excel Scientific Engineering Cookbook. New release excel scientific and engineering cookbook. Extend the capabilities of Excel spreadsheets by designing. Bourg, D. M, Excel Scientific and Engineering Cookbook. That is why engineers will have to excel in a number of.

Betamentality From an analogue to a digital learning environment is not a recipe for success. Youll learn how to. Jul 20, Free UK delivery. MA Engineering Mathematics and Statistics. Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Public Pastes. Arduino 7 min ago 0. Arduino 8 min ago 0. Arduino 9 min ago 0. Arduino 10 min ago 0. Arduino 12 min ago 0. Arduino 13 min ago 0. Moreover, I provide many cross-references throughout the book to point you toward relevant background material. What I mean by this is that I want to show how to use Excel and VBA in general, how to import data from a variety of sources, analyze data, perform calculations, and finally visualize the results for interpretation and presentation.

This book consists of 14 chapters. Experienced Excel users can safely skip this chapter. Experienced VBA users can safely skip this chapter. Chapter 3, Collecting and Cleaning Up Data Shows you how to import data into Excel from a variety of sources, including text files, databases, and the Web. Chapter 4, Charting Shows you how to visualize data in Excel. This chapter shows a variety of example charts, including line, bar, 3D surface, and combination charts. Chapter 5, Statistical Analysis Shows you how to perform standard statistical calculations in Excel, such as computing summary statistics, calculating confidence intervals, and performing analysis of variance, among other calculations. Chapter 6, Time Series Analysis Is an extension of the data analysis topics covered in Chapter 5, but specifically focusing on conducting various time series analysis tasks, including forecasting and Fourier transforms in Excel.

Chapter 8, Curve Fitting and Regression Continues discussion of data analysis, specifically focusing on curve fitting and regression analysis. Chapter 9, Solving Equations Shows you how to leverage Excel to solve linear and nonlinear equations, as well as linear and nonlinear systems. Chapter 10, Numerical Integration and Differentiation Shows you how to perform various numerical integration calculations, such as computing areas and moments of areas, among others. This chapter also shows you how to perform numerical differentiation using standard difference formulas and how to improve the results. Chapter 11, Solving Ordinary Differential Equations Shows you how to solve initial value and boundary value problems using standard numerical techniques such as the Runge-Kutta and shooting methods. Chapter 12, Solving Partial Differential Equations Shows you how to leverage Excel to help solve problems involving partial differential equations.

This chapter also shows you how to implement a not so standard method—a genetic algorithm—using VBA. Also used for code exampes and for text that you type in literally. Constant width italic Indicates placeholders for which you substitute an actual name in examples, cell formulas, and Excel function calls. Constant width bold Used to highlight segments of code. Italic Used for URLs, file extensions, filenames, and directory names. Also used for emphasis and to introduce new terms. Indicates a tip, suggestion, or general note. Using Code Examples This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. Answering a question by citing this book and quoting example code does not require permission. We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. The information in this book has been tested and verified to the best of our ability, but mistakes and oversights do occur.

Special thanks go to my editor, Simon St. Laurent, for his skillful editing and flexibility in allowing me to pursue my vision for this book. Dongming Wei at the University of New Orleans for his thoughtful technical review. And thanks to my friend and Excel power user extraordinaire Kenneth Humphreys for his meticulous review and suggestions. Finally, I want to dedicate this book to my lovely wife, Helena, and beautiful daughter, Natalia, and thank them for their unwavering support. Experienced users may skip this chapter without loss of continuity. The techniques and features summarized in this chapter will be put to use in the recipes throughout the remainder of this book. Solution Start Excel and begin poking around the interface to become familiar with it. Discussion This solution sounds simple and it is. The worst you can do is create a nonsensical spreadsheet—in which case, you can exit Excel without saving anything and start over fresh.

I have a four-yearold daughter who loves playing around in Excel because of the paperclip office assistant. Excel is just like any other standard Windows application: it consists of a multipledocument interface main window containing a main menu bar, toolbars, and a status bar. A multiple-document interface main window is just a window that acts as a container for several of the same type of files, so to speak. For example, in Excel you can have more than one spreadsheet open at once and switch between them. Word is another example; you can have many different Word documents open at once and switch between them as desired. By contrast, an application based on a singledocument interface window is one that allows you to open up and work on only one file at a time. Many specialized programs are based on this paradigm. Figure shows a screenshot of Excel as installed on my computer. Figure These cells are arranged in an organized matrix, with letters identifying columns and numbers identifying rows.

This is the spreadsheet. By default, Excel starts with a blank workbook. A workbook contains multiple sheets, which can be linked together. You can add or remove spreadsheets from a workbook, rename the sheets, put data and formulas on one sheet and refer to them from another sheet, and so on. Edit Access the standard cut, copy, paste, find, replace, and other operations. Insert Add various spreadsheet elements such as cells, rows, and columns, as well as other objects such as notes, comments, and pictures. Format Customize the formatting of text, cells, rows, columns, and other aspects of your spreadsheets. Tools Access various useful tools including spelling tools, change tracking, formula auditing, and a host of useful analysis tools such as goal seek, solver, and data analysis tools.

The tools menu also provides access to the Customize and Options windows, allowing you to tailor Excel to your specific needs and preferences. Data Access various data management tools such as sorting, validation, and tables. These nine menus comprise the standard menus built into Excel. I encourage you to click through these menus in your version of Excel to familiarize yourself with them a little. As with most standard Windows programs, you can access the menu using the mouse or the keyboard. Accessing the menu with the mouse is, of course, as simple as pointing and clicking. Notice in Figure that the View menu is currently visible. Also notice the little down arrow icon at the bottom of the menu.

Whenever you see this icon on a menu, it means there are more menu items available to you, but they are hidden. By default, MS Office applications hide less frequently used menu items from view, showing only your most frequently used items in an effort to simplify the menus. If you click the down arrow icon, the full menu will appear.

Excel Scientific and Engineering Cookbook,Publisher resources

About the Author of Excel Scientific And Engineering Cookbook PDF Free Download Book. Editorial Reviews About the Author As a naval architect and marine engineer, David M. Bourg The act of building a simple budget generally entails listing your incomes on one side a. An oversized pdf file can be hard to send through email and may not upload onto certain file Download As PDF: Excel Scientific And Engineering Cookbook Cookbooks Oreilly Books [PHB]≡ PDF Free Excel Scientific And Engineering Cookbook Cookbooks. Start reading Bourg, D. M, Excel Scientific and Engineering Cookbook. That is why engineers will have to excel in a number of. Betamentality From an analogue to a digital learning environment is not Now the Excel Scientific and Engineering Cookbook shows you how to leverage Excel to perform more complex calculations, too, calculations that once fell in the domain of It’s easy how to download this Excel Scientific And Engineering Cookbook PDF, for free. Excel Scientific And Engineering Cookbook PDF – This is probably one of the most ... read more

In this example, I added a text label, in italics, in the cell C2 to indicate the purpose of the value in cell D2. I often use the Beep statement to play a short tone during execution of a procedure. Start by pressing the button below! When you select the Paste Special option, a dialog box like that shown in Figure appears. Mastering Multiplication 7. You can add or remove spreadsheets from a workbook, rename the sheets, put data and formulas on one sheet and refer to them from another sheet, and so on.

This window can be used for typing in commands or writing output using the Print statement. It shows fluid field variables velocity, pressure, density as functions of position on a 2D grid. Solution Yes, excel scientific and engineering cookbook pdf download, there are other ways to refer cells, such as the R1C1 style. Ranking and Percentiles 5. Entering formulas The cell in column C row 4 contains a formula to divide the number contained in column A row 1 by that in column A row 2. Experienced Excel users can safely skip this chapter.