Manual Calculation Problem
 

I am currently writing a simulation program using excel. The following
code has worked fine in the past but now doesn't want to calculate in
manual mode for some strange reason. Can somebody spot a stupid error
here ? The code works fine in semiautomatic and automatic mode but
takes 4 times as long to calculate :(
Sub RunSimulation()
'Transactor.Hide
Dim termOfLoan
Dim g As Range
Dim LGD_DEFQ
Dim ELLterm
Dim ELL
Dim graceQs As Integer
Dim sourceRange As Range
Dim fillRange As Range
Dim lossRange As Range

Workbooks("Frontend1.xls").Sheets("TMMlnResult").A ctivate
Application.ScreenUpdating = False
'Delete the values from the previous simulation
Range("BZ10:CA5010").ClearContents
Range("B21:BA5021").ClearContents
Range("BB22:BG396").ClearContents
Range("CA10:CA65000").ClearContents

'read in the constants
ELLterm = Evaluate(ActiveWorkbook.Names("ELLterm").RefersTo)
ELL = Evaluate(ActiveWorkbook.Names("ELL").RefersTo)
LGD_DEFQ = Workbooks("Frontend1.xls").Sheets("PortfolioManage r").Range("G17").Value
termOfLoan = Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B26").Value
+ (Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B26").Value
/ 4)
graceQs = Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B16").Value

'Set the term of the loan introducing defered quarters and grace
quarters for LGD
If ELL Then
termOfLoan = ELLterm * 4 + 6 + LGD_DEFQ + graceQs
Else
termOfLoan = termOfLoan * 4 + 6 + LGD_DEFQ + graceQs
End If

str12 = "4:" & termOfLoan
str13 = "4:" & termOfLoan + 1

Application.Calculation = xlManual
Application.EnableEvents = False
'Show progress bar
frmProgress.progbar.Max =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("C1").Value
str = "Transactor is performing simulation." & vbLf & "Please
wait...."
frmProgress.lblProg.Caption = str
DoEvents
Workbooks("Frontend1.xls").Activate
'take the user set number of iterations in future
For i = 1 To Sheets("TMMlnResult").Range("C1").Value
frmProgress.progbar.Value = i

Sheets("MRPs").UsedRange.Rows(str12).Calculate
Sheets("SVgen").Columns("L:U").Calculate
Sheets("LoanCalc Q").UsedRange.Rows(str13).Calculate
Sheets("TMMlnResult").UsedRange.Rows("14:16").Calc ulate

Sheets("TMMlnResult").Range("B20:BA20").Offset(i, 0).Value = _
Sheets("TMMlnResult").Range("B15:BA15").Value
Next



Workbooks("Frontend1.xls").Activate
Set fillRange = Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("BB21:BG396")
Set sourceRange =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("BB21:BG21")
sourceRange.AutoFill Destination:=fillRange, Type:=xlFillValues
Set lossRange = Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("B21:AY5021")
i = 0
'Reset progress bar for next stage
frmProgress.lblProg.Caption = "Calculating PDs and LGDs from
simulation results."
frmProgress.progbar.Value = 0
frmProgress.progbar.Max = lossRange.count

Application.ScreenUpdating = True
For Each g In lossRange.Cells
frmProgress.progbar.Value = frmProgress.progbar.Value + 1
If CSng(g.Value) = 0.0001 Then
i = i + 1
If CSng(g.Value) < 1 Then
Sheets("TMMlnResult").Range("CA9").Offset(i, 0).Value
= g.Value
Else
Sheets("TMMlnResult").Range("CA9").Offset(i, 0).Value
= CSng(g.Value) - 1
End If
End If
Next g


Worksheets("output").Calculate
Sheets("output").Range("D60:I60").Value =
Sheets("output").Range("D36:I36").Value
Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True
End Sub

Sorry for the long post but I really need to fix this

Regards
Nigel Brown
Theisen Securities

Manual Calculation Problem
 

Hi Nigel,

Some comments:
- it looks like you are in automatic mode initially, and hence will do
several, presumably unneccessary, automatic recalculations before you switch
to manual.
- Using Range.calculate may be causing you a problem because it does not
calculate (as Excel normally does) in dependency order.
- Using Sheet calculate assumes that all precednts for that sheet have been
correctly calculated.
- is there a reason for disabling events?
- do you have any UDFs, or circular references?

My recommendation would be to rearrange the model into sheets you want to
calculate each iteration and sheets you dont want to calculate each
iteration, and then in manual mode use worksheets.enablecalculation and
application.calculate to control which sheets in the workbook are calculated
and when.

regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Nigel Brown" wrote in message
om...
I am currently writing a simulation program using excel. The following
code has worked fine in the past but now doesn't want to calculate in
manual mode for some strange reason. Can somebody spot a stupid error
here ? The code works fine in semiautomatic and automatic mode but
takes 4 times as long to calculate :(
Sub RunSimulation()
'Transactor.Hide
Dim termOfLoan
Dim g As Range
Dim LGD_DEFQ
Dim ELLterm
Dim ELL
Dim graceQs As Integer
Dim sourceRange As Range
Dim fillRange As Range
Dim lossRange As Range

Workbooks("Frontend1.xls").Sheets("TMMlnResult").A ctivate
Application.ScreenUpdating = False
'Delete the values from the previous simulation
Range("BZ10:CA5010").ClearContents
Range("B21:BA5021").ClearContents
Range("BB22:BG396").ClearContents
Range("CA10:CA65000").ClearContents

'read in the constants
ELLterm = Evaluate(ActiveWorkbook.Names("ELLterm").RefersTo)
ELL = Evaluate(ActiveWorkbook.Names("ELL").RefersTo)
LGD_DEFQ =
Workbooks("Frontend1.xls").Sheets("PortfolioManage r").Range("G17").Value
termOfLoan =
Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B26").Value
+ (Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B26").Value
/ 4)
graceQs =
Workbooks("Frontend1.xls").Sheets("AUTO-INPUT").Range("B16").Value

'Set the term of the loan introducing defered quarters and grace
quarters for LGD
If ELL Then
termOfLoan = ELLterm * 4 + 6 + LGD_DEFQ + graceQs
Else
termOfLoan = termOfLoan * 4 + 6 + LGD_DEFQ + graceQs
End If

str12 = "4:" & termOfLoan
str13 = "4:" & termOfLoan + 1

Application.Calculation = xlManual
Application.EnableEvents = False
'Show progress bar
frmProgress.progbar.Max =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("C1").Value
str = "Transactor is performing simulation." & vbLf & "Please
wait...."
frmProgress.lblProg.Caption = str
DoEvents
Workbooks("Frontend1.xls").Activate
'take the user set number of iterations in future
For i = 1 To Sheets("TMMlnResult").Range("C1").Value
frmProgress.progbar.Value = i

Sheets("MRPs").UsedRange.Rows(str12).Calculate
Sheets("SVgen").Columns("L:U").Calculate
Sheets("LoanCalc Q").UsedRange.Rows(str13).Calculate
Sheets("TMMlnResult").UsedRange.Rows("14:16").Calc ulate

Sheets("TMMlnResult").Range("B20:BA20").Offset(i, 0).Value = _
Sheets("TMMlnResult").Range("B15:BA15").Value
Next



Workbooks("Frontend1.xls").Activate
Set fillRange =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("BB21:BG396")
Set sourceRange =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("BB21:BG21")
sourceRange.AutoFill Destination:=fillRange, Type:=xlFillValues
Set lossRange =
Workbooks("Frontend1.xls").Sheets("TMMlnResult").R ange("B21:AY5021")
i = 0
'Reset progress bar for next stage
frmProgress.lblProg.Caption = "Calculating PDs and LGDs from
simulation results."
frmProgress.progbar.Value = 0
frmProgress.progbar.Max = lossRange.count

Application.ScreenUpdating = True
For Each g In lossRange.Cells
frmProgress.progbar.Value = frmProgress.progbar.Value + 1
If CSng(g.Value) = 0.0001 Then
i = i + 1
If CSng(g.Value) < 1 Then
Sheets("TMMlnResult").Range("CA9").Offset(i, 0).Value
= g.Value
Else
Sheets("TMMlnResult").Range("CA9").Offset(i, 0).Value
= CSng(g.Value) - 1
End If
End If
Next g


Worksheets("output").Calculate
Sheets("output").Range("D60:I60").Value =
Sheets("output").Range("D36:I36").Value
Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True
End Sub

Sorry for the long post but I really need to fix this

Regards
Nigel Brown
Theisen Securities

Manual Calculation Problem
 

Hi Charles,
Thanks for your response. My comments and answers follow.
it looks like you are in automatic mode initially, and hence will do
several, presumably unneccessary, automatic recalculations before you
switch to manual.
The reason for being in automatic mode initially is to perform a one
time setting of constants in the model. I then would like to switch to
manual mode to iterate through the simulation senario.

Using Range.calculate may be causing you a problem because it does not
calculate (as Excel normally does) in dependency order.
Agreed but excel manually calculates left to right and top to bottom and
the arrangement of the spreadsheet is in this order.

Using Sheet calculate assumes that all precedents for that sheet have
been correctly calculated.
This is currently my problem as the sheets are not calculating.

is there a reason for disabling events?
Not really, however I assumed that it might spead things up.

do you have any UDFs, or circular references?
No, everything is in standard excel functions using the analysis tool
pak addin.

Admitedly the design of this model is not ideal, however this code
worked fine until yesterday. Excel seem to be ignoring the call to
calculate specific ranges and limited amounts of rows. Anymore comments
are welcome, however I am reluctant to rearrange the model unless it is
a last resort.


Regards
Nigel Brown
Theisen Securities

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Manual Calculation Problem
 

Hi Nigel,

The reason for being in automatic mode initially is to perform a one
time setting of constants in the model. I then would like to switch to
manual mode to iterate through the simulation senario.


It is best to take control yourself: switch to manual, change your
constants, then do application.calculate.

Using Range.calculate may be causing you a problem because it does not
calculate (as Excel normally does) in dependency order.
Agreed but excel manually calculates left to right and top to bottom and
the arrangement of the spreadsheet is in this order.

Using Sheet calculate assumes that all precedents for that sheet have
been correctly calculated.
This is currently my problem as the sheets are not calculating.

Admitedly the design of this model is not ideal, however this code
worked fine until yesterday. Excel seem to be ignoring the call to
calculate specific ranges and limited amounts of rows. Anymore comments
are welcome, however I am reluctant to rearrange the model unless it is
a last resort.


If you dont want to rearrange the model then just go for permanent Manual
mode and call application.calculate once per iteration.

Or find out what you have changed in the model that has caused the problem.
Or find out where the calculation time is being spent.


regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com