Utilisation d'un tableau créé a l'ouverture du fichier

**Michou199** · 19/08/2012, 16h52

Bonjour,

Je suis nouveau sur le forum et je ne pratique le VBA que depuis 3 semaines en apprenant sur votre forum dès que je rencontre un problème, donc déjà un grand merci pour ça !
Mon problème est le suivant : j'ai un code qui me crée deux tableaux et les analyse en fonction des besoins, mais je dois le relancer quand je change un critère d'analyse mais la création des tableaux est longue.
J'ai donc modifié ce code et mis le calcul des tableaux a l'ouverture du fichier, mais maintenant lorsque je lance l'analyse il me dis "projet ou bibliothèque introuvable" en parlant du nom de mes tableaux.
Ma question est donc la suivante : comment puis je faire pour faire référence dans mon second module (analyse) aux tableaux du premier module (création des tableaux a l'ouverture du fichier) ?

Merci d'avance,

Michel

**mercatog** · 19/08/2012, 16h57

Déclare tes variables tableaux en Public dans un module standard. Dès lors, elles seront reconnues n'importe où dans ton projet.

Voir lien explicatif http://silkyroad.developpez.com/VBA/LesVariables/#LV

**Michou199** · 19/08/2012, 17h24

Merci Mercatog,
Un autre problème se pose ici : mes tableaux sont dynamiques et dépendent donc d'une variable qui utilise la fonction xlDown et qui n'est pas acceptée a l’extérieur d'une procédure.
Comment puis-je faire ?

**mercatog** · 19/08/2012, 17h34

Sans code, on ne peut pas répondre concrètement. Néanmoins, tu peux toujours redimensionner ton tableau n'importe où du moment qu'il est reconnu.

**Michou199** · 19/08/2012, 17h35

Ce sera peut être plus facile avec mon code :

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
Sub Calculation()
 
Calculate
 
Dim LastDeliveries As Integer
Dim LastNCR As Integer
Dim SupplierCode As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SupplierName As String
Dim MonthCalc As String
Dim NumberParts As Long
Dim NumberNCR As Integer
Dim CountParts As Integer
Dim CountMeters As Integer
Dim CountLitres As Integer
Dim CountKg As Integer
Dim CountGallons As Integer
Dim Count As Integer
Dim code As String
 
Dim t_start As Single, t_end As Single, elapsed As Single
 
LastDeliveries = Sheets("Deliveries").Range("R2").End(xlDown).Row
LastNCR = Sheets("NCRs").Range("A2").End(xlDown).Row
 
SupplierCode = Sheets("Charts").Range("C3").Value 'UCase(Sheets("Charts").Range("C3").Value)
SupplierName = Sheets("Charts").Range("B3").Value 'UCase(Sheets("Charts").Range("B3").Value)
 
t_start = Timer
 
'Dim Deliveries()
'ReDim Deliveries(LastDeliveries - 1, 4)
'For i = 1 To LastDeliveries - 1
'    Deliveries(i, 0) = Sheets("Deliveries").Range("C" & i + 2) 'UCase(Sheets("Deliveries").Range("C" & i + 2)) 'Supplier code
'    Deliveries(i, 1) = Sheets("Deliveries").Range("E" & i + 2) 'PartNumber
'    Deliveries(i, 2) = Sheets("Deliveries").Range("G" & i + 2) ' UCase(Sheets("Deliveries").Range("G" & i + 2)) 'Cell
'    Deliveries(i, 3) = Sheets("Deliveries").Range("I" & i + 2) ' Quantity
'    Deliveries(i, 4) = Sheets("Deliveries").Range("R" & i + 2) & Sheets("Deliveries").Range("S" & i + 2) 'MMMYYYY
'Next i
 
'Dim NCRs()
'ReDim NCRs(LastNCR - 1, 7)
'For i = 1 To LastNCR - 1
'    NCRs(i, 0) = Sheets("NCRs").Range("B" & i + 2) 'Cell
'    NCRs(i, 1) = Sheets("NCRs").Range("D" & i + 2) 'Part
'    NCRs(i, 2) = Sheets("NCRs").Range("H" & i + 2) 'Quantity
'    NCRs(i, 3) = Sheets("NCRs").Range("I" & i + 2) 'Unit
'    NCRs(i, 4) = Sheets("NCRs").Range("J" & i + 2) 'UCase(Sheets("NCRs").Range("J" & i + 2)) 'Supplier
'    NCRs(i, 5) = Sheets("NCRs").Range("Q" & i + 2) 'Fault code
'    NCRs(i, 6) = Sheets("NCRs").Range("Y" & i + 2) 'Status
'    NCRs(i, 7) = Sheets("NCRs").Range("Z" & i + 2) & Sheets("NCRs").Range("AA" & i + 2) 'MMMYYYY
'Next i
 
For i = 1 To 24
    MonthCalc = Sheets("Calculation").Range("B" & i + 1).Value & Sheets("Calculation").Range("A" & i + 1).Value
    NumberParts = 0
    For j = 0 To LastDeliveries - 1
        If Deliveries(j, 0) = SupplierCode Then
            If Deliveries(j, 4) = MonthCalc Then
            NumberParts = NumberParts + Deliveries(j, 3)
            End If
        End If
    Next j
    Sheets("Calculation").Range("I" & i + 1).Value = NumberParts
 
    NumberNCR = 0
    CountParts = 0
    CountMeters = 0
    CountLitres = 0
    CountKg = 0
    CountGallons = 0
    For j = 0 To LastNCR - 1
        If NCRs(j, 7) = MonthCalc Then
            If NCRs(j, 4) = SupplierName Then
            NumberNCR = NumberNCR + 1
                If NCRs(j, 3) = "parts" Then
                    CountParts = CountParts + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "meters" Then
                    CountMeters = CountMeters + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "litres" Then
                    CountLitres = CountLitres + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "kg" Then
                    CountKg = CountKg + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "gallons" Then
                    CountGallons = NCRs(j, 2)
                End If
            End If
        End If
    Next j
    Sheets("Calculation").Range("C" & i + 1).Value = NumberNCR
    Sheets("Calculation").Range("D" & i + 1).Value = CountParts
    Sheets("Calculation").Range("E" & i + 1).Value = CountMeters
    Sheets("Calculation").Range("F" & i + 1).Value = CountLitres
    Sheets("Calculation").Range("G" & i + 1).Value = CountKg
    Sheets("Calculation").Range("H" & i + 1).Value = CountGallons
 
    If Not NumberParts = 0 Then
        Sheets("Calculation").Range("J" & i + 1).Value = ((CountParts + CountMeters + CountLitres + CountKg + CountGallons) / NumberParts) * 1000000
    Else
        Sheets("Calculation").Range("J" & i + 1).Value = 0
    End If
Next i
 
Dim FaultCode(32, 23)
For i = 0 To 32
    code = Sheets("Charts").Range("B" & i + 94).Value
    For j = 0 To 23
        Count = 0
        MonthCalc = Sheets("Calculation").Range("B" & j + 2).Value & Sheets("Calculation").Range("A" & j + 2).Value
        For k = 0 To LastNCR - 1
            If NCRs(k, 4) = SupplierName Then
                If NCRs(k, 7) = MonthCalc Then
                    If NCRs(k, 5) = code Then
                        Count = Count + 1
                    End If
                End If
            End If
        Next k
    FaultCode(i, j) = Count
    Next j
Next i
Sheets("Charts").Range("C94:Z126") = FaultCode
 
 
Calculate
t_end = Timer
elapsed = CSng(Round(t_end - t_start, 2))
MsgBox "Time required " & elapsed & "s"
End Sub

Et j'ai basculé le code de creation des tableaux dans un autre module qui s'execute a l'ouverture du fichier :

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
Public Static Sub Workbook_Open()
 
Dim LastDeliveries As Integer
Dim LastNCR As Integer
Dim SupplierCode As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SupplierName As String
 
LastDeliveries = Sheets("Deliveries").Range("R2").End(xlDown).Row
LastNCR = Sheets("NCRs").Range("A2").End(xlDown).Row
 
SupplierCode = Sheets("Charts").Range("C3").Value 'UCase(Sheets("Charts").Range("C3").Value)
SupplierName = Sheets("Charts").Range("B3").Value 'UCase(Sheets("Charts").Range("B3").Value)
 
Public Deliveries()
ReDim Deliveries(LastDeliveries - 1, 4)
 
For i = 1 To LastDeliveries - 1
    Deliveries(i, 0) = Sheets("Deliveries").Range("C" & i + 2) 'UCase(Sheets("Deliveries").Range("C" & i + 2)) 'Supplier code
    Deliveries(i, 1) = Sheets("Deliveries").Range("E" & i + 2) 'PartNumber
    Deliveries(i, 2) = Sheets("Deliveries").Range("G" & i + 2) ' UCase(Sheets("Deliveries").Range("G" & i + 2)) 'Cell
    Deliveries(i, 3) = Sheets("Deliveries").Range("I" & i + 2) ' Quantity
    Deliveries(i, 4) = Sheets("Deliveries").Range("R" & i + 2) & Sheets("Deliveries").Range("S" & i + 2) 'MMMYYYY
Next i
 
Public NCRs()
ReDim NCRs(LastNCR - 1, 7)
For i = 1 To LastNCR - 1
    NCRs(i, 0) = Sheets("NCRs").Range("B" & i + 2) 'Cell
    NCRs(i, 1) = Sheets("NCRs").Range("D" & i + 2) 'Part
    NCRs(i, 2) = Sheets("NCRs").Range("H" & i + 2) 'Quantity
    NCRs(i, 3) = Sheets("NCRs").Range("I" & i + 2) 'Unit
    NCRs(i, 4) = Sheets("NCRs").Range("J" & i + 2) 'UCase(Sheets("NCRs").Range("J" & i + 2)) 'Supplier
    NCRs(i, 5) = Sheets("NCRs").Range("Q" & i + 2) 'Fault code
    NCRs(i, 6) = Sheets("NCRs").Range("Y" & i + 2) 'Status
    NCRs(i, 7) = Sheets("NCRs").Range("Z" & i + 2) & Sheets("NCRs").Range("AA" & i + 2) 'MMMYYYY
Next i
 
End Sub

Et là ça ne fonctionne plus...
Merci pour ton aide Mercatog !

**mercatog** · 19/08/2012, 17h43

La déclaration doit être dans un module standard (en haut avant toutes les procédure du module)

Code :

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

1
2
3
Option Explicit
Public NCRs()
Public Deliveries()

**Michou199** · 19/08/2012, 18h06

Merci voici mon code actuel après modifications :

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
Option Explicit
Public NCRs()
Public Deliveries()
 
Public Static Sub Workbook_Open()
 
Dim LastDeliveries As Integer
Dim LastNCR As Integer
Dim SupplierCode As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SupplierName As String
 
LastDeliveries = Sheets("Deliveries").Range("R2").End(xlDown).Row
LastNCR = Sheets("NCRs").Range("A2").End(xlDown).Row
 
SupplierCode = Sheets("Charts").Range("C3").Value 'UCase(Sheets("Charts").Range("C3").Value)
SupplierName = Sheets("Charts").Range("B3").Value 'UCase(Sheets("Charts").Range("B3").Value)
 
Dim Deliveries()
ReDim Deliveries(LastDeliveries - 1, 4)
 
For i = 1 To LastDeliveries - 1
    Deliveries(i, 0) = Sheets("Deliveries").Range("C" & i + 2) 'UCase(Sheets("Deliveries").Range("C" & i + 2)) 'Supplier code
    Deliveries(i, 1) = Sheets("Deliveries").Range("E" & i + 2) 'PartNumber
    Deliveries(i, 2) = Sheets("Deliveries").Range("G" & i + 2) ' UCase(Sheets("Deliveries").Range("G" & i + 2)) 'Cell
    Deliveries(i, 3) = Sheets("Deliveries").Range("I" & i + 2) ' Quantity
    Deliveries(i, 4) = Sheets("Deliveries").Range("R" & i + 2) & Sheets("Deliveries").Range("S" & i + 2) 'MMMYYYY
Next i
 
Dim NCRs()
ReDim NCRs(LastNCR - 1, 7)
For i = 1 To LastNCR - 1
    NCRs(i, 0) = Sheets("NCRs").Range("B" & i + 2) 'Cell
    NCRs(i, 1) = Sheets("NCRs").Range("D" & i + 2) 'Part
    NCRs(i, 2) = Sheets("NCRs").Range("H" & i + 2) 'Quantity
    NCRs(i, 3) = Sheets("NCRs").Range("I" & i + 2) 'Unit
    NCRs(i, 4) = Sheets("NCRs").Range("J" & i + 2) 'UCase(Sheets("NCRs").Range("J" & i + 2)) 'Supplier
    NCRs(i, 5) = Sheets("NCRs").Range("Q" & i + 2) 'Fault code
    NCRs(i, 6) = Sheets("NCRs").Range("Y" & i + 2) 'Status
    NCRs(i, 7) = Sheets("NCRs").Range("Z" & i + 2) & Sheets("NCRs").Range("AA" & i + 2) 'MMMYYYY
Next i
 
End Sub
 
Sub Calculation()
 
Calculate
 
Dim LastDeliveries As Integer
Dim LastNCR As Integer
Dim SupplierCode As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim SupplierName As String
Dim MonthCalc As String
Dim NumberParts As Long
Dim NumberNCR As Integer
Dim CountParts As Integer
Dim CountMeters As Integer
Dim CountLitres As Integer
Dim CountKg As Integer
Dim CountGallons As Integer
Dim Count As Integer
Dim code As String
 
Dim t_start As Single, t_end As Single, elapsed As Single
 
LastDeliveries = Sheets("Deliveries").Range("R2").End(xlDown).Row
LastNCR = Sheets("NCRs").Range("A2").End(xlDown).Row
 
SupplierCode = Sheets("Charts").Range("C3").Value 'UCase(Sheets("Charts").Range("C3").Value)
SupplierName = Sheets("Charts").Range("B3").Value 'UCase(Sheets("Charts").Range("B3").Value)
 
t_start = Timer
 
'Dim Deliveries()
'ReDim Deliveries(LastDeliveries - 1, 4)
'For i = 1 To LastDeliveries - 1
'    Deliveries(i, 0) = Sheets("Deliveries").Range("C" & i + 2) 'UCase(Sheets("Deliveries").Range("C" & i + 2)) 'Supplier code
'    Deliveries(i, 1) = Sheets("Deliveries").Range("E" & i + 2) 'PartNumber
'    Deliveries(i, 2) = Sheets("Deliveries").Range("G" & i + 2) ' UCase(Sheets("Deliveries").Range("G" & i + 2)) 'Cell
'    Deliveries(i, 3) = Sheets("Deliveries").Range("I" & i + 2) ' Quantity
'    Deliveries(i, 4) = Sheets("Deliveries").Range("R" & i + 2) & Sheets("Deliveries").Range("S" & i + 2) 'MMMYYYY
'Next i
 
'Dim NCRs()
'ReDim NCRs(LastNCR - 1, 7)
'For i = 1 To LastNCR - 1
'    NCRs(i, 0) = Sheets("NCRs").Range("B" & i + 2) 'Cell
'    NCRs(i, 1) = Sheets("NCRs").Range("D" & i + 2) 'Part
'    NCRs(i, 2) = Sheets("NCRs").Range("H" & i + 2) 'Quantity
'    NCRs(i, 3) = Sheets("NCRs").Range("I" & i + 2) 'Unit
'    NCRs(i, 4) = Sheets("NCRs").Range("J" & i + 2) 'UCase(Sheets("NCRs").Range("J" & i + 2)) 'Supplier
'    NCRs(i, 5) = Sheets("NCRs").Range("Q" & i + 2) 'Fault code
'    NCRs(i, 6) = Sheets("NCRs").Range("Y" & i + 2) 'Status
'    NCRs(i, 7) = Sheets("NCRs").Range("Z" & i + 2) & Sheets("NCRs").Range("AA" & i + 2) 'MMMYYYY
'Next i
 
For i = 1 To 24
    MonthCalc = Sheets("Calculation").Range("B" & i + 1).Value & Sheets("Calculation").Range("A" & i + 1).Value
    NumberParts = 0
    For j = 0 To LastDeliveries - 1
        If Deliveries(j, 0) = SupplierCode Then
            If Deliveries(j, 4) = MonthCalc Then
            NumberParts = NumberParts + Deliveries(j, 3)
            End If
        End If
    Next j
    Sheets("Calculation").Range("I" & i + 1).Value = NumberParts
 
    NumberNCR = 0
    CountParts = 0
    CountMeters = 0
    CountLitres = 0
    CountKg = 0
    CountGallons = 0
    For j = 0 To LastNCR - 1
        If NCRs(j, 7) = MonthCalc Then
            If NCRs(j, 4) = SupplierName Then
            NumberNCR = NumberNCR + 1
                If NCRs(j, 3) = "parts" Then
                    CountParts = CountParts + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "meters" Then
                    CountMeters = CountMeters + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "litres" Then
                    CountLitres = CountLitres + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "kg" Then
                    CountKg = CountKg + NCRs(j, 2)
                End If
                If NCRs(j, 3) = "gallons" Then
                    CountGallons = NCRs(j, 2)
                End If
            End If
        End If
    Next j
    Sheets("Calculation").Range("C" & i + 1).Value = NumberNCR
    Sheets("Calculation").Range("D" & i + 1).Value = CountParts
    Sheets("Calculation").Range("E" & i + 1).Value = CountMeters
    Sheets("Calculation").Range("F" & i + 1).Value = CountLitres
    Sheets("Calculation").Range("G" & i + 1).Value = CountKg
    Sheets("Calculation").Range("H" & i + 1).Value = CountGallons
 
    If Not NumberParts = 0 Then
        Sheets("Calculation").Range("J" & i + 1).Value = ((CountParts + CountMeters + CountLitres + CountKg + CountGallons) / NumberParts) * 1000000
    Else
        Sheets("Calculation").Range("J" & i + 1).Value = 0
    End If
Next i
 
Dim FaultCode(32, 23)
For i = 0 To 32
    code = Sheets("Charts").Range("B" & i + 94).Value
    For j = 0 To 23
        Count = 0
        MonthCalc = Sheets("Calculation").Range("B" & j + 2).Value & Sheets("Calculation").Range("A" & j + 2).Value
        For k = 0 To LastNCR - 1
            If NCRs(k, 4) = SupplierName Then
                If NCRs(k, 7) = MonthCalc Then
                    If NCRs(k, 5) = code Then
                        Count = Count + 1
                    End If
                End If
            End If
        Next k
    FaultCode(i, j) = Count
    Next j
Next i
Sheets("Charts").Range("C94:Z126") = FaultCode
 
 
Calculate
t_end = Timer
elapsed = CSng(Round(t_end - t_start, 2))
MsgBox "Time required " & elapsed & "s"
End Sub

Sais tu pourquoi dans le sub Calculation, dans la première boucle de j, il me dit que l'indice n'appartient pas à la sélection ?

C'est a la ligne 106

Je pense que c'est parce que les tableaux sont vides, mon code est-il le bon ?