Controlling execution of a Sub Procedure -Part 2 :(

[Don McC]

Question:Is there any way to only start a Worksheet_calculate procedure once
the iterative sheet with which it is associated is FINISHED? How can you
detect when it is finished? Details below

In a previous post I was trying to run a lengthy calculation in a Sub
Worksheet_calculate() procedure IF certain conditions on a spreadsheet were
met. Of course I was developing this outside the ultimate workbook
application. I resolved my stack overflow problem by writing out results to
a sheet other than the one that had the Worksheet_calculate() procedure
associated with it. THAT development sheet did NOT have iterative
calculations.

Now the real workbook sheet with which the Worksheet_calculate () sub
procedure would be ported to does have iterative calculations. I added a new
sheet to write out the results, like in the development book and only put the
following code in the worksheet_calculate sub()

Dim VRange As Range
Dim wfflow As Double
Dim wfhhv As Double
Dim sfhhv As Double
Dim sfflow As Double
Dim wfc As Double
Dim sfc As Double
Dim wfh2 As Double
Dim sfh2 As Double
Dim wfO2 As Double
Dim sfO2 As Double
Dim wfN2 As Double
Dim sfN2 As Double
Dim wfS As Double
Dim sfS As Double
Dim wfCl2 As Double
Dim sfCL2 As Double
Dim wfash As Double
Dim sfash As Double
Dim wfubc As Double
Dim sfubc As Double
Dim wfcp As Double
Dim sfcp As Double
Dim wftemp As Double
Dim sftemp As Double
Dim mixhhv As Double
Dim mixcarb As Double
Dim mixH2 As Double
Dim mixO2 As Double
Dim mixN2 As Double
Dim mixS As Double
Dim mixCl2 As Double
Dim mixash As Double
Dim mixubc As Double
Dim mixcp As Double
Dim mixtemp As Double
Dim error As Double
Dim so3error As Double
Dim RPerror As Double
Dim exair As Double
Dim flametemp As Double
Dim newflametemp As Double
Dim carbonburned As Double
Dim theoO2 As Double
Dim theoair As Double
Dim fgco2 As Double
Dim fgH2O As Double
Dim fgN21 As Double
Dim fgSO2 As Double
Dim fgSO3 As Double
Dim fgHCl As Double
Dim fgexo2 As Double
Dim fgexN2 As Double
Dim fgtotalN2 As Double
Dim excessO2 As Double
Dim fgco2mole As Double
Dim fgH2Omole As Double
Dim fgN21mole As Double
Dim fgso2mole As Double
Dim fgso3mole As Double
Dim fgHClmole As Double
Dim fgexO2mole As Double
Dim fgexN2mole As Double
Dim fgtotalN2mole As Double
Dim fgtotal As Double
Dim fgtotalmole As Double
Dim fgmw As Double
Dim totaldryair As Double
Dim totalwetair As Double
Dim O2inair As Double
Dim N2inair As Double
Dim CO2molfrac As Double
Dim H2Omolfrac As Double
Dim SO2molfrac As Double
Dim SO3molfrac As Double
Dim HClmolfrac As Double
Dim exO2molfrac As Double
Dim totalN2molfrac As Double
Dim fgso3new As Double
Dim fuelhof As Double
Dim Basetheta As Double
Dim fueltheta As Double
Dim fuelDH As Double
Dim fuelenthalpy As Double
Dim airtemp As Double
Dim airtheta As Double
Dim O2DH As Double
Dim N2DH As Double
Dim reactantenthalpy As Double
Dim flametheta As Double
Dim CO2DH As Double
Dim CO2hof As Double
Dim CO2enthalpy As Double
Dim H2ODH As Double
Dim H2ODH2 As Double
Dim H2Ohof As Double
Dim H2Oenthalpy As Double
Dim O2prodDH As Double
Dim O2prodenthalpy As Double
Dim N2prodDH As Double
Dim N2prodenthalpy As Double
Dim SO2DH As Double
Dim SO2hof As Double
Dim SO2enthalpy As Double
Dim SO3DH As Double
Dim SO3hof As Double
Dim SO3enthalpy As Double
Dim residueDH As Double
Dim residueenthalpy As Double
Dim HClDH As Double
Dim HClhof As Double
Dim HClenthalpy As Double
Dim productenthalpy As Double
Dim ppSO3 As Double
Dim ppH2O As Double
Dim DewpointK As Double
Dim DewpointF As Double
Dim DewpointC As Double
Dim heatout As Double
Dim heatin As Double
Dim Efficiency As Double
Dim dfg As Double
Dim lfgtEff As Double
Dim minfgt As Double
Dim airflow As Double
Dim AHload As Double
Dim RadConvLoss As Double
Dim lfgt2 As Double
Worksheets("Sheet2").Cells(2, 1).Value = "Combined Higher Heating Value,
Btu/lbm"
Worksheets("Sheet2").Cells(3, 1).Value = "Combined % Carbon"

(Its a lengthly calculation however, all I am doing at this point is writing
out text to a blank cell)

Let's call sheet1 the sheet that has the Worksheet_calculate() sub
associated with it.

Once I made a change on the inputs page, the workbook displayed ITER in
bottom bar indicating that Sheet1 was executing over and over again.

So it would appear to me that each iteration of Sheet1 is a "recalculation"
which calls the Worksheet_calculate Sub. If I interrupt the execution and
look at the call stack, the Worksheet_calculate Sub has been called many
times.