forcing complete calculation

[Claude]

Hi all

Excel often doesn't calculate properly when hitting F9. I
was shown the trick that by hitting Ctrl-Alt-F9, one could
force a complete recalculation of all cells in all open
files.

Is there an equivalent to this in VBA?

 

[Klaus]

Sure:

Application.CalculateFull


For future occations, try recording a macro in Excel and then view in VBA
what it did. Sometimes a really helpful thing.

Klaus

 

[Guest]

Thanks Klaus. In Excel97, recording the macro only gives:

Calculate

 

[Tom Ogilvy]

CalculateFull is available in Excel 2000 and later. In Excel 97, you need
to use sendkeys to sent Ctrl+Alt+F9

Regards,
Tom Ogilvy

 

[Claude]

This doesn't seem to work in Excel97...

 

[Tom Ogilvy]

CalculateFull is not available in Excel 97. You have to use sendkeys to do
Ctrl+Alt+F9

Previously posted by Charles Williams:

if val(application.version)<9 then

Application.SendKeys "%^{F9}"
DoEvents

else
application.calculatefull
endif

 

[Tom Ogilvy]

That is correct. See my response.