Discussion :

[Denis_67]

Bonjour,

Lorsqu'il y a un grand nombre de formules dans un classeur, le temps de calcul peut-être assez long.

Le message de progression n'est pas toujours vu par les utilisateurs qui peuvent continuer à consulter des valeurs pas à jour.

Peut-on ajouter une msgbox pdt le recalcul voire bloquer toute action ?

merci
Denis

[halaster08]

Regarde ce tuto sur les evenments du classeur, il y en a un sur "calculate" :http://silkyroad.developpez.com/VBA/...asseur/#LII-19

[Denis_67]

Merci, mais je ne parviens pas à faire fonctionner la procedure

J'ai mis dans thisworxbook

Code :

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

1
2
3
4
 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Merci de patienter pendant le calcul: " & Sh.Name
End Sub

Pendant le calcul il ne se passe rien puis j'ai le message qui apparait autant de fois que de feuilles recalculées.

Je pense que le probleme vient du fait que je change une valeur dans une feuille (où il n'y a pas de calcul) et que les valeurs sont mise à jour dans les autres feuilles.

J'ai aussi essayé dans les feuilles recalculées de metre le code suivant, mais ça ne marche pas non plus

Code :

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

1
2
3
4
 
Private Sub Worksheet_Calculate()
MsgBox "Merci de patienter pendant le calcul"
End Sub

Qu'est ce que je fait mal ?

merci
Denis

[halaster08]

Qu'est ce que je fait mal ?

Rien , désolé il semble que cet événement se déclenche apres le calcul et pas avant (je ne l'avais jamais utilisé, mais j'avais vu dans le tuto qu'il existe), du coup ça ne conviens pas a ton problème.

[unparia]

Bonjour

Les calculs faits par Excel se font dans un fil distinct de celui qu'utilise VBA (il n'est d'ailleurs à mon sens pas vraiment conseillé de faire un "panachage" entre utilisation de formules et de code VBA, mais bon ...)
Si tu es le concepteur de ce classeur, tu sais quelle est la cellule C dont la modification déclenche (y compris en cascade) les formules. Tu sais également quelle est la dernière cellule D calculée par formule.
Rien ne t'empêche alors de :
- mémoriser dans une variable le contenu actuel, avant recalcul, de la cellule D traitée par le calcul. A faire lors de l'évènement change de la cellule C
- d'utiliser ta msgbox si tu le souhaites à ce même moment
- de mettre en route Application.Ontime pour vérifier périodiquement que la cellule D a été modifiée (preuve de la fin du calcul par formules)
J'espère par ailleurs que tes formules ne créent pas de références circulaires.

[Jean-Philippe André]

Salut,

si tu souhaites avoir une parade pour faire patienter, tu peux passer par un formulaire d'attente =]
http://arkham46.developpez.com/artic...s/formattente/

[Denis_67]

Merci pour vos contributions qui m'ont aide.

En fait le change de valeur d'une cellule n'est pris en compte, qu'une fois les calcul terminé (je pense que c'est prioritaire)

J'ai donc:
- mis mon classeur à l'ouverture en calcul manuel
- au changement de valeur de la cellule de choix (Worksheet_Change) , j'affiche une box d'attente
- je passé en calcul automatique (je pourrais aussi lancer le calcul)
- enfin dans l'événement Workbook_SheetCalculate une fois que le calcul est fini, je ferme ma box et je me remets en calcul manuel.

ça donne
sur la feuille de saisie

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
 
Private Sub Worksheet_Change(ByVal Target As Range)
    'permet de sortir de la procédure si plus d'une cellule est sélectionnée
    '(sinon la suite de la macro renvoie un message d'erreur)
    If Target.Count > 1 Then Exit Sub
 
If Target.Address = ("$B$6") Then
 
'appelle le userform patience
  UserAttente.Show 0
  UserAttente.Repaint
'cache l'exécution de la macro (rapidité)
Application.ScreenUpdating = False
'masque et ignore les messages d'alerte
Application.DisplayAlerts = True
 
'lance le calcul
Application.Calculation = xlCalculationAutomatic
 
End If
End Sub

Et sur le classeur

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
 
Private Sub Workbook_Open()
Application.AutoRecover.Enabled = False
Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
' et on rend la main
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 Unload UserAttente
Application.Calculation = xlCalculationManual
End Sub
 
 
merci 
Denis

[Denis_67]

Infos supplémentaires.

Si on reste en mode manuel et qu'on lance juste le calcul par la commande calculate, le scrutateur d'événement Workbook_SheetCalculate n'est pas activé.

Par ailleurs le fait de changer le mode de calcul auto/manuel affecte toute l'application normal commande Application....

Il est donc dangereux pour un utilisateur de lui forcer le mode de recalcul car cela affetera ses classeurs ouverts et à ouvrir
Si pour l'exécution d'une macro on veut suspender temporairement les calculs il faut Sauvegarder l'état actuel de l'utilisateur dans une variable EtatDépart = Application.Calculation et le remettre en place ensuite.

En conclusion je supprime tous ces addon avant qu'ils ne me rattrapent !
Tant pis les utilisateurs patienteront et de toute façon c'est un standard Excel que la barre de status de calcul ne soit pas plus visible.

Denis

[eriiic]

Bonjour,

tu as Application.CalculationState :
Nom Valeur Description
xlCalculating 1 Les calculs sont en cours.
xlDone 0 Calculs terminés.
xlPending 2 Des modifications déclenchant un calcul ont eu lieu, mais le recalcul n'a pas encore été effectué.
Tu pourrais le scruter et si <>0 dure plus de x secondes alerter.
eric

[Denis_67]

Merci Eric, pour ces précieuses info que je vais garder.

J'ai pris le taureau par les cornes, et j'ai restructuré les gros classeurs hérités d'une conception ancienne.
Entre autre, j'ai supprimé les liaisons externes en incluant les data dans le même classeur
J'ai simplifié toutes les formules genre $A$1:$B$65536 par A:B (rien que cela m'a fait gagner 3Mio sur le fichier).
c'est évident sur un grand nombre de formules (19 000 dans mon exemple) on gagne 10 caractères soit 190 000 caractères
comme dans les formules complexes la séquence se répète on gagne bien plus.

Résultats fichiers moins gros chargés mémoire moins chargée calculs plus rapide.

Les temps de calcul sont maintenant corrects.

Rien que du B A BA, qu'il est bon de se répéter.

Denis