IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Evaluate - Tester si une formule renverrait une erreur Excel [XL-2010]


Sujet :

Macros et VBA Excel

  1. #1
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut 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 : 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

  2. #2
    Inactif  

    Homme Profil pro
    cuisiniste
    Inscrit en
    avril 2009
    Messages
    15 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : cuisiniste
    Secteur : Bâtiment

    Informations forums :
    Inscription : avril 2009
    Messages : 15 379
    Points : 12 513
    Points
    12 513
    Billets dans le blog
    8
    Par défaut 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 : 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!
    mes fichiers dans les contributions:
    mail avec CDO en vba et mail avec CDO en vbs dans un HTA
    survol des bouton dans userform
    prendre un cliché d'un range

    si ton problème est résolu n'oublie pas de pointer : : ça peut servir aux autres
    et n'oublie pas de voter

  3. #3
    Expert éminent sénior
    Avatar de Marc-L
    Homme Profil pro
    Développeur informatique
    Inscrit en
    avril 2013
    Messages
    9 468
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : avril 2013
    Messages : 9 468
    Points : 18 773
    Points
    18 773
    Par défaut
    Bonjour !

    Citation Envoyé par Dédé6621 Voir le message
    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, …
    C'est parce que la vitesse de la lumière est plus rapide que celle du son que tant de gens paressent brillants avant d'avoir l'air con ! (Thomas Boishardy)

  4. #4
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    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 ).

    Citation Envoyé par patricktoulon Voir le message
    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?

  5. #5
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    Bonjour Marc!

    Citation Envoyé par Marc-L Voir le message
    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?

  6. #6
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    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

  7. #7
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    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.

  8. #8
    Expert éminent sénior
    Avatar de Marc-L
    Homme Profil pro
    Développeur informatique
    Inscrit en
    avril 2013
    Messages
    9 468
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : avril 2013
    Messages : 9 468
    Points : 18 773
    Points
    18 773
    Par défaut
    Citation Envoyé par Dédé6621 Voir le message
    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 …
    C'est parce que la vitesse de la lumière est plus rapide que celle du son que tant de gens paressent brillants avant d'avoir l'air con ! (Thomas Boishardy)

  9. #9
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    Citation Envoyé par Marc-L Voir le message
    [...] et là,
    pas d'autre choix de tester le résultat dans une cellule …
    Bon. J'ai ma réponse. Merci!

  10. #10
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    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

  11. #11
    Expert éminent sénior
    Avatar de Marc-L
    Homme Profil pro
    Développeur informatique
    Inscrit en
    avril 2013
    Messages
    9 468
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : avril 2013
    Messages : 9 468
    Points : 18 773
    Points
    18 773
    Par défaut

    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 …
    C'est parce que la vitesse de la lumière est plus rapide que celle du son que tant de gens paressent brillants avant d'avoir l'air con ! (Thomas Boishardy)

  12. #12
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    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

  13. #13
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    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

  14. #14
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut Précision
    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.

  15. #15
    Membre expert
    Profil pro
    Inscrit en
    février 2007
    Messages
    2 263
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2007
    Messages : 2 263
    Points : 3 804
    Points
    3 804
    Par défaut
    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

  16. #16
    Membre régulier
    Homme Profil pro
    Finance d'entreprise
    Inscrit en
    juin 2016
    Messages
    151
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Finance d'entreprise
    Secteur : Bâtiment

    Informations forums :
    Inscription : juin 2016
    Messages : 151
    Points : 77
    Points
    77
    Par défaut
    Citation Envoyé par eriiic Voir le message
    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 Voir le message
    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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [Toutes versions] Imbriquer une formule dans une formule à l'aide de l'outil Insertion de fonction
    Par Philippe Tulliez dans le forum Contribuez
    Réponses: 0
    Dernier message: 27/08/2014, 11h21
  2. [XL-2007] Savoir si une cellule est une formule ou une valeur
    Par Syvolc dans le forum Excel
    Réponses: 3
    Dernier message: 23/06/2014, 14h50
  3. [XL-2010] Formule : attribuer une formule dans une cellule
    Par popi33370 dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 06/03/2014, 18h06
  4. [XL-2007] Afficher une checkbox dans une feuille si une checkbox d'une autre feuille est cochée
    Par JessieCoutas dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 18/08/2009, 13h35
  5. Recherche une valeur d'une cellule dans une colonne d'une autre feuille
    Par kourria dans le forum Macros et VBA Excel
    Réponses: 8
    Dernier message: 21/06/2007, 13h48

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo