Does anyone know how to force a cell re-calculation

[Guest]

I have a special formula in a cell, which I can cause to be calculated via
the .calculate method in Visual Basic. Unfortunately, if the cell, or any
dependencies do not change issuing another .calculate method does not cause a
re-calculation, which is a good idea in most cases (why waste CPU time
calculating a cell that hasn't changed). But as I said, without getting too
involved with the why, I have a need to FORCE a re-calculation even if the
cell has not changed. I can of course force a change, but I was hoping that
someone knew of some more elegant way around Excel's prohibition on
re-calculating cells that haven't changed.

Any useful suggestions would be greatly appreciated.

 

[Niek Otten]

I strongly advise you not to use such a construction. It is almost inevitable that it will get you into trouble eventually.
With "special formula" you probably mean a UDF? Make sure all the input to the UDF is included in the argument list. Never access
cells directly from within a UDF.

Apart from that: you can force a recalculation with CTRL+F9 and you can completely rebuild the dependency tree with
CTRL+SHIFT+ALT+F9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a special formula in a cell, which I can cause to be calculated via
| the .calculate method in Visual Basic. Unfortunately, if the cell, or any
| dependencies do not change issuing another .calculate method does not cause a
| re-calculation, which is a good idea in most cases (why waste CPU time
| calculating a cell that hasn't changed). But as I said, without getting too
| involved with the why, I have a need to FORCE a re-calculation even if the
| cell has not changed. I can of course force a change, but I was hoping that
| someone knew of some more elegant way around Excel's prohibition on
| re-calculating cells that haven't changed.
|
| Any useful suggestions would be greatly appreciated.
|

 

[Guest]

Without knowing the type of formula that is almost impossible to tell.

Why does application.calculate not work?

I've never had an instance where this didnt work. Including a loop from 1 to
1000 that recalculated all formulas on the work sheet every loop...

 

[Guest]

As I mentioned in my request, Excel ONLY re-calculates cells which have
contents that are dependent on other cells which have changed, thus affecting
the value of the cell in question. In my case the current state of the
formula in the target cell does not REQUIRE re-calculation. The .Calculate
method is apparently smart it checks cell dependencies and if none have
changed the .Calculate method is ignored. This is consistent with
information in the Excel help file which states that, to conserve CPU
resources, Excel only re-calculates cells, where dependencies have changed.
I had hoped this only applied to the automatic re-calculation, but apparently
the .Calculate method is SMART. I have confirmed this by forcing a cell
dependency change, and then the .Calculate method does perform a
re-calculation. There is a method which will force a re-calculation of the
entire workbook, but this is not useful for this application. I'm am sure in
your application the .Calculate method worked because the method recognized
changes that required re-calculation.

I have always gone beyond normal constructs to develop creative approaches
to unique problems, unfortunately I've occasionally run into certain
inflexibilities such as the .Calculate method. I was hoping one of the more
advanced users on this forum might know of an undocumented method which would
force the re-calculation.

Thanks.

 

[Dave Peterson]

I don't think I've ever seen anyone complain about all workbooks that they open
having this problem. But a few/lots of people have complained about individual
workbooks.

One suggestion is to re-enter every formula in the workbook.

One way is to select all the worksheets
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

And ungroup the worksheets.

Sometimes it seems to wake up excel's calculation engine.

 

[Guest]

Dave,

Thanks for your reply. But the problem I'm referring to does not effect the
entire workbook or even the worksheet - That's working fine. In my VB code
I'm want to force the re-calculation of a single cell (e.g. Cell.Calculate),
this works only if data that the cell is dependent on changes prior to
issuing the calculate method. Because of a special UDF I have in the cell I
would like it to recalculate even if the dependent data hasn't changed. This
is what fails to occur, UNLESS I deliberatly change dependent data, then the
Cell.Calculate method call works. I can also force a cell re-calculation if
I simply code Cell = Cell.Formula, in other words I write the cells own
formula back into itself, then it automatically re-calculates, I don't even
have to issue the Cell.Calculate instruction: but this just isn't elegent. I
was hoping that someone may know of a better way to force the cell to
re-calculate - apparently .Calculate is too small for it's own good (and
mine) .

Thanks for you comment though.

 

[Dave Peterson]

Maybe you can tie into a worksheet/workbook event (change or selection
change)???

 

[Guest]

Mike,

This is kind of a radical idea, and I am not sure it will work because I
don't know what a UDF formula is =). However, try adding =randbetween(1,1)
somewhere in your formula. Because randbetween calculates itself, it is not
dependant on other cells for updates.

 

[Guest]

Thanks for the suggestion, although I'm not sure that's any cleaner then what
I'm doing and my code is in my UDF. By the way in reply to your statement "I
don't know what a UDF formula is", given the context I've seen UDF used in I
presume it means User Defined Function. .

Cheers.

 

[Guest]

If anyone's interested I found the solution to the re-calculation problem -
In a word "Dirty".

There's a method called .Dirty which marks the cell as changed, which forces
a re-calculation.

Thanks to everyone for your suggestions.