VB Forum / General 2 / July 2008

I'm assisting an Excel 2003 user who is receiving Runtime error 9
"subscript out of bounds" inside her VBA code.
I've pasted the code below, and i've marked the line with #**error
here****# at the beginning of the line.

Any ideas? I've spent 2 days researching forums and trying various
changes, but the weird thing is the script works on all her
colleagues' workstations, and they all have Excel 2003 (same version,
same update level) as well as the same XP installation (same versions,
SP's, updates, etc.)

Thanks in advance!
T

=============================
User's code:

Dim startRow As Integer, startColumn As Integer
Dim colCount As Integer
Dim rowCount As Integer
Dim numCreditLosses As Integer
Dim numFixedRate As Integer
Dim thisCreditLoss As Integer, thisFixedRate As Integer,
thisDiscountRate As Integer
Dim thisIRR As Variant
Dim purchasePrice As Double
Dim baseCaseCreditLosses As Double
Dim baseCaseDiscountRate As Double
Dim baseCasePrepayments As Double
Dim baseCaseLGD As Double
Dim baseCasePPHaircut As Double

Dim rowAssumptionName As String
Dim colAssumptionName As String
Dim tableStartName As String
Dim resultName As String

Dim resultsBook As Workbook
Dim modelBook As Workbook
Dim assumptionsSheet As Worksheet
Dim resultsSheet As Worksheet
Dim modelAssumptionsSheet As Worksheet
Dim modelCashflowsSheet As Worksheet
Dim modelBookName As String

Dim resultNPVName As String
Dim resultNominalName As String
Dim resultDurationName As String
Dim resulttable1StartName As String
Dim resulttable2StartName As String
Dim resulttable3StartName As String
Dim resulttable4StartName As String
Dim resulttable5StartName As String
Dim baseCaseNPVInput As Double

Dim flowChoice As Integer
Dim rateChoice As Integer

Const SUBS_CHOICE = 1
Const BASE_CASE_SCENARIO = 1

Sub sensitivityGenerator()
'

   Application.Calculation = xlCalculationManual

   Set resultsBook = Workbooks("Portugal Sensitivities M3")
   Set assumptionsSheet = resultsBook.Worksheets("Assumptions")

   resultsBook.Activate
   assumptionsSheet.Select

   Calculate
   'flowChoice = Range("portfolio").Value

   'this reads assumptions from the sensitivities' assumptions sheet
   Set resultsSheet = resultsBook.Worksheets("Results")
   resultNPVName = "BaseNPV"
   resultNominalName = "BaseNominal"
   resultDurationName = "BaseDuration"
   resulttable1StartName = "T1Start"
   resulttable2StartName = "T2Start"
   resulttable3StartName = "T3Start"
   resulttable4StartName = "T4Start"
   resulttable5StartName = "T5Start"

   'getting assumptions and cashflows from cashflow model
   modelBookName = Range("Data2").Value
#**error here****#    Set modelBook = Workbooks(modelBookName)
   Set modelAssumptionsSheet = modelBook.Worksheets("Assumptions")
   Set modelCashflowsSheet = modelBook.Worksheets("Quarterly flows")

   'read assumptions from Sensitivities' Assumptions sheet
   resultsBook.Activate
   assumptionsSheet.Select
   baseCaseCreditLosses = Range("BaseCDRMult").Value
   baseCaseDiscountRate = Range("BaseDiscRate").Value
   baseCasePrepayments = Range("BaseCPRMult").Value
   baseCaseLGD = Range("BaseLGD").Value
   'baseCasePPHaircut = Range("baseCasePPHaircut").Value

   'make the purchase price fixed
   modelBook.Activate
   modelAssumptionsSheet.Select
   'set the portfolio choice
   'Range("runChoice").Value = flowChoice

   'reset the base case and enter in the NPV and nominals to the
results sheet
   Call resetBaseCase
   Calculate

   modelBook.Activate
   modelCashflowsSheet.Select
   'input the base case 1 year forward mark
   Range("baseCaseMark") = Range("NPV").Value

   Range("NPV").Copy
   baseCaseNPVInput = Range("NPV").Value

   'paste in the base case NPV which puts the purchase price in the
quarterly flows sheet (cell DM27)to get base IRR
   Range("baseCaseNPV").Value = -baseCaseNPVInput

   'paste base case npv value in the sensitivities sheet
   resultsBook.Activate
   resultsSheet.Select
   Range(resultNPVName).Value = baseCaseNPVInput

   'go back to model to copy the sum of nominal cash flows and
duration and paste each of these in the sensitivities sheet
   modelBook.Activate
   modelAssumptionsSheet.Select
   Range("nominalFlows").Copy

   resultsBook.Activate
   resultsSheet.Select
   Range(resultNominalName).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
       False, Transpose:=False

   modelBook.Activate
   modelAssumptionsSheet.Select
   Range("durationExtended").Copy

   resultsBook.Activate
   resultsSheet.Select
   Range(resultDurationName).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
       False, Transpose:=False


'***********************************************************************************
   '1
   '
   rowAssumptionName = "CDR_Curve_Multiple"
   colAssumptionName = "LGD"
   tableStartName = resulttable1StartName
   resultName = "adjustedIRR"
   Call enterIRRs(rowAssumptionName, colAssumptionName,
tableStartName, resultName)

   Call resetBaseCase


'***********************************************************************************
   '2
   '
   rowAssumptionName = "CDR_Curve_Multiple"
   colAssumptionName = "ppCurveMultiplier"
   tableStartName = resulttable2StartName
   resultName = "adjustedIRR"
   Call enterIRRs(rowAssumptionName, colAssumptionName,
tableStartName, resultName)

   Call resetBaseCase


'***********************************************************************************F
   '3
   '
   rowAssumptionName = "CDR_Curve_Multiple"
   colAssumptionName = "LGD"
   tableStartName = resulttable3StartName
   resultName = "LossPerc"
   Call enterIRRs(rowAssumptionName, colAssumptionName,
tableStartName, resultName)

   Call resetBaseCase


'***********************************************************************************
   '4
   '
   rowAssumptionName = "CDR_Curve_Multiple"
   colAssumptionName = "ppCurveMultiplier"
   tableStartName = resulttable4StartName
   resultName = "LossPerc"
   Call enterIRRs(rowAssumptionName, colAssumptionName,
tableStartName, resultName)

   Call resetBaseCase


'***********************************************************************************
   '5
   '
   rowAssumptionName = "discountRate"
   colAssumptionName = "CDR_Curve_Multiple"
   tableStartName = resulttable5StartName
   resultName = "LossPerc"
   Call enterIRRs(rowAssumptionName, colAssumptionName,
tableStartName, resultName)

   Call resetBaseCase

   'put the -NPV formula back in the model (cell DM27 of Quarterly
flows)
   modelBook.Activate
   modelCashflowsSheet.Select
   Range("BaseCaseNPV").FormulaR1C1 = "=-NPV"

   Calculate
   resultsBook.Activate
   resultsSheet.Select
   Cells(1, 1).Select

End Sub

Sub resetBaseCase()

   modelBook.Activate
   modelAssumptionsSheet.Select
   Range("CDR_Curve_Multiple").Value = baseCaseCreditLosses
   Range("ppCurveMultiplier").Value = baseCasePrepayments
   Range("discountRate").Value = baseCaseDiscountRate
   Range("LGD").Value = baseCaseLGD
   'Range("ppColectRate").Value = baseCasePPHaircut
   'modelCashflowsSheet.Select
   'Range("DiscountRate").Value = baseCaseDiscountRate
   Calculate

End Sub

Sub enterIRRs(rowAssumptionName As String, colAssumptionName As
String, tableStartName As String, _
       resultName As String)

   'find out where the output and results should go
   resultsBook.Activate
   resultsSheet.Select
   startColumn = Range(tableStartName).Column
   startRow = Range(tableStartName).Row

   'size the scenario box
   colCount = 0
   rowCount = 0
   Do Until Cells(startRow, startColumn + colCount + 1) = ""
       colCount = colCount + 1
   Loop
   Do Until Cells(startRow + rowCount + 1, startColumn) = ""
       rowCount = rowCount + 1
   Loop
   numCreditLosses = colCount
   numFixedRate = rowCount
   Range(Cells(startRow + 1, startColumn + 1), _
       Cells(startRow + numFixedRate + 2, startColumn +
numCreditLosses)).ClearContents

   Calculate


'***********************************************************************************
   'run defaults and fixed rate assumptions
   For thisFixedRate = 1 To numFixedRate
       For thisCreditLoss = 1 To numCreditLosses
           'copy over the credit losses
           resultsBook.Activate
           resultsSheet.Select
           Cells(startRow, startColumn + thisCreditLoss).Copy

           modelBook.Activate
           modelAssumptionsSheet.Select
           Range(rowAssumptionName).Select
           Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False

           'copy over the fixed rate
           resultsBook.Activate
           resultsSheet.Select
           Cells(startRow + thisFixedRate, startColumn).Copy

           modelBook.Activate
           modelAssumptionsSheet.Select
           Range(colAssumptionName).Select
           Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False

           Calculate

           'copy back the NPV

           modelBook.Activate
           modelAssumptionsSheet.Select
           Range(resultName).Copy

           resultsBook.Activate
           resultsSheet.Select
           Cells(startRow + thisFixedRate, startColumn +
thisCreditLoss).Select
           Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False

       Next
   Next

End Sub