"Dave Peterson" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
|
Similar Threads
