Formule dynamique qui affiche #NOM : formule sans erreur mais qui nécessite validation "à la main"

**dafchap** · 11/12/2019, 20h30

Bonjour,

voici mon code

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
 
 
Dim Dlmin, nbEssai As Integer
Dim CellFormuleMoyAire, CellFormuleMoyPres, CellFormuleEcartAire, CellFormuleEcartMoy As Variant
Dim tabDl() As Integer
 
nbEssai = 3
ReDim tabDl(0 To nbEssai - 1)
 
 
 
 
 
Cells(2, (nbEssai + 1) * 2 - 1) = "Moyenne"
 
Cells(2, (nbEssai + 1) * 2 - 1) = "Aire Moyenne"
Cells(2, (nbEssai + 1) * 2) = "Pression de surface moyenne"
Cells(2, (nbEssai + 1) * 2 + 1) = "Ecart Type (Aire Moyenne)"
Cells(2, (nbEssai + 1) * 2 + 2) = "Ecart Type (surface moyenne)"
 
'----------------Recherche de la dernière ligne de l'essai le plus "court"----------
 
'ceci afin de s'assurer que toutes les séries auront une valeur à soumettre à la moyenne
 
For j = 0 To nbEssai - 1
col = j * 2 + 1
    tabDl(j) = Cells(Rows.Count, col).End(xlUp).Row
Next j
 
Dlmin = tabDl(0)
 
'recherche de la "dernière ligne" la plus petite parmi les essais
For j = 0 To nbEssai - 1
    If tabDl(j) <= min Then
    Dlmin = tabDl(j)
    End If
Next j
 
'----------------Calcul des moyennes et écart types associés----------
 
For i = 3 To Dlmin
 
CellFormuleMoyAire = ""
CellFormuleMoyPres = ""
CellFormuleEcartAire = ""
CellFormuleEcartPres = ""
 
 
 
    For j = 0 To nbEssai - 1
        colA = j * 2 + 1
        colP = colA + 1
 
 
 
       CellFormuleMoyAire = CellFormuleMoyAire & "+" & Cells(i, colA)
       CellFormuleMoyPres = CellFormuleMoyPres & "+" & Cells(i, colP)
 
'remplacement des virgules par des points dans la formule sinon ça bug
    CellFormuleMoyAire = Replace(CellFormuleMoyAire, ",", ".")
    CellFormuleMoyPres = Replace(CellFormuleMoyPres, ",", ".")
 
 
    Next j
 
Cells(i, (nbEssai + 1) * 2 - 1).Formula = "=(" & CellFormuleMoyAire & ")/" & nbEssai
Cells(i, (nbEssai + 1) * 2).Formula = "=(" & CellFormuleMoyPres & ")/" & nbEssai
 
  For j = 0 To nbEssai - 1
        colA = j * 2 + 1
        colP = colA + 1
 
       'formule dynamique qui s'allonge en fonction du nombre de fichier
       'donc de colonnes à moyenner
       CellFormuleEcartAire = CellFormuleEcartAire & "+" & (Cells(i, colA) - Cells(i, nbEssai * 2 - 1)) * (Cells(i, colA) - Cells(i, nbEssai * 2 - 1))
       CellFormuleEcartPres = CellFormuleEcartPres & "+" & (Cells(i, colP) - Cells(i, nbEssai * 2)) * (Cells(i, colP) - Cells(i, nbEssai * 2))
 'remplacement des virgules par des points dans la formule sinon ça bug
    CellFormuleEcartAire = Replace(CellFormuleEcartAire, ",", ".")
    CellFormuleEcartPres = Replace(CellFormuleEcartPres, ",", ".")
 
    Next j
    Cells(i, (nbEssai + 1) * 2 + 1).Formula = "=RACINE((" & CellFormuleEcartAire & ")/" & nbEssai & ")"
Cells(i, (nbEssai + 1) * 2 + 2).Formula = "=RACINE((" & CellFormuleEcartPres & ")/" & nbEssai & ")"
 
 
Next i

Les formules CellFormuleEcartAire et CellFormuleEcartPres sont des formules dynamiques qui me permettent de calculer un écart type. La formule est à priori correcte et sans faute de syntaxe, mais toutes les cellules dans laquelle cette formule est calculée m'affiche #NOM (aucun problème avec CellFormuleMoyAire et CellFormuleMoyPres). De plus elle apparaît dans la barre de formule Excel et il suffit de la valider à la main pour que le calcul se fasse.

Mais le but n'est pas de valider cellule après cellule une formule correcte écrite en VBA, vous vous en doutez.

Aussi je me demandais s'il y avait une parade à ce bug agaçant.

Merci d'avance pour votre aide,

David

**Philippe Tulliez** · 12/12/2019, 07h06

Bonjour,
A lavue des ces deux lignes

Cells(i, (nbEssai + 1) * 2 + 1).Formula = "=RACINE((" & CellFormuleEcartAire & ")/" & nbEssai & ")"
Cells(i, (nbEssai + 1) * 2 + 2).Formula = "=RACINE((" & CellFormuleEcartPres & ")/" & nbEssai & ")"

il est tout à fait normal que le message #NOM? soit renvoyé.
La propriété Formula attend des instructions en anglais, le remplacement de RACINE par SQR sera apprécié
J'ajouterai qu'il est inutile de faire une boucle pour insérer une formule sur une plage de cellules

Lire éventuellement ces billets traitant de ce sujet

**ARTURO83** · 12/12/2019, 07h26

Bonjour,

Sinon en remplaçant ceci (le + entre guillemets).

Code :

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

1
2
       CellFormuleEcartAire = CellFormuleEcartAire & "+" & (Cells(i, colA) - Cells(i, nbEssai * 2 - 1)) * (Cells(i, colA) - Cells(i, nbEssai * 2 - 1))
       CellFormuleEcartPres = CellFormuleEcartPres & "+" & (Cells(i, colP) - Cells(i, nbEssai * 2)) * (Cells(i, colP) - Cells(i, nbEssai * 2))

par cela

Code :

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

1
2
3
 
      CellFormuleEcartAire = CellFormuleEcartAire + (Cells(i, colA) - Cells(i, nbEssai * 2 - 1)) * (Cells(i, colA) - Cells(i, nbEssai * 2 - 1))
      CellFormuleEcartPres = CellFormuleEcartPres + (Cells(i, colP) - Cells(i, nbEssai * 2)) * (Cells(i, colP) - Cells(i, nbEssai * 2))

Qu'est-ce que ça dit?

Cdlt

**dafchap** · 12/12/2019, 12h06

Apparemment ce que je suspectais et cherchais à faire est possible en écrivant :

Code :

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

1
2
3
4
Dim EcartA As Double
EcartA = Evaluate(CellFormuleEcartAire)
 
Cells(i, (nbEssai + 1) * 2 + 1) = Sqr(EcartA / nbEssai) ' du coup là j'ai remplacé mon texte à inclure dans la zone de formule avec la fonction excel RACINE par la fonction vba sqr

Une page intéressante qui donne plusieurs exemple d'utilisation de la fonction Evaluate : http://boisgontierjacques.free.fr/pa...e/evaluate.htm

**dafchap** · 12/12/2019, 12h19

Confirmation : evaluate m'a sorti de ce mauvais pas.
Merci Philippe et Arturo pour vos suggestions qui m'ont guidé vers une solution.

Voici le code complet corrigé et fonctionnel (bien qu'imparfait) au cas où ça intéressait quelqu'un :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
Sub moyenne()
 
Dim Dlmin, nbEssai As Integer
Dim CellFormuleMoyAire, CellFormuleMoyPres, CellFormuleEcartAire, CellFormuleEcartMoy As Variant
Dim tabDl() As Integer
 
nbEssai = 3
ReDim tabDl(0 To nbEssai - 1)
 
 
 
 
 
Cells(2, (nbEssai + 1) * 2 - 1) = "Moyenne"
 
Cells(2, (nbEssai + 1) * 2 - 1) = "Aire Moyenne"
Cells(2, (nbEssai + 1) * 2) = "Pression de surface moyenne"
Cells(2, (nbEssai + 1) * 2 + 1) = "Ecart Type (Aire Moyenne)"
Cells(2, (nbEssai + 1) * 2 + 2) = "Ecart Type (surface moyenne)"
 
'----------------Recherche de la dernière ligne de l'essai le plus "court"----------
 
'ceci afin de s'assurer que toutes les séries auront une valeur à soumettre à la moyenne
 
For j = 0 To nbEssai - 1
col = j * 2 + 1
    tabDl(j) = Cells(Rows.Count, col).End(xlUp).Row
Next j
 
Dlmin = tabDl(0)
 
'recherche de la "dernière ligne" la plus petite parmi les essais
For j = 0 To nbEssai - 1
    If tabDl(j) <= min Then
    Dlmin = tabDl(j)
    End If
Next j
 
 
 
For i = 3 To Dlmin
 
CellFormuleMoyAire = ""
CellFormuleMoyPres = ""
CellFormuleEcartAire = ""
CellFormuleEcartPres = ""
 
 
 
    For j = 0 To nbEssai - 1
        colA = j * 2 + 1
        colP = colA + 1
 
 
 
       CellFormuleMoyAire = CellFormuleMoyAire & "+" & Cells(i, colA)
       CellFormuleMoyPres = CellFormuleMoyPres & "+" & Cells(i, colP)
 
'remplacement des virgules par des points dans la formule sinon ça bug
    CellFormuleMoyAire = Replace(CellFormuleMoyAire, ",", ".")
    CellFormuleMoyPres = Replace(CellFormuleMoyPres, ",", ".")
 
 
    Next j
 
Cells(i, (nbEssai + 1) * 2 - 1).Formula = "=(" & CellFormuleMoyAire & ")/" & nbEssai
Cells(i, (nbEssai + 1) * 2).Formula = "=(" & CellFormuleMoyPres & ")/" & nbEssai
 
  For j = 0 To nbEssai - 1
        colA = j * 2 + 1
        colP = colA + 1
 
       'formule dynamique qui s'allonge en fonction du nombre de fichier
       'donc de colonnes à moyenner
       CellFormuleEcartAire = CellFormuleEcartAire & "+" & (Cells(i, colA) - Cells(i, (nbEssai + 1) * 2 - 1)) * (Cells(i, colA) - Cells(i, (nbEssai + 1) * 2 - 1))
       CellFormuleEcartPres = CellFormuleEcartPres & "+" & (Cells(i, colP) - Cells(i, (nbEssai + 1) * 2)) * (Cells(i, colP) - Cells(i, (nbEssai + 1) * 2))
 'remplacement des virgules par des points dans la formule sinon ça bug
    CellFormuleEcartAire = Replace(CellFormuleEcartAire, ",", ".")
    CellFormuleEcartPres = Replace(CellFormuleEcartPres, ",", ".")
 
Dim EcartA, EcartP As Double
EcartA = Evaluate(CellFormuleEcartAire) 'nécessaire pour prévalider la formule (sinon #NOM)
EcartP = Evaluate(CellFormuleEcartPres)
 
 
    Next j
 
    Cells(i, (nbEssai + 1) * 2 + 1) = Sqr(EcartA / nbEssai)
    Cells(i, (nbEssai + 1) * 2 + 2) = Sqr(EcartP / nbEssai)
 
 
Next i
 
 
End Sub

**dafchap** · 12/12/2019, 10h42

Bonjour Philippe,

Envoyé par Philippe Tulliez

il est tout à fait normal que le message #NOM? soit renvoyé.
La propriété Formula attend des instructions en anglais, le remplacement de RACINE par SQR sera apprécié

Avec SQR c'est pire. Non seulement #NOM? est toujours renvoyé, mais alors qu'avec RACINE la cellule prenait en compte la formule une fois validée par un clic dans la cellule + un clic dans la zone de formule + un Entrée, avec SQR aucune de ces actions n'affiche le résultat.

Envoyé par Philippe Tulliez

J'ajouterai qu'il est inutile de faire une boucle pour insérer une formule sur une plage de cellules

Merci du conseil. Je me rends compte qu'en effet je me suis compliqué les choses.

Envoyé par Philippe Tulliez

Lire éventuellement ces billets traitant de ce sujet

Merci. J'avais parcouru vos billets avant de poser ma question sans trouver la réponse, mais votre conception des formules dynamiques est sans doute moins brouillonne que la mienne.

Envoyé par ARTURO83

Bonjour Arturo,

Malheureusement ça bug pour toutes les formules en enlevant les guillemets. Je pense que de la façon dont j'ai écrit la formule il est important qu'Excel la voit comme une chaine de caractère que j'aurais rempli à la main dans la zone de formule d'Excel.

Et le problème lié à RACINE et encore plus à SQR est sans doute qu'excel ne les lit pas comme des chaines de caractères. En fait il faut peut-être que je trouve un moyen pour faire passer le "radicande"de chaine de caractère à double.

Formule dynamique qui affiche #NOM : formule sans erreur mais qui nécessite validation "à la main" [XL-2010]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager