Exécution macro excessivement longue

Version imprimable

Bonjour.

Je m'amuse actuellement à faire des statistiques sur les courses de chevaux.
Pour ce faire je récupère toutes les performances des chevaux en créant une feuille par cheval dans un classeur nommé "BDD globale". Chaque feuille prend le nom du cheval en question. La première feuille de ce classeur se nomme "chevaux" et il contient le nom de tous les chevaux.

J'ai crée un deuxième classeur pour le "traitement" des données.
Ce traitement consiste à comparer la performance d'un cheval selon sa performance précédente. Lorsque je traite un petit nombre de chevaux, le code que j'ai mis en place fonctionne correctement. Par contre si je veux traiter tous les chevaux se trouvant dans "BDD globale" alors le traitement dure une éternité.

J'ai donc plusieurs questions:
- Est ce que le fait de travailler avec plusieurs milliers de feuilles dans un classeur est "viable" avec EXCEL?
- Est ce qu'il faudrait mieux passer par ACCESS pour ce que je veux faire? Je me suis dirigé sur Excel car j'y avais quelques notions.

Je vous joins mon code pour que vous puissiez éventuellement me dire si celui ci vous semble correct, pour savoir si la méthode utilisée est la bonne et me donner des pistes pour régler mon problème. Merci.

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
 
Sub pourcentage_dernière_perf()
 
    Dim Discipline As String
    Dim Nombre_de_chevaux As Integer
    Dim NomCheval As String
    Dim Resultat As Variant
    Dim DernPerf As Variant
    Dim compteur As Integer
    Dim pourcentage As Single
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    Dim tableau(3, 6) As Integer
 
 
 
    ''''''''''''''Compteur pour barre de progression
    'compteur = 1
 
    ''''''''''''''Remise à zéro du tableau dernière performance
 
    For i = 1 To 3
        For j = 1 To 6
            tableau(i, j) = 0
            Range("a1").Offset(i, j) = 0
        Next j
    Next i           
 
    Nombre_de_chevaux = Workbooks("BDD globale").Sheets("chevaux").Range("a10000").End(xlUp).Row
 
    For i = 0 To 100 '(Nombre_de_chevaux - 1)
 
        ''''''''''Déterminer NomCheval pour "ouvrir" la feuille cheval
        NomCheval = Workbooks("BDD globale").Sheets("chevaux bis").Range("a1").Offset(i, 0)
 
 
        ''''''''''Déterminer la discipline
        If Workbooks("BDD globale").Sheets(NomCheval).Range("f2") = "Plat" Then
            Discipline = "Plat"
            Else
            Discipline = "Autre"
        End If
 
        ''''''''''Déterminer le nombre de performance du cheval
        NbPerf = Workbooks("BDD globale").Sheets(NomCheval).Range("a300").End(xlUp).Row - 1
 
        ''''''''''Si conditions remplies, faire comparatif entre DernPerf et Resultat
        If (NbPerf > 2) And (Discipline = "Plat") Then
 
            For j = 2 To NbPerf
                DernPerf = Workbooks("BDD globale").Sheets(NomCheval).Range("k1").Offset(j, 0)
                Resultat = Workbooks("BDD globale").Sheets(NomCheval).Range("k1").Offset((j - 1), 0)
                Select Case DernPerf
                    Case 1
                        tableau(1, 1) = tableau(1, 1) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 1) = tableau(2, 1) + 1
                            Case 2 To 3
                                tableau(3, 1) = tableau(3, 1) + 1
                            Case Else
                        End Select
 
                    Case 2
                        tableau(1, 2) = tableau(1, 2) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 2) = tableau(2, 2) + 1
                            Case 2 To 3
                                tableau(3, 2) = tableau(3, 2) + 1
                            Case Else
                        End Select
 
                    Case 3
                        tableau(1, 3) = tableau(1, 3) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 3) = tableau(2, 3) + 1
                            Case 2 To 3
                                tableau(3, 3) = tableau(3, 3) + 1
                            Case Else
                        End Select
 
                    Case 4
                        tableau(1, 4) = tableau(1, 3) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 4) = tableau(2, 4) + 1
                            Case 2 To 3
                                tableau(3, 4) = tableau(3, 4) + 1
                            Case Else
                        End Select
 
                    Case 5
                        tableau(1, 5) = tableau(1, 5) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 5) = tableau(2, 5) + 1
                            Case 2 To 3
                                tableau(3, 5) = tableau(3, 5) + 1
                            Case Else
                        End Select
 
                    Case Else
                        tableau(1, 6) = tableau(1, 6) + 1
                        Select Case Resultat
                            Case 1
                                tableau(2, 6) = tableau(2, 6) + 1
                            Case 2 To 3
                                tableau(3, 6) = tableau(3, 6) + 1
                            Case Else
                        End Select
 
                End Select
            Next j
        End If
 
 
    Next i
 
    '''''''''Affichage des résultats
    For k = 1 To 3
        For l = 1 To 6
            Range("a1").Offset(k, l) = tableau(k, l)
        Next l
    Next k
 
 
End Sub

24/05/2012, 12h58
kiki29

Salut, la réponse est dans la question, c'est oui et Access. De toute façon cela restera "lent"
24/05/2012, 13h00
Mr.Mof

Bonjour,

Si tu as un nombre relativement important de données à traiter,
Ca peut être intéressant de passer par ACCESS. En plus, tu as pas mal de calcul qui n'aident pas à accélerer le traitement.

Tu pourrait dans ACCESS créer une table contenant le nom de tes chevaux, la dernière performance, la performance précédente créer un formulaire dans lequel tu choisit le nom du cheval dont tu veux voir les statistiques et tu calcul les résultats.

Cordialement.
24/05/2012, 14h54
nezguich

Merci pour vos premiers éléments de réponses.

En fouillant un peu, j'ai découvert l'existence des tableaux, et surtout leur utilisation pour réduire les durées de macros. Je vais essayer de passer par cette méthode, ne serait-ce que pour ma "culture" excelienne et si cela ne me convient pas alors j'irai voir sur ACCESS.

Ce principe des tableaux réduiraient quand même considérablement la durée de ma macro, vous pouvez me confirmer?
24/05/2012, 15h42
nchal

Bonjour,

D'après ce que j'ai lu et pour te rassurer, il me semble qu'effectivement, utiliser des tableaux améliore les performances au prix d'une certaine complexité.
24/05/2012, 15h49
ZebreLoup

Oui, je confirme, travailler directement sur les cellules prend beaucoup de temps. En récupérant directement des plages de cellules dans des Variant, tu vas gagner en temps de réponse, même si ça reste un langage interprété et non compilé.

Après quelques tâtonnements et l'utilisation d'un tableau, j'ai réussi à réduire de moitié la durée de traitement, c'est un premier pas.

Je vous joins mon code que vous puissiez éventuellement me dire si de l'optimisation est encore possible. Dans ma petite tête j'imagine qu'il doit être possible de "simplifier" la volée de select case.

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
 
Sub test_tableau_dern_perf()
 
    Dim i, j, k, l As Integer
    Dim NbChevaux As Integer
    Dim NomCheval As String
    Dim TableauPerf() As Variant
    Dim tableau(1 To 3, 1 To 6) As Integer
    Dim NbPerf As Integer
    Dim DernPerf As Variant
    Dim Resultat As Variant
    Dim thetimerstart As Long, thetimerstop As Long
    DernPerf = 0
    Resultat = 0
 
    thetimerstart = Timer
 
    '''''''''''''''''Remise à 0 du tableau
    For i = 1 To 3
        For j = 1 To 6
            Range("a1").Offset(i, j) = 0
            tableau(i, j) = 0
        Next j
    Next i
 
    NbChevaux = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets("chevaux bis").Range("a10000").End(xlUp).Row
 
    For i = 0 To 3 '(NbChevaux - 1)
 
        '''''''''''''''Déterminer NomCheval pour ouvrir la feuille cheval
        NomCheval = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets("chevaux bis").Range("a1").Offset(i, 0)
 
        '''''''''''''''Déterminer le nombre de performance du cheval
        NbPerf = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("a300").End(xlUp).Row - 1
 
            '''''''''''''''Déterminer la discipline
            If Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("f2") = "Plat" Then
                Discipline = "Plat"
                Else
                Discipline = "Autre"
            End If
 
            '''''''''''''''Faire le test si les conditions sont remplies
            If (NbPerf > 2) And (Discipline = "Plat") Then
 
                '''''''''''''''Redimensionner tableau et le remplir
                ReDim TableauPerf(0 To NbPerf)
                For j = 0 To UBound(TableauPerf()) - 1
                    TableauPerf(j) = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Cells(j + 2, 11).Value
                    Debug.Print TableauPerf(j)
                Next j
                Debug.Print "suivant" & NbPerf
 
 
                '''''''''''''''Comparaison au sein de TableauPerf
                For k = 1 To NbPerf - 1
 
                    DernPerf = TableauPerf(k)
                    Resultat = TableauPerf(k - 1)
 
                    Debug.Print DernPerf & " " & Resultat
                    Select Case DernPerf
                        Case 1
                            tableau(1, 1) = tableau(1, 1) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 1) = tableau(2, 1) + 1
                                Case 2 To 3
                                    tableau(3, 1) = tableau(3, 1) + 1
                                Case Else
                            End Select
 
                        Case 2
                            tableau(1, 2) = tableau(1, 2) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 2) = tableau(2, 2) + 1
                                Case 2 To 3
                                    tableau(3, 2) = tableau(3, 2) + 1
                                Case Else
                            End Select
 
                        Case 3
                            tableau(1, 3) = tableau(1, 3) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 3) = tableau(2, 3) + 1
                                Case 2 To 3
                                    tableau(3, 3) = tableau(3, 3) + 1
                                Case Else
                            End Select
 
                        Case 4
                            tableau(1, 4) = tableau(1, 4) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 4) = tableau(2, 4) + 1
                                Case 2 To 3
                                    tableau(3, 4) = tableau(3, 4) + 1
                                Case Else
                            End Select
 
                        Case 5
                            tableau(1, 5) = tableau(1, 5) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 5) = tableau(2, 5) + 1
                                Case 2 To 3
                                    tableau(3, 5) = tableau(3, 5) + 1
                                Case Else
                            End Select
 
                        Case Else
                            tableau(1, 6) = tableau(1, 6) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 6) = tableau(2, 6) + 1
                                Case 2 To 3
                                    tableau(3, 6) = tableau(3, 6) + 1
                                Case Else
                            End Select
 
                    End Select
 
 
                Next k
 
            End If
 
        Next i
 
           '''''''Affichage
                For l = 1 To 3
                For j = 1 To 6
                Range("a1").Offset(l, j) = tableau(l, j)
                Next j
                Next l
      thetimerstop = Timer - thetimerstart
      Range("h1") = thetimerstop      
 
End Sub

Zèbre Loup, excuse mon ignorance mais quand tu parles de langage interprété, tu parles du langage VBA? A la différence d'un langage comme par exemple le C qui serait compilé ou bien je n'ai rien compris?

25/05/2012, 08h27
Theze
Bonjour,

Ici, seul l est déclaré Integer, les autres sont Variant :
Code:

1 2 Dim i, j, k, l As Integer
donc, pour qu'ils soient tous Integer, il te faut faire :
Code:

1 2 Dim I As Integer, J As Integer, k As Integer, L As Integer
Juste deux petites améliorations qui ne vont pas apporter beaucoup de gain de temps mais simplifie un peu le code.
Pour la remise à zéro :
Code:

1 2 3 Range("B2:G4") = 0 Erase tableau()
(Bien que pour la tableau ça n'a aucune important puisque sa portée n'est que de niveau Sub et de ce fait en début de proc il n'est pas encore initialisé)
Pour l'affectation des valeurs du tableau aux cellules :
Code:

1 2 Range("B2:G4") = tableau
Hervé.

C'est effectivement l'accès au cellule qui prend du temps. Donc la dernière remarque de Theze devrait te faire gagner beaucoup et j'y ajouterais de remplacer
Code:

1 2 3 4 5 6 ReDim TableauPerf(0 To NbPerf) For j = 0 To UBound(TableauPerf()) - 1 TableauPerf(j) = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Cells(j + 2, 11).Value Debug.Print TableauPerf(j) Next j Debug.Print "suivant" & NbPerf
par

Code:

TableauPerf = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("K2:K" & UBound(TableauPerf, 1) + 1)

Attention par contre à déclarer TableauPerf comme ça :

Code:

Dim TableauPerf As Variant

Et à faire des

Code:

TableauPerf(k, 1)

plutôt que

Code:

TableauPerf(k)

26/05/2012, 09h44
nezguich

Merci beaucoup pour votre aide.

Pour le "remplissage" du TableauPerf en fait j'ai essayé de faire comme tu me l'indiques ZèbreLoup, mais la macro me renvoie une erreur '13' incompatibilité de type.

Une idée d'où cela peut venir?

J'ai résolu le problème en utilisant un ReDim.
Je remets mon code final qui m'a permis de passer de plus d'une heure de traitement à à peine 4 minutes. Merci pour vos conseils avisés.

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
 
Sub test_tableau_dern_perf()
 
    Dim i As Integer, j As Integer, k As Integer, l As Integer
    Dim NbChevaux As Integer
    Dim NomCheval As String
    Dim TableauPerf As Variant
    Dim tableau(1 To 3, 1 To 6) As Integer
    Dim NbPerf As Integer
    Dim DernPerf As Variant
    Dim Resultat As Variant
    Dim thetimerstart As Long, thetimerstop As Long
    DernPerf = 0
    Resultat = 0
 
    thetimerstart = Timer
 
    '''''''''''''''''Remise à 0 du tableau
    Range("B2:G4") = 0
    Erase tableau()
 
    NbChevaux = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets("chevaux bis").Range("a10000").End(xlUp).Row
 
    For i = 0 To (NbChevaux - 1)
 
        '''''''''''''''Déterminer NomCheval pour ouvrir la feuille cheval
        NomCheval = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets("chevaux bis").Range("a1").Offset(i, 0)
 
        '''''''''''''''Déterminer le nombre de performance du cheval
        NbPerf = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("a300").End(xlUp).Row - 1
 
            '''''''''''''''Déterminer la discipline
            If Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("f2") = "Plat" Then
                Discipline = "Plat"
                Else
                Discipline = "Autre"
            End If
 
            '''''''''''''''Faire le test si les conditions sont remplies
            If (NbPerf > 2) And (Discipline = "Plat") Then
 
                '''''''''''''''Redimensionner tableau et le remplir
                ReDim TableauPerf(NbPerf, 1)
                TableauPerf = Workbooks("Import Fiche Cheval Geny BDD glob").Sheets(NomCheval).Range("K2:K" & UBound(TableauPerf, 1) + 1)
 
                '''''''''''''''Comparaison au sein de TableauPerf
                For k = 1 To NbPerf - 1
 
                    DernPerf = TableauPerf(k + 1, 1)
                    Resultat = TableauPerf(k, 1)
 
                    Debug.Print DernPerf & " " & Resultat
                    Select Case DernPerf
                        Case 1
                            tableau(1, 1) = tableau(1, 1) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 1) = tableau(2, 1) + 1
                                Case 2 To 3
                                    tableau(3, 1) = tableau(3, 1) + 1
                                Case Else
                            End Select
 
                        Case 2
                            tableau(1, 2) = tableau(1, 2) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 2) = tableau(2, 2) + 1
                                Case 2 To 3
                                    tableau(3, 2) = tableau(3, 2) + 1
                                Case Else
                            End Select
 
                        Case 3
                            tableau(1, 3) = tableau(1, 3) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 3) = tableau(2, 3) + 1
                                Case 2 To 3
                                    tableau(3, 3) = tableau(3, 3) + 1
                                Case Else
                            End Select
 
                        Case 4
                            tableau(1, 4) = tableau(1, 4) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 4) = tableau(2, 4) + 1
                                Case 2 To 3
                                    tableau(3, 4) = tableau(3, 4) + 1
                                Case Else
                            End Select
 
                        Case 5
                            tableau(1, 5) = tableau(1, 5) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 5) = tableau(2, 5) + 1
                                Case 2 To 3
                                    tableau(3, 5) = tableau(3, 5) + 1
                                Case Else
                            End Select
 
                        Case Else
                            tableau(1, 6) = tableau(1, 6) + 1
                            Select Case Resultat
                                Case 1
                                    tableau(2, 6) = tableau(2, 6) + 1
                                Case 2 To 3
                                    tableau(3, 6) = tableau(3, 6) + 1
                                Case Else
                            End Select
 
                    End Select
 
 
                Next k
 
            End If
 
        Next i
                                            '''''''Affichage
      Range("B2:G4") = tableau
 
      thetimerstop = Timer - thetimerstart
      Range("h1") = thetimerstop
 
End Sub