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

Version imprimable

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:

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

15/11/2017, 10h42
mjpmjp

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
15/11/2017, 11h04
Jambon-Beurre

Bonjour Tartes et JP,

Citation:

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/
15/11/2017, 11h49
TartesAuxPommes

1 pièce(s) jointe(s)

Bonjour à tous,

En effet jambon-beurra à raison

Je met une image excel

Pièce jointe 327456

voili voilou

Code:

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

15/11/2017, 12h18
boisgontierjacques

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

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 8O8O8O8O8O8O8O8O8O

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

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
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

1 pièce(s) jointe(s)

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

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:

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 :

Pièce jointe 327462

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

15/11/2017, 19h58
Jambon-Beurre

Citation:

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 :D:D:D:D:D (oui je sais c'est juste un coup de bol mais quand même :roll:)