Discussion :

[Dédé6621]

Bonjour,

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

Code :

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

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

De même sans le signe égal:

Code :

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

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

Comment tester si une formule renverrait une erreur dans Excel?

Cordialement

[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

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 :

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

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

tu saura pourquoi ca te donne faux

Code :

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

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

et surtout tu saura pourquoi ca te renvoie #VALEUR!

[Marc-L]

Bonjour !

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, …

[Dédé6621]

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

Le résultat de

Code :

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

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

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?

[Dédé6621]

Bonjour Marc!

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?

[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

[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!

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

[Marc-L]

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 …

___________________________________________________________________________________________________________

     Merci de cliquer sur en bas à droite de chaque message ayant aidé puis sur pour clore cette discussion …

[Dédé6621]

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

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

[eriiic]

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 :

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

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 :

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

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

[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 …

[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

[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 :

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

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

[Dédé6621]

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

Code :

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

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

donne bien "Vrai".

Mais:

Code :

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

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

donne "Faux".

Il faut doubler Evaluate pour que ça fonctionne:

Code :

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

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.

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

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

[Dédé6621]

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.

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. #7). Merci pour les explications.