"Dave Peterson" wrote:
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 -----

"Dave Peterson" wrote in message
...
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.

Joe User wrote:

"Dave Peterson" wrote:
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 -----

"Dave Peterson" wrote in message
...
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.



Prof Wonmug wrote:

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.

--

Dave Peterson

--

Dave Peterson