Excel 2003 F9 wierdness

[John Coleman]

Greetings,
I just "updated" from Excel 2000 to 2003, and discovered that many
of my spreadsheets no longer work. I teach math, including stats. Over
the last 4 years or so I have created a number of what I call F9
animations: sheets with random variables together with graphs (charts)
of functions of those random variables. For example, to illustrate the
notion of confidence intervals I might draw 100 variables using rand(),
compute upper and lower confidence limits for the mean, plot them on a
scatter-chart and then, in a lecture, just hold down the F9 key.
Students could then visually *see* how say a 90% confidence interval
brackets the true mean about 90% of the time, etc. But - with Excel
2003,more often than not, the chart just freezes with the last value
and only updates when I lift my finger off the F9 key. I can rapidly
tap the F9 key and get much the same effect, but this is both slower
and more annoying than being ablt to just hold it down. Using DoEvents
in the Calculate event doesn't seem to help.

To illustrate this problem, which doesn't even require a chart, you can
do the following:

1) in A1:A500 enter RAND().
2) in B1 enter NORMSINV(A1) and copy down to B500
3) in C1 enter AVERAGE(B1:B500)

now - hold the F9 key down. The values in columns A and B will be
rapidly changing, but the C1 value will be frozen. In Excel 2000 it
would also be rapidly changing.

Any ideas for a work around? Otherwise I might have to resign myself to
carpal tunnel syndrome of the index finger from repeatedly tapping the
F9 key in a lecture.

-John Coleman

 

[Tom Ogilvy]

I know they revamped the calculation algorithm in xl2002, so perhaps that is
the cause.

You can run a macro like this:

Sub DoCalc()
For i = 1 To 200
Application.Calculate
Next
End Sub

or if you would prefer to use the F9 key and hold it down you can reassign
F9 to run a macro. Run the SetF9Key routine to do that. Restore its normal
behavior by running RestoreF9Key. This code should be in a general module

Sub SetF9key()
Application.OnKey "{F9}", "DoCalc1"
End Sub

Sub RestoreF9key()
Application.OnKey "{F9}"
End Sub

Sub DoCalc1()
Application.Calculate
End Sub