Discussion :

[RyuAutodidacte]

Re Patrick,

C'est pour cela que le post 2 de Thautheme m'a aiguillé car SpecialCells(xlCellTypeFormulas) est, si on peut dire, d'ordre général/prenant en compte un ensemble
=> d'où mon Intersect <=

Si il n'y a pas de formule dans une feuille la gestion d'erreur est quasi obligatoire …

Tests :

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
Sub PourPatrick()
    'Si tu veux vérifier une cellule et seulement une
    MsgBox EstFormule([A2])
    MsgBox EstFormule([B1])
    'Si tu veux vérifier si dans une plage et seulement dans cette plage si il existe au moins une formule (il peut y en avoir plus)
    MsgBox EstFormule([A2:B2])
    MsgBox EstFormule([A1:B1])
End Sub
 
Function EstFormule(F As Range) As Boolean
    On Error Resume Next
        EstFormule = Not Application.Intersect(F, F.SpecialCells(xlCellTypeFormulas)) Is Nothing
        If Err Then EstFormule = False
    On Error GoTo 0
End Function

Edit : on s'est croisé eriic

[eriiic]

décroisons-nous tout de suite avant que ça jase ;-)

[RyuAutodidacte]

décroisons-nous tout de suite avant que ça jase ;-)

eriic, en effet on peut simplifier :

Code :

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

1
2
3
4
5
6
Function EstFormule(F As Range) As Boolean
    On Error Resume Next
        EstFormule = Not Application.Intersect(F, F.SpecialCells(xlCellTypeFormulas)) Is Nothing
        'If Err Then EstFormule = False
    'On Error GoTo 0
End Function

Patrick (en se mettant sur une autre feuille que la 3)

Code :

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

1
2
3
4
5
6
7
8
Sub PourPatrick2()
    'Si tu veux vérifier une cellule et seulement une
    MsgBox EstFormule(Feuil3.[A2])
    MsgBox EstFormule(Feuil3.[B1])
    'Si tu veux vérifier si dans une plage et seulement dans cette plage si il existe au moins une formule
    MsgBox EstFormule(Feuil3.[A2:B2])
    MsgBox EstFormule(Feuil3.[A1:B1])
End Sub

[patricktoulon]

@eriic pas rigoureux de quoi?,
perso quand je fait

Code :

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

MsgBox [b2].SpecialCells(xlCellTypeFormulas).Address

et qu'il me donne la/les cellules de la/les colonnes a coté c'est mort de chez mort

alors qu'il n'y a qu'une cellule en parametre donc qu'une colonne
alors peu etre comme tu dis ou meme peut etre un unparia
il est possible qu'il est prevu une extention sur le contigues mais perso j'en doute car ca remet la raison d'etre de la fonction

dans cette exercice on teste UNE!! cellule

resultat tu aura faire tout les stratageme que tu veux a partir du moment ou la fonction s'en va dans les contigues tu ne peux lui faire confiance( il y a tellement de cas de figure)

il existe la fonction "hasformula" il m'est impossible de croire que cette truc etrange soit le fruit d'un stratageme du developpeur c'est absurde

je vais tester le truc de ryu voir dans des cas de figure que j'ai concocté

[patricktoulon]

re
désolé ryu au 1 er test simple elle pas passe pas elle me donne vrai en 4 eme msgbox alors que c'est faux
mais c'est pas l'exercice ici
l'exercice c'est une cellule pas 2 ou plus


j'ai modifier ton code pour afficher quel test etait fait c'est tout

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function EstFormule(F As Range) As Boolean
    On Error Resume Next
        EstFormule = Not Application.Intersect(F, F.SpecialCells(xlCellTypeFormulas)) Is Nothing
        'If Err Then EstFormule = False
    'On Error GoTo 0
End Function
Sub PourPatrick2()
    'Si tu veux vérifier une cellule et seulement une
    MsgBox "[A2]" & EstFormule(Feuil1.[A2])
    MsgBox "[B1]" & EstFormule(Feuil1.[B1])
    'Si tu veux vérifier si dans une plage et seulement dans cette plage si il existe au moins une formule
    MsgBox "[A2:B2]" & EstFormule(Feuil1.[A2:B2])
    MsgBox "[A1:B1]" & EstFormule(Feuil1.[A1:B1])
End Sub

c'est mort cherchez pas
meme ce truc a gaume marche mieux attention un seul asterisque a la fin le = doit etre le premier caractere

Code :

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

1
2
3
Sub test()
MsgBox [B1].Formula Like "=*"
End Sub

encore un truc bien tordu comme je les aimes
mais la il faut se rendre a l'évidence pour une cellule specialcells pas fiable

[patricktoulon]

encore mieux ryu
je relance ton code sans rien changer et pour le coup la j'ai tout faux

[eriiic]

Je laisse tomber.
Tu ne lis que ce que tu veux bien lire, et quand tu lis c'est en diagonale sans même essayer d'analyser et de comprendre ce que tu lis.

SPECIALCELLS N'EST VALABLE QUE POUR UNE PLAGE DE PLUSIEURS CELLULES.
UN APPEL DE FONCTION EN VBA N'EST PAS EQUIVALENT A UN APPEL A PARTIR D'UNE FEUILLE.
C'est bon, tu le vois là ?
Sur ce, j'en ai fini.
Good night
eric

[patricktoulon]

re
je comprends ryu ta demarche avec deux cellule mais c'est une mauvaise idée

si tu test A1:B1 pour tester B1 et qu'il y a une formule en A1 la reponse sera "vrai"

vous pouvez tourner ca dans tout les sens c'est pas bon

[RyuAutodidacte]

Re Patrick,

• Post #25 - le résultat que l'on doit avoir

MsgBox "[A2]" & EstFormule(Feuil1.[A2]) Doit être Faux
MsgBox "[B1]" & EstFormule(Feuil1.[B1]) Doit être Vrai
'Si tu veux vérifier si dans une plage et seulement dans cette plage si il existe au moins une formule …
MsgBox "[A2:B2]" & EstFormule(Feuil1.[A2:B2]) Doit être Faux
MsgBox "[A1:B1]" & EstFormule(Feuil1.[A1:B1]) Doit être Vrai

• Post #26 fais attention il peut y avoir des codenames différent entre PC et Mac
en tout cas moi j'ai pas eu le souci

[patricktoulon]

ne crois pas ca eric loin de la
pour te le prouver je teste avec la fonction de ryu appler depuis l'evenement change du sheets
je veut savoir si b1 est une formule ok
bon l'evenement

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox "[A1:B1]" & EstFormule(Feuil1.[A1:B1])
End Sub

la fonction de ryu dans un module standard

Code :

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

1
2
3
4
5
6
Function EstFormule(F As Range) As Boolean
    On Error Resume Next
        EstFormule = Not Application.Intersect(F, F.SpecialCells(xlCellTypeFormulas)) Is Nothing
        'If Err Then EstFormule = False
    'On Error GoTo 0
End Function

demo


voila maintenant dites moi a quoi a servi ce test ???

ben je peux vous le dire "a rien!!!"
et pour ta gouverne eric j'avais fait deja les test a partir du sheets bien avant que tu en parle pour avoir la reponse tout de suite et ca n'a aucune incidence positive ou negative en ce qui concerne le resultat du moins chez moi avec excel 2007

[eriiic]

Mais si il y a une différence entre un appel en vba et un appel sur feuille.
Refait ton test en appelant la fonction en vba :

Code :

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

1
2
    MsgBox hasformule([a1])
    MsgBox hasformule([a2])

Moi je lis VRAI puis FAUX, ce qui est correct...
eric

[patricktoulon]

j'ai trouvé
vous etiez pas loin avec votre intersect pour une cellule
en fait il faut tester la celulle resizée a 3 sur 3 comme ca on est sur
mais le intersect il faut le faire entre le resultat specialcell et la cellule voulue
je vous laisse en faire une fonction
j'ai fait tout les tests ca match

Code :

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

1
2
3
4
5
6
Sub test()
'cellule testé B1
Set range_a_examiner = [b1].Offset(0, -1).Resize(3, 3)
MsgBox range_a_examiner.SpecialCells(xlCellTypeFormulas).Address
MsgBox Not Application.Intersect(range_a_examiner.SpecialCells(xlCellTypeFormulas), Range("B1")) Is Nothing
End Sub

et ben dis donc

[pijaku]

Bonjour,

Bien trop tard, comme souvent...

Pourquoi vouloir s'obstiner avec Specialcells et autre Intersect alors que l'on dispose d'une propriété de cellule?

Cette propriété, comme le souligne Patrick, peux obtenir trois états : True, False et Null.
On ne peut donc pas créer une fonction booléenne pour en retourner le résultat, si l'on souhaite obtenir le vrai résultat.
Il convient donc de faire une fonction de type Variant et de traiter le cas ou hasFormula est égal à Null.
Cela me semble plus sage et plus simple :

Code :

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

1
2
3
4
5
6
7
Function IsFormula(C As Range) As Variant
   If IsNull(C.HasFormula) Then
      IsFormula = "Case Else" 'ou "Null" ou autre chose...
   Else
      IsFormula = C.HasFormula
   End If
End Function

Et si l'on tient vraiment à une fonction Booléenne :

Code :

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

1
2
3
4
5
6
7
Function IsFormula(C As Range) As Boolean
   If IsNull(C.HasFormula) Then
      IsFormula = False
   Else
      IsFormula = C.HasFormula
   End If
End Function

Ceci fonctionne aussi bien depuis une feuille :
exemple en A1 inscrire : =IsFormula(B1:B3)
que depuis VBA :

Code :

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

1
2
3
Sub Essai()
   MsgBox IsFormula(Range("B1:B3"))
End Sub

[halaster08]

Cette propriété, comme le souligne Patrick, peux obtenir trois états : True, False et Null.

Bonjour,

Je ne comprends pas dans quel cas la propriété peux valoir Null, peux tu m'expliquer ? ou m'indiquer le post de Patrick qui en parle car il me semble qu'il testait le Specialcells et non le hasformula

[pijaku]

Bonjour,

C'est très simple. Un Range peut être composé d'une ou de plusieurs cellules.
Si plusieurs, on comprend bien que l'on puisse obtenir trois états :

1. Toutes les cellules du Range contiennent une formule ==> True,
2. Aucune cellule du Range ne contient de formule ==> False,
3. Au moins une cellule du Range ne contient pas de formule alors que d'autre(s) oui ==> Null.

Il est sur que si tu ne testes que sur des Range composés d'une unique cellule, il ne pourra y avoir que deux états, True et False.

[eriiic]

Bonjour à tous,

mais le intersect il faut le faire entre le resultat specialcell et la cellule voulue

Merci pour ce truisme :-)

Pijaku, je pense que tout le monde l'avait en tête. Je l'avais même mis au post #10
eric

[pijaku]

Bonjour Eric,

Pijaku, je pense que tout le monde l'avait en tête. Je l'avais même mis au post #10

En effet, tout comme Patrick, mais sans tester le troisième état, ce que je voulais souligner à Halaster08.

[eriiic]

Exact, je ne me suis même posé la question étant resté dans le titre du sujet :

Tester la présence d'une formule dans une cellule

eric

[patricktoulon]

re

bonjour a tous
oui hasformula fonctionne tres bien c'est juste pour l'exercice le specialcells qui etait interessant
mais tester une cellule avec intersect n'est pas fiable dans le sens ou les address du specialcells donne des resultats surprenant

c'est pour ca que l'idée est bonne du intersect mais sur une plage elargie avec pour base la cellule concernée et le test intersect doit se faire entre le resultat de specialcells et la cells en question


mais tester un intersect entre une plage elargie et cette meme plage triée par specialcell donne vrai si il y a formule mais ne cible pas vraiment la cellule

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub test()
MsgBox isformule([b1])
End Sub
'
'
 
Function isformule(ByRef cel As Range)
Set range_a_examiner = cel.Offset(0, -1).Resize(3, 3)
MsgBox range_a_examiner.SpecialCells(xlCellTypeFormulas).Address
'MsgBox Not Application.Intersect(range_a_examiner.SpecialCells(xlCellTypeFormulas), Range("B1")) Is Nothing
isformule = Not Application.Intersect(range_a_examiner.SpecialCells(xlCellTypeFormulas), cel) Is Nothing
End Function

parcontre je viens de tester si j'ai pas de formule dans la plage elargie j'ai quand meme le message '"pas de cellules correspondante"

[Menhir]

c'est juste pour l'exercice le specialcells qui etait interessant

Le problème, c'est que la méthode SpecialCells est une méthode indisciplinée qui ne se limite pas forcément à la zone qu'on lui assigne.

Un exemple simple.
Mettre "1" en A1 et "=A1" en A2.
Lancer la macro :

Code :

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

1
2
3
Sub test()
Range("A1").SpecialCells(xlCellTypeFormulas).Select
End Sub

C'est A2 qui est sélectionné, malgré le fait qu'on l'ai appliquée uniquement à A1.

Partant de ce constat, il est difficile d'imaginer qu'on puisse utiliser cette méthode dans le cas présent.
Même à supposer qu'on puisse exploiter le résultat obtenu avec On Error ou autre, de toute façon ce résultat n'est pas fiable.
CQFD