Hello everyone:

I am trying to create a spreadsheet to calculate thermodynamic
properties of a steam turbine and am having a number of problems with
Excel. I have been making changes to it over the last couple of months
and gotten myself into a bind. Just when I think I have everything
nailed down and working.... something else craps out. I will try to
explain, as best I can, what has happened (or not happened) in the last
week.

I have a spreadsheet, you can download it from here
http://www.csupomona.edu/~cthompson1...chores-BAD.xls
that keeps aborting with an invalid page fault.

I have been saving backup copies for each of my major changes and
think I have located the problem here between these two backup
versions:
http://www.csupomona.edu/~cthompson1...ies-bu-008.xls
and
http://www.csupomona.edu/~cthompson1...ies-bu-009.xls
.. They both have calculations under the toolbar-options set to
automatic and the iterations checked. The "009" spreadsheet immediately
starts executing the spreadsheet formulas as soon as it is opened, the
"008" version does not. Apparently I didn't notice this until around
version "010" when I started making changes to the debug routines in my
code.

The first thing I did was to add code like the following to identify
the calling cell that caused my visual basic functions to fail:

Public Function TempDPW(Density, Pressure, Optional Guess, Optional
Precision, Optional iterations)

Dim myName As String
Dim myCell As Range
Dim mySheet As Worksheet
Dim myBook As Workbook
Dim aName As Name
Dim CellName As String

If IsError(Density) Or IsError(Pressure) Or IsEmpty(Density) Or
IsEmpty(Pressure) Or Density <= 0 Or Pressure <= 0 Then Exit Function

On Error Resume Next

myName = "TempDPW"

If TypeName(Application.Caller) = "Range" Then
Set myCell = Application.Caller
Set mySheet = myCell.Worksheet
Set myBook = mySheet.Parent
Err.Number = 0
For Each Name In myCell
Set aName = myCell.Name
If Err.Number = 0 Then
CellName = aName.Name
Else
CellName = "#N/A"
End If
Err.Number = 0
Next Name
End If

On Error GoTo Error_routine

....

TempDPW = T

Exit Function

Error_routine:
Debug.Print myName, "Density=", Density, "Pressure=", Pressure
If (TypeName(myCell) = "Range") Then Debug.Print myName, "Sheet=",
mySheet.Name, "Name=", CellName, "Row=", myCell.Row, "Col=",
myCell.Column, "Address=", myCell.Address
Debug.Print myName, "Error Source=", Err.Source, "Num=", Err.Number,
"Line=", lnum, "Desc=", Err.Description
Stop
Resume Next

End Function

After these changes I discovered that I could stop the spreadsheet
("009") from calculating on startup if I set Application.Calculation =
xlCalculationManual before I save it to disc. So I set up a button on
my standard tool bar to switch between automatic and manual, and added
code to turn off the automatic calculation "Before_Save" in my
workbook. The code follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

Application.CommandBars("Standard").Controls("Calc ulation Mode").Delete

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

On Error Resume Next

Me.Application.CalculateBeforeSave = False
Me.Application.Calculation = xlCalculationManual
With Me.Application.CommandBars("Standard").Controls("C alculation
Mode")
.State = msoButtonDown
.TooltipText = "Calculation mode is manual"
End With

End Sub

Private Sub Workbook_Open()

Dim cmd As CommandBarControl

On Error Resume Next

'Debug.Print "Workbook_open is here!"

initialize

With Application.CommandBars("Standard")

Err.Clear

Set cmd = .Controls("Calculation Mode")
If Err.Number < 0 Then Set cmd = .Controls.Add

With cmd
If Application.Calculation = xlCalculationAutomatic Then
.State = msoButtonUp
.TooltipText = "Calculation mode is Automatic"
Else
.State = msoButtonDown
.TooltipText = "Calculation mode is manual"
End If
.BeginGroup = True
.Caption = "Calculation Mode"
.OnAction = "CalcMode"
.FaceId = 2
End With
End With

'Debug.Print "Workbook_Open Is not here!"

End Sub

The way the spreadsheet works in the "008" version it this.
Immediately after the workbook is opened, I run the macro "initialize"
to initialize the water module constants and arrays. Then I can make
changes to the spreadsheet, and run the macro "AllGoalSeek" to update
the cells and do the spreadsheet calculations. It seems like the "F9"
button does nothing here, but <ctl-alt-F9 recalculates all of the
cells.

In the "009" version I was stumped when I opened the workbook and
started running into my debug code "Stop" statements. I finally
realized that none of the constants or arrays had been initialized, and
I started working the problem of turning off the calculation mode at
startup. The first thing I did here was to install "Workbook_Open" code
to execute the "Initialize" subroutine and set the module constants and
arrays. This code was unfortunately never executed and i have no idea
why. I put a "stop" statement on the first line of "Workbook_Open" and
discovered to my horror that it never popped up. So then I added an
"auto_open" macro to my water module with the initialization code
inside, only to discover to my further horror that this was never
executed until AFTER ALL of the cells had calculated their functions in
ERROR with the module UN-initialized. I think "F9" works ok here, but
<Ctl-alt-F9 does nothing. I don't get it!

Where I am at now is this: The "BAD" spreadsheet kinda works and
kinda doesn't work. Before this one I added some new routines
"EnthalpySPW, TempSPW, TempHPW" which do the same thing as GoalSeek in
a function. These seemed to be working ok for the most part until I
found out that Excel sent parameters in error. I added checks to exit
the functions when this occured and thought that all of my problems
were solved and got rid of my "AllGoalSeek" macro. Then I added code
"TempDPW" and screwed everything up. Somehow when I did a search and
replace on temperature and density, and I inadvertantly changed
everything in the module instead of just the highlighted code in the
TempDPW function. So I deleted the module and brought in a fresh "OLD"
copy from the previous spreadsheet. The problem still seems to exist
where a page fault can occur anywhere at anytime... Usually before I
have a chance to save my changes.

What I have noticed today is that my "Calculation Mode" button
doesn't appear to be working. I was pressing the button on the
spreadsheet and noticed that it was NOT changing state. It always
stayed down in "Manual" mode even if the Toolbar-Options-Calculation
said that the spreadsheet was in "Automatic" mode. When I put in a
break into my CalcMode macro to see what was going on, I found that it
got to the line Application.Calculation = xlCalculationAutomatic and as
soon as the line executed, the spreadsheet started calculating cells.
This time though the constants and arrays are initialized and so
everything should run ok. Except that somewhere in the spreadsheet an
EnthalpyW function is called with the temperature in an "ERROR 1021"
state or something. The function sees the error and exits the function
with the "Exit Function" statement and that's it. Nothing else happens
and the code never returns to the CalcMode macro to finish with the
button setup.

If anybody has any ideas how to do a better job with this thing
please post to this group. I am at my wits end on this one.

P.S. Is there any way to tell Excel what order I want it to executed
cells in? Sometimes it looks like it is calculating everything, other
times only a couple of cells. With Iterations turned on, I see a kind
of ripple effect through the cells. The wierd part is when it stops and
in cells where "if statements" check conditions, the results of the "if
statements" don't match the conditions reported in the spreadsheet. Is
there some way I can tell Excel to do everything from this cell to
another cell just one time?

Oh yeah. The other thing that is bugging me is when I open the
spreadsheet and it ask's me if I want to "Enable" or "Disable" macros
and I say "Disable," the next thing I get is a dialog about "Excel type
4.0 macros." If I say "no" the workbook doesn't open, and if I say
"yes" it does. As far as I know I only have Visual Basic type macros.
Are these the "Type 4.0" macros, or do I have a virus or something that
I am unaware of?

Regards from,
Chris Thompson

I forgot to mention that when the Application.Calculation =
xlCalculationAutomatic is set in the CalcMode button macro, and the
spreadsheet starts calculating functions. If I check the call stack, it
shows something like:

[Steam_Turbine].water97_v15.InitializeAll
<Non-Basic Code
[Steam_Turbine].water97_v15.CalcMode

I assume that the <Non-Basic Code is Excel, and It is failing
somewhere for some reason that I am unable to determine.

Regards from,
Chris

Chris,

I was not able to make it thru your entire post, but possibly
some of the problems may lie with the revision level of the
application. Service Release 2 fixed a multiplicity of
problems including several dealing with calculation...

186395 - Formulas with Range_Style Cell Reference are Not Updated.
144508 - Using Calculate Method May not calculate Certain Formulas.
It goes on...You can review the list of fixes and find out how to get the update
he http://support.microsoft.com/default...b;en-us;151020

Regards,
Jim Cone
San Francisco, USA


wrote in message oups.com...
Hello everyone:
I am trying to create a spreadsheet to calculate thermodynamic
properties of a steam turbine and am having a number of problems with
Excel. I have been making changes to it over the last couple of months
and gotten myself into a bind. Just when I think I have everything
nailed down and working.... something else craps out. I will try to
explain, as best I can, what has happened (or not happened) in the last
week.

I have a spreadsheet, you can download it from here
http://www.csupomona.edu/~cthompson1...chores-BAD.xls
that keeps aborting with an invalid page fault.
I have been saving backup copies for each of my major changes and
think I have located the problem here between these two backup
versions:
http://www.csupomona.edu/~cthompson1...ies-bu-008.xls
and
http://www.csupomona.edu/~cthompson1...ies-bu-009.xls
- SNIP -

OK... I installed SR-2 and my "BAD" Spreadsheet is not page faulting
anymore. I think that I can work with it to fixup the rest of the
problems. The old backup versions "008" and "009" are still doing the
same as before. The thing about the "BAD" spreadsheet that is wierd is
when I change the ambient air pressure.

A change in the ambient air pressure calculates a new wet bulb
temperature indirectly with an Excel GoalSeek that operates out of a
Worksheet macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ((Not Intersect(Target, Range("TairE")) Is Nothing) Or _
(Not Intersect(Target, Range("PairE")) Is Nothing) Or _
(Not Intersect(Target, Range("TdpE")) Is Nothing)) Then
Range("Tairwb").Value = 298.15
Range("EbalAirwb").GoalSeek Goal:=0, ChangingCell:=Range("Tairwb")
End If

End Sub

This works fine for all cases except when I tie the output "Tairwb"
into the rest of the spreadsheet.

Down around H77 I calculate Tmin = Tairwb+1, and then this value gets
used in Pmin which then is used directly or indirectly everywhere else
in the spreadsheet. If I change Tmin to a constant not associated with
Tairwb, everything calculates normally. I can change the ambient air
pressure to 10, 20 , 25 anything I want and the goalseek works almost
instantly. Further if I copy and paste the new value into Tmin, the
rest of the spreadsheet converges on a solution to that value. If I
leave the Tmin value to "=Tairwb+1", then the whole spreadsheet goes
whacky and ends up pasting a bunch of "#VALUE" junk into all of the
cells including the ones that are only associated with the goalseek
thing. I think what is happening is that the goalseek tries to test a
value that is outside of the range of one of my functions that in due
course returns a -1 error value, or maybe nothing at all. Then this
gets propogated to another function, and so on and so on. I'll do a
little more work and make sure that I return something, even if it is
-1 and see if that helps.

I guess I can go ahead and paste a constant into the H77 cell before I
do the goalseek, and then put the "=Tairwb+1" back in after the
goalSeek completes.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ((Not Intersect(Target, Range("TairE")) Is Nothing) Or _
(Not Intersect(Target, Range("PairE")) Is Nothing) Or _
(Not Intersect(Target, Range("TdpE")) Is Nothing)) Then

Range("Tmin") = Range("Tairwb").Value+1

Range("Tairwb").Value = 298.15
Range("EbalAirwb").GoalSeek Goal:=0, ChangingCell:=Range("Tairwb")

Range("Tmin") = "=Tairwb+1"

End If

End Sub

Maybe that would work ok.

Regards and Thanks,
Chris.

No... It didn't.

It got to

Range("EbalAirwb").GoalSeek Goal:=0, ChangingCell:=Range("Tairwb")

Started calculating the whole spreadsheet with "#VALUE" errors and then
quit.

I never came back to

Range("Tmin") = "=Tairwb+1"

Next suggestion?

Chris.