XL 2016 Entrée une formule VBA en format matriciel sur excel. #Valeur!

Zhanties · 11 Septembre 2018

Bonjour à tous,

J'ai décidé de créer une formule VBA afin d'appliquer la formule de Black & Scholes directement dans Excel.
Cette formule permet de calculer le prix d'un Call et d'un Put d'une option (produit financier). Dans ma première version, il suffisait de choisir si l'on prenait un call ou un put et cela a très bien marché après la résolution diverse et varié de plusieurs erreurs de codage.

J'ai donc attaqué une seconde version et c'est donc celle-ci qui me bloque. Le but est ici de pouvoir sélectionner sur Excel n'importe où 9 cellules en format 3 colonnes et 3 lignes et de rentrer la formule VBA dans la barre de formule Excel puis de valider le tout en format matriciel avec Ctrl Maj Entrée.
Cependant cela m'affiche un #Valeur! dans chaque cellules comme vous pouvez le voir dans mon fichier. Celles-ci sont colorisées en jaune fluo pour mieux les voir.
Puisque la formule a marché dans ma première version, je pense qu'il s'agit de mon codage censé créer ce tableau.

En attente de réponse de tous ceux qui voudront bien regarder,
Cordialement

zebanx · 11 Septembre 2018

Bonsoir Zanthies, le forum

J'ai récupéré sur stackoverflow une formule de BnS qui semble donner sur les call / put les mêmes résultats que le formule.
Le lien est inséré dans le fichier et l'UDF enregistré dans un module spécifique.

@+

Dranreb · 11 Septembre 2018

Bonsoir.
Il y a des Raise1 et Raise2 qui ne sont pas des méthodes de l'objet Err
Celui ci n'a qu'une méthode approchante: Raise.

Sub Raise(Number As Long, [Source], [Description], [HelpFile], [HelpContext])
Membre de VBA.ErrObject

donc :

VB:

    Err.Raise 9991, Description:="The volatility can be only positive."
…
    Err.Raise 9992, Description:="La maturité doit être une date future."

Zhanties · 11 Septembre 2018

Zebanx , merci du fichier mais néanmoins ça ne résout pas ma façon d'entrée la formule pour créer le tableau souhaité.
Comme je l'ai dis, j'ai déjà réussi à faire la formule, qui est d'ailleurs quasi similaire à celle du fichier (j'ai aussi fait en Select Case).
Je voudrai vraiment pouvoir sélectionner un format de 3 lignes et 3 colonnes en écrivant la formule et que tout apparaissent comme le "tableau exemple" que j'ai mis à coté. Avec bien entendu des valeurs à la place des "x" de l'exemple.

Dranreb, merci, du coup c'est une correction que j'effectuerai à part. Même si ce n'est pas directement se que je souhaite c'est toujours bien de repérer d'autres erreurs.

Dranreb · 11 Septembre 2018

C'est en tout cas la cause des VALEUR! dont vous vous plaigniez: ça tombait sur "La maturité doit être une date future."
Pour installer une formule matricielle affectez la à la propriété FormulaArray d'un objet Range représentant la plage destinatrice.
Je peux, si ça vous intéresse vous fournir un dialogue permettant de sélectionner des plages en vue d'une opération particulière.

Dranreb · 11 Septembre 2018

Ça passe mieux en mettant en B40:D42 validé par Ctrl+Maj+Entrée :

Code:

=Option_Pricer2($C$7;$C$9;$C$12;$B$36;$C$11)

VB:

Feuil1.[B40:D42].FormulaArray = "=Option_Pricer2($C$7,$C$9,$C$12,$B$36,$C$11)"

Zhanties · 11 Septembre 2018

J'ai mis cette portion en parenthèse en laissant juste : t = (Maturity - Now) / 365
qui est nécessaire au calcul mais ça reste en #Valeur! lorsque je relance le calcul sur Excel.
Je suis obligé de mettre un objet Range? Vu que ma plage de sélection peut être n'importe où ? (je suis pas si calé que ça en VBA après ^^)

Dranreb · 11 Septembre 2018

Comme dit, vous aviez spécifié $B$37 (précédé de "Échéance") au lieu de $B$36 qui est bien une date.

Zhanties · 11 Septembre 2018

Effectivement je l'ai vu juste quelque min après. J'aurai du regarder plus attentivement avant de re répondre.
Bon en tout cas, la bonne nouvelle c'est que la structure pour créer un tableau est bonne.

Maintenant je vais améliorer la gestion des erreurs car c'est là que ça pêche. J'aurai du voir que la case n'était pas bonne si j'avais mis un On Error GoTo ou truc similaire.
Merci à vous.

Dranreb · 11 Septembre 2018

En fait j'avars découvert la cause en mettant après les déclarations dans Option_Pricer2 :

VB:

On Error GoTo Erreur

et avant la End Function :

VB:

Exit Function
Erreur: MsgBox "Err " & Err & " lors du calcul." & vbLf & Err.Description
On Error GoTo 0: Stop: Resume

Dans l'optique où je fournirait le dialogue permettant d'installer cette formule n'importe où, faudrait-il prévoir la sélection des cellules contenant les valeurs des paramètres Spot As Double, Strike As Double, Volatility As Double, Maturity As Date, Rates As Double ?

Zhanties · 11 Septembre 2018

Tu n'es pas obligé de faire le dialogue hormis si cela te donnes envie. Dans ce cas, ça m'intéressera toujours à voir.
Je vais pas refuser d'apprendre.
Prévoir la sélection des cellules des paramètres voudrait dire qu'il faudrait les cantonner à un endroit spécifique non?

Dranreb · 11 Septembre 2018

Non, on les choisirait grâce au dialogue.

Zhanties · 12 Septembre 2018

Bonsoir,

je vous reviens sur cet onglet pour éviter de recréer un sujet. Je traite toujours le même fichier mais cette fois ci, je viens demander des solutions par rapport à mon code de débogage pour l'optimiser.

J'ai repris la ligne qu'a fait Dranreb puis j'ai ajouté d'autres variables :

Code:

ErrorManagement:

Select Case Err.Number
    Case 5
    'Application.Undo
    MsgBox "Argument ou appel de procédure incorrect" & vbNewLine & _
    "Vérifier que le spot et le strike sont positifs" & vbNewLine & _
    "Vérifier que la maturité est une date et une valeur future", vbCritical, "Error management"
Exit Function

    Case 11
    MsgBox "Division par 0." & vbNewLine & _
    "La volatilité doit être supérieur à 0.", vbCritical, "Error management"
Exit Function

    Case Else
    MsgBox "Error " & Err & " lors du calcul." & vbLf & Err.Description
Exit Function

Cependant, cette portion ne fait pas tout. Par exemple écrire -5 dans la case Spot de l'Excel entrainera le message Case 5. Mais une fois le message lu, l'action n'aura pas été annulé et le -5 sera mis dans la cellule. Le "Application.Undo" est censé faire revenir l'action en arrière mais n'a pas marché, c'est pourquoi je l'ai mis en parenthèse (et probablement car je ne sais pas m'en servir).

Autre problème, si j'entre du texte dans Rates, Spot, Strike ou Volatility, cela n'entraine pas d'erreur et n'est donc pas traité dans le Case Else.
J'ai tenté cette approche qui n'a pas marché :

Code:

'Il s'agit ici d'une version test. Le but est que Spot, Strike, Volatility et Rates génèrent une erreur si leurs valeurs est une valeur autre que numérique.
If Not IsNumeric(Spot) Then
MsgBox "La valeur spot " & Spot & " entrée dans la cellule n'est pas numérique .", vbExclamation, "Message Erreur"
End If
Exit Function

Enfin, dans une approche différente, j'ai tenté d'effectuer dans la page ThisWorkbook une private sub Workbook_SheetChange qui se déclencherait si la Maturité n'est pas un format date et empêcherait la validation de celle-ci. Il s'agit du même principe que pour empêcher Spot etc. d'avoir une valeur texte mais avec un Private_Sub de This Workbook. Cela n'a pas marché non plus.

Je met bien entendu le fichier Excel/VBA ce qui est plus lisible.

Dranreb · 13 Septembre 2018

Aucune manoeuvre dans Excel ne peut être exécutée pendant l'évaluation d'une formule par une fonction perso, qu'elle soit matricielle ou non.
Si vous voulez pouvoir faire ça il faudrait déjà ranger une consigne dans une collection qui serait exécutée en dehors par une Sub Workbook_SheetCalculate de ThisWorkbook
Dans la mesure ou la fonction attend des Double et non des Range, si on lui transmet des paramètre qui ne peuvent être convertis en double, elle n'est même pas évaluée, et ne peut donc rien détecter.
Vous devez faire un Select Case Target.Address dans une Workbook_SheetChange de ThisWorkbook ou une Worksheet_Change du module de la feuille, afin de ne réagir que lorsqu'on change la cellule concernée et que Target.Value ne respecte pas les critères appropriés

Zhanties · 13 Septembre 2018

J'ai tenté un ultime test en reprenant la méthode Err.Raise pour générer des erreurs mais avec un nouvel échec. Bon au moins je vois beaucoup de différentes façons et je recherche.
Car cette méthode existe, et elle semble bien pratique si j'arrivais à la maîtriser.
En espérant que ça ne reprend pas des explications précédentes à propos des valeurs Range qui ne serait pas détecté (auquel cas mon code ci dessous ne servirait à rien, mais j'aimerai tout de même réussir à utiliser cette méthode).

Code:

Public Const ERROR_INVALID_DATA As Long = vbObjectError + 514



On Error GoTo ErrorManagement



If Not IsNumeric(Spot) Then
    Err.Raise ERROR_INVALID_DATA
End If



'tout le reste du code déjà vu
'les calculs etc.


ErrorManagement:

Select Case Err.Number
    Case 514
    MsgBox "Veuillez mettre un format numérique"
    Exit Function
End Select

XL 2016 Entrée une formule VBA en format matriciel sur excel. #Valeur!

Zhanties

XLDnaute Nouveau

Pièces jointes

zebanx

XLDnaute Accro

Pièces jointes

Dranreb

XLDnaute Barbatruc

Zhanties

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Zhanties

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Zhanties

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Zhanties

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Pièces jointes

Zhanties

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

Zhanties

XLDnaute Nouveau

Discussions similaires