xlcalculationAutomatic not working

[switters_aka]

Hello all, I am running this code to refresh a sheet which has a vba function to calculate values in a cell. For some reason, the function is constantly producing #value errors but on refresh, the function works. The problem is, the refresh sub() is doesn't always work. If I modify it in some way, even if it means removing a blank line, then it works. It's one of those intermittent problems which does your head in. Any ideas where I might be going wrong?

Public Sub refresh()


   Application.Calculation = xlCalculationManual

  Application.Calculate

  Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True
End Sub

 

[RoryA]

Does this happen in any workbook, open on its own?

 

[joeu2004]

when I execute this line of code, Application.Calculation = xlCalculationAutomatic, my VBA program just stops. There's no error message, no break, no crash; the program just stops, as if it encountered an "End" statement.

"Just stops"? Or is it really spinning its wheels for a very very long time?

What does Task Manager tell you about CPU usage?

Caveat: The %CPU number can be deceptive on multi-CPU multicore systems. The question really is: is one "CPU" (or core) 100% busy?

I suspect it is. Reason: setting auto calculation mode might cause Excel to recalculate a huge number of formulas, and that might take many seconds, minutes, hours, even days.