Comprendre un Macro et convertir en formule Excel

[Laurent C]

Bonjour tout le monde,

Je souhaiterai supprimer la macro ci-dessous (voir ci-joint également) et le remplacer par une Formule excel.

Vous trouverez ci-joint un fichier excel avec les différents éléments de la macro et le résultat affiché à la colonne "R".

Je vous remercie vivement de votre aide.

Cordialement,

Laurent


Function ExcessMileage(PenaltyType As String, AvgBurnrate As Double, StdBurnrate As Double, HoldingPeriod As Double, Penalty As Double, MileageLimit As Double, Penalty2 As Double, MileageLimit2 As Double, Penalty3 As Double, MileageLimit3 As Double) As Double
Dim LimitBurnrate As Double
Dim LimitBurnrate2 As Double
Dim LimitBurnrate3 As Double
Dim FreeFleet As Double
Dim FreeFleet2 As Double
Dim FreeFleet3 As Double
Dim AvgMileageNonFreeFleet As Double
Dim AvgMileageNonFreeFleet2 As Double
Dim AvgMileageNonFreeFleet3 As Double
Dim Pi As Double
Pi = WorksheetFunction.Pi
If HoldingPeriod = 0 Then
ExcessMileage = 0
Else
If PenaltyType = "Fixed amount" Then
ExcessMileage = Penalty
Exit Function
End If

If PenaltyType = "Total Mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / HoldingPeriod
LimitBurnrate2 = MileageLimit2 / HoldingPeriod
LimitBurnrate3 = MileageLimit3 / HoldingPeriod
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Or FreeFleet2 = 1 Or FreeFleet3 = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate

End If

If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

ExcessMileage = 0
Exit Function

End If


If PenaltyType = "Monthly mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / 30.5
LimitBurnrate2 = MileageLimit2 / 30.5
LimitBurnrate3 = MileageLimit3 / 30.5
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate

End If

If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If

ExcessMileage = 0
Exit Function
Exit Function
End If
End If

End Function

 

[Compte Supprimé 979]

Re : Comprendre un Macro et convertir en formule Excel

Bonjour

Bonjour tout le monde,
Je souhaiterai supprimer la macro ci-dessous (voir ci-joint également) et le remplacer par une Formule excel.

Tu veux rire je pense 😕

Si une telle fonction a été développée en VBA,
à mon avis, c'est qu'elle ne peut pas être reproduite par une simple formule... non 😕

 

[Laurent C]

Re : Comprendre un Macro et convertir en formule Excel

Bonjour Bruno,

Désolé pour cette réponse tardive. Je cours toujours derrière cette solution en vain.

Peut-être que vous pourriez m'aider à traduire la macro? Je pourrais ainsi voir comment le gérer par la suite.

Merci d'avance pour votre aide.

Cordialement,
Abdoullahi

 

[Compte Supprimé 979]

Re : Comprendre un Macro et convertir en formule Excel

Bonjour Laurent ou Abdoullahi 🙄

Peut-être que vous pourriez m'aider à traduire la macro? Je pourrais ainsi voir comment le gérer par la suite.

Qui que tu sois.... je ne suis que très rarement sur ce forum et qui plus est je ne suis pas là pour former 😛