Résultats calculs non mis à jour (phénomène paranormal !!!)

Le 10/12/2015 à 22:31

Bonjour à tous,

Donc là, je tiens un bon phénomène paranormal d'excel destiné aux experts!

Je vais essayer d'expliquer précisément mais rapidement car je viens déjà de rédiger un long post à ce sujet qui a disparu au moment de le publier... Bref...

J'ai un gros fichier excel avec des formules et des macros dans tous les sens.

Je travaille en mode de calcul manuel.

Evidemment, certaines cellules sont dépendantes les unes des autres, c'est à dire que comme un parcours de dominos, le résultat de l'une est repris consécutivement dans une chaine de cellules : A -> B -> C -> D -> E

Depuis 2 jours j'ai remarqué ponctuellement qu'en modifiant la formule de A (dans mon exemple ci-dessus), la valeur dans B restait l'ancienne valeur de A et n'était pas mise à jour malgré mes tentatives de recalcul. Par voie de conséquences, toutes les cellules dépendantes de B affichent donc un résultat erronné.

Il me semble que la première fois que ça s'est produit, cela concernait la même chaîne de cellules qu'aujourd'hui, donc c'est peut être un problème lié à tous les classeurs, mais peut-être aussi seulement à certains feuillets, voire à certaines cellules (?).

Même en passant le fichier en mode de calcul automatique, cela ne changeait rien.

J'ai rebooté excel plusieurs fois sans résultat. J'ai même rebooté le PC...

En cherchant sur internet, j'ai trouvé un article qui dit :

"Cliquer sur F9 lance le recaclul des classeurs ouverts. Parfois, cela est inopérant alors que la validation manuelle de chaque cellule provoque bien le recalcul."

C'est exactement ce que je constate dans mon fichier.

Je ne comprends pas pourquoi, et surtout, je voudrais résoudre ce problème pour éviter que cela ne se reproduise sans que je m'en rende compte (parce que là, ça fait quand même 3 fois en 2 jours...).

J'ai suivi les conseils de l'article ci-dessus : la combinaison ctrl+alt+F9 force dans ce cas le recalcul.

OK, ponctuellement, ça permet d'obtenir le bon résultat final, mais ça ne règle pas le problème sur le long terme.

J'ai aussi supprimé de mes macros les :

Application.ScreenUpdating = False
Application.Calculate = False
Application.EnableEvents = False

Peut-être ai-je mal utilisé ces expressions (?)

Toutefois, la suppression de celles-ci ne résout pas le problème.

Quelqu'un a-t-il déjà expérimenté cela?

Comment m'assurer que cela ne se reproduise pas?

Merci de votre aide.

PS : Evidemment, si je ne partage pas le dit fichier, c'est parce que je n'en ai pas le droit.

Par ailleurs, sur cet autre forum je peux lire que les Macros événementielles peuvent provoquer des dysfonctionnements.

Or, parmi les très nombreuses macros de mon fichier, j'ai :

Private Sub Worksheet_Calculate()
ThisWorkbook.Activate
Select Case Range("D206").Value
    Case Is = "A"
        ThisWorkbook.Sheets("AHP").Activate
        ActiveSheet.Range("b23").Select
    Case Is = "B"
        ThisWorkbook.Sheets("AHP").Activate
        ActiveSheet.Range("h23").Select
    Case Is = "C"
        ThisWorkbook.Sheets("AHP").Activate
        ActiveSheet.Range("n23").Select
    Case Is = "D"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("b23").Select
    Case Is = "E"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("h23").Select
    Case Is = "F"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("n23").Select
    Case Is = "G"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("t23").Select
    Case Is = "H"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("Z23").Select
    Case Is = "I"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("AF23").Select
    Case Is = "J"
        ThisWorkbook.Sheets("AHF").Activate
        ActiveSheet.Range("AL23").Select
    Case Is = "K"
        ThisWorkbook.Sheets("AHT").Activate
        ActiveSheet.Range("B23").Select
    Case Is = "L"
        ThisWorkbook.Sheets("AHT").Activate
        ActiveSheet.Range("H23").Select
    Case Is = "M"
        ThisWorkbook.Sheets("AHR").Activate
        ActiveSheet.Range("B23").Select
    Case Is = "N"
        ThisWorkbook.Sheets("AHR").Activate
        ActiveSheet.Range("H23").Select
    Case Is = "O"
        ThisWorkbook.Sheets("AHR").Activate
        ActiveSheet.Range("n23").Select
    Case Is = "P"
        ThisWorkbook.Sheets("AHP").Activate
        ActiveSheet.Range("t23").Select

End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$14" Then
If Target <> valeur Then
 Auto_New 'ta macro
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$14" Then valeur = Target
End Sub

Global valeur

Je ne sais pas si ces macros peuvent être gênantes?

Le 11/12/2015 à 03:43

Bonjour Nicopat,

...alors que la validation manuelle de chaque cellule provoque bien le recalcul."

Ça ressemblerait à des symptômes de calcul itératif... mais sans fichier, 'faut d'viner.

à vérifier dans les "Options", "Formules" ou dans les lignes de macros

qui bouclent des calculs sur une ou plusieurs cellules.

Dans ce cas, la valeur n'est modifiée que par la logique de macro.

à tester, autrement, avec SUPPR sur une autre cellule quelconque, même vide.

S'il y a réaction dans ce cas, c'est très probablement cette origine.

Dans tous les cas, que la case "Calcul Itératif" soit cochée (ou non) pour les besoins du concept,

je te souhaite bien du courage pour trouver un remède efficace sans effet secondaire.

Bonne suite

Cordialement

axion

Le 11/12/2015 à 11:03

Merci Axion pour ton message,

la case "activer le calcul itératif" n'est pas cochée.

Quant aux macros, ce ne sont pas des macros qui calculent (je ne sais pas s'il est possible de calculer par macro), mais ce sont des macros qui disent "insére telle valeur X dans telle cellule", "supprime la valeur dans telle cellule", etc...

Tous les calculs se font par formules. Je n'ai pas de formules matricielles.

Je ne comprends pas comment je peux procéder pour chercher l'origine de ce problème.

Sachant que je ne parviens pas à provoquer le bug (à l'instant t, tout semble OK), je n'ai pu que le constater par hasard à plusieurs reprises...

Le 11/12/2015 à 12:42

Bonjour,

Il n'y paraît rien d'évident et je ne vois pas comment (hors calcul itératif) trouver une solution,

ni pas où commencer.

Je passe... désolé !

Cordialement

axion

Le 11/12/2015 à 13:53

Personne d'autre n'a expérimenté ce problème?

Le 11/12/2015 à 15:25

J'ajoute que j'utilisais des macros commençant par :

Application.ScreenUpdating = False
Application.Calculation = False
Application.EnableEvents = False

et finissant par :

Application.ScreenUpdating = True
Application.EnableEvents = True

J'appliquais les conseils trouvés dans le forum pour accélérer les perfs des macros.

Le fait de mettre : "Application.Calculation = False"

sans mettre derrière de "Application.Calculation = True"

ne peut-il pas bloquer certains calculs, comme dans mon cas?

Dans le doute, à présent, j'ai retiré ces bouts de code, mais peut-être qu'un problème persiste à ce niveau?

Et je ne comprends toujours pas comment se peut-il que F9 (Calculate) ne permette pas d'afficher le bon résultat dans les cellules concernées alors que Ctrl+Alt+F9 (CalculateFull) le permet???

Le 12/12/2015 à 11:30

j'ai du mal à croire que personne n'ait jamais rencontré ce problème?

Le 12/12/2015 à 12:19

Bonjour,

Et je ne comprends toujours pas comment se peut-il que F9 (Calculate) ne permette pas d'afficher le bon résultat dans les cellules concernées alors que Ctrl+Alt+F9 (CalculateFull) le permet???

A chaque modif de cellule excel le stocke dans une pile pour savoir que les cellules dépendentes sont à recalculer, et il ne recalcule que celles-ci. En calcul manuel F9 lance ce recalcul.

Ctrl+Alt+F9 s'affranchit de cette pile et recalcule tout, même ce qui n'est pas nécessaire, même celles qui aurait y être et qui n'y sont pas pour une raison x.

Si tu sais quelles plages sont à recalculer tu peux le faire par macro sur celles-ci uniquement pour éviter la perte de temps des calculs inutiles.

Ex : sheets("Feuil1").[C:C].calculate ne recalcule que la colonne C.

sheets("Feuil1").calculate ne recalcule que cette feuille

Et pour les cas désespérés tu as :

CalculateFullRebuild

For all open workbooks, forces a full calculation of the data and rebuilds the dependencies.

Les dépendances sont les formules qui dépendent d'autres cellules. Par exemple, la formule « =A1 » dépend de la celulle A1. La méthode CalculateFullRebuild est similaire à entrer de nouveau toutes les formules.

eric

Le 12/12/2015 à 12:31

eriiic a écrit :
Bonjour,
Et je ne comprends toujours pas comment se peut-il que F9 (Calculate) ne permette pas d'afficher le bon résultat dans les cellules concernées alors que Ctrl+Alt+F9 (CalculateFull) le permet???
A chaque modif de cellule excel le stocke dans une pile pour savoir que les cellules dépendentes sont à recalculer, et il ne recalcule que celles-ci. En calcul manuel F9 lance ce recalcul.
Ctrl+Alt+F9 s'affranchit de cette pile et recalcule tout, même ce qui n'est pas nécessaire, même celles qui aurait y être et qui n'y sont pas pour une raison x.
Si tu sais quelles plages sont à recalculer tu peux le faire par macro sur celles-ci uniquement pour éviter la perte de temps des calculs inutiles.
Ex : sheets("Feuil1").[C:C].calculate ne recalcule que la colonne C.
sheets("Feuil1").calculate ne recalcule que cette feuille
Et pour les cas désespérés tu as :
CalculateFullRebuild
For all open workbooks, forces a full calculation of the data and rebuilds the dependencies.
Les dépendances sont les formules qui dépendent d'autres cellules. Par exemple, la formule « =A1 » dépend de la celulle A1. La méthode CalculateFullRebuild est similaire à entrer de nouveau toutes les formules.
eric

Merci Beaucoup, ça m'aide à comprendre.

Vu que t'as l'air de sacrément t'y connaître, as-tu un avis sur ce point stp ?

https://forum.excel-pratique.com/excel/performances-version-d-excel-la-rapide-t71632.html

eriiic a écrit :
Bonjour,
Et pour les cas désespérés tu as :
CalculateFullRebuild
For all open workbooks, forces a full calculation of the data and rebuilds the dependencies.
Les dépendances sont les formules qui dépendent d'autres cellules. Par exemple, la formule « =A1 » dépend de la celulle A1. La méthode CalculateFullRebuild est similaire à entrer de nouveau toutes les formules.
eric

Je viens de créer un macro contenant uniquement cette ligne :

CalculateFullRebuild

Et en la lançant, j'ai eu un message d'erreur "espace pile insuffisant".

Que puis-je faire?

Le 13/12/2015 à 13:18

Bonjour,

j'ai eu un message d'erreur "espace pile insuffisant"

C'est fâcheux... Ca t'aurait permis de reconstruire toutes les dépendances à coup sûr.

Reboote, n'ouvre que le nombre de fichiers nécessaires et reteste.

Pour ton pb de

la valeur dans B restait l'ancienne valeur de A et n'était pas mise à jour malgré mes tentatives de recalcul.

peut être qu'excel rencontre le même type de soucis sans l'annoncer. Vu que tu passes en calcul manuel il sature peut-être.

Essaie de faire un .calculate par feuille plutôt qu'un global. N'en oublie pas et fait-les dans l'ordre.

Si feuil2 a besoin de résultats de feuil5 il faut calculer feuil5 avant : sheets(Feuil5).calculate

as-tu un avis sur ce point stp ?

L'essentiel y a été dit.

Pour moi ce n'est pas la version d'excel qui changera grand chose même si certaines fonctions ont été optimisées. Si ce n'est pas celles que tu utilises le gain sera nul.

C'est l'optimisation de ta feuille au maximum qu'il faut viser. Pour la majorité des formules vba sera plus lent, exception faite pour sommeprod() et autres fonctions matricielles très chronophages.

Pour optimiser ta feuille recherche les parties de formule utilisées soit plusieurs fois dans la formule, soit utilisées dans plusieurs colonnes. Et met ce calcul dans une colonne intermédiaire (dans une colonne à gauche de son utilisation, ça joue) que tu réutilises ensuite. Elle sera calculée 10000 fois au lieu de x fois 10000.

Si sur une feuille tu arrives à avoir toutes les dépendences d'une colonne à gauche tu pourras utiliser .CalculateRowMajorOrder

Range.CalculateRowMajorOrder
Range.CalculateRowMajorOrder calcule la plage une ligne à la fois, de gauche à droite et de haut en bas, mais elle ignore totalement toutes les dépendances. Il s’agit de la même méthode que celle utilisée par Microsoft Excel 97 et Excel 2000. CalculateRowMajorOrder n’essayant pas de résoudre les dépendances, elle est généralement plus rapide que Range.Calculate.
Si vous pouvez vous assurer que toutes les dépendances d’un bloc de formules font toujours référence aux cellules situées à gauche ou plus haut, Range.CalculateRowMajorOrder peut constituer la méthode de calcul la plus rapide dans Excel sur un système à processeur unique.
Range.CalculateRowMajorOrder est l’un des outils les plus utiles dans Excel pour l’optimisation des performances car il permet de déterminer et de comparer les vitesses de calcul de différentes formules tout en ignorant les effets des dépendances.

Ce qui te donne une chance de résoudre tes problèmes de non calcul puisqu'il ne sert plus des dépendances.

Evite les fonctions volatile. Par exemple Maintenant() en est une. Si tu t'en sers dans les colonnes met-la dans une cellule et sert-toi de cette cellule à la place. Elle ne sera calculée qu'une fois au lieu de milliers de fois.

Fonctions volatiles : Alea(), Maintenant(), Aujourdhui(), decaler(), Cellule(), Indirect() ou Info()

Pour les macros banni les .select inutiles dans 99% des cas ainsi que les lectures/écritures cellule par cellule. Lit par plage complète que tu stockes dans un tableau (mon_tableau=A2:C10000). Ensuite utilise ce tableau : a=mon_tableau(3,2)

eric

Le 14/12/2015 à 11:33

Merciii pour tes réponses Eriiic,

Concernant CalculateFullRebuild, voici ma macro :

Sub CalculateFullRebuild()

CalculateFullRebuild

End Sub

Ce que je trouve bizarre, c'est que j'ai le message d'erreur "Erreur d'exécution 28 : espace pile insuffisant" apparaît instantanément en lançant la macro.

A la rigueur, si ça moulinait avant de m'afficher ce message, ça me semblerait + normal...

Le 14/12/2015 à 13:54

Dans ce cas écrit à MS que tu penses avoir découvert un bug.

eric

Le 14/12/2015 à 14:00

Bonjour,

Bonjour Eric,

Et avec :

Sub modCalculation()

Application.CalculateFullRebuild

End Sub

Le 14/12/2015 à 15:34

Jean-Eric a écrit :
Bonjour,
Bonjour Eric,
Et avec :
Sub modCalculation()

Application.CalculateFullRebuild

End Sub

Alors celle-ci fonctionne (bizarre car lorsque j'ai tenté celle d'Eric, je n'avais que ce fichier ouvert).

Cependant, je constate actuellement que ne sont pas retrouvées des cellules dépendantes de certaines cellules : je clique sur "repérer les dépendants" et s'affiche la fenêtre "la commande n'a pas repéré etc...".

Alors qu'inversement, si je me positionne sur la cellule faisant référence à la cellule en question, et que je recherche les antécédents, ça me trouve bien la cellule...

Je commence à sacrément flipper sur l'intégrité de mon fichier....

des avis?

Le 14/12/2015 à 15:52

J'ai fermé mon fichier excel, mais j'ai aussi fermé les autres fenêtres excel (sachant que comme je travaille sur 2 écrans, je lance 2 fois excel pour mettre chaque excel sur chaque écran).

Là, ça a planté lors de la fermeture.

Et en réouvrant mon fichier, les dépendances sont retrouvées à présent.

Questions :

A. Quelqu'un connaît-il un utilitaire pour vérifier l'intégrité d'un fichier excel?

B. Quelqu'un a-t-il déjà eu des problèmes en lançant plusieurs fois excel (je ne parle pas de plusieurs fichiers ouvert dans la même fenêtre excel, mais du logiciel ouvert plusieurs fois), notamment pour un usage multi-écran?

Le 14/12/2015 à 15:53

Ben donne déjà ton avis sur ça : https://forum.excel-pratique.com/excel/resultats-calculs-non-mis-a-jour-phenomene-paranormal-t71590.html#p409575

J'ai l'impression que j'ai passé du temps pour les mouches là...

Le 14/12/2015 à 16:16

eriiic a écrit :
Ben donne déjà ton avis sur ça : https://forum.excel-pratique.com/excel/resultats-calculs-non-mis-a-jour-phenomene-paranormal-t71590.html#p409575
J'ai l'impression que j'ai passé du temps pour les mouches là...

Ben je t'ai remercié, ça va m'être utile.

Pour recalculer feuille par feuille en respectant l'ordre des dépendances, ce n'est pas possible dans mon cas car mon fichier compte + de 100 feuillets et que les dépendances vont dans tous les sens.

Pour .CalculateRowMajorOrder, c'est un peu le même problème. En revanche, ça peut être utile pour remplacer Calculate dans des macros.

J'ai bien lu tes conseils pour les fonctions et macros.

A priori la macro proposée par Jean-Eric résout le pb de CalculateFullRebuild.

Je vais observer si le problème de résultats non mis à jour se reproduit, mais le problème évoqué ci-dessus de dépendances perdues est peut-être lié, non? (ce problème s'est posé avant et après le recours à "Application.CalculateFullRebuild")

Et c'est peut-être lié au fait d'avoir ouvert le programme excel 2 fois?

Le 14/12/2015 à 17:39

Si tes problèmes de pile sont dus à un manque de mémoire évite de l'ouvrir 2 fois.

Il sera temps de tester cette configuration quand tu auras résolus tes pb.

Le 15/12/2015 à 10:44

eriiic a écrit :
Si tes problèmes de pile sont dus à un manque de mémoire évite de l'ouvrir 2 fois.
Il sera temps de tester cette configuration quand tu auras résolus tes pb.

J'ai 8GO de RAM, normalement je dois pouvoir ouvrir excel plusieurs fois, non?

Mon PC :

Intel Core i5 - 4440 CPU @ 3.10 GHz
RAM : 8GO
OS : windows 10 64 bits

Le 15/12/2015 à 15:47

C'est toi qui a émis cette éventualité, je ne vois pas d'autre façon de confirmer ou d'infirmer. Fais ce que tu veux.

eric

Rechercher des sujets similaires à "resultats calculs mis jour phenomene paranormal"