Excel Scientific and Engineering Cookbook

Content preview·Buy reprint rights for this chapter

This chapter is necessarily basic and is intended for readers new to Excel or with little Excel experience. Experienced users may skip this chapter without loss of continuity. The aim of this chapter is to get you acquainted with Excel's interface and the main features and capabilities that we'll use throughout the remainder of this book. I'm also going to show you several techniques that will help you create presentable spreadsheets and allow you to work efficiently in Excel. The techniques and features summarized in this chapter will be put to use in the recipes throughout the remainder of this book.

You've never used Excel before and don't know where to start.

Start Excel and begin poking around the interface to become familiar with it.

This solution sounds simple and it is. I believe there's no better way to learn something on the computer than to just start exploring and experimenting. Don't worry about breaking anything. 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-year-old daughter who loves playing around in Excel because of the paperclip office assistant. If she can't ruin anything with all her random clicking and typing, then I think you're in good shape to do some purposeful exploring. That said, I won't just throw you to the wolves, but will point you in the right direction to begin your journey.

Excel is just like any other standard Windows application: it consists of a multiple-document 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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter is necessarily basic and is intended for readers new to Excel or with little Excel experience. Experienced users may skip this chapter without loss of continuity. The aim of this chapter is to get you acquainted with Excel's interface and the main features and capabilities that we'll use throughout the remainder of this book. I'm also going to show you several techniques that will help you create presentable spreadsheets and allow you to work efficiently in Excel. The techniques and features summarized in this chapter will be put to use in the recipes throughout the remainder of this book.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've never used Excel before and don't know where to start.

Start Excel and begin poking around the interface to become familiar with it.

This solution sounds simple and it is. I believe there's no better way to learn something on the computer than to just start exploring and experimenting. Don't worry about breaking anything. 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-year-old daughter who loves playing around in Excel because of the paperclip office assistant. If she can't ruin anything with all her random clicking and typing, then I think you're in good shape to do some purposeful exploring. That said, I won't just throw you to the wolves, but will point you in the right direction to begin your journey.

Excel is just like any other standard Windows application: it consists of a multiple-document 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 single-document 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 1-1 shows a screenshot of Excel as installed on my computer. You'll notice that it looks like any other Windows application, with one distinct difference.

Figure 1-1: Excel's main window

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've familiarized yourself with Excel's layout and now you're ready to enter data into a spreadsheet but don't know how.

Select a cell in the spreadsheet and starting typing. Press Enter when done.

When you select a cell in a spreadsheet, it becomes highlighted with a thick black border, as shown in Figure 1-3. In that figure the cell in row 3 and column B is the selected cell. Notice the row and column headings of the currently selected cell are highlighted. Once a cell is selected in this manner, you can simply start typing on the keyboard to insert text in the cell. Press Enter when you are finished and notice that the cell below the one within which you entered text is now automatically selected. This allows you to type and enter text in a column of cells rapidly, without having to select the next cell using the mouse.

Figure 1-3: Excel Help task pane

Upon entering text, you can also press the Tab key to commit your entry and move the cell selection to the next cell to the right. Alternatively, you can use the arrow keys on your keyboard to commit an entry and move to any cell adjacent to the cell within which you've entered text. Of course you can always use the mouse to click on a cell, selecting it for input; however, doing this may slow you down if you are trying to enter text in a contiguous group of cells, since you'll have to remove your hands from the keyboard very often.

You can also enter text into a cell using the formula bar (located just above the spreadsheet grid and just below the toolbars), as shown in Figure 1-4. The formula bar has an f _x icon, adjacent to a long white rectangular area.

Click anywhere in the white rectangular area of the formula bar to give it the input focus and then start typing. Your text will appear in both the formula bar and the currently selected cell. To commit the entry, press the Enter key or press the green checkmark icon on the formula bar. To cancel your entry, press the red × icon or press the Esc key on your keyboard.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've learned how to enter text from the previous recipe, but now you want to enter data other than text (e.g., numbers, dates, and currency).

Enter the data just as you would text and let Excel automatically figure out its type, or use the Format → Cells... menu to open a dialog box allowing you to manually format the data type for a cell.

In the previous recipe, I had you simply enter text in cells. Whether you entered a word or a number, Excel automatically figured out what type of data you entered. In general, input starting with letters is automatically interpreted as text, and input starting with numbers is automatically interpreted as numeric. There are some special cases worth noting here. Preceding any string of characters—numbers or letters—with the ' symbol forces Excel to interpret the data as text. Preceding a string of numbers with the $ symbol forces Excel to interpret the data as currency. Using E (or e) while entering a number in scientific notation forces Excel to interpret the string as a number in scientific notation. Entering numbers with dashes between them will cause Excel to interpret the number as a date. I encourage you to try entering various types of data like those I describe here to see how Excel handles the data. In some cases you'll notice that Excel will reformat your data a little. For example, if you type 1.2345e3 in a cell, it will appear as 1.23E+3 in the cell and 1234.56 in the formula bar (when the cell is selected).

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 . You can do so by selecting Format → Cells... from the menu or by using the shortcut key combination, Ctrl-1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You want to select more than a single cell at one time; for example, so you can set the format of a group of cells at once rather than individually.

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.

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. This is called the Select All button (the Select All shortcut is Ctrl-A ).

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 1-8.

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).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've entered data and are ready to perform some calculations, but don't know how.

Figure 1-9: Group of selected cells

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. A formula to add two numbers contained in cells A1 and A2 would look like this: =A1+A2.

All formulas start with an equals sign, as in the =A1+A2 example. You can enter a formula in the same way you enter text, as discussed in Recipe 1.2. 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 1-10.

Figure 1-10: 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.

Formulas may contain the usual mathematical operators such as +, -, /, and *, as well as any number of other built-in functions, as discussed in Recipe 1.10.

I should mention here that spreadsheets are a bit different from procedural programs with which you may be used to working. With a procedural program, you have to explicitly run it after writing the code (we'll do this when we write custom macros and functions using Visual Basic for Applications in Chapter 2). 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've seen the use of the A1 style of cell reference in Recipe 1.5 and are wondering if there are other ways to refer to cells.

Yes, there are other ways to refer cells, such as the R1C1 style.

Besides using the A1 style of cell reference, you can also use the so-called R1C1 style of cell reference. This is not the default style, but in some cases it can be more intuitive or conducive to matrix operations and programming, as we'll see later.

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. To do so, go to the Tools → Options... menu to open the Options dialog box. Once it's open, press the General tab (see Figure 1-12).

Figure 1-12: General tab in the Options dialog box

Check the box next to R1C1 reference style under the Settings heading to activate the R1C1 style. When you press OK and return to your spreadsheet, you'll see that the column headings have changed from letters to numbers, as shown in Figure 1-13.

Figure 1-13: R1C1 reference style

Also notice that Excel automatically changed the formulas. For example, the formula in cell D7 (now R7C4) was =B7*C7; now it's =RC[-2]*RC[-1], which is a relative reference in R1C1 style.

When using the R1C1 style, if you enter a cell reference like R3C5 (i.e., R followed by a number and C followed by another number), you are using absolute cell references. The equivalent in A1 style would be $E$3. 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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Sometimes you need to refer to a group of cells, not just a single cell. For example, some formulas discussed in Recipe 1.10 take more than a single cell as an argument. Thus you need to know the syntax for referring to more than one cell.

Use cell ranges .

A cell range is simply a contiguous group of cells in rows or columns, or both. For example, the cell reference A1:A10 refers to the range of cells in column A from row 1 to row 10. The colon character (:) is used to indicate a range reference. The reference A1:B10 refers to the range of cells from column A row 1 to column B row 10. Technically speaking, the cell reference A1 is itself a range of only a single cell; thus, in a sense, all cell references can be thought of as ranges.

See Recipe 1.10 for examples on where ranges are required as function arguments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You want to learn the specific order in which Excel executes operations in formulas.

Excel performs operations in formulas from left to right following the leading equals sign. In doing so, Excel performs specific operations, if they are encountered in your formula, in the following order of precedence: negation, exponentiation, multiplication and division, and addition and subtraction.

You can change operator precedence using parentheses. For example, if you enter the formula =A1+B2/C3 in a cell, Excel will perform the division first and then the addition. This may be what you want—that is, you want to add the result of B2 divided by C3 to A1. However, if you intend to divide the sum of A1 and B2 by C3, then you need to write =(A1+B2)/C3. The parentheses force Excel to perform the addition operation first, followed by the division.

You can also nest parentheses. For example you could write =((A1+B2)/C3)*C4, and so on. I find it is always good practice to use parentheses liberally to be sure your formula is executed as intended.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You need to write a formula that raises some number to some power but you don't know the syntax for exponentiation in Excel.

Use the caret (^) operator.

Raising a number to some power is a common calculation task. In Excel, the caret operator (^) is used for exponentiation. For example, to raise the number contained in cell A1 to the third power, you could enter the formula =A1^3. You can use whole number exponents as well as decimal numbers or even other operations. For example, the formulas =A1^0.25 and =A1^(1/4) both raise the value in cell A1 to the one-fourth power.

You need not hardcode exponents. You could use a number contained in another cell as an exponent; e.g., =A1^C5, or =A1^(C5+D10), or =(A1+A2)^(C5/E8), and so on.

See Recipe 1.10 for other common mathematical functions .

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The basic mathematical operators (+, -, /, *, and ^) are not enough to perform all the calculations you need.

Use Excel's built-in functions, such as ABS( ), SQRT( ), and SIN( ), as needed.

Throughout this book, I'm going to use all sorts of built-in functions in various calculations from data analysis to unit conversions to various engineering calculations. 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. For example, you may want to calculate the number of working days between two dates, in which case you can use the NETWORKDAYS function. 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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've got data and some calculations, but the spreadsheet looks unpresentable.

Format your spreadsheet to make it more presentable and better organized.

In Recipe 1.3, I discussed how to format cells to specify the type of data (e.g., text, numbers, currency, etc.). In this recipe, I'll show you a few other formatting techniques. Take a look at the spreadsheet in Figure 1-16.

This simple spreadsheet merely calculates values for a function of the form y = x ⁿ, where n is the exponent shown on the spreadsheet. 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. Chapter 4 covers charting in detail, so I won't discuss the chart here. 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. More than likely you'll want to include your calculations in reports or share them with others, so making your spreadsheets presentable is a good idea. 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. There's (almost) nothing worse than opening an old spreadsheet and seeing just a grid of scattered numbers.

Figure 1-16: 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. If you're a keen observer, you may have noticed that I also changed the column width of several columns—I reduced the width of columns A and B so they wouldn't take up too much space and I increased the width of column E to accommodate the area column label. These are some of the most common formatting tasks I make on virtually every spreadsheet I write. They are simple and effective.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You find yourself applying the same formats repeatedly to different cells and would like to save time when doing so.

Define your own custom style and apply it to cells, taking care of many format settings all in one step.

Select Format → Style from the main menu bar to open the Style dialog box as shown in Figure 1-17.

Figure 1-17: Style dialog box

The drop-down combo box next to the "Style name" label contains a list of predefined styles that you can apply to cells. 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 1-6 in Recipe 1.3). 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. One way to apply your custom style is to select the desired cell (or cells) and then select Format → Style... 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).

A faster approach involves adding a style drop-down list component to one of the toolbars on the main window so that you can simply select cells and then select a style to apply from the toolbar.

To add a style drop-down list to a toolbar, select View → Toolbar → Customize... from the main menu bar to open the Customize dialog box. Select the Commands tab (shown in Figure 1-18).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You'd like to take advantage of standard Windows copy-and-paste functionality but aren't familiar with the caveats for doing so within Excel.

Read the following discussion.

Under Excel's Edit menu , you'll find the usual Cut, Copy, and Paste menu items. 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. There are a few things to be aware of when performing these operations in Excel:

When you cut or copy and then paste, all of the cell's attributes are copied, along with the data or formula that it contains. This means things like font, alignment, borders, and patterns will be copied as well.
When you cut and paste data to a new location, any formulas referring to its original cell location are automatically updated to refer to its new location. Copying a cell to another location has no effect on formulas that refer to the original cell.
When you copy a cell containing a formula, relative cell references in the formula will be adjusted by the relative distance of the pasted cell from the copied cell.
When you cut a formula and paste it to a new location, the formula will remain unchanged; i.e., it should still refer to the same cells and give the same results as before the cut-and-paste operation.

Sometimes you may want to cut or copy and then paste all of the format settings and data from one cell to another. In cases when you don't, you can use the Paste Special option, which is also under the Edit menu. When you select the Paste Special option, a dialog box like that shown in Figure 1-19 appears.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You frequently use a particular cell in formulas and are tired of typing the reference. You'd like a more descriptive syntax, similar to that used for variable or constant names in traditional programming languages.

Use cell names .

Select the cell or cell range for which you want to define a name. Next, select Insert → Name → Define... from the main menu bar to open the Define Name dialog box (see in Figure 1-20).

Figure 1-20: Define Name dialog box

Enter a name for the selected cell range in the edit field under "Names in workbook." Make sure the "Refers to" field does indeed refer to the cell range you'd like to name. If you had the range selected when you opened this dialog box, then the cell range should already be correct. If you didn't have it selected, press the icon in the lower-right corner; this will allow you to select the proper range without leaving the Define Name dialog box. Once your name is entered and the cell reference is correct, press the Add button. You'll now be able to use that name to refer to the associated cell range in any formulas in your workbook.

Notice that the cell reference in the "Refers to" field in Figure 1-20 includes the sheet name, Sheet1, followed by the exclamation mark (!), which precedes the absolute A1-style cell reference. This format, using the sheet name followed by !, makes the reference refer to the specified cell on that specific sheet. 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. Typically, if I'm performing calculations that require the use of empirical constants or commonly used data, I'll set up a specific sheet in my workbook that contains only constants and commonly used data (or formulas). 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You want to make sure users of your spreadsheet don't enter inappropriate data.

Use Excel's Data Validation feature.

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. Let's say you want to restrict the range of values the user can enter in the input cell, in order to minimize the possibility of misuse. 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. Basically, you don't want the user to attempt to extrapolate beyond the data range used in the regression analysis, as such extrapolations can sometimes yield dangerously inaccurate results.

To specify validation for a cell, select the cell you want to set validation for and then open the Data Validation dialog box by selecting Data → Validation... from the main menu bar. Figure 1-21 shows the Data Validation dialog box.

Figure 1-21: 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 1-21, I set 0 to 560 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You find yourself repeating the same actions over and over and would like to automate that process.

Use Excel's macro-recording feature to record your actions, which can then be executed again using a keyboard shortcut.

Let's say you find yourself repeatedly applying the same format settings to cells; for example, you select a cell, set the font style to bold, set justification to center, and apply a pattern and a border. You could define a custom style reflecting these format settings and use the style as discussed in Recipe 1.12, or you could record a macro to automate the process of setting these formats.

Although I'm using formats as an example, you should be aware that the macro-recording feature lets you record any sequence of actions taken in Excel, thus allowing you to automate almost anything. 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.

Take these steps to record a macro:

Select Tools → Macro → Record New Macro... 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. Press OK when you're done; this will return you to the spreadsheet in macro-recording mode.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You'd like to add comments to your spreadsheet, just as you'd comment your code in a traditional programming language.

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.

Figure 1-23 shows a sample spreadsheet that includes a comment and an equation object documenting parts of the spreadsheet.

Figure 1-23: Spreadsheet with comment and equation

The comment is the rectangle containing the text "David Bourg: This is the exponent," with an arrow pointing to cell D2. 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.

To add a comment, first select the cell to which you want the comment attached, then select Insert → Comment from the main menu bar. A comment box will appear (with input focus, so you can immediately begin typing your comment). When you're done typing, use the mouse to select where on the spreadsheet you want the comment box to be placed. To edit an existing comment, select the cell containing the comment and select Insert → Edit Comment from the main menu bar. 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You're off to a great start and would like to know where you can go to find help within Excel .

You can access Excel's online help system by selecting Help → Microsoft Excel Help from the main menu bar. Or you can use the shortcut Ctrl-F1 to access the Help task pane, enabling you to select the Excel Help page, where you can click the "Table of Contents" link or enter a search phrase to search for help topics.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

I started using Visual Basic in the early 1990s, way back in the days of DOS and Windows 3.1. At the time, I was not all that impressed with Visual Basic and preferred Visual C and a few Borland products over Visual Basic. I still prefer C/C++ for most of the commercial applications I develop for clients; however, when I need to perform quick calculations for my own research or when a client needs something done quickly, I do now turn to Visual Basic for Applications (VBA).

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).

Indeed, I've since started integrating standalone applications developed for my own clients using C/C++ with Office applications such as Word, Outlook, and Excel. For example, a standalone application written in C/C++ can perform calculations and then automatically send the output to Word in a nicely formatted report, which can then be emailed automatically using Outlook. VBA allows you to do this sort of thing too. We'll cover some of these cool things, such as making Excel talk to Word, later in the book. But before we do, you should be familiar with the VBA development tools, language, and interface to Excel.

This chapter is meant to give you that familiarity if you don't already have it. 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.

You want to start exploring the Visual Basic for Applications (VBA) language and don't know where to begin.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

I started using Visual Basic in the early 1990s, way back in the days of DOS and Windows 3.1. At the time, I was not all that impressed with Visual Basic and preferred Visual C and a few Borland products over Visual Basic. I still prefer C/C++ for most of the commercial applications I develop for clients; however, when I need to perform quick calculations for my own research or when a client needs something done quickly, I do now turn to Visual Basic for Applications (VBA).

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).

Indeed, I've since started integrating standalone applications developed for my own clients using C/C++ with Office applications such as Word, Outlook, and Excel. For example, a standalone application written in C/C++ can perform calculations and then automatically send the output to Word in a nicely formatted report, which can then be emailed automatically using Outlook. VBA allows you to do this sort of thing too. We'll cover some of these cool things, such as making Excel talk to Word, later in the book. But before we do, you should be familiar with the VBA development tools, language, and interface to Excel.

This chapter is meant to give you that familiarity if you don't already have it. 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You want to start exploring the Visual Basic for Applications (VBA) language and don't know where to begin.

Open Excel and select Tools → Macro → Visual Basic Editor from the main menu bar to open the VBA integrated development environment (IDE ). Start exploring the IDE as described in the following discussion.

The VBA IDE is basically the VBA editor. It's where to write VBA code, manage code modules , and debug your code. 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-F11. You can also use the Visual Basic toolbar, shown in Figure 2-1.

Figure 2-1: 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 2-2.

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 2-2, 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. I'll come back to these

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You want to write VBA code to perform some task or calculation but you're not sure where to begin.

Open the VBA IDE, create a code module for your workbook, and then start writing your custom procedures (functions and subroutines).

When working with Excel and VBA, you write custom code in functions and subroutines. Unlike in traditional application programming, we're not going to write a main program from which we manage the application loop, making calls to other functions and subroutines, and so on. We will, however, make heavy use of custom VBA procedures and call them from other VBA procedures. Moreover, we're going to call our VBA procedures from within Excel itself. Therefore, we're sort of attaching our custom code to Excel's main program and invoking our code within cell formulas or in response to certain events (for example, when a user presses a button). 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 2-1.

Example 2-1. 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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You are unfamiliar with VBA data types and their syntax.

Visual Basic supports the usual data types you'd expect to see in any programming language. Table 2-1 summarizes some of the VBA data types that I use most often for the sorts of calculations covered in this book.

Table 2-1: VBA data types
Data type	Storage	Values
`Boolean`	2 bytes	`True` or `False`
`Byte`	1 byte	0 to 255
`Integer`	2 bytes	−32, 768 to 32,767
`Double`	8 bytes	−1.79769313486231 E 308 to +1.79769313486231 E 308
`String`

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You're ready to write some code and would like to declare some variables but are not sure of the proper VBA syntax for doing so.

Use the Dim statement to declare a variable, as illustrated in Example 2-4.

Example 2-4. 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.3, and VariableName is a name you supply that conforms to the naming rules discussed in Recipe 2.2.

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 2-5.

Example 2-5. 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 2-5 declares three Double type variables, a, b, and c

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You'd like to declare some constants and need to know the correct VBA syntax.

VBA constants are declared using the syntax Const Name As DataType = Value, as illustrated in Example 2-6.

Example 2-6. Declaring constants

    Const MyConstant As Integer = 14

    Public MyConstant2 As Double = 1.025

Just as with variables, you can declare constants with local or global scope. 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 2-6, 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.Pi. See Recipe 2.13 for more information on Excel Objects.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·