LinkBack URL
About LinkBacksHello 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("Calculation 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("Calculation
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
Register To Reply
Bookmarks