Discussion :

[Trystan441]

Bonjour à tous,

Grâce au magnifique tutoriel sur les variables tableaux de silkyroad, j'ai finalement réussi à optimiser ma procédure.

Celle-ci me permet d'analyser mes volumes de commandes par semaine sur une année complète.
Concrètement, j'ai environ 8000 références qu'il a fallu que je retrace dans 25000 lignes de commandes sur l'année (ça fait donc 3 boucles relativement balaises).

Cependant, comme c'est ma première procédure où j'utilise les variables tableaux, je me doute bien qu'il y a beaucoup mieux à faire et donc optimisable.
En ce moment tout fonctionne pour le mieux, cependant, le temps de traitement est d'environ 30min (37sec par semaine)

Structure du classeur :
1ère feuille :
- Colonne A : Répertoire des références articles
- Colonne B : volume commandé en semaine 1
- Colonne C : volume commandé en semaine 2
- Etc jusqu'à 52

Voici le code que j'utilise :

4 tableaux chargés des données dont j'ai besoin

Tableau 1 : Références articles
Tableau 2 : Références commandées
Tableau 3 : N° de semaine
Tableau 4 : Volume commandé

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
Sub Prévision_Semaine()
 
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
Dim Debut As Date, Temps As Date, Fin As Date
Début = Time
 
'Compteur de ligne afin d'obtenir le nombre de références articles
Ligne = Sheets("Base").Range("A13:A" & Rows.Count).End(xlDown).Row
 
'Compteur de ligne afin de connaître le nombre de commandés passées
Dim LigneAccdep As Long
LigneAccdep = Sheets("accdep80").Range("A2:A" & Rows.Count).End(xlDown).Row
 
'Variable pour compteur ligne
Dim i As Long
 
'Variable pour compteur de semaine
Dim Sem As Byte
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Déclaration des tableaux
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Tableau chargé des numéros d'articles et nom du fournisseur
'présent en colonne A de la feuille "Prévision semaine"
Dim TabArticle() As Variant
ReDim TabArticle(Ligne)
 
'Tableau chargé des numéros d'articles et nom du fournisseur
'présent en colonne H de la feuille "accdep80"
Dim TabArticle2() As Variant
ReDim TabArticle2(LigneAccdep)
 
'Tableau chargé des numéros de semaine de livraison des articles
'présent dans la feuille "accdep80"
Dim TabArticle3() As Variant
ReDim TabArticle3(LigneAccdep)
 
Dim TabArticle4() As Variant
ReDim TabArticle4(LigneAccdep)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
'Variable permettant d'obtenir le volume total commandé sur une semaine par référence
Dim VolCde As Long
 
'Création de la feuille de synthèse
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Prévision semaine"
 
'Transfert des références articles vers la feuille de synthèse
Sheets("Base").Range(Sheets("Base").Cells(13, 3), Sheets("Base").Cells(Ligne, 3)).Copy
Sheets("Prévision semaine").Range("A2").PasteSpecial
 
'Chargement des données dans le tableau N°1
For i = 2 To Ligne
    TabArticle(i) = Sheets("Prévision semaine").Range("A" & i)
Next i
 
'Chargement des données dans le tableau N°2
For i = 2 To LigneAccdep
    TabArticle2(i) = Sheets("accdep80").Range("H" & i)
Next i
 
'Chargement des données dans le tableau N°3
For i = 2 To LigneAccdep
    TabArticle3(i) = Sheets("accdep80").Range("AX" & i)
Next
 
'Chargement des données dans le tableau N°4
For i = 2 To LigneAccdep
    TabArticle4(i) = Sheets("accdep80").Range("R" & i)
Next
 
'Boucle pour les semaines
For Sem = 1 To 52
 
    'Seconde boucle pour passer sur toutes les références articles
    For i = 2 To Ligne
 
        'Troisième boucle pour passer sur toutes les lignes de commandes
        For j = 2 To LigneAccdep
 
            'Test pour récupérer le volume commandé par référence article
            If TabArticle(i) = TabArticle2(j) And TabArticle3(j) = Sem Then
 
                VolCde = VolCde + TabArticle4(j)
            End If
 
        Next j
 
    'Associe le volume commandé à la référence article sur la feuille synthèse
    Cells(i, Sem + 3) = VolCde
    VolCde = 0
 
    Next i
 
Next Sem
 
Fin = Time
 
Temps = Fin - Début
 
MsgBox ("c'est fini !" & Chr(10) & "temps de traitement" & Temps)
 
End Sub

Auriez-vous des pistes à me faire part pour gagner en efficacité ?

En espérant avoir été assez clair, sinon n'hésitez pas à me poser des questions,

Bonne journée à vous,

[Philippe Tulliez]

Bonjour,
Pourquoi quatre tableaux ?
Une variable tableau peut charger toute la liste de données et tu traiteras en mémoire la table comme tu le ferais avec une plage de données.

[Trystan441]

Bonjour Philippe,

Mon raisonnement était le suivant :

4 plages de données non adjacentes, donc 4 tableaux pour ne pas intégrer des données inutiles.

Ne serait-ce pas un tableau à 4 dimensions qui me permettrait de tout intégrer ? (Réflexion du moment)

[Philippe Tulliez]

Bonjour,
Je n'ai pas fait de tests de vitesse mais personnellement je chargerais la liste complète et puis j'effectuerais le traitement dans une boucle.
Voici un exemple du chargement d'un tableau structuré nommé T_TimeSheet avec l'affichage ensuite du nombre de lignes et de colonnes de cette liste

Code :

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

1
2
3
 Dim myTable()
 myTable = Range("T_TimeSheet")
 MsgBox UBound(myTable) & " / " & UBound(myTable, 2)

[Trystan441]

Je reviens vers vous après test !

Une erreur est survenue (il en fallait bien une) :
Erreur d'exécution 7 : Mémoire insuffisante

Ceci n'est qu'une partie de ma procédure :

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim TabArticle() As Variant
'Dimensionnement du tableau à 4 dimensions
ReDim TabArticle(1 To Ligne, 1 To LigneAccdep, 1 To LigneAccdep, 1 To LigneAccdep)
 
For i = 2 To Ligne
    TabArticle(i, 1) = Sheets("Prévision semaine").Range("A" & i)
    TabArticle(i, 2) = Sheets("accdep80").Range("H" & i)
    TabArticle(i, 3) = Sheets("accdep80").Range("AX" & i)
    TabArticle(i, 4) = Sheets("accdep80").Range("R" & i)
Next i

Questions :
1. Mon tableau est-il réellement à 4 dimensions ?
2. Mon erreur de mémoire est-elle due au type Variant ?

[Philippe Tulliez]

Bonjour,

Mon tableau est-il réellement à 4 dimensions ?

Quatre dimensions c'est difficile de se le représenter.
Une dimension c'est une liste simple, un peu l'équivalent d'une colonne à une ou plusieurs lignes. Attention que pour Excel en vba une colonne c'est déjà deux dimensions), deux dimensions plusieurs lignes, plusieurs colonnes (un tableau classique), trois dimensions c'est un cube.

Je te conseille la lecture de cet excellent tutoriel de Didier Gonard Conceptualisation des variables tableau en VBA et Application à l'optimisation du code sous Excel

[Trystan441]

Bonjour,

Philippe, j'avais eu l'occasion effectivement de parcourir ce tutoriel particulièrement complet. Après lecture, mon tableau est bien à deux dimensions.

Après test du remplacement de 4 tableaux par un seul, aucune amélioration du temps de traitement. J'en suis toujours à 37 sec. J'ai bien l'impression que je ne vais pas pouvoir faire beaucoup mieux.
Ce qui est toujours beaucoup mieux que 25min par semaine de données

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
'Tableau chargé des numéros d'articles et nom du fournisseur
'présent en colonne A de la feuille "Prévision semaine"
Dim TabArticle() As Variant
ReDim TabArticle(LigneAccdep, 4)
 
'Chargement des données dans le tableau
For i = 2 To Ligne
    TabArticle(i, 1) = Sheets("Prévision semaine").Range("A" & i)
Next i
 
For i = 2 To LigneAccdep
    TabArticle(i, 2) = Sheets("accdep80").Range("H" & i)
    TabArticle(i, 3) = Sheets("accdep80").Range("AX" & i)
    TabArticle(i, 4) = Sheets("accdep80").Range("R" & i)
Next i
 
'Boucle pour les semaines
For Sem = 1 To 52
 
    'Seconde boucle pour passer sur toutes les références articles
    For i = 2 To Ligne
 
        'Troisième boucle pour passer sur toutes les lignes de commandes
        For j = 2 To LigneAccdep
 
            'Test pour récupérer le volume commandé par référence article
            If TabArticle(i, 1) = TabArticle(j, 2) And TabArticle(j, 3) = Sem Then
 
                VolCde = VolCde + TabArticle(j, 4)
            End If
 
        Next j
 
    'Associe le volume commandé à la référence article sur la feuille synthèse
    Cells(i, Sem + 3) = VolCde
    VolCde = 0
 
    Next i
 
Next Sem
 
Fin = Time
 
Temps = Fin - Début
 
MsgBox ("c'est fini !" & Chr(10) & "temps de traitement" & Temps)
 
End Sub

Bonne journée à vous,