Excel perd les pédales

[ltsv38]

Bonjour

J'ai réalisé un gros classeur Excel (V2003) : une dizaine de feuilles, quelques dizaines de milliers de cellules à calculer, plusieurs graphiques, des milliers de lignes de code VBA.
En temps normal le recalcul complet de ce classeur prend environ 3 minutes sur un P4.

Par moment Excel perd les pédales : comme beaucoup de cellules font appel à d'autres cellules, il semble ne pas arriver à trouver l'ordre optimal de calcul de ces cellules.
La conséquence est qu'il essaye de calculer une cellule faisant appel à une autre cellule qui n'est pas encore rafraichie ... il affiche donc partout des #VALEUR# et boucle en permanence dans les codes de macros.

Pour le remettre sur les rails il faut :
- Cellule par cellule retaper la formule existante (F2 + Return) : il n'y a jamais de problème de calcul à ce niveau
- Le faire pour les quelques milliers de cellules en erreur (une heure de saisie ...)
- Espérer qu'à ce moment il se remette sur les rails sinon on peut recommencer plusieurs fois !

L'un d'entre vous a t'il déjà rencontré ce type de problème et surtout découvert une parade ?

Merci

Pierre

 

[Samantha]

Re : Excel perd les pédales

Bonjour,

Quelques questions pour t'aider à trouver des pistes :

As tu nommé les plages de cellules auxquelles tes formules font référence ?

As tu nommé les formules les plus utilisées

Si ce n'est fait tu peux commencer par là en nommant de façon croissante dans l'ordre d'entrée en scène des plages et des formules

Conséquences :
1- un petit régime pour ton fichier
2- un début de solution puisque tu indiques à excel un ordre des éléments par l'ordre alpha/num des noms des éléments

Pour ce qui est des macros, tu comprendras à ma signature que je ne puis t'aider sur ce sujet 😉

A tester

Reviens nous dire


Sam

 

[ltsv38]

Re : Excel perd les pédales

Bonjour et merci

Oui mes cellules importantes ont été nommées pour permettre plus de facilité de lecture et de maintenance. J'ai essayé de d'utiliser de noms significatifs (sans respect d'un ordre quelconque).
Mais la plupart des problème sont dans des "tableaux de valeurs" : Excel refuse dans des formules appelant des fonctions/macros d'utiliser le nom de la colonne. Par exemple si ma première colonne est nommée X : je peux mettre dans une cellule "=X+1" il prend le X de la même ligne mais je ne peux pas mettre "=MaFonction(X)".
Je suis donc obligé de ne pas utiliser que des noms

A suivre

 

[Samantha]

Re : Excel perd les pédales

Re :

Dans le vide c'est assez difficile de saisir les nuances : peux tu essayer de nous passer un petit exemple sur ce poiint précis ?

Bien sûr si il y a interaction avec certaines macro sur ces plages là il faudra les mettre aussi mais sinon pas nécessaire pour le moment

A te lire


Sam

 

[ltsv38]

Re : Excel perd les pédales

Bonjour

Sur quel point veux tu un exemple ?
Et comment joindre un fichier Excel à un post ?

Merci

Pierre

 

[Samantha]

Re : Excel perd les pédales

Re :

Ta question

Par exemple si ma première colonne est nommée X : je peux mettre dans une cellule "=X+1" il prend le X de la même ligne mais je ne peux pas mettre "=MaFonction(X)".

Pour joindre un fichier : quand tu est dans la fenêtre de saisie du message, au dessous tu as 2 boutons "Envoyer la réponse rapide" et "Aller en mode avancé".

Tu vas en mode avancé et tu utilises le trombonne comme dans un courriel normal ou bien en dessous du cadre de saisie tu as un bouton dans le cadre Options supplémentaires : "Gérer les poèces jointes" tu cliques et tu suis les instructions et tu oublies pas le bouton uploader avant de fermer la fenêtre.

Ensuite tu peux cliquer sur envoyer la réponse

A te lire

Sam

 

[ltsv38]

Re : Excel perd les pédales

Ci-joint un petit exemple dans lequel on voit qu'Excel refuse une plage nommée comme paramètre d'une fonction VBA alors que les fonctions intégrées d'Excel l'acceptent.

Merci

Pierre

 

[Samantha]

Re : Excel perd les pédales

Re :

Merci donc voila y'a plus qu'à attendre le passage d'un de nos talentueux VBAistes puisque là je me vois bien incapable de te tendre la main à part pour te souhaiter bon courage 😉

Mais ça va pas tarder il vont arriver les programmateurs fous 😉

Je suis le fil avec attention néamoins (nez en moins) 😀

Sam

 

[ltsv38]

Re : Excel perd les pédales

Merci pour ton aide

Mais pour en revenir à l'origine de nos derniers échanges, tu disais :

tu peux commencer en nommant de façon croissante dans l'ordre d'entrée en scène des plages et des formules

Conséquences :
1- un petit régime pour ton fichier
2- un début de solution puisque tu indiques à excel un ordre des éléments par l'ordre alpha/num des noms des éléments

Es tu sûre que Excel va respecter l'ordre des cellules nommées dans son calcul ? Surtout dans mon cas où il coince sur un tableau de 300 lignes identiques dans lequel chaque cellule fait référence à d'autres cellules de la même ligne ou à une cellule de la ligne précédente : je ne peux pas me permettre de nommer 300 lignes x 25 colonnes = 7 500 noms

A suivre

Pierre

 

[Samantha]

Re : Excel perd les pédales

Re :

Bah je pense que tu n'as pas 7 500 formules différentes ?

En conséquence le nommage de la formule type devrait suffire

N'ayant pas la connaissance de la hiérarchie exacte de ton fichier je pense pouvoir cependant deviner que la formule qui donne le résultat de la cellule qui est prise en référence dans la ligne du dessous est prioritaire donc nomme la A
la seconde formule nomme la B

Je ne puis garantir qu'Excel respecte la hiérarchie du nommage mais ça me parait logique

A tester

Sam

 

[ltsv38]

Re : Excel perd les pédales

Merci

Je crois avoir mal compris ton premier message.

Je sais dans Excel nommer une cellule ou une plage de cellule.
Je ne sais pas nommer une FORMULE ....

Peux tu m'en dire plus ?

Pierrre

 

[Samantha]

Re : Excel perd les pédales

Bonjour,

Pour nommer une formule :

Insertion/Nom/Définir et dans la case 'Fait référence à' saisir la formule et valider par ok

Ensuite tu colles le nom de ta formule dans la cellule où tu veux que le résultat apparaisse

A te lire


Sam

 

[Gorfael]

Re : Excel perd les pédales

Bonjour,

Pour nommer une formule :

Insertion/Nom/Définir et dans la case 'Fait référence à' saisir la formule et valider par ok

Ensuite tu colles le nom de ta formule dans la cellule où tu veux que le résultat apparaisse

A te lire


Sam

Salut à toutes et tous
Question : as-tu un plan de l'ordre des recalculs à faire ? si oui
dans ThisWorkBook :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Calculation = xlAutomatic
End Sub

Private Sub Workbook_Open()
    Application.Calculation = xlManual
End Sub

Pour bloquer le calcul automatique d'Excel quand tu ouvres ton fichier, et le remettre en route quand tu sorts
inconvénient : tous les fichiers ouverts sont concernés

ensuite, tu utilises (Toujours sur ThisWorkBook) :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Err_Workbook_SheetChange
Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("feuille à recalculée 1").Range("Plage à recalculer 1").Calculate
Sheets("feuille à recalculée 2").Range("Plage à recalculer 2").Calculate
Sheets("feuille à recalculée 3").Range("Plage à recalculer 3").Calculate

Sort_Workbook_SheetChange:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Err_Workbook_SheetChange:
    MsgBox (Err.Number & " - " & Err.Description)
    Resume Sort_Workbook_SheetChange
End Sub

🙂 dès que je bloque les événement, je gères les erreur pour être sûr de passer par la remise en route
dans cette macro : sh est la feuille et Traget la plage (mais en lecture seule-ByVal obloge) qui ont été modifiées : mais attention comme tu bloque les événements et que tu n'as pas de calcul automatique, il faut redéfinir l'intégralité des feuilles/plages du classeur

si c'est trop lourd, tu remplace le nom de la macro par sub ReCalcul par exemple :
Sub Recalcul ()
etc....
avec un lancement manuel

ou tu crées une macro par feuille qui recalcule dans l'ordre la feuille et qui lance les macros des feuilles dépendantes (mais attention aux bouclages circulaires comme :
sub Macro_feuil1 ()
sheets("Feuil1").calculate
call Macro_feuil2
End sub

sub Macro_feuil2 ()
sheets("Feuil2").calculate
call Macro_feuil1
End sub

avec un truc comme ça, Excel se fait sauter le caisson 🙂 )

A+

 

[ltsv38]

Re : Excel perd les pédales

Bonjour Gorfael et merci pour ta réponse.

Mon classeur est déjà utilisé avec un calcul sur demande pour éviter les temps de réponses liès à des recalculs permanents : à l'ouverture j'appelle le code suivant :
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Et dans mes boîtes de dialogue j'appelle un "Calculate" lorsque il le faut.

Les erreurs sont toujours dans la même feuille.
J'ai retenu par contre la méthode suivante dans ta réponse :
Sheets("feuille à recalculer 1").Range("Plage à recalculer 1").Calculate

Je me demande cependant si, pour ne pas avoir à spécifier une à une toutes les plages à calculer je peux faire (après une longue cogitation sur le "bon" ordre) :
Sheets("feuille principale").Range("Plage à recalculer 1").Calculate
Sheets("feuille principale").Range("Plage à recalculer 2").Calculate
....
Sheets("feuille principale").Range("Plage à recalculer N").Calculate
Calculate

Ce dernier "Calculate" étant destiner à tout recalculer sur la feuille principale comme sur tout le reste du classeur : en espérant que, même s'il recalcule ce qui a déjà été imposé au préalable il n'y aura pas d'erreurs puisque les paramètres n'ont pas changés entre ces 2 calculs !

A tester et à suivre...

Pierre

 

[Gorfael]

Re : Excel perd les pédales

Bonjour Gorfael et merci pour ta réponse.

Mon classeur est déjà utilisé avec un calcul sur demande pour éviter les temps de réponses liès à des recalculs permanents : à l'ouverture j'appelle le code suivant :
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Et dans mes boîtes de dialogue j'appelle un "Calculate" lorsque il le faut.

Les erreurs sont toujours dans la même feuille.
J'ai retenu par contre la méthode suivante dans ta réponse :
Sheets("feuille à recalculer 1").Range("Plage à recalculer 1").Calculate

Je me demande cependant si, pour ne pas avoir à spécifier une à une toutes les plages à calculer je peux faire (après une longue cogitation sur le "bon" ordre) :
Sheets("feuille principale").Range("Plage à recalculer 1").Calculate
Sheets("feuille principale").Range("Plage à recalculer 2").Calculate
....
Sheets("feuille principale").Range("Plage à recalculer N").Calculate
Calculate

Ce dernier "Calculate" étant destiner à tout recalculer sur la feuille principale comme sur tout le reste du classeur : en espérant que, même s'il recalcule ce qui a déjà été imposé au préalable il n'y aura pas d'erreurs puisque les paramètres n'ont pas changés entre ces 2 calculs !

A tester et à suivre...

Pierre

Re...
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

peut être remplacé par :
Application.Calculation = xlManual
Le reste est une re-définition d'Excel qui ne sait pas si tu as changé les valeur ou pas

Sheets("feuille principale").Range("Plage à recalculer 1").Calculate
Sheets("feuille principale").Range("Plage à recalculer 2").Calculate
....
Sheets("feuille principale").Range("Plage à recalculer n").Calculate

soit tu actives la feuille
Sheets("feuille principale").activate
Range("Plage à recalculer 1").Calculate
Range("Plage à recalculer 2").Calculate
....
Range("Plage à recalculer n").Calculate

soit tu utilises With
with Sheets("feuille principale")
.Range("Plage à recalculer 1").Calculate
.Range("Plage à recalculer 2").Calculate
....
.Range("Plage à recalculer n").Calculate
end with
Avec une indentation, c'est plus lisible

Pense à faire un ScreenUpDating=False en début et =True en fin de macro

A+