Formule matricielle en VBA - Instruction Evaluate

Bonsoir,

J’ai deux questions :

Question 1

Sur un très vieux fil, j’avais demandé comment transformer une ligne de code enregistrée et contenant une formule matricielle en une ligne de code permettant l’utilisation d’une variable et on m’avait aidé à transformer la première ligne ci-dessous en la deuxième :

Range("G3").FormulaArray = "=SQRT(MMULT(MMULT(TRANSPOSE(R[-1]C[-3]:R[3]C[-3]),R[-1]C[2]:R[3]C[6]),R[-1]C[-3]:R[3]C[-3]))"

devenait

x = Evaluate([SQRT(MMULT(MMULT(TRANSPOSE(D2:D6),I2:M6),D2:D6))])

De la même manière j’ai tenté de transformer la ligne enregistrée

Range("C1").FormulaArray = "=MAX(IF(C[-2]<>1111,C[-2]))"

en

x = Evaluate([MAX(IF(A:A<>1111,A:A)])

ou quelque chose d’analogue, en utilisant “A:A”, (“A:A”), Range(“A:A”) ou que sais-je d’autre, mais rien n’y fait.

Pour information, la ligne enregistrée ci-dessus correspond à la formule {=MAX(SI(A:A<>1111;A:A))}

Savez-vous m’aider ? Vous trouverez quelques essais de macros dans le fichier ci-joint.

Y a-t-il un truc afin de savoir transformer n’importe quel enregistrement Range("C1").FormulaArray = ...... en ligne

x = Evaluate([……]) ???

NB : Je pourrais bien entendu utiliser l'une des deux lignes de codes ci-dessous, tirées de formules proposées par Amadeus et CousinHub et d'autres membres sur un autre fil, mais la solution avec l'instruction Evaluate m'intéresse un peu plus.

y = WorksheetFunction.Large(Range("A:A"), WorksheetFunction.CountIf(Range("A:A"), 1111) + 1)

z = WorksheetFunction.Large(Range("A:A"), WorksheetFunction.SumProduct(WorksheetFunction.CountIf(Range("A:A"), 1111)) + 1)

Question 2

Si j’inscris par exemple =MAX(SI($A:$A<>1111;$A:$A)) dans la cellule C1 et que j’enregistre une macro durant le moment où je transforme cette formule en matricielle, j’obtiens la macro enregistrée

Range("C1").FormulaArray = "=MAX(IF(C1<>1111,C1))"

Si je lance maintenant cette macro enregistrée, le résultat retourné en C1 est

{=MAX(SI(C1<>1111;C1))}

c’est-à-dire que les références à la colonne A ont disparu et sont remplacées par les références de la cellule C1 !!!!

Afin que ça fonctionne je dois transformer la macro enregistrée – ça je ne l’avais encore jamais vu – en

Range("C1").FormulaArray = "=MAX(IF($A:$A<>1111,$A:$A))"

Avez-vous une explication ?

Cordialement.

36demo.zip (9.07 Ko)

Bonsoir Yvouille,

Dans la fenêtre exécution, ça le fait

? evaluate("MAX(IF($A$2:$A$9<>1111,$A$2:$A$9))")
Sub test_Evaluate()
Dim lastrow As Long, x
    lastrow = Range("A1", Range("A" & Rows.Count).End(xlUp)).Count
    x = Evaluate("MAX(IF($A$2:$A$9<>1111,$A$2:$A$9))")
    x = Evaluate("MAX(IF($A$2:$A$" & lastrow & "<>1111,$A$2:$A$" & lastrow & "))")
End Sub

klin89

Salut Klin, merci beaucoup pour ta réponse rapide et géniale

Je n'ai par contre pas compris comment la fenêtre Exécution faisait cela ??

Tu peux m'en dire un peu plus ?

Amicalement.

Bonjour,

Ceci fonctionne :

x = Evaluate("=MAX(IF(C1<>1111,C1))")

Je suis par ailleurs un peu surpris que la formulation avec A:A ne déclenche pas d'erreur ! (Mais c'est un fait ! )

Microsoft stipulant (aide sur FormulaArray :

Si vous utilisez cette propriété pour entrer une formule sous forme matricielle, vous devez utiliser le style de référence L1C1, et non le style de référence A1

Cordialement.

Bonjour,

1) une alternative si ta formule n'est pas lié à une ligne relative comme dans ton cas (sinon le résultat dépend de sa position sur la feuille) :

  • mettre ta formule dans un nom (maxi par exemple), elle sera calculée en matricielle
  • et dans vba plus qu'à appeler le nom : a=[maxi]
Evaluate() évalue toujours en matriciel aussi.

2)

aide sur FormulaArray :

Si vous utilisez cette propriété pour entrer une formule sous forme matricielle, vous devez utiliser le style de référence L1C1, et non le style de référence A1

Une astuce car c'est souvent prise de tête d'inscrire dans une cellule une formule matricielle en vba :

L'inscrire en validation normale (ce qui permet, si on veut, d'utiliser .formulaLocal= "=ta_formule_en_français_références_type_A1"),

et la revalider en matriciel :

Range("A2").FormulaArray = Range("A2").Formula

3) quant à C1 c'est colonne1 en référence R1C1

eric

bonsoir

essayer ca

Sub test()
Dim x,  InputFormula, OuputFormula

InputFormula = "=MAX(IF(C[-2]<>1111,C[-2]))"
OuputFormula = Application.ConvertFormula( _
    Formula:=InputFormula, _
    fromReferenceStyle:=xlR1C1, _
    toReferenceStyle:=xlA1)
 MsgBox Evaluate(OuputFormula)
'pour ecrit la formule dans la cellue
'ActiveSheet.Evaluate("C1").FormulaArray = OuputFormula

End Sub

Bonsoir,

Un tout grand merci à vous tous pour votre collaboration

Après avoir reçu la toute première réponse de Klin – avant ses deux éditions – j’ai indiqué que sa réponse me convenait absolument. Sur la base de sa réponse, j’ai pu transformer son instruction par la ligne ci-dessous qui dépassait toutes mes attentes:

x = Evaluate("MAX(IF(A:A<>1111,A:A))")

Puis Klin, tu as édité ta réponse, mais je n’ai pas trop compris pourquoi ; ça complique plus les choses que ce que ça les simplifient, non ?

MFerrard, tu apportes une solution très proche de celle de Klin.

Eriic, tu me donnes une quantité d’informations que je n’ai pas eu le courage d’étudier vu la solution simplissime déjà reçue.

Amir, ta solution me semble bien compliquée en regard de la solution trouvée se limitant à une ligne de code.

Une chose que je n’ai quand même toujours pas comprise : comment transformer une instruction Range("C1").FormulaArray = ...... en une instruction x = Evaluate([……]) par l’intermédiaire de la fenêtre d’exécution tel qu’indiqué par Klin dans son premier message !!!!!

Recevrais-je une info à ce sujet ?

Chaleureusement.

Bonjour,

?[A1].formula

C'est bon ? Tu as réussi à tout lire ?

eric

Bonjour

Yvouille a écrit :

Amir, ta solution me semble bien compliquée en regard de la solution trouvée se limitant à une ligne de code.

Moi j ai pensé que vous voulez autre chose compliqué que vous ne saviez pas,

prq la solution était entre tes mains, en fait la solution de Klin89 n est pas une solution radicale

Tous ce qu’il a fait une rectification banale, il ya une règle mathématique qui dit compte trs les parenthèses : le nombre des parenthèses fermées doit égale aux parenthèses ouvertes

Si vous rejetez un coup d'œil sur to annonce vous constater que c est un faute de frappe ou une chose similaire :

x = Evaluate([MAX(IF(A:A<>1111,A:A)]) ‘ ta ligne
x = evaluate("MAX(IF($A$2:$A$9<>1111,$A$2:$A$9))") 'ligne de Klin89

À part la parenthèse (oublié) c est la même chose

Alors si vous relancez ca (ta ligne) et ajoutez la parenthèse (oublié)

MsgBox Evaluate([MAX(IF(A:A<>1111,A:A))])’ vous aurez 45

Cordialement

Bonjour à tous, merci pour vos réponses

Un merci spécial à Amir qui m'a permis de me rendre compte de mon erreur au sujet de cette parenthèse

Très bonnes salutations.

Rechercher des sujets similaires à "formule matricielle vba instruction evaluate"