The spinner offers an easy way to test different input variables to see the effect on a financial model — without altering your underlying formulas. This example shows how to put this useful feature to work.

Sensitivity analysis determines how a spreadsheet’s outputs will vary in response to changes made to the spreadsheet’s inputs. Excel gives us a number of tools for sensitivity analysis. One of these is the spinner. A spinner is linked to a cell containing one of the variable inputs. As the user clicks on the spinner, the value of the linked cell changes.

For example, the following worksheet can be used to examine how changes in three variable cialis soft tabs online inputs (price, unit cost, and fixed cost) affect profit. To set up the formulas for the worksheet, follow these steps:

Figure A

1. Go to Tools | Options, click the Calculation tab, and then select the Accept Labels In Formulas check box. (In Excel 2007, click the Formulas tab, click Create From Selection in the Defined Names group, and then click OK.)
2. Enter 2 in cell D1, which is the current price.
3. Enter the rest of the formulas as shown in Figure B.

Figure B

1. Type Enter Unit Cost (in hundredths) in cell H1.
2. Type Enter Fixed Cost (in thousands) in cell H2.
3. Enter 44 in cell I1 (the current Unit Cost).
4. Enter 20 in cell I1 (the current Fixed Cost), as shown in Figure C.

Figure C

1. Go to View | Toolbars | Forms to display the Forms toolbar. (In Excel 2007, click the Developer tab.)
2. Click the Spinner button on the Forms toolbar. (In Excel 2007, click Insert in the Forms Group and then click the Spin button under Form Controls.)
3. Click and drag in cell F1 to create the Price spinner.
4. Repeat step 9 and then click and drag in cell F3 to create the Unit Cost spinner.
5. Repeat step 9 and then click and drag in cell F4 to create the Fixed Cost spinner.
6. Right-click the spinner in F1, click Format Control, and enter the values shown in Figure D.

Figure D

1. Right-click the spinner in F3, click Format Control, and enter the values shown in Figure E.

Figure E

1. Right-click the spinner in F4, click Format Control, and enter the values shown in Figure F.

Figure F

Your manager just learned that increases in material costs have raised the unit cost to .60. Using the spinner to adjust for this higher cost, the profit decreases to $45,800. Clicking the Price spinner up arrow once to raise the price to $3 immediately shows a drop in demand (assuming the demand formula is correct). However, even with this drop in demand, profit goes up significantly, which may warrant the price increase.

You can get a list of the columns in a primary key manually, but depending on the task at hand, you may want to handle it programmatically instead. Susan Harkins shares a VBA function that uses ADOX objects to obtain the column information.

Working with key values is serious work, and assigning a primary key is just the beginning of the process. If you need to manipulate a primary key programmatically, you need to know the columns that the key comprises. There are easy ways to do that manually, but doing so programmatically can prove useful if the task is part of the application’s internal workings or you’re dealing with external tables.

Note: This information is also available as a PDF download, along with a BAS file containing the code listing.

The code

You might think that listing the columns in a primary key would be easy, but that’s not the case. Perhaps the most efficient process is to use ADOX objects. Specifically, the function in Listing A uses ADOX catalog, table, index, and column objects. A series of For…Each loops and If…Then…Else statements cycle through three collections to determine the table’s primary key index and then build a string variable from the names of the columns that belong to that key. All that, just to list a few columns!

Listing A

Function ListPK(tbl As String) As String

'List primary keys for passed table. 'Must reference ADOX library: 'Microsoft cialis soft tablets ADO Ext. 2.8 for DDL and Security. Dim cat As New ADOX.Catalog Dim tblADOX As New ADOX.Table Dim idxADOX As New ADOX.Index Dim colADOX As New ADOX.Column

  cat.ActiveConnection = CurrentProject.AccessConnection

  On Error GoTo errHandler

  For Each tblADOX In cat.Tables

If tblADOX.Name = tbl Then If tblADOX.Indexes.Count <> 0 Then For Each idxADOX In tblADOX.Indexes With idxADOX If .PrimaryKey Then For Each colADOX In .Columns ListPK = colADOX.Name & ", " & ListPK Next End If End With Next End If End If Next

  If ListPK = "" Then    ListPK = "No primary key" Else ListPK = Left(ListPK, Len(ListPK) – 2) End If

  Set cat = Nothing  Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing Exit Function

errHandler:  MsgBox Err.Number & ": " & Err.Description, vbOKOnly, _ "Error" Set cat = Nothing Set tblADOX = Nothing Set idxADOX = Nothing Set colADOX = Nothing

End Function

To enter this function, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu, enter the code, and save the module. This code uses ADOX objects, so be sure to reference the Microsoft ADO Ext. 2.8 For DDL And Security library. Choose References from the Tools menu, select the library (Figure A), and click OK.

Figure A

Reference the ADOX library.

To execute the function, open the Immediate window by pressing Ctrl + G. Type the following line:

ListPK("tablename")

where tablename is the table for which you’re listing primary key columns. Now, press Enter. Figure B shows the results of passing the Northwind table Employees to ListPK(). (Northwind is an example database that comes with Access.)

Figure B

Use the Immediate window to pass a table to the ListPK() function.

The first For…Each loop cycles through the Tables collection looking for tbl, the passed string, which in this case is Employees. When the code finds a match, the next statement makes sure that Employees has at least one index to examine. If it does, the code loops through the Indexes collection until it finds the primary key index. The next For…Each loop builds a string that includes the names of all the columns in the primary key in column1, column2, column3 format. Finally, the function returns that string.

If a table has an index but no primary key, the function returns the string “No primary key.” If the table has no index, the function returns the string “No primary key.” You could just as easily use a subprocedure to print the results to the Immediate window.

Print keys

Primary keys are an integral part of any relational database. You can use ListPK() while debugging a new database. With some customization, you could use it to manipulate primary keys programmatically.

Word 2003 offers a gallery of Table AutoFormat styles — but they may not always suit your needs. See how to build your own style to quickly make tables look exactly the way you want.

Word’s Table AutoFormat feature offers an assortment of prefab styles you can use to jazz up your tables. But those styles may not necessarily match your document design or serve your table’s purpose. Luckily, you can specify your own set of attributes and save them as a user-defined style. Then, you can just apply the style to a table whenever you want to use your custom formatting.

Note: This information is also available as a PDF download.

To demonstrate, let’s say you generate a weekly dashboard report whose data is contained in a nice-looking, easily interpreted Word table… but manually formatting the table is getting a little old. Time to build a custom style:

Figure A

1. Enter a name for the style (e.g., Dashboard) and choose the style you want to base your new style on (Figure B). We’re going to base our sample style on the basic Table Grid style, but you can start off with something fancier if you prefer. Or choose Table Normal, which is unformatted, if you want to start with a blank slate.

Figure B

1. Now you can use the various tools in the New Style dialog box to specify the desired formatting. Just choose the table component you want to format from the Apply To drop-down list (Figure C) and make your selections. For this example, we specified 11-point Arial for the entire table, 14-point bold formatting and a light yellow fill for the heading row, a 1.5-point blue outside border, a 1-point yellow inside border, light blue fill for odd rows and light yellow fill for even rows (Figure D). (You’ll notice that some of the formatting — such as the font — doesn’t display in the preview.)

Figure C

Figure D

1. Click the Format button to access additional options governing the appearance of table elements and table text (Figure cialis shelf life E). In this case, we selected Paragraph and specified 6 points of space above and below each paragraph. Not all options are available to include in your table style. For instance, you can’t set Preferred Width or Text Wrapping in the Table Properties dialog.

Figure E

1. If you want the style to be available to other documents based on the current template, click Add To Template. Otherwise, the style will belong to the current document only. Click OK to return to the Table AutoFormat dialog box.
2. If you’d like this table style to be the default for all new tables you create, click Default in the Table AutoFormat dialog box. Word will let you choose between setting the default for the current document or for all documents that use the current template (Figure F). Make your selection and click OK. If you don’t want to set a default, skip this step and simply close out of the Table AutoFormat dialog box.

Figure F

The payoff

To apply the style, click in a table and open the Table AutoFormat dialog box. Choose User-Defined Table Styles from the Category drop-down list box to display your custom style(s) (Figure G). Now, just select the style and click Apply.

Figure G

Do you use the miniature slides in MS PowerPoint for navigation purposes?

cialis pro

Did you know you can choose to use multiple slide design templates within the same MS PowerPoint presentation?

As I've observed people using PowerPoint, I've noticed they always seem to apply a slide design template to the master, with the assumption it's their one and only chance to make that decision.

But cialis normal dose what if you have a long presentation with some very definite sections and you're thinking that changing the design template might be a good idea? You know, a visual clue that the subject is changing. I know we don't want to go crazy and make each slide different, but do they all have to be the same?

We all would like to think that PowerPoint isn't forcing us into anything, so I'd hope we have some control over the matter as well!

As it turns out, we do have control, so whatever your reason for wanting a design template switch, the big question now is how can you make it happen?

Fortunately, this one is probably easier to accomplish than you may think!

If you're using an older version of PowerPoint, you need the Normal View from the View menu. Now, open the Slide Design Task Pane (the Format menu, Slide Design choice will work).

For all you PowerPoint 2007 users, you're looking for the View tab on the ribbon, Normal choice. To get to your slide design choices, you need the Themes section under the Design tab on the ribbon.

With all of that accomplished, everyone needs to use the Slides tab on the left hand side of the program window to select the slide(s) that will have the Design Template. You can select a single slide or multiple slides with the Shift/Ctrl keys.