Amélioration d'un code fonctionnel

Version imprimable

Bonjour,

Je suis actuellement sur la fin de mon stage et l'outil que j'ai réalisé est entièrement terminé. Je suis donc actuellement dans la rédaction d'un manuel d'utilisation et je commence à former les personnes qui se serviront de cet outil. Cependant, je me demande aussi si le code que j'ai réalisé ne serait pas "améliorable". En effet, j'ai énormément appris à programmer en VBA via mes études, et je vous laisse imaginer que pendant les études, on nous apprend à coder d'une seule et une unique manière (qui n'est pas toujours la meilleur) et qu'on a pas vraiment le droit d'essayer de faire autrement. Mais je ne vois pas pourquoi je n'essayerai pas de faire autrement dans le cadre de mon stage. Je me tourne donc vers vous pour avoir vos suggestions et vos différents avis. :)

Comme vous l'aurez compris, le but de ce post est simple : s'instruire !

Bref, rentrons dans le vif du sujet.

Mon outil permet à l'utilisateur de calculer le nombre d'emplacement qui sera disponible dans leur stock sur un horizon de un an via une extraction SAP. L'extraction SAP est un fichier composé de la façon suivante:

Chaque ligne du fichier correspond à un article, on y retrouve plusieurs informations mais seul le numéro d'article et les prévisions de stocks nous intéressent. Il y a 12 colonnes (une pour chaque mois de l'horizon) qui contiennent des informations sur le stocks prévus. Cependant ce stock est indiqué en unité et je dois le convertir en nombre de palettes (une palette correspond à un emplacement dans le stock).

Une fois la conversion faite, j'affiche le résultat sur l'outil.

Voici la manière donc j'ai procédé pour réaliser ceci :

1) Je lis les quantités par palette de chacun des articles présentes dans la feuille "DONNEE" que je stocks dans un tableau.
2) Je demande à l'utilisateur de choisir son fichier d'extraction.
3) Je récupère les données du fichier ouvert dans un tableau.
4) Je calcule dans un 3éme tableau le nombre de palette de chaque article.
5) Je fais la somme pour chaque mois du nombre de palette et j'affiche le résultat sur la feuille "CAPACITE"

Voilà ce que sa donne.

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
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
Option Explicit
 
Public Sub main(typeArt As String, ligne As Integer)
 
    Dim wb As Workbook 'Variable contenant le fichier de l'extraction SAP
    Dim annee As Integer 'Variable contenant l'année en cours
    Dim mois As Integer 'Variable contenant le mois en cours
    Dim extraction() As String 'Tableau récupérant les données de l'extraction SAP
    Dim nbPalettes() As Integer 'Tableau permettant de calculer le nombre de palettes de chaques articles
    Dim classeur As String 'Varaible contenant le nom du fichier de l'extraction SAP
    Dim nomFeuille As String 'Nom de la feuille sur laquelle on travaille
    Dim donnee() As String 'Tableau conteant les données de tout les articles
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    Call LireDonnee(donnee, typeArt)
 
    nomFeuille = ActiveSheet.name 'Récupération du nom de la feuille active
    annee = Year(Date) 'Récupération de l'année en cours
    mois = Month(Date) 'Récupération du mois en cours
 
    Worksheets(nomFeuille).Cells(ligne, 1).Value = mois & "/" & "01/" & annee 'Affiche le début de l'horizon étudié sur la feuille excel
 
    Call OuvertureFichier(classeur, wb)
 
    If classeur = "Faux" Then
 
        Exit Sub
 
    End If
 
    Call LireTab(extraction, wb)
 
    wb.Close
 
    Call CalculPalette(extraction, nbPalettes, nomFeuille, donnee)
    Call Affichage(nbPalettes, nomFeuille, ligne)
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub
 
'Procédure permettant de récupérer les données des articles
Public Sub LireDonnee(tablo() As String, typeArt As String)
 
    Dim i As Integer
    Dim colonne As Integer
    Dim taille As Integer
 
    'Permet d'utiliser les bonnes données d'article présentent dans l'onglet "Donnee" du fichier
    Select Case typeArt
 
        Case "MP"
 
            colonne = 7
 
        Case "AC"
 
            colonne = 4
 
        Case "PSO"
 
            colonne = 10
 
        Case Else
 
            colonne = 1
 
    End Select
 
    'Permet de récupérer la taille du tableau
    taille = Sheets("DONNEE").Cells(Rows.Count, colonne).End(xlUp).Row
 
    ReDim tablo(taille - 2, 2)
 
    'Récupère les valeurs de la feuille "Donnee"
    For i = 1 To UBound(tablo, 1)
 
        tablo(i, 1) = Worksheets("DONNEE").Cells(i + 2, colonne).Value
        tablo(i, 2) = Worksheets("DONNEE").Cells(i + 2, colonne + 1).Value
 
    Next i
 
End Sub
 
'Procédure permettant de récupérer les données de l'extraction dans un tableau
Public Sub LireTab(tablo() As String, wb As Workbook)
 
    Dim i As Integer
    Dim j As Integer
    Dim ligne As Integer
    Dim taille As Integer 'Variable contenant le nombre de lignes de données de l'extraction
 
    taille = wb.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
 
    'Redimentionne le tableau en fonction du nombre de lignes de données trouvées
    ReDim tablo(59, taille - 1)
 
    'Ajoute toutes les données de l'extraction au tableau
    For i = 1 To UBound(tablo, 1)
 
        For j = 1 To UBound(tablo, 2)
 
            tablo(i, j) = wb.Worksheets("Sheet1").Cells(j + 1, i).Value
 
        Next j
 
    Next i
 
End Sub
 
'Procédure permettant de calculer le nombre de palette de chaques articles
Public Sub CalculPalette(extraction() As String, nbPalettes() As Integer, feuille As String, donnee() As String)
 
    Dim i As Integer
    Dim j As Integer
    Dim z As Integer
    Dim ligne As Integer
    Dim sortie As Boolean
 
    'Redimentione le tableau en fonction de la taille du tableau "extraction"
    ReDim nbPalettes(12, UBound(extraction, 2))
 
    'Pour chaque articles de l'extraction
    For j = 1 To UBound(extraction, 2)
 
            'On parcourt le tableau "Donnee" pour trouver les informations sur l'article
            For z = 1 To UBound(donnee, 1)
                'Si l'on a trouvé l'article
                If donnee(z, 1) = extraction(1, j) Then
 
                    ligne = 1
 
                    'On calcule le nombre de palettes de l'articles prévues dans l'horizon
                    For i = 2 To UBound(extraction, 1)
 
                        'Permet de sélectionner les bonnes colonnes du tableau extraction
                        If i = 14 Or i = 18 Or i = 22 Or i = 26 Or i = 30 Or i = 34 Or i = 38 Or i = 42 Or i = 46 Or i = 50 Or i = 54 Or i = 58 Then
 
                            nbPalettes(ligne, j) = Application.RoundUp(extraction(i, j) / donnee(z, 2), 0)
                            ligne = ligne + 1
 
                        End If
 
                    Next i
 
                    Exit For
 
                End If
 
            Next z
 
    Next j
 
End Sub
 
'Procédure permettant d'afficher les résultats trouvés
Public Sub Affichage(nbPalettes() As Integer, feuille As String, ligne As Integer)
 
    Dim i As Integer
    Dim j As Integer
    Dim somme As Integer 'Variable contenant le nombre de palettes prévues
 
    'Pour chaque période de l'horizon
    For i = 1 To UBound(nbPalettes, 1)
 
        somme = 0
 
        'On calcule le nombre de palettes prévues
        For j = 1 To UBound(nbPalettes, 2)
 
            If nbPalettes(i, j) >= 0 Then
 
                somme = somme + nbPalettes(i, j)
 
            End If
 
        Next j
 
        'On affiche le résultat sur la feuille correspondante
        Worksheets(feuille).Cells(ligne + 1, i).Value = somme
 
    Next i
 
End Sub
 
'Procédure permettant d'ouvrir un fichier Excel sélectionné pour en soustraire des données par la suite
Public Sub OuvertureFichier(classeur As String, wb As Workbook)
 
    classeur = Application.GetOpenFilename("Classeurs Excel,*xlsx") 'Permet d'ouvrir le fichier d'extraction voulu
 
    'Gestion d'erreur durant l'ouverture
    If classeur = "Faux" Then
 
        Exit Sub
 
    End If
 
    Set wb = Workbooks.Open(classeur, , True) 'Ouvre le fichier d'extraction selectionné
 
    wb.Windows(1).Visible = False 'Permet d'empécher l'affichage du classeur à l'écran
 
End Sub

Voilà, voilà, je suis ouvert à toute suggestion et toute question. :mrgreen:

23/06/2017, 13h53
mjpmjp

bonjour,
tu as une liste de "QuantitéStock"
tu as une liste de "QuantitéPalette"

tu ouvres les fichiers contenant ces listes et celui du "Résultat"

la fonction coté excel est "INDIRECT" elle permet de "faire correspondre" ta ligne stock et ta ligne "palette"

coté VBA
une boucle sur "stock"
tu écris dans résultat = ta conversion stock/palette

@+JP
23/06/2017, 13h59
Invité

Que veux-tu dire par "INDIRECT" ?
23/06/2017, 14h10
Patrice740

Bonjour,

Deux remarques :
1. Tes tableaux sont mal dimensionnés (ce qui peut occuper beaucoup de mémoire inutilement, par exemple ReDim tablo(taille - 2, 2) est l'équivalent de ReDim tablo(0 To taille - 2, 0 To 2) alors que tu n'utilises que l’équivalent de ReDim tablo(1 To taille - 2, 1 To 2).
2. Au lieu d'utiliser une boucle pour remplir le tableau, on peut simplement écrire Tableau=Range(champ).Value.

Bonjour,

Je ne connaissais pas cette syntaxe pou redimensionner les tableaux, merci de l'info. :)

Pour ta 2ème remarque voilà ce que j'ai essayé :

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
Public Sub LireTab(tablo() As String, wb As Workbook)
 
    Dim i As Integer
    Dim j As Integer
    Dim ligne As Integer
    Dim taille As Integer 'Variable contenant le nombre de lignes de données de l'extraction
 
    taille = wb.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
 
    'Redimentionne le tableau en fonction du nombre de lignes de données trouvées
    ReDim tablo(1 To 59, 1 To taille - 1)
 
    tablo() = wb.Sheets("Sheet1").Range("A2:BG" & taille).value
 
    'Ajoute toutes les données de l'extraction au tableau
    'For i = 1 To UBound(tablo, 1)
 
        'For j = 1 To UBound(tablo, 2)
 
            'tablo(i, j) = wb.Worksheets("Sheet1").Cells(j + 1, i).Value
 
        'Next j
 
    'Next i
 
End Sub

Mais j'ai une erreur d'incompatibilité de type à la ligne 13.

J'ai aussi une question pour ce cas :

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
Public Sub LireDonnee(tablo() As String, typeArt As String)
 
    Dim i As Integer
    Dim colonne As Integer
    Dim taille As Integer
 
    'Permet d'utiliser les bonnes données d'article présentent dans l'onglet "Donnee" du fichier
    Select Case typeArt
 
        Case "MP"
 
            colonne = 7
 
        Case "AC"
 
            colonne = 4
 
        Case "PSO"
 
            colonne = 10
 
        Case Else
 
            colonne = 1
 
    End Select
 
    'Permet de récupérer la taille du tableau
    taille = Sheets("DONNEE").Cells(Rows.Count, colonne).End(xlUp).Row
 
    ReDim tablo(1 To taille - 2, 1 To 2)
 
    'Récupère les valeurs de la feuille "Donnee"
    For i = 1 To UBound(tablo, 1)
 
        tablo(i, 1) = Worksheets("DONNEE").Cells(i + 2, colonne).Value
        tablo(i, 2) = Worksheets("DONNEE").Cells(i + 2, colonne + 1).Value
 
    Next i
 
End Sub

Ma variable colonne correspond au numéro de la colonne Excel où je récupère mes données, comment je peux récupérer le nom de la colonne (par exemple si colonne = 1, je dois récupérer A) pour pouvoir par la suite utiliser ta méthode de remplissage de tableau avec Range ?

Finalement en cherchant j'ai réussi à appliquer toutes tes remarques, voici le résultat :

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
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
Option Explicit
 
Public Sub main(typeArt As String, ligne As Integer)
 
    Dim wb As Workbook 'Variable contenant le fichier de l'extraction SAP
    Dim annee As Integer 'Variable contenant l'année en cours
    Dim mois As Integer 'Variable contenant le mois en cours
    Dim extraction() As Variant 'Tableau récupérant les données de l'extraction SAP
    Dim nbPalettes() As Integer 'Tableau permettant de calculer le nombre de palettes de chaques articles
    Dim classeur As String 'Varaible contenant le nom du fichier de l'extraction SAP
    Dim nomFeuille As String 'Nom de la feuille sur laquelle on travaille
    Dim donnee() As Variant 'Tableau conteant les données de tout les articles
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    Call LireDonnee(donnee, typeArt)
 
    nomFeuille = ActiveSheet.name 'Récupération du nom de la feuille active
    annee = Year(Date) 'Récupération de l'année en cours
    mois = Month(Date) 'Récupération du mois en cours
 
    Worksheets(nomFeuille).Cells(ligne, 1).Value = mois & "/" & "01/" & annee 'Affiche le début de l'horizon étudié sur la feuille excel
 
    Call OuvertureFichier(classeur, wb)
 
    If classeur = "Faux" Then
 
        Exit Sub
 
    End If
 
    Call LireTab(extraction, wb)
 
    wb.Close
 
    Call CalculPalette(extraction, nbPalettes, nomFeuille, donnee)
    Call Affichage(nbPalettes, nomFeuille, ligne)
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub
 
'Procédure permettant de récupérer les données des articles
Public Sub LireDonnee(tablo() As Variant, typeArt As String)
 
    Dim i As Integer
    Dim index1 As String
    Dim index2 As String
    Dim colonne As Integer
    Dim taille As Integer
 
    'Permet d'utiliser les bonnes données d'article présentent dans l'onglet "Donnee" du fichier
    Select Case typeArt
 
        Case "MP"
 
            colonne = 7
            index1 = "G"
            index2 = "H"
 
        Case "AC"
 
            colonne = 4
            index1 = "D"
            index2 = "E"
 
        Case "PSO"
 
            colonne = 10
            index1 = "J"
            index2 = "K"
 
        Case Else
 
            colonne = 1
            index1 = "A"
            index2 = "B"
 
    End Select
 
    'Permet de récupérer la taille du tableau
    taille = Sheets("DONNEE").Cells(Rows.Count, colonne).End(xlUp).Row
 
    ReDim tablo(1 To taille - 2, 1 To 2)
 
    tablo = Sheets("DONNEE").Range(index1 & "3:" & index2 & taille).Value
 
End Sub
 
'Procédure permettant de récupérer les données de l'extraction dans un tableau
Public Sub LireTab(tablo() As Variant, wb As Workbook)
 
    Dim i As Integer
    Dim j As Integer
    Dim ligne As Integer
    Dim taille As Integer 'Variable contenant le nombre de lignes de données de l'extraction
 
    taille = wb.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
 
    'Redimentionne le tableau en fonction du nombre de lignes de données trouvées
    ReDim tablo(1 To taille - 1, 1 To 59)
 
    tablo() = wb.Sheets("Sheet1").Range("A2:BG" & taille).Value
 
End Sub
 
'Procédure permettant de calculer le nombre de palette de chaques articles
Public Sub CalculPalette(extraction() As Variant, nbPalettes() As Integer, feuille As String, donnee() As Variant)
 
    Dim i As Integer
    Dim j As Integer
    Dim z As Integer
    Dim ligne As Integer
    Dim sortie As Boolean
 
    'Redimentione le tableau en fonction de la taille du tableau "extraction"
    ReDim nbPalettes(1 To UBound(extraction, 1), 1 To 12)
 
    'Pour chaque articles de l'extraction
    For j = 1 To UBound(extraction, 1)
 
            'On parcourt le tableau "Donnee" pour trouver les informations sur l'article
            For z = 1 To UBound(donnee, 1)
                'Si l'on a trouvé l'article
                If donnee(z, 1) = CLng(extraction(j, 1)) Then
 
                    ligne = 1
 
                    'On calcule le nombre de palettes de l'articles prévues dans l'horizon
                    For i = 2 To UBound(extraction, 2)
 
                        'Permet de sélectionner les bonnes colonnes du tableau extraction
                        If i = 14 Or i = 18 Or i = 22 Or i = 26 Or i = 30 Or i = 34 Or i = 38 Or i = 42 Or i = 46 Or i = 50 Or i = 54 Or i = 58 Then
 
                            nbPalettes(j, ligne) = Application.RoundUp(extraction(j, i) / donnee(z, 2), 0)
                            ligne = ligne + 1
 
                        End If
 
                    Next i
 
                    Exit For
 
                End If
 
            Next z
 
    Next j
 
End Sub
 
'Procédure permettant d'afficher les résultats trouvés
Public Sub Affichage(nbPalettes() As Integer, feuille As String, ligne As Integer)
 
    Dim i As Integer
    Dim j As Integer
    Dim somme As Integer 'Variable contenant le nombre de palettes prévues
 
    'Pour chaque période de l'horizon
    For i = 1 To UBound(nbPalettes, 2)
 
        somme = 0
 
        'On calcule le nombre de palettes prévues
        For j = 1 To UBound(nbPalettes, 1)
 
            If nbPalettes(j, i) >= 0 Then
 
                somme = somme + nbPalettes(j, i)
 
            End If
 
        Next j
 
        'On affiche le résultat sur la feuille correspondante
        Worksheets(feuille).Cells(ligne + 1, i).Value = somme
 
    Next i
 
End Sub
 
'Procédure permettant d'ouvrir un fichier Excel sélectionné pour en soustraire des données par la suite
Public Sub OuvertureFichier(classeur As String, wb As Workbook)
 
    classeur = Application.GetOpenFilename("Classeurs Excel,*xlsx") 'Permet d'ouvrir le fichier d'extraction voulu
 
    'Gestion d'erreur durant l'ouverture
    If classeur = "Faux" Then
 
        Exit Sub
 
    End If
 
    Set wb = Workbooks.Open(classeur, , True) 'Ouvre le fichier d'extraction selectionné
 
    wb.Windows(1).Visible = False 'Permet d'empécher l'affichage du classeur à l'écran
 
End Sub