Optimiser le rafraichissement d'une user form

Version imprimable

10/12/2012, 15h03
andy.spit

Optimiser le rafraichissement d'une user form

Rebonjour,
Encore une petite question concernant une macro.
Lors d'un tri d'une centaine de cellules, je fais du autofit sur la hauteur des cellules. Cela prend un certain temps.
J'ai ajouté, pour faire patienter l'utilisateur, un userform avec une barre de progression de la macro (en %).
Mon souci c'est que pendant le tri, la userform ne se rafraichit pas et reste "blanc" (avec excel ne répond pas en haut).

Est ce qu'il y a moyen d'optimiser le rafraichissement ?

merci d'avance
10/12/2012, 15h11
jfontaine
Bonjour,

La gestion de l'affichage ralenti le code. On a plutôt tendance à désactiver le rafraîchissement d'écran et les calculs quand on exécute une macro
Code:

1 2 3 4 5 6 7 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ... CODE MACRO Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic
10/12/2012, 15h13
andy.spit

à quoi correspond : application.calculation ?

edit : à éviter qu'EXCEL ne recalcule toutes les cellules à chaque ligne de code

cependant, malgré le screenupdating = false, je vois mes cellules changer de hauteur une par une, est ce normal ?
10/12/2012, 15h18
jfontaine

Non pas normal, regardes si ton code contient une ligne de raffraichissement (désactive la userform)
10/12/2012, 15h24
Theze
Bonjour,

Déjà, l'AutoFit il ne faut pas le faire au fur et à mesure pour chaque ligne mais une fois le traitement de ta plage fini, du genre :
Code:

1 2 Plage.Rows.AutoFit
Hervé.

@These : je ne savais pas, je vais reprendre mes lignes de code pour faire du autofit sur une plage entière
Code:

1 2 3 4 For a = 3 To i - 1 Step 1 Workbooks("maintenance").Worksheets("signalements").Rows(a & ":" & a).EntireRow.AutoFit If Workbooks("maintenance").Worksheets("signalements").Rows(a).EntireRow.RowHeight < 30 Then Workbooks("maintenance").Worksheets("signalements").Rows(a).EntireRow.RowHeight = 30 Next
@jfontaine : je regarde si j'ai un screen updating qui traine mais je ne pense pas

10/12/2012, 17h06
andy.spit

super pour le autofit sur une plage et non ligne par ligne !

par contre, je test une par une la hauteur des lignes. Si elle est inférieur à 30, je la corrige à 30. t c'est ca qui prend du temps, malgré le screenupdating = false
10/12/2012, 17h12
patricktoulon

Bonjour
Tu n'a qu'as régler le problème de la même manière.
Puisque si c'est plus petit que 30 tu mets à 30
pourquoi fais tu le test? Cà n'est pas la peine,fais le directement.
S'il y en a qui sont déjà à 30 çà ne changera rien.
Il faut appliquer le rows height à ta plage tout simplement, du moins c'est mon avis

Edit :
J'ai lu plus haut que tu avais ton userform qui devenait tout blanc
avec "Excel ne répond plus ".
C'est dû à une grande utilisation de la mémoire, il y certainement une erreur quelque part qui te fait mouliner l'ordi pour rien.

Au plaisir
10/12/2012, 17h22
andy.spit

Le autofit me permet d'agrandir la hauteur à 60 ou 70, mais malheureusement il diminue la hauteur des cellules à 15 quand il n'y a qu'une seule ligne.
Pour plus de clarté, je suis donc obligé de remonter ces hauteur à 30.
D'où le test sur chaque ligne. et ça prend du temps.
A moins qu'il n'y ai une option du autofit pour régler la hauteur au minima à 30.
10/12/2012, 17h27
patricktoulon
J'aimerais voir le code de cette macro en entier pour voir.
Je suis sûr qu'il y a possibilité de faire plus léger.

Si je comprends bien ton autofit agrandi tes cellule en hauteur.
Mais quelques unes non car le texte n'est pas assez grand.
Alors ta solution est de les mettre à 30 pour avoir un peu de marge (visuelle)

Après ton autofit
Adapte l'index de ligne à ton cas
Code:

1 2 Rows("1:10").RowHeight = Rows("1:10").RowHeight + 30 / 2
tu auras à mon avis une marge visuelle suffisante

Au plaisir
10/12/2012, 17h39
andy.spit

c'est sur sur même :)
mais je suis pas un spécialiste en la matière alors j'ai fais un peu comme j'ai pu en ajoutant des lignes au fur et à mesures de mes besoins

D'accord je comprend mieux le ralentissement.

Il y a la possibilité de supprimer toute tes boucle do loop (il y en a bien trop).
Tu détermines la dernière ligne utilisée ou vide avec ces boucles.
Il y a un autre moyen de déterminer plus vite la dernière cellule utilisée.
Exemple avec référence la colonne "a"

Code:

1
2
3
 
dim lastligne as long
lastligne=sheets(lenomdeton sheets).range("a"&rows.count).end(xlup).row

exemple avec la plage utilisée
dim lastligne as long
lastligne=sheets(lenomdeton sheets).usedrange.rows.count

d'autant plus que la variable i te sert visiblement pour plusieurs opérations sur des colonnes différentes ce qui implique que tu doives te servir du "usedrange.rows.count"

Donc un exemple d'une partie de ton code remanié. A la place de:

Code:

1
2
3
4
5
6
 
Dim i As Integer
i = 2
Do
i = i + 1
Loop Until Worksheets("signalements").Cells(i, 1) = Empty

tu peux mettre

Code:

1
2
3
 
Dim i As Long
i = Sheets(1).Range("h" & Rows.Count).End(xlUp).Row 'te donne la dernière ligne utilisée sur la feuille en colonne "h"

'ou au cas où tes colonnes n'ont pas le même nombre de lignes on va choisir la plus grande avec "usedrange"

Code:

1
2
3
4
 
dim i as long 
i = Sheets(1).UsedRange.Rows.Count
'plus besoins de faire 36 boucles pour trouver la dernière ligne d'une plage

donc par la suite

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Worksheets("signalements").Sort.SortFields.clear
Worksheets("signalements").Sort.SortFields.Add Key:=Range( _
    "H3:H" & i - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
Worksheets("signalements").Sort.SortFields.Add Key:=Range( _
    "C3:C" & i - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With Worksheets("signalements").Sort
    .SetRange Range("A2:M" & i - 1)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Tu vois ce que je veux dire ?

J'ai regardé aussi le code remplissage des listbox et combobox
Voilà ce que çà donne:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
tablo1 = Sheets("data").Range("a3:" & Sheets("data").Range("a" & Rows.Count).End(xlUp).Row)
            signalement.ComboBoxnomcentrale.List = tablo1
 
 'ajout de la liste des intervenants dans la combobox
tablo2 = Sheets("data").Range("j3:" & Sheets("data").Range("j" & Rows.Count).End(xlUp).Row)
            signalement.ComboBoxintervenant.List = tablo2
 
 'ajout de la liste des priorités dans la combobox
tablo3 = Sheets("data").Range("K3:" & Sheets("data").Range("K" & Rows.Count).End(xlUp).Row)
            signalement.ComboBoxpriorite.List = tablo3
 
'ajout de la liste des codes erreurs dans la combobox
tablo4 = Sheets("data").Range("c3:" & Sheets("data").Range("c" & Rows.Count).End(xlUp).Row)
            signalement.ComboBoxcode.List = tablo4
 
 'ajout de la liste des signalement onduleurs dans la combobox
tablo5 = Sheets("data").Range("L3:" & Sheets("data").Range("l" & Rows.Count).End(xlUp).Row)
            signalement.ComboBoxsignond.list = tablo5

A la place de

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
'ajout de la liste des centrales dans la combobox
i = 2
                Do
                i = i + 1
                Loop Until (Sheets("data").Cells(i, 1) = Empty)
 
            signalement.ComboBoxnomcentrale.RowSource = "Data!A3:A" & i - 1
 
'ajout de la liste des intervenants dans la combobox
i = 2
                Do
                i = i + 1
                Loop Until (Sheets("data").Cells(i, 10) = Empty)
 
            signalement.ComboBoxintervenant.RowSource = "Data!J3:J" & i - 1
 
'ajout de la liste des priorités dans la combobox
i = 2
                Do
                i = i + 1
                Loop Until (Sheets("data").Cells(i, 11) = Empty)
 
            signalement.ComboBoxpriorite.RowSource = "Data!K3:K" & i - 1
 
 
'ajout de la liste des codes erreurs dans la combobox
i = 2
                Do
                i = i + 1
                Loop Until (Sheets("data").Cells(i, 3) = Empty)
 
            signalement.ComboBoxcode.RowSource = "Data!C3:C" & i - 1
 
 
'ajout de la liste des signalement onduleurs dans la combobox
i = 2
                Do
                i = i + 1
                Loop Until (Sheets("data").Cells(i, 12) = Empty)
 
            signalement.ComboBoxsignond.RowSource = "Data!L3:L" & i - 1

Au plaisir

10/12/2012, 18h47
KekeStreet

Bonjour,

Qu'entends tu par "ça prend du temps" ? en terme de secondes, ça donne quoi ? et quelle quantité dois tu traiter ? en nombre de lignes ?

Avec le code que tu donnes (du style "test une par une la hauteur des lignes") mais avec le ScreenUpdating = false, l'autofit prend 0,1s, 10000 lignes se checkent en 0,30s. Compte 0,0001s de plus par ligne qui dois être réajustée si en dessous de la valeur que tu veux.

Je trouve que c'est suffisant puisque ce code n'est en théorie exécuté qu'une seule fois de temps en temps si j'ai bien compris ? mais à voir si c'est toujours valable quand tu nous aura donné un peu plus de précisions sur tes exigences :ccool: Et d'ailleurs combien de temps exactement ça prend sur ton fichier et ton pc ? (en mettant des balises [T = Timer] au début puis [msgbox Timer - T] à la fin par exemple)

De plus les résultat chronométrés que je viens de donner c'est sur mon vieux PC portable personnel qui a déjà 7 ans. Très facile de faire plus rapide.
Et chez moi le "ScreenUpdating" ça divise le temps par 2 à 4 selon que le cpu soit bouillant ou pas, donc à ne pas négliger.

EDIT : allons bon ça a écrit et je n'ai pas rafraichit.. bon en stand by pour le moment.
Oki patricktoulon, désolé j'avais édité.
Je reste tout de même curieux quand aux performances exigées, ça donnera une bonne idée de ce qu'il faut mettre en œuvre.
10/12/2012, 18h58
patricktoulon

re

Bonjour KekeStreet....

Cà dépend effectivement du nombre de lignes à modifier.
Disons que pour le moment je n'ai pas trop de précision sur le contexte.
Il est vrai que dans son code il y a une boucle do loop pour chaque liste
qui réduit les performances surtout qu'il n'y a pas un seul "doEvents"pour laisser tourner le reste du code.
Alors j'ai tranché.
Au plaisir
11/12/2012, 08h43
andy.spit

merci beaucoup je vais m'occuper d'optimiser le code ce matin
en terme de temps, ça prend 10 secondes pour une 50aine de ligne environ.
j'essaye de vous donner plus de précisions dans la matinée,
je prends mon temps pour remplacer les boucles et je vous tiens au jus.
merci beaucoup en tout cas,
a tout de suite,
11/12/2012, 10h53
andy.spit

Super merci beaucoup, j'ai enlevé toutes les boucles de mon vba.
sauf pour les combobox, j'ai une erreur (application non définie...)
faut il déclarer qqchose ?