Utiliser variable (Range) avec la méthode Evaluate

Bonjour,

Pour un cours j'ai besoin de faire une macro qui, à partir d'une liste de cours de différents indices boursiers dans mon fichier excel, m'indique la volatilité moyenne annualisée. Mon problème c'est que ma série de données ne contient pas que des nombres mais aussi des #N/A (quand il s'agit d'un jour férié dans le pays où côte l'indice). Sur Excel j'ai utilisé cette formule : =ECARTYPEP(SI(ESTNUM(Perf_CAC40);Perf_CAC40;""))*RACINE(252) (en sachant, je sais pas si ça une importance, que c'est du calcul matricielle). En effet il ne faut prendre compte seulement les valeurs numériques pour que la fonction écart-type fonctionne.

Pour ma macro j'ai défini une variable Perf_Indice qui est la plage de données des performance de l'indice qui sera choisi lorsqu'on lance la macro (je n'ai pas encore réalisé cette partie). Mon problème est que je n'arrive pas à reproduire cette formule dans VBA. Je sais que cette ligne de code fonctionne

WorksheetFunction.Round(Evaluate("STDEVP(IF(ISNUMBER(Exercice1!H3:H6907),Exercice1!H3:H6907,""""))*SQRT(252)*100"), 2)

mais je voudrais que Exercice1H3:H6907 soit remplacé par la plage de ma variable.

Après une recherche sur les forums j'ai essayé ça mais ça ne fonctionne pas mieux :

Round(Evaluate("stdev(if(isnumber("" & perf_indice & ""),"" & perf_indice & "",""""))*sqrt(252)*100"), 2)

(j'ai une erreur de type (13))

(j'ai le même problème pour le Maximum DrawDown mais je pense que si l'un est réglé, l'autre le sera aussi)

Sauriez-vous comment faire ? Si c'est le cas je vous remercie énormément par avance !

Le code du bout de macro qui nous intéresse :

Sub exercice2_0()

'on nomme les plages des cours de chaque indice pour pouvoir les appeler plus facilement
Dim Eurostoxx, CAC40, DAX, FTSE100, IBEX35 As Range

Set Eurostoxx = Range("Exercice1!B2:Exercice1!B6907")
Set CAC40 = Range("Exercice1!C2:Exercice1!C6907")
Set DAX = Range("Exercice1!D2:Exercice1!D6907")
Set FTSE100 = Range("Exercice1!E2:Exercice1!E6907")
Set IBEX35 = Range("Exercice1!F2:Exercice1!F6907")

'on définit les variables qu'on va utiliser dans les formules plus tard
'on les définit comme variant parce que les méthodes utilisées ensuite, notamment Evaluate, le requierent
Dim Indice, Perf_Indice, DD_Indice As Range

'on attribue un indice à la variable indice een fonciton de l'indice choisi par l'utilisateur. De cette manière on utilise la variable Indice et ses dérivées dans toute la macro qui est alors indépendante de l'indice choisi
Set Indice = CAC40

'pour les variables Perf_Indice et DD_Indice utilisées pour la perf moyenne annualisée et le max DD on utilise la propriété offset, comme ça ça marhce quelque soit l'indice choisi
Set Perf_Indice = Indice.Offset(0, 5)
Set DD_Indice = Indice.Offset(0, 20)

'pour les calculs suivants on va utiliser la variable Indice et ses dérivées définies plus haut

'performance moyenne annualisée

    Dim Perf_Moy As Variant

    Perf_Moy = Round(((1 + Perf_Abs / 100) ^ (252 / J) - 1) * 100, 2)

'volatilité annualisée

    Dim Vol As String

    Vol = Round(Evaluate("stdev(if(isnumber("" & perf_indice & ""),"" & perf_indice & "",""""))*sqrt(252)*100"), 2)

    MsgBox Vol

'Max DD

    Dim DD As String

    DD = Round((WorksheetFunction.Max(DD_Indice) * 100), 2)

End Sub