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

Signaler
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
-
 EGM -
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 ?



10 réponses

Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
851
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å
2
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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
2
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
851
Re le forum

Au plaisir de t'aider de nouveau sur une prochaine discussion.

Mytå
Merci beaucoup, mon problème d'affichage a été résolu grâce à DoEvents (j'avais le sablier, mais pas le userform à jour).
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 357
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.
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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).


Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 357
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.
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
Je remonte, entre temps j''ai essayé d'ajouter :

Declare Function UpdateWindow Lib "user32" (ByVal hwnd As Long) As Long

et dans la boucle ou se trouve le frmStatus.Repaint :

UpdateWindow Application.hwnd

Mais ça ne change rien.
Messages postés
9890
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
27 février 2021
1 129
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

Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 357
Bonjour Le Pingou,

Je suppose que dans ta procédure tu modifies le contenu de "patience" et que tu vois le résultat ?

J'ai créé à peu près le même procédé pour générer une application internet HTML qui comporte de nombreuses pages (que je liste au fur et à mesure) à partir d'une base de données et cela fonctionne comme prévu.

Aussi je suis surpris que Patrice rencontre ce problème avec le repaint qui se met en grève.
Messages postés
9890
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
27 février 2021
1 129
Bonsoir gbinforme,
Oui c'est bien comme cela.
Mon intervention était juste pour montrer ma solution, qui en regardant bien est la même, sauf l'utilisation de la constante [vbModeless].
Bon dimanche.
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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

Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 357
bonjour à tous,

La durée entre 2 repaint n'a, à mon avis, pas beaucoup d'importance mais il me semble qu'il n'est utile de l'utiliser que lorsque la valeur à afficher change.

Il me semble que la durée "utile" soit plutôt supérieure à 10-15 secondes car en deçà, l'on ralenti la macro et la réaction de l'utilisateur n'est pas de l'ordre de la seconde : seul Usain Bolt fait plus de 10 m dans ce temps !

La taille du fichier n'est pas seule en cause car elle a de l'importance surtout en fonction de la mémoire disponible : s'il en manque et qu'il faille recourir au swap, le temps grimpe.

As-tu inhibé le rafraichissement écran ?
Essaies de mettre un repaint toutes les 10 lignes pour voir.
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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.


Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 357
re bonjour,

Je ne vois pas l'intérêt du repaint si tu ne changes pas de valeur, à moins de ralentir la macro.

Pour que l'utilisateur voit que cela fonctionne, il faut que les valeurs affichées changent sinon rien ne se passe : ton problème ne viendrait-il pas de là ?
Messages postés
8439
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
26 février 2021
1 612
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