VBA Excel : Dysfonction UserForm après 6 sec. Résolu/Fermé

Question

Bonjour, 

J'utilise un formulaire non modal (fStatus) avec un intitulé (lblStatus) pour afficher la progression de l'exploration par macro d'un gros classeur Excel (170 pages d'environ 50 lignes et 30 colonnes).

Environ 6 secondes après avoir lancé la macro, le formulaire cesse d'être actualisé (à l'écran) alors que la macro continue normalement. A ce moment la, le curseur sablier est remplacé par le curseur normal et parfois le formulaire s'efface et son emplacement est tout blanc 

J'ai ajouté un BlockInput pour éviter toute interaction de l'utilisateur pendant l'execution de la macro (avant, le clic droit provoquait le même phénomène).

Voici le code (très simplifié) :

Option Explicit
Option Private Module

Declare Function BlockInput Lib "user32" (ByVal fBlockIt As Long) As Long

Public Sub Test()
  
  Dim frmStatus As fStatus
  Dim wsh As Excel.Worksheet
  Dim wbkCible As Excel.Workbook
  Dim cell As Excel.Range
  Dim ctr As Long
  
  Application.Calculation = xlCalculationManual
  Application.Cursor = xlWait
  Application.ScreenUpdating = False
  
  Set wbkCible = Application.ActiveWorkbook
  Set frmStatus = New fStatus
  frmStatus.Show
  BlockInput 1
  
  For Each wsh In wbkCible.Worksheets
    wsh.Select
    frmStatus.lblStatus.Caption = wsh.Name
    frmStatus.Repaint
    For Each cell In wsh.UsedRange
      cell.Select
`      For ctr = 1 To 2000: Next
    Next cell
  Next wsh
  
  Application.ScreenUpdating = True
  Application.Cursor = xlDefault
  Application.Calculation = xlCalculationAutomatic
  
  frmStatus.Hide
  Unload frmStatus
  Set frmStatus = Nothing
  BlockInput 0


End Sub

Différentes valeurs de la boucle  
`      For ctr = 1 To 2000: Next
m'ont permis de vérifier que le problème survient toujours après un délai d'environ 6 secondes.

Quelqu'un a-t-il une idée de ce qui se passe ?


Configuration: XP / Mozilla / office 2003

Mytå · Accepted Answer

Salut le forum

Il y aurait peut-être une solution avec DoEvents.

DoEvents passe la main au système d'exploitation jusqu'à ce que ce dernier ait terminé de traiter les événements de sa file d'attente

Mytå

gbinforme · Answer

bonjour

Cela m'a l'air d'un phénomène bien connu pour un affichage dans une boucle longue.

Lorsque tu modifies ton intitulé (lblStatus), essaies de rajouter : 
lblStatus.Repaint
Cela force l'affichage qui devrait mieux fonctionner.

Patrice33740 · Answer

Bonjour gbinforme

la ré-actualisation de l'affichage du formulaire est déjà présente dans le code :  frmStatus.Repaint 

lorsque j'essaye de l'appliquer à l'intitulé (frmStatus.lblStatus.Repaint) j'ai une erreur de compilation « Membre de méthode ou de données introuvable » puisque la méthode ne s'applique pas aux Intitulés (label).

gbinforme · Answer

bonjour

Utilises plutôt une textbox pour afficher car effectivement un intitulé n'ai pas fait pour cela.

C'est effectivement sur la userform qu'il faut appliquer la fonction et si tu ne veux pas qu'elle ralentisse trop le processus, il ne faut modifier le contenu qu'au bout de plusieurs secondes (20-30) car l'affichage prend beaucoup de temps par rapport aux calculs.

Patrice33740 · Answer

Bonjour gbinforme, merci de ta réponse.

J'ai essayé de remplacer le Label par un TextBox mais ça ne change rien !

Par contre j'ai exploré la piste de mettre plusieurs .Repaint dans la boucle même si le contenu du UserForm ne change pas.

En plaçant un .Repaint dans la boucle Cell, ça fonctionne mais, bien sur, la durée de la macro devient excessivement longue !

En compromis, j'ai essayé d'utiliser la fonction Timer pour faire un rafraichissement toutes les secondes mais ça ne fonctionne pas, la fréquence n'est pas suffisante.

J'ai placé un compteur dans la boucle Cell et j'ai fait plusieurs essais avec une fréquence de rafraichissement de plus en plus grande: c'est pas satisfaisant.

Curieusement, j'ai trouvé un compromis acceptable en effectuant un .Repaint à chaque début de ligne (alors qu'avec la méthode précédente il en fallait plusieurs par ligne !) :

For Each cell In wsh.UsedRange
      If cell.Column = 1 Then frmStatus.Repaint
...

Next


Cela ne résout pas le problème mais simplement, ça évite qu'il se produise.

J'avais déjà constaté un phénomène similaire avec Application.StatusBar
lors de l'exploration de gros fichier : au bout d'un certain temps, l'affichage se fige, il n'est plus mis à jour à chaque changement alors que la macro continue de fonctionner.

Je pense qu'un processus extérieur à Excel reprend la main et ne la restitue pas correctement

Il y probablement une solution en utilisant l'API windows, mais je n'ai rien trouvé à ce sujet.

Patrice

Le Pingou · Answer

Bonjour,
Juste au passage, j'ai utilisé le code qui suit pour afficher  un message de patience (nom de la UserForm) dans une procédure assez longue pour l'envoi de courriel et cela marche bien.
Le code :

Application.Cursor = xlWait 'affiche le sablier
Patience.Show vbModeless     'affiche la UserForm mais continu le traitement
Patience.Repaint     'raffraichit le contenu affiché sinon on a une boite blanche vide

` ****
` Ici ma  procédure de traitement [ClientAdresseCourriel clofpro, clsage, nbli, clbcmde, connecte]
`****
Workbooks(clsage).Close ` ferme le classeur d'adresse à traiter
Unload Patience        ' fermer la UserForm
Application.Cursor = xlDefault  'remet le curseur par défaut

Patrice33740 · Answer

Bonjour Le Pingou et gbinforme,

Merci de vous intéresser à mon problème.

J'ai déclaré mon UserForm non modal avec VBE en mettant directement ShowModal sur False, or j'ai vu en utilisant l'explorateur d'objet que ShowModal n'apparait pas dans les propriétés du UserForm (c'est la seule qui manque), le doute s'est donc installé et j'ai suivi le conseil de Le Pingou :

j'ai essayé frmStatus.Show vbModeless pour être plus sur, mais le problème persiste.

Quelques précisions :
- dans la boucle qui créé le problème, la fréquence de rafraichissement est  très légèrement supérieure à une seconde (mise en grève à la 6ème ou à la 7ème  feuille).
- j'utilise ce même UserForm dans d'autres procédures du module et il fonctionne très bien, mais la fréquence de rafraichissement est plus élevée (0,5 à 0.7 secondes).
- dans la boucle lorsque j'ajoute un Repaint à chaque ligne explorée (sans changement du userform) le userform fonctionne correctement mais le temps d'exécution ...
- je suis convaincu que la taille du fichier exploré influe sur le comportement de l'affichage de l'application, mais ce n'est qu'une conviction. 
- le fichier exploré n'est pas vraiment énorme : 9 Mo contenant principalement des données, des formules et quelques graphiques Excel (moins d'une quinzaine), aucun objet extérieur, aucune image, des milliers de liens hypertextes mais tous à destination de cellules internes au fichier. 

Cordialement
Patrice

Patrice33740 · Answer

Bonjour gbinforme,

j'ai essayé de rafraichir toutes les 10, 5, 2 lignes mais ce n'est pas suffisant, ça ne fonctionne qu'avec 1 ligne (ou moins), de plus les Repaint à chaque ligne sont effectués alors que la valeur ne change pas (elle change à chaque feuille uniquement).

Au début du code j'ai un 

  Application.ScreenUpdating = False
La quantité de mémoire est largement suffisante pour éviter le swap.

Patrice33740 · Answer

Re bonjour

Si je ne met pas ce Repaint, sans intérêt théorique, le Userform se met en grève au bout de 6 secondes ! 

C'est là qu'est mon problème.

Cordialement
Patrice

Patrice33740 · Answer

Merci à tous,

la solution de Mytå fonctionne bien, j'ai placé le DoEvents dans la boucle juste avant de modifier le contrôle: 

For Each wsh In wbkCible.Worksheets
    DoEvents
    frmStatus.lblStatus.Caption = wsh.Name
    frmStatus.Repaint
    ....

La macro retrouve sa vélocité normale.

Je met le poste en résolu

Patrice