Discussion :

[Denis_67]

Bonjour,

J'utilise la fonction index equiv pour retrouver une valeur à l'intersection d'une colonne et d'une ligne.
cela fonction comme formule dans une cellule, mais comment obtenir le résultat directement dans une variable.
La transcription suivante ne fonctionne pas, car MATCH n'est pas une fonction vba (erreur compilation).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
dim Ma_Valeur
Ma_Valeur = INDEX(C51:M61;MATCH(C3;B51:B61;0);MATCH(C2;C50:M50;0))

Merci

Denis

[Menhir]

cela fonction comme formule dans une cellule, mais comment obtenir le résultat directement dans une variable.
La transcription suivante ne fonctionne pas, car MATCH n'est pas une fonction vba (erreur compilation).

Deux solutions : la méthode Evaluate et la propriété WorksheetFunction.
https://docs.microsoft.com/fr-fr/off...ation.evaluate
https://docs.microsoft.com/fr-fr/off...ksheetfunction

[Denis_67]

Merci Menhir,
Je vais etudier evaluate
pour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.WorksheetFunction.

J'ai une erreur 1004 Impossible de lire la propriete Matchde la classe WorsheetsFuncrtion.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
         chgt_ext = Application.WorksheetFunction.Match(ThisWorkbook.Sheets("Chgt").Range("C3"), ThisWorkbook.Sheets("Chgt").Range("C51:C61"), 0)

[Menhir]

J'ai une erreur 1004 Impossible de lire la propriete Matchde la classe WorsheetsFuncrtion.

La syntaxe elle-même est bonne et Match n'est pas incompatible avec WorksheetFunction (j'ai testé).
Généralement, quand l'exécution renvoie ça, c'est que le Match n'a pas trouvé la valeur cherchée.

[Denis_67]

Merci Menhir !!!

Je cherchais une erreur application (d'après le message) alors que c'était bien une erreur dans la plage de recherche et donc il ne trouvait pas de réponse.

Cependant cela m'a appris qu'il faut donc que je mette en place une gestion d'erreur pour que si la valeur cherchée n'existe pas il continue, alors que j'avais juste prévu de tester le contenu de la variable en retour.

En tout cas.

Merci
Denis

[Menhir]

Quand on n'est pas sûr que la valeur cherchée soit trouvée, il faut toujours faire une gestion d'erreur.
Dans Excel, la fonction SIERREUR fait ça très bien.
Il suffit de trouver son équivalent anglophone pour l'appliquer à VBA.

Dès qu'on utilise plus d'une fonction dans une formule, Evaluate est plus intéressant que WorksheetFunction.

[Pierre Fauconnier]

Salut.

Avec le résultat de Evaluate poussé dans une variable de type Variant, il ne saurait pas y avoir une levée d'exception. On Error... ne sert donc à rien ici et ne sert de toute façon pas à cela, selon mes principes de bonne programmation. On Error sert à gérer les exceptions, pas les erreurs de programmation. Je préfère écrire ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Dim Result
 
Result = Evaluate(...)
If Not IsError(Result) Then
   ... ' on continue
Else
   ... ' on gère le fait que EVALUATE a renvoyé une valeur d'erreur
End If

N'oublie pas non plus de traduire les fonctions en international, EVALUATE ne comprenant pas les versions locales... Perso, pour réaliser cela, je crée la fonction en Excel, puis en gardant la cellule qui contient la formule active, je vais en VBA et dans la fenêtre d'exécution (CTRL+G si elle n'est pas visible), j'utilise ? activecell.formula pour la récupérer en vba international. Ca te permettra de voir que SIERREUR dont parle Menhir (Hello ) est traduit en IFERROR. Tu peux donc, soit choisir d'envoyer le résultat dans une variable de type variant puis tester si cette variable vaut une erreur, soit rédiger ta fonction avec IFERROR comme le propose Menhir et tester alors si le résultat de EVALUATE renvoie ce que tu as mis dans ton IFERROR. Tu pourrais donc écrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub TEst3()
Dim Result
 
Result = Evaluate("iferror(Index(...,Match(...)), ""Pas trouvé!"")")
If Result <> " Pas trouvé" Then
   ... ' on continue
Else
   ... ' on gère le fait que EVALUATE a renvoyé un valeur d'erreur
End If
End Sub

Je te conseille de passer par une variable pour reconstruire la formule qui sera utilisée par EVALUATE. Cela te permet de voir plus facilement sa valeur lors du débogage, surtout si tu la recomposes en y insérant des variables, ce qui sera très souvent le cas. Le but n'est pas d'écrire le moins de lignes possible, mais d'écrire du code lisible, compréhensible et qui peut être facilement testé.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Dim Formula As String
Dim Result
 
Formula = "IfError(Index(...,Match(...)), 0)"
Result = Evaluate(Formula)

Dans la formule que tu souhaites utiliser en VBA, je ne vois pas de variables. Ca me semble pour le moins étrange. Tu sembles utiliser C3 et C2 pour déterminer la cellule d'intersection de ton tableau. Dès lors, pourquoi n'utiliserais-tu pas une cellule pour le résultat, par exemple C4, avec ta formule EXCEL INDEX(...,EQUIV(...),EQUIV()))) et récupérer la valeur de C4 dans ton code? Ca t'éviterait de compliquer ton code VBA avec des formules qui sont statiques, non? (Voir ma signature "penser Excel avant de penser VBA").

[...]
Cependant cela m'a appris qu'il faut donc que je mette en place une gestion d'erreur pour que si la valeur cherchée n'existe pas il continue, alors que j'avais juste prévu de tester le contenu de la variable en retour.[...]

Tu vois donc qu'avec Evaluate, tu peux te contenter de tester la valeur de retour, pour autant que tu la pousses dans un variant

Merci Patrick,

Je mets cette séqeunce dans ma boite à outil.[...]

Je te conseille plutôt de la foutre à la poubelle au plus vite...





@Patrick: Ton niveau ne s'améliore pas, manifestement.


Le code que tu donnes pose plusieurs problèmes. Teste toujours ton code avec des valeurs qui passent et d'autres qui plantent. Ici, ton msgbox renverra toujours "No Found!", tel que tu as écrit ton code. Es-tu capable de comprendre cela?

re

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub test()
Dim Ma_Valeur As Variant
mavaleur = "No Found!"
On Error Resume Next
Ma_Valeur = Evaluate("INDEX(C51:M61,MATCH(C3,B51:B61,0);MATCH(C2,C50:M50,0))")
On Error GoTo 0
MsgBox mavaleur
End Sub

[...]

D'abord, tu n'utilises pas la déclaration explicite des variables, manifestement. Tu n'as donc toujours pas compris à quoi ça sert, sinon, tu l'aurais coché dans tes options et tu serais obligé de toujours les déclarer. Je ne comprends pas comment on peut coder et proposer du code sans cette sécurité. Ca me dépasse, depuis le temps qu'on te le dit. C'est impossible de produire du code propre et professionnel sans cela. Et ne viens pas nous dire que tu l'as enlevé après. Ca n'a aucun sens. On met cette option une fois pour toute et on s'y tient.

Ma_Valeur <> mavaleur, donc tu valorises une pomme puis une poire puis tu testes la pomme . Forcément que mavaleur renvoie "No Found!", il n'y a aucune ligne de code qui modifie cette variable après affectation (il est où ton Option Explicit qui t'aurait permis de mettre le doigt sur cette erreur?).

Tu pousses EVALUATE dans un Variant, il n'y aura donc pas de levée d'exception, et dès lors, même avec les bonnes variables, mettre Ma_Valeur à "No Found!" ne sert à rien puisque cette variable sera toujours modifiée par EVALUATE. Ce code est tout simplement inopérant. As-tu essayé de comprendre comment fonctionne EVALUATE? Sais-tu comment fonctionne la gestion d'erreurs, ce qu'elle produit, ce qu'elle utilise comme ressources? Tu continues donc à produire du code qui correspond à ce que tu as envie de démontrer, en t'arrêtant sur le premier résultat qui te convient du petit bout de ta lorgnette et tu en tires des conclusions génériques. C'est de plus en plus pitoyable.

Si tu veux vraiment jouer avec On Error, ce qui serait une ineptie dans le cas présent, tu dois utiliser une variable correspondant au type de donnée de la valeur renvoyée par Evaluate

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub TEst3()
Dim Result As Long
 
On Error Resume Next
Result = Evaluate("Index(...,Match(...))")
If Err.Number <> 0 Then
  ' Gestion de l'erreur
  On Error GoTo 0
Else
  ' On continue
End If
End Sub

Mais pour moi, la gestion d'erreurs (on devrait plutôt dire Exception) ne sert pas à cela et ne devrait pas être utilisée ici. On ne programme pas par l'erreur et On Error n'est pas là pour pallier tes nombreuses déficiences en programmation. La bonne façon de faire est d'utiliser un Variant et de tester ce qu'il a reçu avec IsError(Result). (Je sais: pensée unique).

Je rappelle encore une fois que si l'option du VBE "Arrêt sur toutes les erreurs" est cochée, les On Error seront inopérants dans un code non protégé et le code s'arrêtera donc si une erreur est rencontrée sans que le On Error y change quoi que ce soit.

[Denis_67]

Merci Pierre pour avoir pris le temps de décortiquer mes questions et d'y avoir apporté les réponses circonstanciées.

Pour ma part ça fait un bout de temps que je mets l'option explicit en tête de module. et un petit coup de compil m'indique mes absences de déclaration.

Encore merci
Denis