Hi all

I'm adding a new row with complicate formula, then I calculate that row only
and use "Shell" to call a EXE program to read a worksheet

However, when EXE read the worksheet, the value is still #VALUE or #NAME,
which means the calculation has not yet completed.

Is there any function I can use to wait until the excel calculation complete?

Hi Shawn

Thanks for your reply.

I've tried calling application.calculate then save the workbook before
calling the shell command, but it doesn't work.

I cannot find Application.CalculationState property, is it a new
property come with a newer version of Excel, I'm using Excel 2000 and
this program will be run using Excel 97 as well.

" wrote:
I cannot find Application.CalculationState property, is it a new
property come with a newer version of Excel, I'm using Excel 2000 and
this program will be run using Excel 97 as well.

Hmm. I guess CalculationState is a new property. Sorry about that.

Here's another thing to try. It isn't pretty...

Create a worksheet-module-level flag variable that indicates whether the
calculation is done. Before your code does whatever it does that triggers
the recalculation, set the flag to False.

In the Worksheet's Calculate event, which (I believe) fires *after* the
calculations are done, set that variable to True.

In your code, loop until that flag variable is True. Then call the shell
command.

That, too, may not work, since Excel is supposedly doing everything in one
thread, and the calculation should be done before the VBA code ever gets to
the loop.

You may have to add some idle time in your EXE. Your EXE is definitely
running in a different thread than Excel, and resting that thread may allow
Excel enough time to get itself all updated. (I'm curious how you're getting
the data out of Excel into the EXE. Other folks doing what you're doing
should know about the difficulty.)

Worst comes to worst, write whatever data the EXE needs to a temp file, then
call the EXE on the temp file.

I wish I knew exactly what was going on here. Let us know if you ever find
out.

Thanks Shawn, I added a loop in my EXE to check the vartype is vberror
or not.
After the calculation complete, the values are shown and no more #NAME,
#VALUE
so the EXE can run smoothly.

I have tested that in the loop was added in the VBA module (A) before
calling the EXE, it loops forever as the calculation was triggerring
another VBA module (B). The module A will not release its control for
module B to calculate, so it loops forever.