Why can't a calculation and macro execute at the same time?

[Katy]

Is there ant way of getting around this?

When my sheet is calculating my macro freezes up until the
calculation is completed on the worksheet, is there any
way I can set up my worksheet so that a calculation and a
macro can execute at the same time?

 

[Frank Kabel]

Hi
AFAIK not possible. But why do you need this?

 

[pikus]

Well, yes. I believe if you add "Application.Calculate" to th
appropriate place in your code it will take care of that for you.
Piku

 

[pikus]

And as for why, Frank, anything in VBA and the calculation of the sheet
formulas are separate processes. If you're relying on the result of
formula for part of your code you must tell VBA to calculate it fo
you. You could just do the math within your code, but especially whe
there are multiple formulas it's easier to do it the other way. - Piku

 

[Tom Ogilvy]

This certainly doesn't appear to be correct.

 

[pikus]

Huh? What's the deal then? I hate it when I'm wrong! Dammit! Pikus
confused. Tom Ogilvy explain.

 

[Katy]

There are a lot cells that have formulas which causes a 40
sec delay when a task in executed. I wanted to create a
progress bar which would increase while the calculation
was being completed. There probably is a way better way of
doing this, just don't know how.

So your advice would be appriciated =)

 

[Tom Ogilvy]

to the best of my knowledge and experience, calculation and vba work very
well together. If a vba action causes a calculation to occur, the macro
waits until it is complete to continue. Excel isn't multithreaded from a
VBA and calculation execution standpoint. I can't say there aren't some
bugs that could cause problems, but largely I believe I have represented the
situation. If you code does not perform an action that would cause a
calculation (perhaps calculation is set to manual) and you need a
calculation, then you would need to cause a calculate.

The OP seems to have similar experiences and is trying to create the
situation you described as I read the posts.

 

[pikus]

Thanks... I feel like such a noob! :-

 

[Dana DeLouis]

Here's an article to add to your library:

How Formula Calculations Are Performed in Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;825012&Product=xlw

HTH
Dana DeLouis

<snip>