Formule Excel dans case via VBA (solver)
Hey tout le monde
J'ai un petit soucis, je vous explique....
J'essaie de faire une minimisation de la volatilité en utilisant le solver via vba uniquement, les pondération doivent changer pour minimiser la varince ( matrice poids qui se trouve sur la feuille "optimization" colonne D3... j'essaie donc de faire comprendre à excel comment sont reliés les calculs entre eux mais mon solveur ne fonctionne pas... il ne reconnait pas ma formule :
ActiveCell.FormulaR1C1 = "=racine(52)*racine(produitmat=(poids,mat_var,transpose(poids))"
voici mon code et voici aussi mon fichier
Dim poids As Range, mat_var As Range
Set poids = Range(Worksheets("Optimization").Cells(3, 4), Worksheets("Optimization").Cells(3, 4).End(xlDown))
Set mat_var = Range(Worksheets("Calculs").Cells(4, c + 6), Worksheets("Calculs").Cells(3 + c, c * 2 + 5))
Range("L5").Select
ActiveCell.FormulaR1C1 = "0,1"
ActiveCell.FormulaR1C1 = "=racine(52)*racine(produitmat=(poids,mat_var,transpose(poids))"
SolverOk SetCell:="$L$5", MaxMinVal:=2, ValueOf:=0, ByChange:="poids"
,Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
fichier :
merci à tous
Sab
Bonjour,
1) .Formula et .FormulaR1C1 veulent les fonctions en anglais et les séparateurs ,
Tu as .FormulaLocal si tu veux conserver en français.
2) la formule est une chaine.
Si tu veux y insérer une variable il faut :
- fermer ta chaine avec "
- insérer ta variable avec & ta_var &
- ouvrir la suite de la chaine avec "
- doubler les " internes avec ""
Ex :
"=B9*2+3&" euros"
devient
x=2
m=3
"=B9*" & x & "+" & m & "&"" euros"
eric
merci pour ta réponse eriic mais meme en passant du français à l'anglais , vba ne fait rien.. j'ai donc changé mon code mais ça ne mache tjrs aps
Dim poids As Range, mat_var As Range
Range("L5").Select
ActiveCell.FormulaR1C1 = "0,1"
ActiveCell.FormulaR1C1 = "=sqrt(52)*sqrt(mmult(transpose(r[-2]c[7]:r[-2+c]c[7]),Optimization![-1]c[-11+c+5]:r[-1+c]c[-11+c+c+5], r[-2]c[7]:r[-2+c]c[7])"
SolverOk SetCell:="$L$5", MaxMinVal:=2, ValueOf:=0, ByChange:="poids"
',Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
Au plaisir de te relire
Sab
Ben si tu ne fais que le 1/4 de ce que j'ai écrit ça n'ira toujours pas oui
dsl eriic merci pour ton aide, mais je suis débutante et je comprends pas toujours tout
Dim poids As Range, mat_var As Range
'Set poids = Range(Worksheets("Optimization").Cells(3, 4), Worksheets("Optimization").Cells(3, 4).End(xlDown))
'Set mat_var = Range(Worksheets("Calculs").Cells(4, c + 6), Worksheets("Calculs").Cells(3 + c, c * 2 + 5))
Range("L5").Select
ActiveCell.FormulaR1C1 = "=sqrt(52)*sqrt(mmult(transpose(r[-2]c[7]:r[-2+" & c & "]c[7]),Optimization!r[-1]c[-11+" & c & "+5]:r[-1+" & c & "]c[-11+" & c & "+" & c & "+5], r[-2]c[7]:r[-2+" & c & "]c[7])"
SolverOk SetCell:="$L$5", MaxMinVal:=2, ValueOf:=0, ByChange:="r[-2]c[7]:r[-2+c]c[7]"
',Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
End Sub
Sab
Pour précision vba me dit " erreur définie par l'application ou par l'objet"
merciii beaucoup beaucoup beaucoup
Sab
Si il n'est veut pas, c'est sans doute que ta formule n'est pas bonne.
Met un Stop avant ActiveCell.FormulaR1C1 =...
Lance ta macro (n'oublie pas que c doit être évalué puisque tu t'en sers dans Set mat_var = ...)
A l'arrêt, sélectionne "=sqrt(52)*sqrt(mmult(transpose(r[-2]c[7]:r[-2+" & c & "]c[7]),Optimization!r[-1]c[-11+" & c & "+5]:r[-1+" & c & "]c[-11+" & c & "+" & c & "+5], r[-2]c[7]:r[-2+" & c & "]c[7])"
et fais glisser l'expression dans la fenêtre espion (dans Affichage si tu ne l'as pas) pour la faire évaluer par vba.
Si tu vois ici l'erreur dans ta formule tu corriges ton code,
sinon tu la copies d'ici pour la coller à sa place dans la feuille (en enlevant les " " d'extrémité) et tu vois ce qui ne plait pas à excel pour la corriger.
Le mieux étant quand même de partir de la formule correcte mise sur la feuille et d'utiliser .formulaLocal ou .FormulaR1C1Local selon tes habitudes.
Entraine-toi avant avec une formule plus simple telle que celle que je te t'avais mise, ça te permettra d'assimiler les bases.
merci eriic
effectivement quand je fais le calcul sur excel, ça me met erreur
merci Eriic
Sab
re
j'ai trouvé mon soucis par rapport à transpose mais la fonction ne plait tjrs pas .. je ne comprends absolument pas ce qui le dérange, la taille de mes matrices sont correctes, mon code le semble aussi...............
As-tu pris la bonne option ?
Qu'est-ce qui te pousse à vouloir écrire ta fonction en vba ?
Si tu mettais un classeur avec ta fonction sur la feuille on y verrait plus clair
oui c'est la bonne solution car je veux utiliser le solver avec vba hors le solver sil ny a pas de fonction associé à la cellule cible le solver ne compile pas. De plus, je code dans le cadre de la gestion de portefeuille , l'utilisateur va choisir ses actifs et ensuite minimiser la variance pour avoir les pondérations optimales... sachant que le nombre d'actifs dépend de l'utilisateur je suis obligée de coder une formule dynamique ( la taille de la matrice variance covariance se modifie, de meme pour le vecteur des pondérations , c ce que j'essaie de rentrer dans la formule activecell.ormulaR1C1.....
Tu peux rendre dynamique une formule sans vba.
c'est une consigne je suis obligée de travailler avec uniquement vba ....as tu une idée de pourquoi j'ai ce message?
ça me prend la tête, ça me bloque complètement j'avance plus depuis des heures ....
Si tu mettais un classeur avec ta fonction sur la feuille on y verrait plus clair
bis repetita
merci pour tes réponses, mais c'est pas tres clair ce que tu me demandes ..
Si tu veux que ça avances un peu dépose un fichier avec ta formule correcte mise à la main sur la feuille.
Pour l'instant on n'a que du vent pour la connaitre puisque tu ne proposes qu'une ligne de code erronée.
Et précise sa (ou ses) partie variable et ses conditions qui te pousse à vouloir la refabriquer en vba.
j'ai mis en cellule L9 de la feuille optimization la formule que je code sur vba et qui ne fonctionne pas. Mon travail est dans le cadre de la construction de portefeuille, c'est lutilisateur à la base qui chosiit ses actifs ( et donc le nombre d'actifs peut varier d'où le coté dynamique de ma formule car si ya 5 actif ou 10 actifs ma matrice de varaince covariance naura pas la meme taille ( c le carré quon voit sur la feuille calculs ni mes pondérations etc...et donc quand je vais vouloir miimiser ma volatilité pour chosiir mes pondérations optimales de mes actifs je ais devoir prendre en compte la taille de mon vecteur pondérations ( qui varient avec le nombre d'actifs) et ma matrice de variances covariances.... et je bloque ............ pour lancer la macro vous devez aller sur la feuille optimization puis cliquer sur le bouton construire le portefeuille puis la macro est lancée
voici mon fichier :
merci beaucoup
Sab
Bonjour,
Je t'ai créé 2 noms dynamiques et ta formule d'adapte automatiquement.
Plus besoin de la faire en vba (mais comme c'est une validation matricielle c'est .FormulaArray qu'il aurait fallu. Et comme c'est une chaine c'est .address de tes plages qu'il fallait utiliser pour avoir les références).
Je me base sur la colonne B pour connaitre le nombre d'actifs, ne plus rien mettre d'autre dans cette colonne ou adapter les noms.
Pas mis de nom pour Calculs!H4:I5, je n'ai pas compris si c'était toujours une plage fixe 2x2 ou non.
Je l'ai quand même défini sous le nom tablCalculs si besoin (plage carrée du nombre d'actifs)
eric
PS : fichier trop gros non passé. Le voilà : https://www.cjoint.com/c/FCBkFrYErqX
Fait Ctrl+Fin sur chacune de tes feuilles pour voir la dernière cellule.
Supprimer toutes les lignes et colonnes inutiles qui alourdissent pour rien
merci enormément eriic c'est sympa
merci encore
Sab
ça marche super mercii j'aurais pas trouver sans vous
sab