Temps d'exécution trop long

**pomdeterfrite** · 23/05/2012, 10h24

Hello tout le monde,

J'ai un souci avec mon tableau excel qui à l'enregistrement, au choix de l'utilisateur, peut copier vers un autre tableau excel. Il y a 6 colonnes à copier sur une boucle de 1700 lignes, et le temps d'exécution de cette boucle est très long ... environ 1h !!

En mode pas à pas, je vois que ce qui prend du temps ce sont les lignes suivantes, qui collent les données copiées du premier fichier :

Code :

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

1
2
3
4
5
6
7
8
 
Range("B" & k).Value = HC
Range("C" & k).Value = Titre
Range("D" & k).Value = Ref
Range("E" & k).Value = Issue
Range("F" & k).Value = Autorite
Range("G" & k).Value = Status
Range("H" & k).Value = Revision

Et voici le code complet ... Merci pour votre aide

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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
 
 
Option Explicit
 
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Dim Rep As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim Pays As String
Dim Version As String
Dim HC As String
Dim Ref As String
Dim Issue As String
Dim Titre As String
Dim Autorite As String
Dim Status As String
Dim Revision As String
Dim lWorkbook As Workbook
Dim lFound As Boolean
Dim lFoundCH As Boolean
lFound = False
lFoundCH = False
Dim NbLignesDashbd As Integer
Dim NbLignesTdB As Integer
 
 
j = 5
 
Rep = MsgBox("Save to TdB VTC ?", vbYesNo + vbQuestion, "Save")
    Select Case Rep
        Case vbYes
 
        For Each lWorkbook In Workbooks
            If lWorkbook.Name = "Change classification.xlsm" Then
                lFoundCH = True
                Exit For
            End If
        Next
 
        If lFoundCH = True Then
            MsgBox ("Enregistrement vers TdB VTC impossible tant que Change Classification.xls est ouvert")
            GoTo FinEnr
        End If
 
 
    On Error Resume Next
    Workbooks("VTC Fiches clients.xls").Activate
    If Err <> 0 Then
 
 
 
    Windows("Dashboard.xlsm").Activate
    Sheets("Dashboard").Select
    NbLignesDashbd = Range("C1").Value
 
    For Each lWorkbook In Workbooks
        If lWorkbook.Name = "TdB VTC_new.xlsm" Then
            lFound = True
            Exit For
        End If
    Next
 
    If lFound = True Then
        MsgBox ("Fermer le TdB VTC pour l'enregistrement")
        GoTo FinEnr
    End If
 
 
    Application.EnableEvents = False
    Workbooks.Open Filename:= _
    "\\Giono\Navig\CONSULT\Commercialised helicopters\Certification\certification status in the world\TdB VTC_new.xlsm"
    Application.EnableEvents = True
 
    Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
    NbLignesTdB = Range("I3").Value
 
 
    Rows("5:5777").Select
    Selection.Delete
 
'    Application.ScreenUpdating = False
'    Application.Visible = False
'
'    UserForm1.Show False
 
 
For i = 5 To NbLignesDashbd
 
'rafraichissement de la userform de progression de l'enregistrement
        SleepVBA
        DoEvents
        UserForm1.LabelProgress.Width = (i / NbLignesDashbd) * 288
 
 
        Windows("Dashboard.xlsm").Activate
        Worksheets("Dashboard").Select
 
 
If Range("A" & i).Value = "Major change" Then
 
            Pays = Range("C" & i).Value
            HC = Range("D" & i).Value
            Ref = Range("F" & i).Value
            Issue = Range("G" & i).Value
            Titre = Range("M" & i).Value
            Autorite = Range("B" & i).Value
            Status = Range("N" & i).Value
            Revision = Range("I" & i).Value
 
 
            Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
 
            Range("A" & j).Value = Pays
 
            If Pays = "Pays EASA" Then
                k = j
                    For m = 2 To 34
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("A" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 33
                GoTo Fin
            End If
 
            If Pays = "Pays IAC" Then
                k = j
                    For m = 2 To 10
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("B" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 9
                GoTo Fin
            End If
 
            If Pays = "EASA Basis" Then
                If HC = "AS350B3" Then
                k = j
                    For m = 2 To 38
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("C" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 37
                GoTo Fin
                End If
            End If
 
            If Pays = "FAA Basis" Then
                If HC = "AS350B3" Then
                k = j
                    For m = 2 To 6
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("D" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 5
                GoTo Fin
                End If
            End If
 
 
 
            If Pays = "EASA Basis" Then
                If HC = "AS350B2" Then
                k = j
                    For m = 2 To 35
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("E" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 9
                GoTo Fin
                End If
            End If
 
 
            If Pays = "FAA Basis" Then
                If HC = "AS350B2" Then
                k = j
                    For m = 2 To 4
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("F" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 3
                GoTo Fin
                End If
            End If
 
 
            If Pays = "EASA Basis" Then
                If HC = "EC130B4" Then
                k = j
                    For m = 2 To 20
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("G" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 19
                GoTo Fin
                End If
            End If
 
 
            If Pays = "FAA Basis" Then
                If HC = "EC130B4" Then
                k = j
                    For m = 2 To 3
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("H" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 2
                GoTo Fin
                End If
            End If
 
 
 
            If Pays = "EASA Basis" Then
                If HC = "AS355NP" Then
                k = j
                    For m = 2 To 8
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("I" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 7
                GoTo Fin
                End If
            End If
 
            If Pays = "FAA Basis" Then
                If HC = "AS355NP" Then
                k = j
                    For m = 2 To 2
                        Range("A" & k).Value = Workbooks("Dashboard.xlsm").Worksheets("Pays").Range("J" & m).Value
                        Range("B" & k).Value = HC
                        Range("C" & k).Value = Titre
                        Range("D" & k).Value = Ref
                        Range("E" & k).Value = Issue
                        Range("F" & k).Value = Autorite
                        Range("G" & k).Value = Status
                        Range("H" & k).Value = Revision
                        k = k + 1
                    Next m
                j = j + 1
                GoTo Fin
                End If
            End If
 
 
            Cells(j, 2) = HC
            Cells(j, 3) = Titre
            Cells(j, 4) = Ref
            Cells(j, 5) = Issue
            Cells(j, 6) = Autorite
            Cells(j, 7) = Status
            Cells(j, 8) = Revision
 
            j = j + 1
 
Fin:
 
 
End If
 
Next i
 
 
        Unload UserForm1
 
        Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
 
        Range("A1").Select
        ActiveWorkbook.Save
        ActiveWorkbook.Close
 
        Windows("Dashboard.xlsm").Activate
        Range("A1").Select
        GoTo FinVTC
    Else:
        Windows("Dashboard.xlsm").Activate
        Range("A1").Select
        GoTo FinVTC
 
    End If
 
FinVTC:
 
            Case vbNo
        End Select
 
FinEnr:
    Application.ScreenUpdating = True
    Application.Visible = True
 
 
End Sub

**Mr.Mof** · 23/05/2012, 10h35

Bonjour,

Pourquoi ne pas utiliser une autre méthode qu'une boucle comme par exemple la copie d'une plage de données:

Code :

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

1
2
3
4
With Sheets("Feuil1")
.Range(.Cells(1, 1), .Cells(6, 5)).Copy _
Sheets("Feuil2").Range("B5")
End With

Cordialement.

**Jean-Philippe André** · 23/05/2012, 10h53

Salut,

la gestion du

Code :

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

1
2
3
4
if .... Then
...
GoTo Fin
End If

n'est pas "bien conçu", prefere une structure de code avec

Code :

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

1
2
3
4
Select Case
Case 1
Case 2
End Select

L'utilisation du simple

Code :

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

Range()

me gène, car on ne sait pas dans quelle feuille tu travailles. Surtout avec le

Code :

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

DoEvents

que tu lances en début de code, l'utilisateur se balade dans un autre classeur ca peut poser des problèmes.

Code :

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

If Pays = "EASA Basis" Then

et

Code :

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

If Pays = "FAA Basis" Then

Le code est long car pas du tout optimisé je pense

Regroupe tes tests tu vas déjà te rendre compte que tu peux factoriser énormément de choses, puis tu pourras supprimer des étapes inutiles.
Tu fais plusieurs fois le test

**pomdeterfrite** · 23/05/2012, 15h04

J'ai suivi tes conseils et j'ai épuré un peu le code pour voir d'où ca pourrait encore venir... mais c'est toujours aussi long !

Voici le nouveau code, là je ne vois pas ce qui peut prendre autant de temps ... C'est quand meme simple comme boucle non ?

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
Option Explicit
 
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim HC As String
Dim Titre As String
Dim Ref As String
Dim Issue As String
Dim Autorite As String
Dim Status As String
Dim Revision As String
Dim k As Integer
Dim Pays As String
Dim Rep As Integer
Dim i As Integer
Dim j As Integer
Dim m As Integer
Dim lWorkbook As Workbook
Dim lFound As Boolean
Dim lFoundCH As Boolean
lFound = False
lFoundCH = False
Dim NbLignesDashbd As Integer
Dim NbLignesTdB As Integer
 
 
j = 5
 
 
    Application.EnableEvents = False
    Workbooks.Open Filename:= _
    "\\Giono\Navig\CONSULT\Commercialised helicopters\Certification\certification status in the world\TdB VTC_new.xlsm"
    Application.EnableEvents = True
 
    Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
    NbLignesTdB = Range("I3").Value
 
 
    Rows("5:5777").Select
    Selection.Delete
 
 
For i = 5 To NbLignesDashbd
 
        Windows("Dashboard.xlsm").Activate
        Worksheets("Dashboard").Select
 
 
Select Case Cells(i, 1)
 
    Case Is = "Major change"
        Pays = Range("C" & i).Value
        HC = Range("D" & i).Value
        Ref = Range("F" & i).Value
        Issue = Range("G" & i).Value
        Titre = Range("M" & i).Value
        Autorite = Range("B" & i).Value
        Status = Range("N" & i).Value
        Revision = Range("I" & i).Value
        Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
        Range("A" & j).Value = Pays
 
 
Range("B" & j).Value = HC
Range("C" & j).Value = Titre
Range("D" & j).Value = Ref
Range("E" & j).Value = Issue
Range("F" & j).Value = Autorite
Range("G" & j).Value = Status
Range("H" & j).Value = Revision
 
 
j = j + 1
 
End Select
 
Next i
 
 
End Sub

**ZebreLoup** · 23/05/2012, 16h49

Alors enlève déjà tous ces affreux "Select" et "Activate" et travaille avec des objets Worksheet.
Ensuite, ce qui est long, c'est de mettre les valeurs une par une. Le principe pour que ça aille vite, c'est de passer par un Variant qui sera un tableau. Je vais essayer de te faire un exemple.

J'ai fait ce petit code. Je n'ai pas pu tester forcément n'ayant pas les données, mais tu peux t'en inspirer :

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
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Dim HC As String
Dim Titre As String
Dim Ref As String
Dim Issue As String
Dim Autorite As String
Dim Status As String
Dim Revision As String
Dim Pays As String
Dim Rep As Integer
 
'On va commencer par déclarer la plage sur laquelle on travaille
Dim wbDash As Workbook
Dim wsDash As Worksheet
Dim nbLignesDash As Integer
Dim rgDash As Range
Dim vDash As Variant
 
Set wbDash = ThisWorkbook 'Je suppose que c'est le classeur qui lance la macro...
Set wsDash = wbDash.Worksheets("Dashboard")
 
nbLignesDash = wsDash.Range("A65000").End(xlUp).Row
 
Set rgDash = wsDash.Range(wsDash.Range("A5"), wsDash.Range("N" & nbLignesDash))
vDash = rgDash
 
'On créé le tableau cible
Dim vNew As Variant
Dim i, j As Integer
 
ReDim vNew(1 To UBound(vDash, 1), 1 To 8)
 
j = 1
 
For i = 1 To nbLignesDash - 4
    If vDash(i, 1) = "Major change" Then
 
        Pays = vDash(i, 3)
        HC = vDash(i, 4)
        Ref = vDash(i, 6)
        Issue = vDash(i, 7)
        Titre = vDash(i, 13)
        Autorite = vDash(i, 2)
        Status = vDash(i, 14)
        Revision = vDash(i, 9)
 
        vNew(j, 1) = Pays
        vNew(j, 2) = HC
        vNew(j, 3) = Titre
        vNew(j, 4) = Ref
        vNew(j, 5) = Issue
        vNew(j, 6) = Autorite
        vNew(j, 7) = Status
        vNew(j, 8) = Revision
 
        j = j + 1
 
    End If
Next i
 
'vNew est trop grand mais dans notre cas ce n'est pas grave
'Sinon, il aurait fallu créer un nouveau tableau de la même taille maintenant que l'on connait
'la nouvelle taille
 
Dim wbNew As Workbook
Dim wsNew As Worksheet
Dim rgNew As Range
 
Application.EnableEvents = False
Set wbNew = Workbooks.Open(Filename:="\\Giono\Navig\CONSULT\Commercialised helicopters" _
    & "\Certification\certification status in the world\TdB VTC_new.xlsm")
Application.EnableEvents = True
 
Set wsNew = wbNew.Worksheets("Major Changes")
 
wsNew.Rows("5:5777").Delete
 
Set rgNew = wsNew.Range(wsNew.Range("A5"), wsNew.Range("H" & nbLignesDash))
rgNew = vNew
 
End Sub

**patricktoulon** · 23/05/2012, 18h03

bonjour

je n'est pas bien regarder les propositions que tu a eu mais déjà quand je regarde ça:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 For Each lWorkbook In Workbooks
            If lWorkbook.Name = "Change classification.xlsm" Then
                lFoundCH = True
                Exit For
            End If
        Next
 
        If lFoundCH = True Then
            MsgBox ("Enregistrement vers TdB VTC impossible tant que Change Classification.xls est ouvert")
            GoTo FinEnr
        End If

je me dis que ça fait beaucoup de "if "tu pourrais déjà rassembler les deux et supprimer l'utilisation de la variable "lFoundCH "
un peu comme ceci:

Code :

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

1
2
3
4
5
6
7
8
9
For Each lWorkbook In Workbooks
    If lWorkbook.Name = "Change classification.xlsm" Then
        MsgBox ("Enregistrement vers TdB VTC impossible tant que Change Classification.xls est ouvert")
 
        Exit For
    End If
Next
GoTo FinEnr
End If

je vais regarder le code au complet de plus prêt

au plaisir

**patricktoulon** · 23/05/2012, 18h41

rebonjour

j'avoue que je suis pas étonné que ça dure des plombes

avec tout ces select ou activate

un exemple que je ne comprend pas dans ton code tout du moins l'utilité
tu active l'un apres l'autre deux classeurs
sans rien faire dessus j'avoue que la je suis sans voix

Code :

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

1
2
3
4
5
On Error Resume Next
    Workbooks("VTC Fiches clients.xls").Activate
    If Err <> 0 Then
 
    Windows("Dashboard.xlsm").Activate

un autre exemple d'incohérence ici
tu boucle sur tout les fichier ouvert et si "TdB VTC_new.xlsm" est ouvert ,tu le ferme ensuite tu le rouvre pour récupérer une valeur dans une cellule
j'avoue que je comprend pas bien pourquoi

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
For Each lWorkbook In Workbooks
        If lWorkbook.Name = "TdB VTC_new.xlsm" Then
            MsgBox ("Fermer le TdB VTC pour l'enregistrement")
        GoTo FinEnr
            Exit For
        End If
    Next
 
    If lFound = True Then
 
    End If
 
 
    Application.EnableEvents = False
    Workbooks.Open Filename:= _
    "\\Giono\Navig\CONSULT\Commercialised helicopters\Certification\certification status in the world\TdB VTC_new.xlsm"
    Application.EnableEvents = True
 
    Workbooks("TdB VTC_new.xlsm").Worksheets("Major Changes").Activate
    NbLignesTdB = Range("I3").Value

je pense que je vais rejoindre les autres en te disant que la méthode que tu emploie n'est pas la meilleure

Temps d'exécution trop long

Macros et VBA Excel

Discussions similaires

Partager

Partager