Discussion :

[toteuf]

Bonjour,

Voilà comme beaucoup, j'ai une macro qui tourne est qui dure super longtemps (22 000 lignes à comparer) sur deux feuilles.
J'ai donc souhaité mettre une barre de progression.

Donc j'ai créé un userform que j'ai dû mettre en affichage non-modal (car sinon la suite de la macro ne tournait pas).

Jusque là tout va bien. Mais je voudrais faire un peu plus. La macro met quand même un bon bout de temps (car je dois colorier, copier, et retourner une valeur en fin de ligne s'il y a eu des modifications, ou non etc...).

Comme elle met beaucoup de temps (je dirais une 20 aine de minutes) je souhaiterais pouvoir la mettre en pause à l'aide de bouton présent au niveau de la barre de progression (histoire de pouvoir bosser sur un autre truc et relancer la macro pour la finir).

Bref les boutons sont réalisés mais j'ai remarqué que je ne peux cliquer dessus. Donc déjà ça me pause problème et ensuite j'aimerais savoir comment faire pour faire une pause (j'ai lu sur un autre forum la commande PAUSE nomdelamacro mais ça n'a pas fonctionné), d'ailleurs je ferai sûrement un switch d'un bouton reprendre et Pause pour géré la pause et la reprise.

Donc voila comment garder le focus sur un userform en mode non-modal, et quelle fonction permet de mettre en pause et de reprendre.

Merci

[jfontaine]

Bonjour,

Pour que ta macro s'execute plus rapidement, tu peux désactiver :

les calculs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Application.Calculation = xlCalculationManual
... Code
Application.Calculation = xlCalculationAutomatic

la mise a jour de l'affichage

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.ScreenUpdating = False
... Code
Application.ScreenUpdating = True

Si le code est toujours trop long et que tu souhaites pouvoir insere la fonction DoEvents dans ta boucle

[toteuf]

Pour la MAJ de l'affichage , je l'emploi déjà actuelement.

Peut-tu m'expliquer :
Application.Calculation

Qu'est ce que cela réalise exactement sur le traitement de la macro? (bon je sais que je pourrais recherché mais c'est plus l'heure de bosser pour moi )

[Invité]

Bonsoir,

Le code que vous donne jfontaine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Application.Calculation = xlCalculationManual
... Code
Application.Calculation = xlCalculationAutomatic

correspond à ce que vous pourriez faire manuellement sous Excel avec "Outils", "Options" puis onglet "Calcul" et cocher sous "Mode de calcul" la case "Calcul sur ordre".

C'est-à-dire que si votre feuille comporte de nombreuses formules, la mise à jour des calculs ne se fait qu'à la fin du traitement au lieu de se faire à chaque modification d'une cellule ou d'une plage de cellules ce qui peut permettre une amélioration du temps de traitement.

[laetitia]

bonsoir toteuf jfontaine l'ami jacques le forum
en general pour optimiser je mets ce code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

option explicit

en debut de module
declaration des variables au plus juste!!!
tu utilises une barre de progression facteur de ralentissement!!!!
de plus il faudrait voir ton code pour voir si on peut l'optimiser
exemple sur des boucles utiliser un "tablo" intermediere beaucoup plus rapide que les boucles classiques ect...
en general deja pour optimiser la vitesse je l'ecris comme cela mais avec ta barre de progression ca va poser des pb..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 Sub es()
Dim x As Integer, r As Byte, c As Long 'pour exemple
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
'ton code
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub

[toteuf]

Pour mes feuilles, il n'y a aucune formule dans mes cellules.

En fait j'ai 22 000 lignes pour 80 colonnes à comparer intégralement.
Mon parc informatique fait 22 000 lignes, et dans ce parc j'ai 80 colonnes un pour l'identifiant matériel, et les autres pour des informations diverses (localisation, type de bien etc...) Je ne maîtrise absolument pas ces données qui me sont fournie au format excel. J'ai donc 2 feuilles qui contiennent chacun à peu prêt les même données mais ou je dois détecter des changements (un bien affecté à un autre utilisateur, sur un autre domaine etc...).
Objectif : pour les lignes identiques remplir la dernière colonne par OK, sinon par Modifier. Les cellules restant vide étant des nouveaux biens.
Sur un autre feuille les biens qui étaient affecté mais qui ne le sont plus (feuille TEMP).

Le but de cette comparaison est qu'en filtrant sur la dernière colonne sur la valeur modifier on puisse avoir toutes les lignes modifier et donc après parcourir chacune d'entre elle et voir quelle cellule est différente (pour ce faire j'ai colorié en orange celle qui était différente). Il peut y avoir des lignes qui sont entièrement différents et d'autre qui sont différentes juste pour une cellule


VOici mon code (du moins une parti, car j'ai une autre macro qui fusionne deux fichiers pour en tirer les feuilles, le nom des feuilles est stocker sur la feuille mode d'emploi en M2 et N2)
Voila donc vite fait mon code (sûrement à améliorer):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
 
Sub Comparaison()
 
    '****************************** Déclaration des variables **********************************
    Dim i As Integer
    Dim j As Integer
    Dim z As Integer
    Dim Feuil1 As String
    Dim Feuil2 As String
    Dim ta(100) As String
    Dim temp As Integer
    Dim l As Integer
 
    Application.ScreenUpdating = False
    UserForm1.Show vbModeless
    UserForm1.Visible
 
    Windows("Comparaison des fichiers PARCS.xls").Activate
    Sheets("Mode d'emploi").Select
    Feuil1 = Range("m2") 
    Feuil2 = Range("n2") 
    '****************************** Vidage des couleurs en place et suppression contenu de la
    '*******************************colonne bk
 
    Sheets(Feuil1).Select
        Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Cells.Select
    Range("B1").Activate
    Selection.Interior.ColorIndex = xlNone
    Sheets(Feuil2).Select
    Cells.Select
    Range("B1").Activate
    Selection.Interior.ColorIndex = xlNone
    Columns("bo:bo").Select
    Selection.ClearContents
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
 
    '****************************** Début boucle sur la 1er feuille *****************************
    temp = 1
    For i = 2 To 25000
    Sheets(Feuil1).Select
    UserForm1.abelProgress.Value = i / 2500 * 10
    UserForm1.Pourcentage.Caption = i / 2500 * 10 & "%"
    UserForm1.Repaint
    '****************************** Teste fin de feuil1 *****************************************
    If Range("a" & i) = "" Then
    i = 25001
    End
    Else
 
    '****************************** Copie de la ligne de la feuil1 dans ta **********************
    For j = 1 To 67
    ta(j) = Cells(i, j)
    Next j
 
    '****************************** Début boucle sur 2ème feuille *******************************
     Sheets(Feuil2).Select
    For j = 2 To 25000
 
    '****************************** Teste fin de feuil2 *****************************************
    If Range("a" & j) = "" Then
    j = 25001
    Else
 
    '****************************** PI trouvé ***************************************************
    If Range("a" & j) = ta(1) Then
    flag = 0
    l = 2
    '****************************** Parcours de la ligne ****************************************
    For z = 2 To 100
 
    '****************************** Différence entre cellule feuil1 et cellule feuil2 ***********
        If Not Cells(j, z) = ta(z) Then
    '****************************** Coloriage en orange *****************************************
        Cells(j, z).Select
        With Selection.Interior
        .ColorIndex = 45
        .Pattern = xlSolid
    End With
    l = l + 1
    flag = 1
 
    End If
    Next
 
    '****************************** Si les lignes sont bien conformes met "Ok" et colorie en vert
    'dans la colonne K et l'indice de la ligne (à modifier selon besoin) sinon "Erreur" et en rouge
 
    If flag = 1 Then
    Range("bo" & j) = "modification"
    Range("bo" & j).Select
        With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
    Else
    Range("bo" & j) = "Ok"
    Range("bo" & j).Select
        With Selection.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
    End With
    End If
    j = 25000
 
       End If
       End If
 
    Next
 
    '****************************** Sortie de la boucle sans trouvé de PI dans la feuil2 ********
    If j = 25002 Then
    If temp = 1 Then
 
    Sheets.Add
    ActiveSheet.Select
    ActiveSheet.Name = "Temp"
 
    End If
    Sheets(Feuil1).Select
    Rows(i).Select
    Selection.Copy
    Sheets("temp").Select
    Range("a" & temp).Select
    ActiveSheet.Paste
    temp = temp + 1
    End If
 
    End If
    Next
    Application.ScreenUpdating = True
End Sub
 
 
 
Sub UpdateProgressBar(PctDone As Single)
    With UserForm1
 
        ' Update the Caption property of the Frame control.
        .FrameProgress.Caption = Format(PctDone, "0%")
 
        ' Widen the Label control.
        .LabelProgress.Width = PctDone * _
            (.FrameProgress.Width - 10)
    End With
 
    ' The DoEvents allows the UserForm to update.
    DoEvents
End Sub

[toteuf]

Je te remercie concernant ces méthodes de programmation vbA.
Concernant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sheets(Feuil1).Select
        Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
   'Pourquoi Cells.select ? 
   Cells.Select
    Range("B1").Activate
    Selection.Interior.ColorIndex = xlNone
    Sheets(Feuil2).Select
'Pourquoi Cells.select ?     
    Cells.Select
    Range("B1").Activate
    Selection.Interior.ColorIndex = xlNone
    Columns("bo:bo").Select
    Selection.ClearContents

Je pense que je voulais vider les couleurs de tous le tableau, je ne sais pas pourquoi j'ai mis après Range("B1").Activate.

Après bon tous ces petites modifs sont réalisés hors de la boucle, en terme de temps je ne gagne rien du tout.
Puis surtout le fond de la question c'est comment faire tourner la macro avec avec une userform qui est une barre de progression et comment faire en sorte d'avoir la main sur ce userform afin de pouvoir cliquer sur des boutons du style pause/reprise afin de mettre en pause toute la macro ou de la reprendre, car celle-ci est très très longue (alors peut-être qu'il y a moyen de la raccourcir, mais le volume de donnée à traiter est sufisament conséquent pour que même une fonction optimisé ne la traite pas en moin de 10 minute ce qui est lent et qui prend toute les ressources de l'ordinateurs).

[ucfoutu]

Bonjour,

et comment faire en sorte d'avoir la main sur ce userform afin de pouvoir cliquer sur des boutons du style pause/reprise afin de mettre en pause toute la macro ou de la reprendre

Attention : peut s'avérer une manoeuvre assez dangereuse...
Faire cela pourquoi ? Pour permettre à l'utilisateur de revenir à sa feuille s'il le veut et y travailler ?
Quid s'il travaille sur des cellules traitées par les instructions de la macro interrompue ?
Pour éviter dans un tel cas de figure une catastrophe, il faudrait reprendre la macro à son début ! et je ne vois alors pas l'intérêt (en matière de gain de temps).
Et ce et surtout : même en prenant, au sein de la macro, un repère (cellule venant par exemple d'être traitée), pour la réexécuter depuis ce "repère"...

[toteuf]

C'est vrai que la question mérite d'être posé. Et la réponse est qu'il n'y aura pas de modif sur les feuilles. La pause se veut exclusivement si la personne a besoin d'ouvrir un autre fichier excel, ou de travailler sans voir son poste ralentir.

Il n'y a pas de modification par l'utilisateur des deux feuilles. Ce sont des extractions d'un outil, et cette comparaison sert à reperé pour modification ultérieure du référenciel (dans l'outil qui gère ces extractions). Bon après il peut y avoir des modifs manuel, mais ce ne peut être que par erreur. Le but de ces fichiers n'est pas de le modifier manuelement, à la rigueur je pourrais le verrouillé lors de la pause pour éviter tous problème.

[ucfoutu]

Bon après il peut y avoir des modifs manuel, mais ce ne peut être que par erreur. Le but de ces fichiers n'est pas de le modifier manuelement, à la rigueur je pourrais le verrouillé lors de la pause pour éviter tous problème.

La vie étant faite de devoirs et de désobeissances, ===>> oui : à verrouiller tant que tout n'a pas été traité.

Pour le reste : ce n'est qu'un mécanisme à mettre en oeuvre :
- mémorise dans une cellule (ou un fichier texte) le Range à traiter (de sorte à la retrouver y comporis en cas de fermeture de l'appli
- à l'interruption (car c'est carrément une interruption, qu'il est alors conseillé de faire) : modification du repère (Range non encore traité et donc à traiter)
- à la reprise : ne traiter que ce Range

[toteuf]

Oui mais encore faut-il que je puisse cliquer sur les boutons de mon userform. lol

Je sais mettre en pause et reprendre. Vérrouillé les feuilles.

Mais je n'arrive pas à cliquer sur mon userform.

L'affichage étant non modal (ou modal je confond les deux) pour que la macro tourne derrière, je n'ai donc pas la main donc sur mon userform. Ce qu iest un problème pour cliquer sur les boutons