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

 

[Dranreb]

Bonsoir.
Non, ce qui est long à exécuter ce sont les boucles qui accèdent à une seule cellule à chaque passage !

 

[_Thierry]

Bonsoir @luigiF , @Dranreb

Tu es sur un PC ou un remote server en RDP (Terminal Server, par exemple) ?
Selon certaines instructions (manipulation de Fichiers TXT, ou autre, sans selectionner quoique ce soit sur les Cellules Excel, il m'arrive de voir un écran blanc avec Not Responding, ou encore on voit la Feuille, mais le UserForm s'est "barré" temporairement puis revient quand le process est complété...

Depuis un PC le même programme ne fait pas ceci, mais bon j'ai plus de ressources en RAM et en Carte Graphique que sur un serveur partagé...

Disons que tant que ca ne plante pas et que ca dure que quelques millisecondes, je ne pense pas que tu puisses y faire grand chose, ou alors un carte graphique de gamer ! Car là j'ai l'impression que c'est plus Windows qui gère les ressources et l'affichage passe à la trape (Application.UpdateScreen ou pas)

Bonne soirée
@+Thierry

 

[Dranreb]

Je n'avais pas vu, 100000 lignes ?! Ça fait quand même beaucoup !
Il vaudrait mieux préparer plus judicieusement dans un tableau dynamique ce qu'on veut y mettre, plutôt que de devoir en supprimer d'inutiles après coup !

 

[mapomme]

Bonsoir,

A mon avis, c'est un phénomène que VBA ne peut pas contrôler sans ralentir considérablement son exécution.
En revanche, on peut (parfois) revoir le code pour changer sa manière de faire le travail afin de réduire considérablement le temps d'exécution.

Exemple de réécriture (vite fait, on pourrait faire mieux) considérablement plus rapide et donc sans blocage de l'écran (sur mon PC) :

Sub Test()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   With Feuil1
      If .Range("a1") = "bidon" Then .Columns(1).Delete
      .Range("a2:a100000").Formula = "=RANDBETWEEN(1,5)"
      .Range("a2:a100000").Value = .Range("a2:a100000").Value
      .Columns(1).Insert: .Range("a1") = "bidon"
      .Range("a2:a100000").FormulaR1C1 = "=IF(RC[1]<=3,NA(),row())"
      .Range("a2:a100000").Value = .Range("a2:a100000").Value
      Intersect(.Range("a2:a100000").EntireRow, .UsedRange).Sort key1:=.Range("a2"), order1:=xlAscending, Header:=xlNo
      On Error Resume Next
      .Range("a2:a100000").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
      On Error GoTo 0
      If .Range("a1") = "bidon" Then .Columns(1).Delete
   End With
   Application.Calculation = xlCalculationAutomatic
   MsgBox "fini"
End Sub

 

[Dranreb]

La solution de mapomme est pas mal.
Moi j'avais plus en tête ça :

Option Explicit
Sub Test()
Dim T(), LS As Long, LE As Long, N As Byte, C As Long
ReDim T(1 To 100000, 1 To 1)
   Randomize
   For LS = 1 To 100000
      N = Int(Rnd * 5 + 1)
      If N > 3 Then T(LS, 1) = N
      Next LS
   Feuil1.[A2].Resize(100000).Value = T
   MsgBox "debut suppression vides dans " & Intersect(Feuil1.Columns(1), Feuil1.UsedRange).Address
   With Feuil1.UsedRange
      T = .Value
      LS = 1
      For LE = 2 To UBound(T, 1)
         If Not IsEmpty(T(LE, 1)) Then
            LS = LS + 1
            For C = 1 To UBound(T, 2)
               T(LS, C) = T(LE, C)
               Next C: End If: Next LE
      .ClearContents
      .Resize(LS).Value = T
      End With
   MsgBox "fini"
   End Sub

 

[GALOUGALOU]

re luigi f, bonsoir le fil, bonsoir le forum
je ne vais tirer aucune conclusion de ce que j'ai constaté.
sur mon système avec cocher désactiver l'accélération matérielle, j'ai le même comportement que Luigif
avec décocher désactiver l'accélération matérielle, le comportement de l'écran est normal,
précision : windows 10 et excel 64 bits.
cordialement
galougalou

 

[jmfmarques]

Bonjour à tous
Ce n'est pas la première fois que :
- ce genre d'ennui graphique est constaté avec Excel 2010
- que j'ai l'occasion d'émettre l'hypothèse selon laquelle certains processus, traités en mode synchrone sur d'autres versions, l'étaient en mode asynchrone avec 2010, avec alors pour conséquence évidente la poursuite des instructions du processusA alors que le processusB est encore en cours de traitement.
- que j'appelle l'attention (su cette hypothèse est avérée) que ne seraient que "bancales" des solutions d'attente d'une durée "estimée".
- que je dis que seul Microsoft (à consulter en ce qui concerne cet aspect) est alors en mesure de "corriger"

 

[jmfmarques]

Bonjour à tous
Ce n'est pas la première fois que :
- ce genre d'ennui graphique est constaté avec Excel 2010
- que j'ai l'occasion d'émettre l'hypothèse selon laquelle certains processus, traités en mode synchrone sur d'autres versions antérieures, le sont en mode asynchrone avec 2010, avec alors pour conséquence évidente la poursuite des instructions du processusA alors que le processusB est encore en cours de traitement.
- que j'appelle l'attention (si cette hypothèse est avérée) sur le fait que ne seraient que "bancales" des solutions d'attente d'une durée "estimée".
- que je dis que seul Microsoft (à consulter en ce qui concerne cet aspect) est alors en mesure de "corriger"

 

[Dranreb]

Bonjour.
Moi je trouve cette réaction d'Excel assez normale en soit. On pourrait tout au plus critiquer qu'elle ne soit pas légèrement différente quand il est seul à exécuter une tâche exigeant plus de temps que le délai à partir duquel il réagit de cette façon. Mais qu'on puisse se préparer à l'idée que ce soit mort, comme si on avait écrit une boucle sans fin qu'il est impossible d'arrêter, et qu'il n'y aura probablement pas d'autre issue possible que de planter l'application, pour moi ça va, c'est bon !
Mais comme nous l'avons montré mapomme et moi la seule chose à faire c'est de trouver une solution différente, gaspillant moins de ressource CPU pour arriver au même résultat.
Mais il faut admettre une fois pour toutes qu'appliquée à un Range multizones la méthode Delete fait partie de la cohorte d'opérations très insuffisament performantes d'Excel qu'il ne faut jamais utiliser. S'il y avait de plus dans la plage à traiter des formules disparates à préserver, je crois qu'il n'y aurait pas d'autre solution que de la classer préalablement de sorte que les lignes à supprimer soient regroupées à la fin, comme dans la solution de mapomme, afin que la plage cesse d'abord d'être multizones.

 

[sylvanu]

Bonjour tout le monde,Peut être une autre approche en passant par un array. (Voir PJ)
Sur mon PC le temps de traitement est autour de 1.2s.

Sub test2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A:A").ClearContents
T0 = Timer
Dim tablo()
ReDim tablo(100000)
Indextablo = 0
    For i = 0 To 100000
        N = Application.RandBetween(1, 5)
        If N > 3 Then
            tablo(Indextablo) = N
            Indextablo = Indextablo + 1
        End If
    Next i
ReDim Preserve tablo(Indextablo)
Cells(2, 1).Resize(UBound(tablo)) = Application.Transpose(tablo)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Temps de traitement " & Round(Timer - T0, 3) & "s" & Chr(10) & Indextablo & " nombres générés."
End Sub

 

[Dranreb]

@sylvanu, il s'en fichait, du temps qu'il mettait à initialiser une plage comportant des cellules vides, c'était probablement juste pour tester la suite. Ce qui le préoccupait consistait seulement à les supprimer ensuite.

 

[sylvanu]

@Dranreb,
L'exemple était juste pour montrer que tout traiter par un array est toujours plus rapide.
Le "probablement" n'est pas explicite dans la demande.
Considérons donc que mon post est inutile.
Mais comme souvent le demandeur ne revient pas pour juger la pertinence des réponses. Dommage.

 

[patricktoulon]

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

donc tu rempli 100000 case avec un alea entre 1 et 5
puis tu clear toutes les cases qui sont = ou en dessous de 3
tu te retrouve donc avec les X cases restantes supérieures a 3

ouais.... 🙄 🙄 🙄 🙄 🙄 🙄 🙄 🙄 🙄
perso je fait comme ca

Sub Test()
    Dim tablo(), L&, I&, X#

    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é"
End Sub

et pour être honnête ,le screenupdating= false ne change pas grand chose ou si peu avec cette méthode

 

[Dranreb]

Comme j'avais fini par comprendre, et que je viens d'expliquer au #12, ce n'était pas le sujet de la discussion. D'ailleurs le code initial comportait déjà des MsgBox pour pouvoir apprécier les temps mis par chaque étape. Et la seule quête réelle était un moyen acceptable de supprimer dans une plage des ligne comportant des cellules vides dans une certaine colonne.