1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
|
Function calculPrime(ByVal annee As Integer, ByVal choixLimite As Integer, ByVal cotRisque As Double) As Double
Application.Volatile
Dim borneInferieure As Double
Dim borneSuperieure As Double
Dim myRangeName As String
Dim myRange As Range
Dim nbRows As Integer
Dim txPrimeInferieur
Dim txPrimeSuperieur
Dim i As Integer
Dim wbk As Workbook
myRangeName = "Taux" & annee
borneInferieure = -1
i = 1
Set wbk = GetObject("L:\CSST\Outils\Addins\Parametres\ParamTauxPrime.xlsx")
Set myRange = wbk.Worksheets("ParamTaux").Range(myRangeName)
nbRows = myRange.Rows.Count
Select Case choixLimite
Case Is = 150
colonneChoix = 2
Case Is = 200
colonneChoix = 3
Case Is = 250
colonneChoix = 4
Case Is = 300
colonneChoix = 5
Case Is = 400
colonneChoix = 6
Case Is = 500
colonneChoix = 7
Case Is = 600
colonneChoix = 8
Case Is = 700
colonneChoix = 9
Case Is = 800
colonneChoix = 10
Case Is = 900
colonneChoix = 11
End Select
Application.ScreenUpdating = False
While borneInferieure < 0
If cotRisque < myRange.Cells(i, 1).Value Then
borneInferieure = 0
borneSuperieure = myRange.Cells(i, 1).Value
calculPrime = myRange(i, colonneChoix)
End If
If cotRisque >= myRange.Cells(nbRows, 1).Value Then
borneInferieure = myRange.Cells(nbRows, 1).Value
borneSuperieure = myRange.Cells(nbRows, 1).Value
calculPrime = myRange(nbRows, colonneChoix)
End If
If cotRisque >= myRange.Cells(i, 1).Value And cotRisque < myRange.Cells(i + 1, 1).Value Then
borneInferieure = myRange.Cells(i, 1).Value
borneSuperieure = myRange.Cells(i + 1, 1).Value
txPrimeInferieur = myRange(i, colonneChoix)
txPrimeSuperieur = myRange(i + 1, colonneChoix)
calculPrime = txPrimeInferieur - ((cotRisque - borneInferieure) * (txPrimeInferieur - txPrimeSuperieur)) / (borneSuperieure - borneInferieure)
End If
i = i + 1
Wend
End Function |
Partager