XL 2010 écran non figé lors d'un calcul long malgré "Application.screenupdating = false"

luigiF · 16 Juin 2020

Salut à vous,
Je rencontre un problème lié à un traitement nécessitant pas mal de temps qui semble contrarier l'instruction "Application.screenupdating = false" .
Malgré cette instruction, Excel affiche après un certains temps un écran complètement gris au lieu de rester figé, puis indique "Ne répond pas", puis n'indique plus rien du tout ...
Lorsque le traitement est terminé, l'affichage redevient "normal" sans difficultés, mais je souhaiterais éviter la période durant laquelle l'utilisateur est confronté à cet écran gris.
Pour illustrer le problème, je joins un fichier contenant une version simplifiée du traitement effectué (supression de nombreuses lignes). Ce fichier contient la macro suivante :

VB:

Sub Test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Feuil1

    For i = 2 To 100000
        .Cells(i, 1) = Application.RandBetween(1, 5)
    Next i
    MsgBox "rempli"
    
    For i = 2 To .UsedRange.Rows.Count
        If .Cells(i, 1) <= 3 Then .Cells(i, 1).Clear
    Next i
    MsgBox "debut suppression"
    
    'debut du traitement long ...
    On Error Resume Next
    .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
End With

Application.Calculation = xlCalculationAutomatic
MsgBox "fini"
End Sub

Quelqu'un pourrait-il m'expliquer comment éviter cet écran gris ? Je ne pense pas pouvoir utiliser doevents dans la mesure où l'instruction longue à éxécuter ( .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ) n'est pas une boucle.

Merci pour vos conseils.
Cordialement,
LuigiF

patricktoulon · 17 Juin 2020

bonjour Dranreb
Ouais mais alors quelle en est l'utilité
@sylvanu
je viens de tester ton model
a ma grande surprise il semblerait que le redim preserve itératif soit plus rapide
j'ai bloqué les scrennupdating et calculate la différence est insignifiante chez moi

VB:

   Dim tablo(), L&, I&, X#, T#
    T = Timer
    For I = 1 To 100000
        X = Application.RandBetween(1, 5)
        If X > 3 Then L = L + 1: ReDim Preserve tablo(1 To L): tablo(L) = X
    Next I

    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual
    Feuil1.Cells(2, 1).Resize(UBound(tablo), 1) = Application.Transpose(tablo)
    'Application.Calculation = xlCalculationAutomatic
    MsgBox "Terminé" & vbCrLf & "Temps de traitement " & Round(Timer - T, 3) & "s" & Chr(10) & L & " nombres générés."
  
End Sub

Dranreb · 17 Juin 2020

À vrai dire la question du demandeur était plus générale. Ça pourrait se traduire par :
Que faire quand Excel se met à s'afficher en mode 'égaré' pendant l'exécution d'une de ses propre méthodes ? Et il n'y a qu'une seule réponse: Atteindre le résultat recherché par une solution plus pertinente, moins gourmande en ressources, un point c'est tout.

sylvanu · 17 Juin 2020

Re,
Question de lecture et d'interprétation.
Moi j'y ai lu :

J'ai un programme qui rame et screenupdating ne fonctionne pas

Alors j'ai fait en sorte que le programme ne rame plus.
Mais notre ami LuigiF quand il réapparaîtra nous donnera la bonne réponse.

mapomme · 17 Juin 2020

Re,

L'utilisation d'une colonne auxiliaire permet :

d'utiliser une formule qui renvoie #N/A si la ligne est à supprimer ou le numéro de ligne si la ligne à conserver
le tri de la colonne auxiliaire permet de rassembler en un seul bloc les lignes à supprimer (ce qu'Excel fait très rapidement pour un seul bloc)
le tri de la colonne auxiliaire permet de conserver l'ordre relatif initial des lignes restantes
dans une certaine mesure (à vérifier à chaque fois) de conserver les formules.

luigiF · 17 Juin 2020

Salut à vous,

Veuillez excuser ma réponse tardive. Je confirme évidemment ce qu'indique Dranreb : ma question ne porte que sur le comportement d'Excel pendant l’exécution de l'instruction ".Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete"

L'exemple que j'ai envoyé n'est qu'une reproduction simplifiée des traitements réels que j'effectue, mais qui entraine déjà l’apparition de l’écran gris que j'aimerais éviter.
Le véritable fichier comporte de nombreuses colonnes de données et plus de 150 000 lignes.

Il convient de noter que le traitement lui même ne provoque aucune erreur. La gène est seulement esthétique pour l'utilisateur. Durant les calculs, dans le "vrai" fichier, j'affiche une userform indiquant que les calculs sont en cours ... mais elle disparait après quelques secondes lorsque que l'écran gris apparait, pour ne réapparaitre qu'en fin de traitement.

Transférer dans un tableau les lignes à conserver puis les réinjecter dans la feuille est plus long que d'utiliser l'instruction .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
(j'avais fait le test mais je n'ai plus le fichier sous la main).
Qui plus est, cette méthode comporte ne nombreux autres inconvénients liés à la nature du fichier sur lequel je travaille.

D'après vos réponses il ne semble pas y avoir de solution pour éviter qu'Excel fige simplement l'écran au lieu d'afficher "Ne répond pas " et l'écran gris. Je peux au mieux prévenir l'utilisateur dans ma Userform tant qu'elle est visible en début de traitement : "Attention, Excel risque de perdre pied un moment mais ensuite il va reprendre la main"

Merci pour vos contributions. Cordialement,
LuigiF

mapomme · 17 Juin 2020

luigiF à dit:
Transférer dans un tableau les lignes à conserver puis les réinjecter dans la feuille est plus long que d'utiliser l'instruction .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

A quoi ça sert de se décarcasser et d'expliquer ???

luigiF · 17 Juin 2020

Restons calme ...

Je rappelle que ma question ne portait que sur la possibilité d'éviter qu'Excel passe en mode "ne répond pas" au lieu de simplement se figer conformément à l'instruction "application .screenupdating = false".

J'ai bien noté la solution que tu proposes pour accélérer le traitement et je t'en remercie encore une fois.

Cordialement,
LuigiF

GALOUGALOU · 17 Juin 2020

re luigiF
une petite question pour la curiosité
avez vous lu mon fil précédent ? et donc avez vous essayé d'activer ou de désactiver l'accélération graphique ?
cordialement
galougalou

luigiF · 17 Juin 2020

@GALOUGALOU
Oui j'ai lu votre post mais c'est une option que je peux retenir car le fichier est utilisé par de nombreux utilisateurs auxquels je ne peux imposer cette manipulation.
Je testerai cette solution sur mon propre PC ... dès que j'aurai compris comment désactiver l'accélération graphique
Merci pour votre contribution.
Cordialement,
LuigiF

GALOUGALOU · 17 Juin 2020

re luigiF
alors ce n'est pas dans les ordi, mais dans le fichier excel en question.
options excel/options avancées.

je n'affirme rien sur le résultat, tout dépend de votre configuration, mais essayer ne coute rien.
cordialement
galougalou

luigiF · 17 Juin 2020

Merci pour cette indication.
J'ai fait le test : cela ne change rien pour moi.
Par contre, j'ai bien l'impression que ce paramétrage est valable pour Excel en général (en tous cas pour un utilisateur) et non pour un fichier particulier.
Cordialement,
LuigiF

_Thierry · 17 Juin 2020

Bonsoir à Tous

Oui il me semble aussi, ce genre d'options ne sont pas propres à un classeur mais au profile utilisateur :

Contrairement à ce genre d'options propres au WorkBook :

Ou encore à une Worksheet Particulière :

Et pour l'écran qui freeze, comme je disais au début dans mon Post #3, parfois on n'y peut rien, Windows fige Excel, juste prendre son mal en patience...
(Ca arrive aussi sur Microsoft Dynamics NAV aussi, et là ce n'est pas du à des macros mal-optimisées)

Bonne soirée,
@+Thierry

XL 2010 écran non figé lors d'un calcul long malgré "Application.screenupdating = false"

luigiF

XLDnaute Junior

Pièces jointes

patricktoulon

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

luigiF

XLDnaute Junior

mapomme

XLDnaute Barbatruc

luigiF

XLDnaute Junior

GALOUGALOU

XLDnaute Accro

luigiF

XLDnaute Junior

GALOUGALOU

XLDnaute Accro

luigiF

XLDnaute Junior

_Thierry

XLDnaute Barbatruc

Discussions similaires