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 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
|
'Déclaration des constantes
Dim i As Integer
Dim j As Integer
Dim Total As Double
Dim Taxe_Plein As Double
Dim Taxe_Reduit As Double
Dim Duree_Contrat As Double
Dim Taux_Actualisation As Double
Dim Actualisation As Double
Dim Taux_CSPE As Double
Dim Prix_Abo As Double
Dim Prix_kWh As Double
Dim Duree_Fonctionnement As Double
Dim Conversion As Double
Dim Abo As Double
Dim Conso As Double
Dim CSPE As Double
Dim Colonne As Integer
Dim Pas_Puissance As Double
Dim Pas_Conso As Double
Dim Final_HT As Double
Dim Final_TTC As Double
Dim Ligne_P1 As Integer
Dim Ligne_P2 As Integer
Dim Ligne_P3 As Integer
Dim Ligne_MG As Integer
Dim Ligne_SActuelle As Integer
Dim Duree_P1 As Double
Dim Duree_P2 As Double
Dim Duree_P3 As Double
Dim Duree_MG As Double
Dim Duree_SA As Double
Dim Total_P1 As Double
Dim Total_P2 As Double
Dim Total_P3 As Double
Dim Total_MG As Double
Dim Total_SA As Double
Public Sub Calcul_Energie_PPP()
Worksheets("XXX").Range("Z4:AC70").ClearContents
'Attribution des constantes
Taxe_Plein = Worksheets("Hypothèses").Range("B72").Value
Taxe_Reduit = Worksheets("Hypothèses").Range("B73").Value
Taux_CSPE = Worksheets("Hypothèses").Range("B74").Value
Prix_Abo = Worksheets("Hypothèses").Range("B61").Value
Prix_kWh = Worksheets("Hypothèses").Range("B62").Value
Duree_Fonctionnement = Worksheets("Energie").Range("B3").Value
Conversion = Worksheets("Energie").Range("D3").Value
Dim Total_HT As Double
Dim Total_TTC As Double
Duree_Contrat = Worksheets("CHIFFRAGE TOTAL").Range("C4").Value
Taux_Actualisation = Worksheets("Hypothèses").Range("B75").Value
Actualisation = 1
Duree_P1 = Worksheets("Reconstruction").Range("B6").Value
Duree_P2 = Worksheets("Reconstruction").Range("B40").Value
Duree_P3 = Worksheets("Reconstruction").Range("B73").Value
Colonne = 26
Ligne_P1 = 4
Ligne_P2 = Ligne_P1 + Duree_P1
Ligne_P3 = Ligne_P2 + Duree_P2
Dim Puissance_P1 As Double
Puissance_P1 = Worksheets("ENERGIE").Range("B18").Value
Dim Puissance_P2 As Double
Puissance_P2 = Worksheets("ENERGIE").Range("B37").Value
Dim Puissance_P3 As Double
Puissance_P3 = Worksheets("ENERGIE").Range("B56").Value
Dim Conso_P1 As Double
Conso_P1 = Worksheets("ENERGIE").Range("B19").Value
Dim Conso_P2 As Double
Conso_P2 = Worksheets("ENERGIE").Range("B38").Value
Dim Conso_P3 As Double
Conso_P3 = Worksheets("ENERGIE").Range("B57").Value
'Calcul de l'énergie pour la Phase 1
Pas_Conso = (Conso_P2 - Conso_P1) / Duree_P1
Pas_Puissance = (Puissance_P2 - Puissance_P1) / Duree_P1
For i = Ligne_P1 To Duree_P1
If i = Ligne_P1 Then
Abo = ((Puissance_P1 / 1000) * Conversion) * Prix_Abo
Conso = Conso_P1 * Prix_kWh
CSPE = Conso_P1 * Taux_CSPE
Total_HT = Abo + Conso + CSPE
Total_TTC = Abo * (1 + Taxe_Reduit) + (Conso + CSPE) * (1 + Taxe_Plein)
Final_HT = Final_HT + Total_HT
Final_TTC = Final_TTC + Total_TTC
Worksheets("XXX").Cells(i, Colonne) = Total_HT * Actualisation
Worksheets("XXX").Cells(i, Colonne + 1) = Total_TTC * Actualisation * (1 + Taxe_Plein)
Worksheets("XXX").Cells(i, Colonne + 2) = Worksheets("XXX").Cells(i, Colonne)
Worksheets("XXX").Cells(i, Colonne + 3) = Worksheets("XXX").Cells(i, Colonne + 1)
End If
MsgBox "Jusqu'ici tout va bien... " & i
Abo = ((Puissance_P1 + Pas_Puissance * (i - Ligne_P1)) / 1000 * Conversion) * Prix_Abo * Actualisation
Conso = (Conso_P1 + Pas_Conso * (i - Ligne_P1)) * Prix_kWh * Actualisation
CSPE = (Conso_P1 + Pas_Conso * (i - Ligne_P1)) * Taux_CSPE * Actualisation
Total_HT = Abo + Conso + CSPE
Total_TTC = Abo * (1 + Taxe_Reduit) + (Conso + CSPE) * (1 + Taxe_Plein)
Final_HT = Final_HT + Total_HT
Final_TTC = Final_TTC + Total_TTC
Worksheets("XXX").Cells(i, Colonne) = Total_HT
Worksheets("XXX").Cells(i, Colonne + 1) = Total_TTC
Worksheets("XXX").Cells(i, Colonne + 2) = Worksheets("XXX").Cells(i - 1, Colonne + 2) _
+ Worksheets("XXX").Cells(i, Colonne)
Worksheets("XXX").Cells(i, Colonne + 3) = Worksheets("XXX").Cells(i - 1, Colonne + 3) _
+ Worksheets("XXX").Cells(i, Colonne + 1)
Actualisation = Actualisation * (1 + Taux_Actualisation)
Next i
End Sub |
Partager