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 92
| Function calculPrime(annee, choixLimite, cotRisque) As Double
Application.Volatile
Dim borneInferieure As Double
Dim borneSuperieure As Double
Dim myRangeName As String
Dim nbRows As Integer
Dim txPrimeInferieur
Dim txPrimeSuperieur
Dim i As Integer
Dim wbk As Workbook
Set wbk = GetObject("L:\CSST\Outils\Addins\Parametres\ParamTauxPrime.xlsx")
myRangeName = "Taux" & annee
borneInferieure = -1
i = 1
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 = Round(myRange(i, colonneChoix), 4)
End If
If cotRisque >= myRange.Cells(nbRows, 1).Value Then
borneInferieure = myRange.Cells(nbRows, 1).Value
borneSuperieure = myRange.Cells(nbRows, 1).Value
calculPrime = Round(myRange(nbRows, colonneChoix), 4)
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 = Round(txPrimeInferieur - ((cotRisque - borneInferieure) * (txPrimeInferieur - txPrimeSuperieur)) / (borneSuperieure - borneInferieure), 4)
End If
i = i + 1
Wend
wbk.Close
wbk = Nothing
End Function |
Partager