Can I get Excel to recalculate just one cell?

[Prof Wonmug]

I have a bug somewhere in a UDF I wrote. I put some breakpoints in the
code, but it's called from 30-40 cells in the worksheet. Is there a
way to get Excel to recalculate just one cell so I don't have to keep
setting and resetting the breakpoints or hit F5 30-40 times until all
of the other cells finish?

In the past, I've just pressed F2 on the cell in question and I
thought it only re-executed that one cell. Today it is recalculating
the entire sheet and, sometimes, the entire workbook.

 

[Wouter HM]

Hi Prof Wonmug,

Try This

Open Tools -> Options
Goto tab Calculation
Select option Manual
Click OK


If you have the line of code below comment it out:
Application.Volatile

Use the F2 option as before

HTH,

Wouter

 

[Dave Peterson]

Maybe you could add a check.


if ucase(application.caller.address) = ucase("$A$1") then
stop
end if


Or create a new subroutine and use something like:

mycell.formula = mycell.formula

Where myCell is the cell that you're interested in.

 

[Joe User]

Is there a way to get Excel to recalculate just one cell [....]
In the past, I've just pressed F2 on the cell in question
and I thought it only re-executed that one cell. Today it
is recalculating the entire sheet and, sometimes, the
entire workbook.

Did you remember to set Manual calculation mode? (Tools > Options >
Calculation in Excel 2003.)

If not, certainly there are circumstances where "changing" one cell (F2,
then Enter) will cause other cells to be recalculated.

But even with Manual mode set, I think I encountered situations where Excel
recalculates a single cell multiple times for a single change. The first
redundant calculations might call UDFs in the formula with empty or otherwise
invalid parameters. I am sure that Application.Volatile was not an issue; I
almost never use that feature.

That is certainly sometimes the case in Automatic mode. My recollection
about Manual mode might be wrong.

In any case, I finally resorted to the following macro for one reason or
another.

Sub doit()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Range("a1").Calculate

With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub


----- original message -----

 

[Joe User]

Maybe you could add a check.
if ucase(application.caller.address) = ucase("$A$1") then
stop
end if

But I don't think that will protect against the circumstances (unclear to
me) when Excel calculates the same cell multiple times, calling UDFs in the
formula with bogus parameters (empty or zero) each time except the last
time.

Also, for my edification, why do you use UCase?

In my experience, simply Application.Caller.Address = "$A$1" has sufficed.

Am I wrong to expect that?

(Well, surely UCase("$A$1") is unnecessary.)

Or create a new subroutine and use something like:
mycell.formula = mycell.formula

But I am quite sure that will not prevent calculation of other cells unless,
of course, Manual calculation mode is set.

For testing purposes, in Automatic calculation mode, I have relied on
assignments of that form to cause other cells to recalculate. For example,
I have used the following paradigm:

Range("A1").formula = "=ROUND(A2,15)"
For d = lo to hi
Range("A2") = d
If Range("A1") <> d Then Stop
Next

This has worked for me. But for my edification, am I wrong to assume that
A1 will be recalucated when the macro changes A2 and Automatic calculation
mode is set?


----- original message -----

 

[Prof Wonmug]

Hi Prof Wonmug,

Try This

Open Tools -> Options
Goto tab Calculation
Select option Manual
Click OK


If you have the line of code below comment it out:
Application.Volatile

Use the F2 option as before

I've never used Application.Volatile. I'm volatile enough without
setting a special option. ;-)

The manual option seems to work. Is there any downside other than I
have to press F9 to see the results if I change anything? What won't
work that used to with option automatic?

I noticed that there is also an option manual except table. I'm not
sure exactly what that does, but it seems to make F2 work just like
with option manual, but if I change a constant that is used in a
table, the table gets updated.

Do you run with option manual always on or just when you need it?

 

[Joe User]

The manual option seems to work. Is there any downside other
than I have to press F9 to see the results if I change anything?
What won't work that used to with option automatic?

Lots of stuff "won't work" in Manual mode, depending on your definition of
"work". You certainly need to keep your wits about you when you use Manual
mode.

One confusing thing: if you copy formulas, the copies will usually appear
not to work initially, returning the value of the original formula instead
of the copy. You usually need to calculate each copy individually.

Alternatively, to force more pervasive calculation in Manual mode, see the
F9 alternatives in the help page "Change when and how formulas are
calculated" (in Excel 2003).


----- original message -----

 

[Charles Williams]

You can use the RangeCalc addin downloadable fron
http://www.Decisionmodels.com/downloads.htm

put Excel in Manual Calc mode, then select the cel(s) you want to
recalculate and click the Rangecalc button.

regards
Charles

 

[Bernd P]

I have a bug somewhere in a UDF I wrote. I put some breakpoints in the
code, but it's called from 30-40 cells in the worksheet. Is there a
way to get Excel to recalculate just one cell so I don't have to keep
setting and resetting the breakpoints or hit F5 30-40 times until all
of the other cells finish?

In the past, I've just pressed F2 on the cell in question and I
thought it only re-executed that one cell. Today it is recalculating
the entire sheet and, sometimes, the entire workbook.

Range("A1").Calculate

Regards,
Bernd

 

[Prof Wonmug]

Is there a way to get Excel to recalculate just one cell [....]
In the past, I've just pressed F2 on the cell in question
and I thought it only re-executed that one cell. Today it
is recalculating the entire sheet and, sometimes, the
entire workbook.

Did you remember to set Manual calculation mode? (Tools > Options >
Calculation in Excel 2003.)

If not, certainly there are circumstances where "changing" one cell (F2,
then Enter) will cause other cells to be recalculated.

I didn't know about manual mode until today. I just tried it. It seems
to fix that problem, but I'm not sure I want to run that way all the
time.

But even with Manual mode set, I think I encountered situations where Excel
recalculates a single cell multiple times for a single change. The first
redundant calculations might call UDFs in the formula with empty or otherwise
invalid parameters. I am sure that Application.Volatile was not an issue; I
almost never use that feature.

I've had that happen too. I had one UDF that kept getting called 2-3
times with really strange arguments. I had a devil of a time debugging
it. I think I posted a question here and someone told me I was nuts.
I'm glad to hear that I'm not the only one.

That is certainly sometimes the case in Automatic mode. My recollection
about Manual mode might be wrong.

In any case, I finally resorted to the following macro for one reason or
another.

Sub doit()
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Range("a1").Calculate

With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Thanks for that. I'll play with it when I get a chance.

 

[Prof Wonmug]

Lots of stuff "won't work" in Manual mode, depending on your definition of
"work". You certainly need to keep your wits about you when you use Manual
mode.

One confusing thing: if you copy formulas, the copies will usually appear
not to work initially, returning the value of the original formula instead
of the copy. You usually need to calculate each copy individually.

That's enough for me. I'll run in automatic most of the time and go to
manual just when I need it. I've put them on the QAT (2007). I just
wish there were a toggle so I didn't need 2 buttons up there.

 

[Prof Wonmug]

Maybe you could add a check.


if ucase(application.caller.address) = ucase("$A$1") then
stop
end if


Or create a new subroutine and use something like:

mycell.formula = mycell.formula

Where myCell is the cell that you're interested in.

I've experimented with things like that, but I still have to keep
editing the code to change the cell name. I even added an option
parameter, but then I had to edit the cell. (sigh)

 

[Prof Wonmug]

You can use the RangeCalc addin downloadable fron
http://www.Decisionmodels.com/downloads.htm

put Excel in Manual Calc mode, then select the cel(s) you want to
recalculate and click the Rangecalc button.

Thanks

 

[Dave Peterson]

If the OP was concerned with the number of times hitting F5 to skip past the
calculations for the insignificant cells, then this would be a way to stop when
the function was calculating the cell that was significant.

As for the ucase() stuff. Yes, I know that I don't need it.

But there are lots of times that the person posting the question doesn't realize
that the address has to be in uppercase (with no other options set). This stops
the follow-up question of why doesn't the code work. It was a pedagogical
choice.

I don't see any reason why:
mycell.formula = mycell.formula
would cause other cells to recalculate. I guess it would depend on the UDF and
its dependents and if other cells contained volatile functions.

I used:

Option Explicit
Function myFunc(rng As Range)

If UCase(Application.Caller.Address) = UCase("$A$1") Then
Stop
End If
myFunc = rng.cells(1).Value

End Function

If I filled A1:A10 with values and then put:
=myFunc(A1)
in B1 and dragged down to B10.

Then told excel to "reenter" the formula in B5 (say), then I wouldn't expect
excel to recalc B1:B4 and B6:B10. And I didn't see that occur with calculation
was set to automatic.

I'd want to see your real function and what you were passing to it to guess its
behavior.

 

[Dave Peterson]

If you're testing, then what's the big deal about changing the address in the
code?

I don't understand the problem.

 

[Gord Dibben]

Stick this in your Personal.xls and assign it to a button on the QAT

Sub calc_toggle()
With Application
If .Calculation = xlManual Then
.Calculation = xlAutomatic
Else
.Calculation = xlManual
End If
End With
End Sub


Gord Dibben MS Excel MVP

 

[ryguy7272]

Maybe this:
http://support.microsoft.com/default.aspx/kb/213490?p=1

 

[Prof Wonmug]

Stick this in your Personal.xls and assign it to a button on the QAT

Sub calc_toggle()
With Application
If .Calculation = xlManual Then
.Calculation = xlAutomatic
Else
.Calculation = xlManual
End If
End With
End Sub


Gord Dibben MS Excel MVP

Very slick. Thank you.

 

[Joe User]

I don't see any reason why:
mycell.formula = mycell.formula
would cause other cells to recalculate. I guess it
would depend on the UDF and its dependents and if
other cells contained volatile functions.

Exactly.

Obviously something caused Excel to recalculate other cells when Wonmug
pressed F2, then Enter in the desired cell ("mycell"). Wonmug wrote:

"In the past, I've just pressed F2 on the cell in question and I thought it
only re-executed that one cell. Today it is recalculating the entire sheet
and, sometimes, the entire workbook."

I was saying that simply executing the assignment statement above would not
avoid the extraneous recalculations under the same conditions, whatever they
may be.


----- original message -----