How to force all of the cells in a worksheet to recalculate?

[LurfysMa]

I have a worksheet that calls some custom macros.

When I change the macro, the cells don't recalculate.

How can I tell Excel to recalculate all of the cells in the worksheet?

--

 

[Gary Keramidas]

are you turning calculation off with the macro or is it set to manual?

this will set it to automatic, add it to the end of your code and see if it
helps

application.Calculation = xlautomatic

 

[Dave Peterson]

You could always create another macro that recalculates everything

or even just show the immediate window in the VBE (ctrl-g).

Then type this and hit enter:
application.calculatefull

There are different levels of recalculating. Look in VBA's help for more info.
(And some have been added since xl97.)

 

[LurfysMa]

are you turning calculation off with the macro

The macro is 1 line of code to calculate an exponential function. Here
it is:

Public Function DSExp( _
ByVal x As Double, _
ByVal Xa As Double, _
ByVal Ya As Double, _
ByVal Yb As Double, _
ByVal h As Double) _
As Double
DSExp = Yb + (Ya - Yb) * Exp((-Log(2) / h) * (x - Xa))

'Note: In VBA, Log() is the natural log, Log10 is the base 10 log.
' In Excel, Ln() is the natural log and Log() is the base 10 log.
End Function

or is it set to manual?

Calculation is set to Automatic.

this will set it to automatic, add it to the end of your code and see if it
helps

application.Calculation = xlautomatic

The macro isn't even getting called. I set a break point to see for
sure. So I can't see how this will help. I added it, but no
difference.

Here's what's weird. If I click on the calculate now icon (this is
Excel 2007), nothing happens. But if I click on the cell and press F2,
then tab, the calculation is performed, the breakpoints in my macros
happen, and the new values appear.

How come "Calculate now" doesn't calculate now?

--

 

[LurfysMa]

You could always create another macro that recalculates everything

or even just show the immediate window in the VBE (ctrl-g).

Then type this and hit enter:
application.calculatefull

That did it. Now how come that works, but clicking the Calculate Now
icon (Excel 2007) doesn't?

I don't want to always be opening up the IDE to enter that in the
Immediate window.

I suppose I could write a recalculate macro, as you suggest, but isn't
thay what Calculate Now is supposed to do?

There are different levels of recalculating. Look in VBA's help for more info.
(And some have been added since xl97.)

--

 

[Dave Peterson]

I figure that if you're in the VBE making changes to the the UDF (that's what
you meant in your original post, right???), then it's just as easy to force the
calculation while you're there.

But if you look at the different ways to force calculation in Excel's help,
you'll see something like this (taken from xl2003's help):

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation,
and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless
of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

======
I didn't look in xl2007's help file, but you can. Then you can test to see
which set of keystrokes recalculates for you.

 

[LurfysMa]

I figure that if you're in the VBE making changes to the the UDF (that's what
you meant in your original post, right???), then it's just as easy to force the
calculation while you're there.

But if you look at the different ways to force calculation in Excel's help,
you'll see something like this (taken from xl2003's help):

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation,
and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless
of whether they have changed since last time or not.

This one did it. Thanks.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

--