Evaluate - Tester si une formule renverrait une erreur Excel

Version imprimable

11/04/2018, 15h00
Dédé6621

Evaluate - Tester si une formule renverrait une erreur Excel

Bonjour,

Je n'arrive pas à saisir pourquoi ce code me renvoie "Faux" alors la formule provoque une erreur #VALEUR! dans Excel:

Code:

MsgBox IsError(Evaluate("=D70:N70"))

De même sans le signe égal:

Code:

MsgBox IsError(Evaluate("D70:N70"))

Comment tester si une formule renverrait une erreur dans Excel?

Cordialement
11/04/2018, 15h43
patricktoulon

re

une formule renvoie son resultat lors de l'evenement calculate d'un sheet forcement tu aura toujour faux en l'etat tu test un string

il te faut faire l'operation en vba pur

pour etre encore plus precis il est bien pratique de regarder dans les variables local ce que renvoie tes vazriables
ou tester en vba
comme suit
quand tu aura vu le resulat de ca

Code:

MsgBox TypeName((Evaluate("=D70:N70")))

tu saura pourquoi ca te donne faux

Code:

MsgBox IsError(Evaluate("=D70:N70"))

et surtout tu saura pourquoi ca te renvoie #VALEUR!
;)
11/04/2018, 18h03
Marc-L

Bonjour !

Citation:

Envoyé par Dédé6621

Je n'arrive pas à saisir pourquoi ce code me renvoie "Faux" alors la formule provoque une erreur #VALEUR! dans Excel

C'est normal car ce n'est pas toujours tout à fait la même chose entre une cellule et une évaluation
comme tu peux déjà le voir toi-même côté VBE dans la fenêtre Variables locales après avoir exécuté

V = Evaluate("=D70:N70") que constates-tu donc concernant le contenu de cette variable V ?

Évidemment il est bien plus simple de tester le résultat d'une cellule contenant une formule …

___________________________________________________________________________________________________________
Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …
11/04/2018, 18h08
Dédé6621

Merci pour ta réponse Patrick, même si j'avoue avoir du mal à comprendre.

Le résultat de

Code:

MsgBox TypeName((Evaluate("=D70:N70")))

est Range.

Personnellement, je me serais attendu à avoir un type Error (oui, je sais pourquoi "=D70:N70" renvoie VALEUR!, merci ;)).

Citation:

Envoyé par patricktoulon

une formule renvoie son resultat lors de l'evenement calculate d'un sheet forcement tu aura toujour faux en l'etat tu test un string

il te faut faire l'operation en vba pur

C'est ce que j'essaie de faire. Je veux tester la formule dans VBA avant de la mettre dans une cellule.

En fait, j'aimerais pouvoir facilement enlever des fonctions SOMME quand elles ne sont pas nécessaires, mais les conserver lorsqu'elles le sont. Ainsi, j'aimerais conserver "=SOMME(D70:N70)", mais transformer "=SOMME(D70)" en "=D70. Seulement voilà, je n'aimerais pas non plus avoir "=D70:N70". D'où le test. :)

Une idée?
11/04/2018, 18h23
Dédé6621

Bonjour Marc!

Citation:

Envoyé par Marc-L

V = Evaluate("=D70:N70") que constates-tu donc concernant le contenu de cette variable V ?

V devient une variable tableau Variant à deux dimensions, la première se rapportant à la ligne 70 (1 to 1) et la deuxième se rapportant aux 11 colonne de D à N (1 to 11) (je suis en base 1). Intéressant, merci!

Mais du coup, n'y a-t-il pas un moyen de tester si une formule entraînerait une erreur dans Excel sans passer par Excel?
11/04/2018, 18h26
eriiic

Bonjour,

tu as pris un mauvais exemple avec =D70:N70
Met cette formule sur feuille, puis le curseur dans l'édition des formules en haut et fais F9 pour l'évaluer.
Tu obtiens {0\0\0\0\0\0\0\0\0\0\0}
Cette formule n'est pas en erreur, c'est juste qu'excel ne peux pas afficher une matrice dans une cellule.
Fais tes tests avec =1/0, là tu la verras l'erreur, même en vba.
eric
11/04/2018, 18h45
Dédé6621

Bonjour Eric,

C'est vrai que c'est bizarre. Je n'avais jamais vu ce type de formule renvoyer autre chose qu'une erreur. Mais dans une feuille vide, avec "=D70:N70", j'obtiens 0, et 1 si la plage est remplie de 1. J'écris "="F6:H6" dans la cellule à côté et j'obtiens #VALEUR! 8O

... sauf que je n'ai pas choisi "=D70:N70", c'est un cas pratique :?

PS: j'ai bien fait les tests indiqués, mais pour moi, #VALEUR! est bien une erreur Excel à éviter, même si on pourrait représenter le contenu de la formule dans une matrice.
11/04/2018, 20h14
Marc-L

Citation:

Envoyé par Dédé6621

Mais dans une feuille vide, avec "=D70:N70", j'obtiens 0, et 1 si la plage est remplie de 1.

De mon côté j'obtiens l'erreur dans les deux cas, plage vide ou remplie !

La méthode Evaluate - non expliqué dans sa documentation - peut agir comme une formule matricielle
expliquant ainsi la différence parfois possible avec une simple formule et là,
pas d'autre choix de tester le résultat dans une cellule …

___________________________________________________________________________________________________________

:arrow: Merci de cliquer sur :plusser: en bas à droite de chaque message ayant aidé puis sur :resolu: pour clore cette discussion …
11/04/2018, 20h51
Dédé6621

Citation:

Envoyé par Marc-L

[...] et là,
pas d'autre choix de tester le résultat dans une cellule …

Bon. J'ai ma réponse. Merci!

Non, on peut tester l'erreur d'un Evaluate() sans mettre la formule dans une cellule. La variable contient le numéro de l'erreur.

Mais, je m'excuse d'insister, il faut différencier un #VALEUR! dû à une matrice (ce qui n'est pas une erreur mais une impossibilité d'afficher le résultat), d'un vrai #VALEUR!
Cette matrice peut être totalement exempte d'erreur, ou bien en contenir une ou plusieurs ( et peut-être aucun #VALEUR!).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Sub test()
    Dim result, ar, f As String, msg As String, i As Long, j As Long, nbErr As Long
    ar = Array("=B2:B3", "=B2:B3", "=A1", "=1/0")
    [B2].ClearContents
    For i = 0 To UBound(ar)
        f = ar(i)
        result = Evaluate(f)
        msg = "": nbErr = 0
        If IsArray(result) Then
            For j = 1 To UBound(result) ' je considère que le tableau ne fait qu'une dimension
                If IsError(result(j, 1)) Then nbErr = nbErr + 1
            Next j
            msg = "la formule """ & f & """ retourne une matrice" & vbLf
            msg = msg & "et contient " & nbErr & " erreur(s)"
            If nbErr = 0 Then msg = msg & " !!!" & vbLf & "MEME SI ELLE AFFICHE #VALEUR!"
        ElseIf IsError(result) Then
            msg = "la formule """ & f & """ est en erreur " & libelErr(result)
        Else
            msg = "la formule """ & f & """ est correcte"
        End If
        MsgBox msg
        [B2].Formula = "=32/0"
    Next i
End Sub
 
Private Function libelErr(erreur) As String
    Select Case erreur
    Case CVErr(xlErrDiv0)
        libelErr = "#DIV/0!"
    Case CVErr(xlErrNA)
        libelErr = "#N/A"
    Case CVErr(xlErrName)
        libelErr = "#NOM?"
    Case CVErr(xlErrNull)
        libelErr = "#NULL!"
    Case CVErr(xlErrNum)
        libelErr = "#NOMBRE!"
    Case CVErr(xlErrRef)
        libelErr = "#REF!"
    Case CVErr(xlErrValue)
        libelErr = "#VALEUR!"
    End Select
End Function

En résumé :

Code:

1
2
3
4
If IsArray(formule)=true : 
     on aura toujours #VALEUR!. En toute rigueur il faut balayer la matrice pour savoir s'il y a réellement des erreurs.
sinon If IsError(formule)= true :  
     la formule est en erreur.

J'ajouterai qu'en plus il y a des cas où #VALEUR! ne s'affiche pas bien que ce soit une formule qui retourne une matrice.
Par curiosité après le test ci-dessus mettre =B2:B3 dans une cellule. On obtient 0 et non #VALEUR!, pourtant c'est bien une matrice. Vider B3 pour supprimer l'erreur #DIV/0! , on retrouve le #VALEUR!.
Une raison de plus pour utiliser IsArray() pour connaitre le type de résultat.
eric

12/04/2018, 00h32
Marc-L

Eric,

dans le cas d'une feuille vide (cf post #7), pas d'erreur via Evaluate même s'il y en a une dans une cellule via la formule
donc dans ce cas pas d'autre choix de vérifier directement le résultat de la cellule …
12/04/2018, 01h29
eriiic

Je répète : #VALEUR! dans ce cas n'est pas forcément une erreur.
C'est dû à un résultat qui retourne une matrice, qui se teste avec IsArray() s'il veut exclure ce cas.

Et il aura la bonne réponse. qu'il y ait le #VALEUR! ou pas dans la cellule. Dans certains cas on ne l'a pas, avec pourtant une matrice en retour.
eric
12/04/2018, 09h09
Dédé6621
Rebonjour,

Pour la petite histoire, voici le bricolage que j'ai mis en place pour tester s'il s'agit d'une plage:
Code:

1 2 3 Dim txt as String 'la formule à tester txt="=A1:B1" If Evaluate("=ISERROR(FIND("":"",""" & txt & """))") = "Vrai" Then ...
Mais le IsArray d'Eric me paraît plus académique ;)
Je l'adopte sans hésitation! Merci Eric.

Quand au débat de savoir si #VALEUR! provoqué par une plage est une erreur ou pas, il me semble que par définition, il s'agit d'une erreur dans Excel (https://support.office.com/fr-fr/art...B-0A11A20E409E), même si Evaluate renvoie une variable tableau dans VBA. Ça dépend de l'environnement dans lequel on se situe.

Cordialement
12/04/2018, 09h37
Dédé6621

Précision
J'aurais dû tester le code jusqu'au bout.

Code:

v = IsArray(Evaluate("=A1:B1"))

donne bien "Vrai".

Mais:
Code:

1 2 txt="=A1:B1" v = IsArray(Evaluate("""" & txt & """"))
donne "Faux".

Il faut doubler Evaluate pour que ça fonctionne:
Code:

1 2 txt="=A1:B1" v = IsArray(Evaluate(Evaluate("""" & txt & """")))
Une première fois pour obtenir l'expression de la formule, une deuxième fois pour obtenir la fameuse variable tableau.
12/04/2018, 10h28
eriiic

Pourquoi ajouter des """" ?
txt étant une chaine contenant la formule Evaluate(txt) suffit.

v = IsArray(Evaluate("""" & txt & """"))
donne "Faux" ne parait pas anormal s'il ne voit plus une formule mais une chaine comme "bonjour" avec les """" ajoutés.

Citation:

Quand au débat de savoir si #VALEUR! provoqué par une plage est une erreur ou pas, il me semble que par définition, il s'agit d'une erreur dans Excel (https://support.office.com/fr-fr/art...B-0A11A20E409E), même si Evaluate renvoie une variable tableau dans VBA. Ça dépend de l'environnement dans lequel on se situe.

ce n'est pas parce que ce cas n'est pas listé ici que ça invalide ce que j'ai dit.
Fait simplement le test d'évaluer la formule dans la barre d'édition avec F9 et tu verras enfin la matrice, avec ou sans valeurs d'erreur.
Comment veux-tu qu'excel affiche une matrice 1000x1000 cellules dans une seule ? C'est impossible et il le signale par #VALEUR! c'est tout.
eric
18/04/2018, 15h25
Dédé6621

Citation:

Envoyé par eriiic

Pourquoi ajouter des """" ?
txt étant une chaine contenant la formule Evaluate(txt) suffit.

v = IsArray(Evaluate("""" & txt & """"))
donne "Faux" ne parait pas anormal s'il ne voit plus une formule mais une chaine comme "bonjour" avec les """" ajoutés.

Juste. Je te remercie Eric.

Citation:

Envoyé par eriiic

ce n'est pas parce que ce cas n'est pas listé ici que ça invalide ce que j'ai dit.
Fait simplement le test d'évaluer la formule dans la barre d'édition avec F9 et tu verras enfin la matrice, avec ou sans valeurs d'erreur.
Comment veux-tu qu'excel affiche une matrice 1000x1000 cellules dans une seule ? C'est impossible et il le signale par #VALEUR! c'est tout.
eric

J'ai bien fait l'essai (cf. ). Merci pour les explications.