Réduire le temps d’exécution - code VBA

**TartesAuxPommes** · 15/11/2017, 09h46

Bonjour à tous,

Dans le cadre du travail je dois réaliser le transfert de données depuis un fichier Excel vers une base Access. Avant de faire cela, il faut donc mettre en forme mes données.

Etat initial
1 2 3 a b
4 5 6 d e

Etat Final
1 2 3 a
1 2 3 b
4 5 6 d
4 5 6 e

pour cela j'ai réalisé une macro simple qui fonctionne plutôt bien qui sert à passe de l'état initial à l'état final (voir ci dessus) :

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
k = 2
i = 0
j = 0
 
For j = 28 To 172
    For i = 17 To 232
 
         Sheets("Feuil1").Cells(k, 1).Value = Sheets("2017Original_V1").Cells(i, 1).Value
         Sheets("Feuil1").Cells(k, 2).Value = Sheets("2017Original_V1").Cells(i, 2).Value
         Sheets("Feuil1").Cells(k, 3).Value = Sheets("2017Original_V1").Cells(i, 3).Value
         Sheets("Feuil1").Cells(k, 4).Value = Sheets("2017Original_V1").Cells(i, 4).Value
         Sheets("Feuil1").Cells(k, 5).Value = Sheets("2017Original_V1").Cells(i, 5).Value
         Sheets("Feuil1").Cells(k, 6).Value = Sheets("2017Original_V1").Cells(i, 6).Value
         Sheets("Feuil1").Cells(k, 7).Value = Sheets("2017Original_V1").Cells(i, 7).Value
         Sheets("Feuil1").Cells(k, 8).Value = Sheets("2017Original_V1").Cells(i, 8).Value
         Sheets("Feuil1").Cells(k, 9).Value = Sheets("2017Original_V1").Cells(i, 9).Value
         Sheets("Feuil1").Cells(k, 10).Value = Sheets("2017Original_V1").Cells(i, 10).Value
         Sheets("Feuil1").Cells(k, 11).Value = Sheets("2017Original_V1").Cells(i, 11).Value
         Sheets("Feuil1").Cells(k, 12).Value = Sheets("2017Original_V1").Cells(i, 12).Value
         Sheets("Feuil1").Cells(k, 13).Value = Sheets("2017Original_V1").Cells(i, 13).Value
         Sheets("Feuil1").Cells(k, 14).Value = Sheets("2017Original_V1").Cells(i, 14).Value
         Sheets("Feuil1").Cells(k, 15).Value = Sheets("2017Original_V1").Cells(i, 15).Value
         Sheets("Feuil1").Cells(k, 16).Value = Sheets("2017Original_V1").Cells(i, 16).Value
         Sheets("Feuil1").Cells(k, 17).Value = Sheets("2017Original_V1").Cells(i, 17).Value
         Sheets("Feuil1").Cells(k, 18).Value = Sheets("2017Original_V1").Cells(i, 18).Value
         Sheets("Feuil1").Cells(k, 19).Value = Sheets("2017Original_V1").Cells(i, 19).Value
         Sheets("Feuil1").Cells(k, 20).Value = Sheets("2017Original_V1").Cells(i, 20).Value
         Sheets("Feuil1").Cells(k, 21).Value = Sheets("2017Original_V1").Cells(i, 21).Value
         Sheets("Feuil1").Cells(k, 22).Value = Sheets("2017Original_V1").Cells(i, 22).Value
         Sheets("Feuil1").Cells(k, 23).Value = Sheets("2017Original_V1").Cells(i, 23).Value
         Sheets("Feuil1").Cells(k, 24).Value = Sheets("2017Original_V1").Cells(i, 24).Value
         Sheets("Feuil1").Cells(k, 25).Value = Sheets("2017Original_V1").Cells(i, 25).Value
         Sheets("Feuil1").Cells(k, 26).Value = Sheets("2017Original_V1").Cells(i, 26).Value
         Sheets("Feuil1").Cells(k, 27).Value = Sheets("2017Original_V1").Cells(i, 27).Value
 
         Sheets("Feuil1").Cells(k, 28).Value = Sheets("2017Original_V1").Cells(15, j).Value 'programmes
 
         Sheets("Feuil1").Cells(k, 29).Value = Sheets("2017Original_V1").Cells(i, j).Value 'couts
 
            k = k + 1
  Next i
Next j

Cependant vous pouvez voir qu'il y à 27 colonnes à reporter (144*215) 30960 fois ( soit 30960 lignes).
J'ai fait tourné le code de 17h le soir à 9h le matin pour au final n'avoir que 10 000 lignes transposées.

Comment faire pour optimiser la rapidité de mon code ? Ou quelle méthode alternative utiliser ?

En vous remerciant d'avance,
TartesAuxPommes

**mjpmjp** · 15/11/2017, 10h42

bonjour,
titre : Etat initial
123ab ?????? c'est quoi ?

titre : Etat Final
123a ?????
123b ?????

explique simplement
comment de 123ab on arrive à 123a et 123 b ????

@+JP

**Jambon-Beurre** · 15/11/2017, 11h04

Bonjour Tartes et JP,

Envoyé par mjpmjp

explique simplement
comment de 123ab on arrive à 123a et 123 b ????

A mon avis pour chaque lettre présente sur une colonne il génère une ligne avec sa séquence de nombres avec une lettre unique.

Pour ce qui est de la rapidité d'exécution effectivement ton code peut être optimisé notamment avec des variables tableaux. Un très bon tutoriel est disponible sur developpez.net --> http://didier-gonard.developpez.com/...s-tableau-vba/

**TartesAuxPommes** · 15/11/2017, 11h49

Bonjour à tous,

En effet jambon-beurra à raison

Je met une image excel

Nom : Sans titre.png
Affichages : 545
Taille : 3,0 Ko

voili voilou

**TartesAuxPommes** · 15/11/2017, 12h22

Merci jambon-beurre pour ta réponse,

J'ai donc utilisé un tableau VBA pour stocker mes données puis "coller" mes données sur Excel.

Je suis passé d'un temps de macro de 2 jours à 8 secondes

Pour ceux qui auraient le même problème je joint le code final utilisé

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
Option Explicit
 
Private Sub macro667()
 
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim MacroDebut As Double
Dim tableau(50000, 30) As Variant
 
MacroDebut = Now
 
'-------------------------------
'Debut parite 2 :
'Mise en colonne des dates
'Mise en colonne des numero de cnq
 
k = 0
i = 0
j = 0
 
For j = 28 To 172
    For i = 17 To 232
 
 
         tableau(k, 1) = Sheets("2017Original_V1").Cells(i, 1).Value
         tableau(k, 2) = Sheets("2017Original_V1").Cells(i, 2).Value
         tableau(k, 3) = Sheets("2017Original_V1").Cells(i, 3).Value
         tableau(k, 4) = Sheets("2017Original_V1").Cells(i, 4).Value
         tableau(k, 5) = Sheets("2017Original_V1").Cells(i, 5).Value
         tableau(k, 6) = Sheets("2017Original_V1").Cells(i, 6).Value
         tableau(k, 7) = Sheets("2017Original_V1").Cells(i, 7).Value
         tableau(k, 8) = Sheets("2017Original_V1").Cells(i, 8).Value
         tableau(k, 9) = Sheets("2017Original_V1").Cells(i, 9).Value
         tableau(k, 10) = Sheets("2017Original_V1").Cells(i, 10).Value
         tableau(k, 11) = Sheets("2017Original_V1").Cells(i, 11).Value
         tableau(k, 12) = Sheets("2017Original_V1").Cells(i, 12).Value
         tableau(k, 13) = Sheets("2017Original_V1").Cells(i, 13).Value
         tableau(k, 14) = Sheets("2017Original_V1").Cells(i, 14).Value
         tableau(k, 15) = Sheets("2017Original_V1").Cells(i, 15).Value
         tableau(k, 16) = Sheets("2017Original_V1").Cells(i, 16).Value
         tableau(k, 17) = Sheets("2017Original_V1").Cells(i, 17).Value
         tableau(k, 18) = Sheets("2017Original_V1").Cells(i, 18).Value
         tableau(k, 19) = Sheets("2017Original_V1").Cells(i, 19).Value
         tableau(k, 20) = Sheets("2017Original_V1").Cells(i, 20).Value
         tableau(k, 21) = Sheets("2017Original_V1").Cells(i, 21).Value
         tableau(k, 22) = Sheets("2017Original_V1").Cells(i, 22).Value
         tableau(k, 23) = Sheets("2017Original_V1").Cells(i, 23).Value
         tableau(k, 24) = Sheets("2017Original_V1").Cells(i, 24).Value
         tableau(k, 25) = Sheets("2017Original_V1").Cells(i, 25).Value
         tableau(k, 26) = Sheets("2017Original_V1").Cells(i, 26).Value
         tableau(k, 27) = Sheets("2017Original_V1").Cells(i, 27).Value
 
 
         tableau(k, 28) = Sheets("2017Original_V1").Cells(15, j).Value 'programmes
 
         tableau(k, 28) = Sheets("2017Original_V1").Cells(i, j).Value 'couts
 
            k = k + 1
  Next i
Next j
 
Sheets("Feuil1").Select
Sheets("Feuil1").Range("A2:AD50000") = tableau
 
 
MsgBox "Durée d'exécution: " & Format(Now - MacroDebut, "hh:mm:ss")
 
End Sub

merci encore à vous, super forum !

Cordialement
TartesAuxPommes

**Jambon-Beurre** · 15/11/2017, 19h58

Envoyé par TartesAuxPommes

Merci jambon-beurre pour ta réponse,

J'ai donc utilisé un tableau VBA pour stocker mes données puis "coller" mes données sur Excel.

Je t'en prie, mais c'est surtout boisgontierjacques qu'il faut remercier !! Son site http://boisgontierjacques.free.fr/ est une mine d'or sans égale admirablement fournie d'exemples et de classeurs! Il te permettra au même titre que le tuto de developpez.net d'approfondir ta connaissance des "array" et des "dictionnaires" qui sont fondamentaux pour la manipulation d'un grand nombre de données.

nb : Je n'aurais jamais cru un jour qu'on me remercie à sa place dans une conversation

(oui je sais c'est juste un coup de bol mais quand même

)

Invité · 15/11/2017, 11h56

Code :

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

1
2
3
4
5
6
7
8
For j = 28 To 172
    For I = 17 To 232
        Range(Sheets("Feuil1").Cells(k, 1), Sheets("Feuil1").Cells(k, 27)).Value = Range(Sheets("2017Original_V1").Cells(I, 1), Sheets("2017Original_V1").Cells(I, 27)).Value
        Sheets("Feuil1").Cells(k, 28).Value = Sheets("2017Original_V1").Cells(15, j).Value 'programmes
        Sheets("Feuil1").Cells(k, 29).Value = Sheets("2017Original_V1").Cells(I, j).Value 'couts
        k = k + 1
  Next I
Next j

**boisgontierjacques** · 15/11/2017, 12h18

Bonour,

-Travailler directement sur des cellules en lecture ou écriture est lent.

-Il faut transférer un champ dans un Array.
-Travailler sur l'Array.
-Transférer l'Array dans un champ.

S'il y a une clé dans l'Array, indexer l'Array par un dictionnaire.

Boisgontier
http://boisgontierjacques.free.fr

**Tonton Nico** · 15/11/2017, 12h27

Edit: je vois que tu as résolu entre deux le soucis et encore plus rapidement avec des tableaux !

Bonjour à tous,

Je pense que la première chose à faire pour réduire ton temps est de tout faire sur le même fichier du genre ton "original" et de faire ta recopie dans une nouvelle sheet et au pire de la déplacer après sur un nouveau classeur, je pense que le copier/coller entre deux classeurs n'est pas le plus rapide surtout si tu peux l'éviter "facilement"

Je ne comprends pas toute la stratégie sur une double boucle car j'ai l'impression qu'au final ton code fait beaucoup plus que tes schémas mais pour répondre à ta demande :

avec 27 colonnes fixes (1-27 dans ton schéma) puis 2 colonnes specifiques (A et B dans ton schéma), avec une feuille DATA de l'étape initial et une feuille RECOPÏE pour l'étape finale:

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
 
Sub TRI()
 
COL = 1
LIGNE_DATA = 2
LIGNE_RECOPIE = 2
NB_COLONNE_FIXE = 27
 
Do Until Sheets("DATA").Cells(LIGNE_DATA, COL).Value = ""
 
    'on reprend les  colonnes communes
    For i = 0 To NB_COLONNE_FIXE - 1
        Sheets("RECOPIE").Cells(LIGNE_RECOPIE, COL + i).Value = Sheets("DATA").Cells(LIGNE_DATA, COL + i).Value
        Sheets("RECOPIE").Cells(LIGNE_RECOPIE + 1, COL + i).Value = Sheets("DATA").Cells(LIGNE_DATA, COL + i).Value
    Next
    'on reprend les deux colonnes specifiques
    Sheets("RECOPIE").Cells(LIGNE_RECOPIE, COL + i).Value = Sheets("DATA").Cells(LIGNE_DATA, COL + i).Value
    Sheets("RECOPIE").Cells(LIGNE_RECOPIE + 1, COL + i).Value = Sheets("DATA").Cells(LIGNE_DATA, COL + i + 1).Value
 
    'on décale les lignes, une pour le DATA et deux pour la récopie car on divise 1 ligne en 2
    LIGNE_DATA = LIGNE_DATA + 1
    LIGNE_RECOPIE = LIGNE_RECOPIE + 2
Loop
 
End Sub

je n'arrive pas à joindre le fichier excel (pas de réponse de developpez.net du coup je mets un screen :

Nom : tri.png
Affichages : 559
Taille : 29,3 Ko

Cela prends 2 min en gros chez moi pour faire 30960 lignes sur la feuille DATA, mais j'imagine qu'avec du format et des chaines de caractère plus complexe dans les cases cela va tout changer.
Du coup cela répond à tes schémas mais j'ai un doute sur ce que tu voulais faire au final vraiment ^^

En espérant aider

TontonNico

Réduire le temps d’exécution - code VBA [XL-2010]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager