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

[luigiF]

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 :

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]

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

   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]

À 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]

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]

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]

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]

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]

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]

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]

@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]

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]

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]

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