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.
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]
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.