PB fonction mathématique utilisant pi

Question

Bonjour l'appelle de la fonction définie ci dessous donne l'erreur Type d'argument Byref incompatible pourquoi? Comment résoudre cela svp?

Le compilatuer bloque sur l'appelle de la fonction Nprime avec l'instruction Nprime(d1)

Merci d'avance.

Cordialement.

Ludo

Function Nprime(z As Double) As Double

Nprime = (1 / (WorksheetFunction.Power(2 * WorksheetFunction.Pi, 0.5))) * Exp(-0.5 * WorksheetFunction.Power(z, 2))

End Function

Sub BSCALLPRICE()

Dim St, t, Ct, Ct2, Pt, K, K2, r, M, sigma, d1, d2, d12, d22, deltac, deltap, gammac, gammap, vegac, vegap, thetac, thetap, rhoc, rhop As Double

St = Worksheets("Feuil1").Cells(3, 1)

t = Worksheets("Feuil1").Cells(3, 6)

K = Worksheets("Feuil1").Cells(3, 2)

K2 = Worksheets("Feuil1").Cells(5, 2)

r = Worksheets("Feuil1").Cells(3, 4)

M = Worksheets("Feuil1").Cells(3, 5)

sigma = Worksheets("Feuil1").Cells(3, 3)

d1 = (WorksheetFunction.Ln(St / K) + (r + 0.5 * WorksheetFunction.Power(sigma, 2)) * (M - t)) / (sigma * WorksheetFunction.Power((M - t), 0.5))

d2 = d1 - sigma * WorksheetFunction.Power((M - t), 0.5)

If d1 < 0 Then

Ct = St * (1 - WorksheetFunction.NormDist(-d1, 0, 1, True) - K * Exp(-r * (M - t)) * (1 - WorksheetFunction.NormDist(-d2, 0, 1, True)))

ElseIf d1 > 0 Then

Ct = St * WorksheetFunction.NormDist(d1, 0, 1, True) - K * Exp(-r * (M - t)) * WorksheetFunction.NormDist(d2, 0, 1, True)

End If

d12 = (WorksheetFunction.Ln(St / K2) + (r + 0.5 * WorksheetFunction.Power(sigma, 2)) * (M - t)) / (sigma * WorksheetFunction.Power((M - t), 0.5))

d22 = d12 - sigma * WorksheetFunction.Power((M - t), 0.5)

If d12 < 0 Then

Ct2 = St * (1 - WorksheetFunction.NormDist(-d12, 0, 1, True)) - K2 * Exp(-r * (M - t)) * (1 - WorksheetFunction.NormDist(-d22, 0, 1, True))

Else

Ct2 = St * WorksheetFunction.NormDist(d12, 0, 1, True) - K2 * Exp(-r * (M - t)) * WorksheetFunction.NormDist(d22, 0, 1, True)

End If

Worksheets("Feuil1").Cells(3, 7) = Ct

Pt = Ct - St + K * Exp(-r * (M - t))

Worksheets("Feuil1").Cells(3, 8) = Pt

Worksheets("Feuil1").Cells(3, 10) = Pt + Ct2

deltac = WorksheetFunction.NormDist(d1, 0, 1, True)

deltap = deltac - 1

Worksheets("Feuil1").Cells(9, 2) = deltac

Worksheets("Feuil1").Cells(10, 2) = deltap

Dim Nprimed1 As Double

Nprimed1 = Nprime(d1)

gammac = Nprimed1 / (St * sigma * WorksheetFunction.Power(M - t, 0.5))

gammap = gammac

Worksheets("Feuil1").Cells(9, 3) = gammac

Worksheets("Feuil1").Cells(10, 3) = gammap

vegac = sigma(M - t) * WorksheetFunction.Power(St, 2) * gammac

vegap = vegac

Worksheets("Feuil1").Cells(9, 4) = vegac

Worksheets("Feuil1").Cells(10, 4) = vegap

thetac = -(St * sigma / (2 * WorksheetFunction.Power(M - t, 0.5))) * Nprime(d1) - r * K * Exp(-r * (M - t)) * WorksheetFunction.NormDist(d2, 0, 1, True)

thetap = -(St * sigma / (2 * WorksheetFunction.Power(M - t, 0.5))) * Nprime(d1) + r * K * Exp(-r * (M - t)) * (1 - WorksheetFunction.NormDist(d2, 0, 1, True))

Worksheets("Feuil1").Cells(9, 5) = thetac

Worksheets("Feuil1").Cells(10, 5) = thetap

rhoc = (M - t) * K * Exp(-r * (M - t)) * WorksheetFunction.NormDist(d2, 0, 1, True)

rhop = (WorksheetFunction.NormDist(d2) - 1) * (M - t) * K * Exp(-r * (M - t))

Worksheets("Feuil1").Cells(9, 6) = rhoc

Worksheets("Feuil1").Cells(10, 6) = rhop

End Sub

2pricer-d-options-vanilles.xlsm (26.40 Ko)

h2so4 · Answer

Je pense t'avoir signalé hier que ton instruction Dim St, t, Ct, Ct2, Pt, K, K2, r, M, sigma, d1, d2, d12, d22, deltac, deltap, gammac, gammap, vegac, vegap, thetac, thetap, rhoc, rhop As Double n'était pas correcte, si tu voulais définir toutes ces variables avec un type double. je t'invite à relire le message de Steelson et le mien à ce sujet. https://forum.excel-pratique.com/viewtopic.php?f=2&t=118190 Ta variable d1 n'a pas le type correct, elle a le type variant et ta procédure attend une variable de type double...