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