There are a few different ways to play the macro



Fifth Handout

Recording a Macro

First, retrieve the workbook titled “CubicsMacro” from the NewData folder in our shared hard drive. Save it to your floppy disk with your own personalized name with File >Save as. We will use this momentarily.

Then open a new workbook with File > New.

This book is empty, and probably begins with three sheets (this default can be changed).

Turn on the macro recorder:

1. Tools>Macro>Record

2. Give the macro a name by typing over (it’s highlighted; just start typing).

3. Give this macro a control letter, “a” is good. Avoid letters like C and V if you use these for Excel “speed” commands (copy and paste). These macro controls will take precedence over the original default values.

4. Enter or OK.

5. So that you can see it actually do something, Insert > Worksheet

6. Activate B2 (or anything other than A1)

7. Enter your name and Enter

8. Enter the formula =now()

9. This formula keeps track of the date and time. However, it updates to the current date and time. Change this formula to a value:

a. Highlight the cell with the formula

b. Edit > Copy

c. Highlight same cell

d. Edit > Paste Special > Values

e. Return to Sheet1

10. Stop the recording by pressing the Stop-Recording button.

There are a few different ways to play the macro. If we failed to give the macro a control letter, then

Tools > Macro > Play

will bring up a list of the available macros for this or all open workbooks. Highlight the macro you want and then Run. Try it. Did it do anything? Look carefully.

You can also hold the Ctrl key and press the letter a, the keystroke-automatic play method you gave to your macro. Try it. Look carefully again.

The easiest way to play a macro is to place a “play” button in the worksheet, but I’ll only go over this if we have time.

The Visual Basic Editor

The macro you recorded becomes a part of the workbook; macros can be saved, edited, and copied. To view this just-created macro, use Tools > Macro > Visual Basic Editor or Alt+F11.

Many things can happen; if you are not looking at a window similar to mine, let me help you.

We are not going to try to become proficient code-writers in this course. I only want to note that it is fairly easy to see how to write the code. If I am trying to do something and I am unsure of the code, I record what I want to do, and then see the code it creates. Then I might modify this code to run in some repetitious way to get my task done.

For example, I see that the standard column width is insufficient to display my date/time stamp. Suppose I want to automate this part of the program, but have no idea of the “code.” We’ll let the software tell us the code.

1. Activate a sheet where you created the name and date/time stamp.

2. Turn on the macro recorder (Tools > Macro > Record, or the record button).

3. Autofit the column that has the date/time by double clicking between the column labels to the right of the column with the time. If that seems tricky, activate the column and then Format > Column > Autofit selection.)

4. Turn off the macro recorder by pressing the Stop Recording button.

5. Enter the visual basic editor with Alt+F11 or Tools > Macro > Visual Basic Editor.

6. Find your new macro. It should have only one line of code, Columns("B:B").EntireColumn.AutoFit

7. Highlight that line of code, and then copy and paste it into the first macro.

a. Where should it be pasted? Does it matter?

b. OK, everyone paste it wherever you think appropriate, and then we’ll figure out where it should go.

8. Return to the spreadsheet part of the code, and run your first macro again.

9. Did it work? If not, then maybe we need to think about step 7 again.

Example of a Simple Useful Macro

Activate the CubicsMacro program. Note that I have reduced the workbook to a single sheet of values in preparation to run the macro. Key End > Home to find out the extent of the sheet. Use End and the arrow navigation keys to note that the worksheet seems to have some regression output left over in it, and some formulas.

Run the CubicRegression... program.

1. Tools > Macro > Macros

2. Find the one with the appropriate name and highlight

3. Run

Notice that the macro did the work of running cubic regressions, grabbing the data for each gel, and recorded the pertinent results for each gel in the sheet.

Look at the macro. (Alt+F11). Try to decide what each line of the program is doing and why. There is only one line that for which the code appears ridiculous. How would I know how to write that line? How would I know how to write the whole thing? It’s not so hard; do what we want to do once, and record the results while you’re doing it.

Go back to the fourth handout, and follow the steps for doing the cubic regression, except that we want to do this with the recorder turned on:

1. Open your OriginalStandards… from previous day. Find cubic regression sheet.

2. Tools>Macro>Record

3. Give it a name like OneGelCubicRegression (anything not used before is OK).

4. Use the data from the second gel by copying it, with label, to A14

5. Tools > Data Analysis > Regression (Alt+T D RRR Enter).

6. For the output, choose a25, but we’ll change that later.

7. When it asks if you want to overwrite the existing data, say OK.

8. Stop Recording.

9. Scroll with the scroll bars to see how much space the Regression Output occupied.

Examine the code created, and note that the really hard line is recorded for us. Unfortunately, very specific values of all the inputs for the regression and for the copy steps were hard-wired into the macro. These will have to be modified. Also, the macro would run slowly if it had to stop and ask you if you wanted to overwrite the existing data every time. There are several ways to eliminate this stoppage; one is to simply clear the contents of the cells where the data will be output. To see how to do this, copy and paste the body of this macro into the visual basic code of the Cubics Macro. Notice how one can vary the gel for the input values of the regression (How?).

The original macro ends with many copy steps. What are these doing, and why will they work without modifying what’s being copied?

For now, and in case you struggle with the homework, copy the results from the Cubics macro into the workbook that has all your other results.

Homework

1. Write a Macro that finds the regression of log molecular weight(y) based on a quadratic polynomial of relative mobility. Have your macro record both R^2 and adjusted R^2, along with predicted values, coefficient, residuals, etc.

2. Cofirm that the R^2 values fall between those for the linear model and the cubic model. Explain why this must be the case.

3. Which of the two models quadratic or cubic appears to be the best in terms of adjusted R^2 values?

4. How does the SLIC model rate vis-a-vis adjusted R^2 values versus quadratic and cubic.

5. Someone suggests that the model should be

y = a + bx + c(x + error

Is this an improvement? (Hint: You cannot answer this question without spending some time seeing what this model does; be careful not to overwrite any of your previous work.)

Recall the Goal

We have considered several models for the standards data that Virginia Hutchins prepared and measured. Often, a statistician has only one set of data for which to choose a model, and she needs a criterion for selecting the best one considered. One criterion would be the one with the smallest residual variation (relatively); this is equivalent to the one with the largest R 2 . Since models with more parameters to choose for fitting will tend to have larger R 2 values, she may choose the model with the largest adjusted R 2 .

However, the goal is to estimate the molecular weights of the proteins in an unknown sample or samples from its mobility in a gel. Perhaps a better criterion would be the model that estimates these values most accurately. This process is not quite the same as the R 2 criterion because the data in the sample gel are not actually fit to the model. They are going to be estimated by comparison to the weights in the known sample.

Some computer-intensive methods of comparing models by predictability have been devised. One of them is called cross-validation; predictive sample reuse is another name for the same method. Here is an example of how it works: Suppose we were trying to estimate the molecular weight of something in the 50 – 80 kilodalton (kd) range. The molecular weight of one of the proteins in our sample, serum albumin, is 66 kd. Imagine that we had standards that contained only eight of our proteins, and that serum albumin was missing. However, serum albumin was in the unknown sample data. Which model would best estimate its weight?

A statistician could fit all of the models he had to the eight data points, use these to predict the molecular weight of serum albumin, and then compare this predicted value to the actual weight. The winning model, for this particular protein, would be the one that came closest to 66. If we had several replicates of this same data, the best model would have to be based on all of the errors produced, perhaps by computing the total variation about 66, as measured by the sum of squares (or some other measure, like the sum of the absolute differences). It could be that a model that is good for molecular weights in one range is not good for molecular weights in a different range. It is also possible that the models that work best for one scientist’s gels are not the same as for another, and that the accuracy of the predictions differ because some measurements are inherently more repeatable than others.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download