Optimisation de macro

**klash384** · 21/04/2017, 13h03

Bonjour tout le monde !

Je souhaiterai votre avis sur une macro que j'ai faite. Je suis convaincu qu'elle n'est absolument pas optimisée et d'ailleurs elle met environ 2 minutes pour s'executer. Bon après celle-ci doit traiter un tableau d'environ 100 colonnes sur 60000 lignes...(petit fichier de 35 Mo)

Je suis ouvert à toute critique qui pourrait me permettre de m'améliorer et d'améliorer ma macro

Je vous remercie par avance si vous jetez un coup d'oeil à la macro et que vous pouvez m'apporter votre contribution !

Bonne journée à vous la communauté

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
Sub Donnees()
 
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
 
Dim Source As String
Dim Cible As String
Dim Col As Long 'col pour la commune
Dim Col2 As Long 'col pour la superficie des constructions
Dim Col3 As Long 'col pour le type en habitation
Dim Col4 As Long 'col pour le type de construction (maison ou appartement)
Dim Col5 As Long 'col pour le nombre de nouvelles contructions
Dim Col6 As Long 'col pour l'année de construction
Dim Col7 As Long 'col pour le nombre de nouvelles maisons
Dim Col8 As Long 'col pour le nombre de nouveaux appartements
Dim Ligne As Long
Dim Ligne2 As Long
Dim DernLigne As Long
Dim DernLigne2 As Long
Dim DernCol As Long
 
Dim SomSup As String
Dim SomNewConsM As String
Dim SomNewConsA As String
Dim SomNewConsMi As String
Dim SomNewConsDep As String
Dim SomNewConsAct As String
Dim SomSupNewCons As String
 
Source = "source"
Cible = "tableau"
DernLigne = Sheets(Source).Range("A" & Rows.Count).End(xlUp).Row
DernLigne2 = Sheets(Cible).Range("A" & Rows.Count).End(xlUp).Row
DernCol = Sheets(Source).Cells(1, Columns.Count).End(xlToLeft).Column
Ligne2 = 3
 
 
For Col = 1 To DernCol
    If Sheets(Source).Cells(1, Col).Value = "idcomtxt" Then Exit For 'trouve la colonne de la commune
Next
 
For Col2 = 1 To DernCol
    If Sheets(Source).Cells(1, Col2).Value = "dcntpa" Then Exit For 'trouve la colonne de la superficie
Next
 
For Col3 = 1 To DernCol
    If Sheets(Source).Cells(1, Col3).Value = "tpevdom_s" Then Exit For 'trouve le type de construction en habitation
Next
 
For Col4 = 1 To DernCol
    If Sheets(Source).Cells(1, Col4).Value = "tlocdomin" Then Exit For 'trouve la colonne du type de construction en appart ou maison ou mixte ou dépendance ou activité
Next
 
For Col5 = 1 To DernCol
    If Sheets(Source).Cells(1, Col5).Value = "nlochabit" Then Exit For 'trouve la colonne nombre de construction nouvelles
Next
 
For Col6 = 1 To DernCol
    If Sheets(Source).Cells(1, Col6).Value = "jannatmin" Then Exit For 'trouve la colonne de la date de construction
Next
 
For Col7 = 1 To DernCol
    If Sheets(Source).Cells(1, Col7).Value = "nlocmaison" Then Exit For 'trouve la colonne de nombre de maisons nouvelles
Next
 
For Col8 = 1 To DernCol
    If Sheets(Source).Cells(1, Col8).Value = "nlocappt" Then Exit For 'trouve la colonne de nombre d'appartements nouveaux
Next
 
'MsgBox ("Col1 " & Col & " ; Col2 " & Col2 & " ; Col3 " & Col3 & " ; Col4 " & Col4 & " ; Col5 " & Col5 & " ; Col6 " & Col6)
 
For Ligne2 = 3 To DernLigne2
    SomSup = 0
    SomNewConsM = 0
    SomNewConsA = 0
    SomNewConsMi = 0
    SomNewConsDep = 0
    SomNewConsAct = 0
    SomSupNewCons = 0
 
    For Ligne = 2 To DernLigne
        If Sheets(Cible).Cells(Ligne2, 1) = Sheets(Source).Cells(Ligne, Col) Then 'correspondance des communes
 
            If Sheets(Source).Cells(Ligne, Col5) = "" Then      'on calcule nlochabit en faisant la somme de nlocmaison + nlocappt
                Sheets(Source).Cells(Ligne, Col5) = Sheets(Source).Cells(Ligne, Col7).Value + Sheets(Source).Cells(Ligne, Col8).Value
            End If
 
            If Sheets(Source).Cells(Ligne, Col5) > 0 Then                           'calcul du SomSup
                If Sheets(Source).Cells(Ligne, Col3) = "HABITATION" Then
                    If Sheets(Source).Cells(Ligne, Col2) < 10000 Then
                        SomSup = SomSup + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                End If
            End If
 
 
            If Sheets(Source).Cells(Ligne, Col6) >= 1999 Then                   'calcul des somnewcons
                If Sheets(Source).Cells(Ligne, Col3) = "HABITATION" Then
 
                    If Sheets(Source).Cells(Ligne, Col4) = "MAISON" Then
                        SomNewConsM = SomNewConsM + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "APPARTEMENT" Then
                        SomNewConsA = SomNewConsA + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "MIXTE" Then
                        SomNewConsMi = SomNewConsMi + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "DEPENDANCE" Then
                        SomNewConsDep = SomNewConsDep + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "ACTIVITE" Then
                        SomNewConsAct = SomNewConsAct + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                End If
            End If
 
        End If
    Next
    Sheets(Cible).Cells(Ligne2, 2) = SomSup                                         'place les valeurs calculées dans le tableau
    Sheets(Cible).Cells(Ligne2, 3) = SomNewConsM
    Sheets(Cible).Cells(Ligne2, 4) = SomNewConsA
    Sheets(Cible).Cells(Ligne2, 5) = SomNewConsMi
    Sheets(Cible).Cells(Ligne2, 6) = SomNewConsDep
    Sheets(Cible).Cells(Ligne2, 7) = SomNewConsAct
    Sheets(Cible).Cells(Ligne2, 8) = SomSupNewCons
Next
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

**unparia** · 21/04/2017, 13h10

Bonjour

Je suis ouvert à toute critique qui pourrait me permettre de m'améliorer et d'améliorer ma macro

Je vous remercie par avance si vous jetez un coup d'oeil à la macro et que vous pouvez m'apporter votre contribution !

je veux retenir que tu veux y mettre du tien pour "améliorer" et non attendre d e voir un code écrit autrement et à copier/coller --->>
Ma réponse sera donc pour l'instant celle-ci : intéresse-toi déjà à l'utilisation de Select Case (rubrique Select Case, instruction de l'aide VBA interne)
Reviens avec ce que tu auras écrit sur cette première base.

**klash384** · 21/04/2017, 13h40

Merci pour la réponse !
Et oui j'ai envie de m'améliorer, donc tâtonner peu faire du bien quand on est mis sur la piste

Je viens de gribouiller un peu (sans tout de suite tester le code pour avoir ton retour dans un premier temps)

et ça donne ça :

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
 
 
                    Dim MaVariable
                    MaVariable = Sheets(Source).Cells(Ligne, Col4)
                    Select Case MaVariable
                        Case "MAISON"
                            SomNewConsM = SomNewConsM + 1
                            SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                        Case "APPARTEMENT"
                            SomNewConsA = SomNewConsA + 1
                            SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                        Case "MIXTE"
                            SomNewConsMi = SomNewConsMi + 1
                            SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                        Case "DEPENDANCE"
                            SomNewConsDep = SomNewConsDep + 1
                            SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                        Case "ACTIVITE"
                            SomNewConsAct = SomNewConsAct + 1
                            SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End Select

Qui viendrai remplacer ceci

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
 
 
                    If Sheets(Source).Cells(Ligne, Col4) = "MAISON" Then
                        SomNewConsM = SomNewConsM + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "APPARTEMENT" Then
                        SomNewConsA = SomNewConsA + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "MIXTE" Then
                        SomNewConsMi = SomNewConsMi + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "DEPENDANCE" Then
                        SomNewConsDep = SomNewConsDep + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If
                    If Sheets(Source).Cells(Ligne, Col4) = "ACTIVITE" Then
                        SomNewConsAct = SomNewConsAct + 1
                        SomSupNewCons = SomSupNewCons + Sheets(Source).Cells(Ligne, Col2).Value
                    End If

Edit : je viens de tester le code et il fonctionne nickel

, mais la procédure reste assez "longue" tout de même. Faudrait que j'essaye de chronométrer avec les if puis le select case pour voir le gain de temps.

**Pierre Fauconnier** · 21/04/2017, 13h58

Salut.

Je ne suis pas persuadé par le fait que remplacer des if par des select case améliore le temps d'exécution de manière significative. Par contre, c'est incontestablement mieux en termes de lisibilité et de maintenabilité du code.

Au delà, ce qu'il y a peut-être à améliorer, c'est le processus, même si 200 colonnes sur 60.000 lignes, ça prendra inévitablement un peu de temps.

Ce qui serait donc intéressant, c'est de savoir en quoi consiste tes traitements... Suite à une lecture très rapide de ton code, je me dis que tu pourrais peut-être t'appuyer sur Excel et ses formules plutôt que de réaliser des sommes conditionnelles par VBA...

A toi d'expliquer ce que tu souhaites obtenir, en gardant à l'esprit une des lignes de ma signature: "Lorsque l'on fait du VBA pour Excel, on pense Excel avant de penser VBA..."

[EDIT 14:09] Et tant qu'à faire, travailler sur la conception de ton classeur peut incontestablement faciliter et accélérer les traitements. [/EDIT]

**klash384** · 21/04/2017, 14h12

Bonjour Pierre,
Et merci pour ta réponse !

Effectivement je viens de chronométrer la macro avec les If puis avec le select case et c'est exactement le meme temps : 1min08 (pour être précis

)

Dans un premier temps j'ai pu réduire le nombre de colonnes car dans le traitement des données dans ce cas je n'ai besoin que de 6 colonnes. Donc via une autre macro je supprime toutes les colonnes dont je n'ai pas besoin. Ce qui me laisse un tableau de 6 colonnes par 74000 lignes.

Ce tableau représente la répartition de l'habitation sur 90 communes que doit traiter un bureau d'études. Ils m'ont demander un petit coup de main pour ne pas avoir à le faire à la main justement

Et ce fichier pourra être réutilisé par la suite pour d'autres fichiers du genre avec d'autres communes qui seront référencées dans un autre fichier. Ce que je voulais donc faire c'est leur proposer un outil tout prêt dans lequel ils n'auront pas de formules excel (qu'ils pourraient supprimer par inadvertance et ne pas savoir comment remettre), mais juste un bouton sur lequel appuyer pour que tout le traitement se fasse automatiquement.

Nom : magic.jpg
Affichages : 138
Taille : 26,7 Ko

Nom : magic.jpg
Affichages : 138
Taille : 26,7 Ko

voici un aperçu de l’entête du tableau où vont aller les données triées.

Je pense qu'il n'est pas nécessaire que je mette le tableau "source", ce sont juste des données brutes, donc pas très intéressantes.

J'espère que j'ai réussi à exprimer clairement ma pensée par l'écrit, sinon dites le moi et j'essayerai de le reformuler

**Pierre Fauconnier** · 21/04/2017, 14h17

Envoyé par klash384

[...]
Effectivement je viens de chronométrer la macro avec les If puis avec le select case et c'est exactement le meme temps : 1min08 (pour être précis

)[...]

Ben oui. Select Case, ce n'est jamais qu'une manière plus élégante d'écrire une suite de bloc IF...END IF qui porte sur la même valeur

Envoyé par klash384

[...]
Je pense qu'il n'est pas nécessaire que je mette le tableau "source", ce sont juste des données brutes, donc pas très intéressantes.[...]

Je pense au contraire qu'il serait intéressant de voir tes données source, ou tout du moins la structure, car je me dis qu'un TCD pourrait si pas convenir, à tout le moins t'aider.

Une autre piste serait de formuler le tableau en VBA puis de copier-coller en valeurs. Je gage que le traitement serait beaucoup plus rapide (SOMMEPROD, SOMME.SI.ENS, ...).

[EDIT 14:22]
Il y a peut-être et même très probablement moyen d'utiliser ces fonctions au sein de VBA pour n'ajouter que les valeurs dans le tableau des indicateurs.

Toutes ces solutions seront en tout cas plus rapides et plus maintenables qu'une itération et d'un codage "en dur" de données métier dans le code => Avec ta technique, une simple modification de libellé d'une catégorie oblige à une modification du code => maintenabilité = 0 et transposabilité = 0...
[/EDIT]

**klash384** · 21/04/2017, 14h27

Voici donc l'aperçu du tableau a exploiter. Je n'ai conservé que les colonnes dont j'ai besoin.
En revanche c'est compliqué dans un imprim écran d'y mettre les 74000 lignes

Sinon le fichier excel sur lequel je travaille avec macros et tableau a exploiter (après suppression des colonnes inutiles ne fait plus que 2,6 Mo) Si besoin je peux le joindre

Nom : magicsource.jpg
Affichages : 229
Taille : 295,7 Ko

Nom : magicsource.jpg
Affichages : 229
Taille : 295,7 Ko

**Pierre Fauconnier** · 21/04/2017, 15h09

Sans aucune hésitation, je te conseille de travailler avec des formules Excel puis de convertir en valeurs en fin de calcul.

Tu crées la formule à la main, tu en récupères la version internationale en VBA, tu places ta (tes) formule(s) en VBA et tu copies-colles en spécial valeurs. Je pense que tu remplaceras ta Deux Chevaux par une Ferrari

[EDIT 15:28]
Sinon, il y a la possibilité de travailler avec un TCD puis de ramener les valeurs issues du TCD dans ton tableau d'indicateur.

Mais avant ces solutions, je proposerais déjà un simple TCD au client. Si ça tombe, ça suffirait largement, et il n'y a alors besoin d'aucune macro...
[/EDIT]

Invité · 21/04/2017, 15h30

Bonjour,
en ce qui concerne le select caise c'est incontestablement plus rapide encore faut il le présenté en ce qui est statistiquement du plus usité au moins!

j'ai vérifié l'assembleur d'un code compilé en Obj y a pas photo!
je ne sais plus si c'est dysorthographie ou rdurupt mais l'un des deux moi même à déjà posté à ce sujet sur ce forum!

Code :

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

1
2
3
4
5
celect case
Case PlusUsité
Case MoyennementUsité
case MoisUsité
end select

en ce qui concerne les formule je suis d'accord ace Pierre Plage.formulaR1C1=formule Plage.value=Plage.value!

**Pierre Fauconnier** · 21/04/2017, 15h43

Salut Robert,

Oui, tu as raison, bien sûr...

Je réfléchissais dans le cas d'un IF ELSE END IF où je pense que le gain du SELECT CASE est moindre.

Mais si les IF ne sont pas imbriqués (comme ici), c'est certain que SELECT CASE est beaucoup plus rapide, surtout ordonné comme tu le proposes...

Merci d'avoir rappelé cette précision...

**patricktoulon** · 21/04/2017, 15h44

je ne sais plus si c'est dysorthographie ou rdurupt mais l'un des deux moi même à déjà posté à ce sujet sur ce forum!

et oui le dédoublement de personnalité provoque des perte de mémoire je ne sais plus qui est qui ou moi même etc.....

**unparia** · 21/04/2017, 16h46

Ce n'est pas la différence de temps d'exécution entre les deux bouts de codes montrés par klash384 en message N° 3, qui est vraiment significative (surtout si l'on utilise des clauses else pour éviter de tout vérifier), mais celle (très significative, celle-là) entre le code montré dans le tout premier message d'ouverture de la discussion et un code qui utiliserait soit Select Case, (plus lisible), soit des clauses else if.
Regardez-le à nouveau, le code en question --->>
J'en extrais la moelle et le mal :

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
For Col = 1 To DernCol
    If Sheets(Source).Cells(1, Col).Value = ...
Next
 
For Col2 = 1 To DernCol
    If Sheets(Source).Cells(1, Col2).Value = ...
Next
 
For Col3 = 1 To DernCol
    If Sheets(Source).Cells(1, Col3).Value = ...
Next
 
For Col4 = 1 To DernCol
    If Sheets(Source).Cells(1, Col4).Value = ...
Next
 
For Col5 = 1 To DernCol
    If Sheets(Source).Cells(1, Col5).Value = ...
Next
 
For Col6 = 1 To DernCol
    If Sheets(Source).Cells(1, Col6).Value = ...
Next
 
For Col7 = 1 To DernCol
    If Sheets(Source).Cells(1, Col7).Value = ...
Next
 
For Col8 = 1 To DernCol
    If Sheets(Source).Cells(1, Col8).Value = ...
Next

J'espère que vous comprendrez maintenant mieux (le demandeur, surtout) le sens de mon intervention.

EDIT : le passage par une matrice "tremplin" (que je ne proposerai qu'après, en complément) accélèrerait encore un peu plus.

**klash384** · 21/04/2017, 17h15

Oula !

Vous m'avez perdu !

Unparia, si je comprends bien, tu dis que ce qui fait ralentir pas mal de chose serait le bout de code que tu viens de montrer ?

Pierre, passer par des formules via le tableur pour les ramener sur des formules en vba, c'est bien ça l'idée ? mais ça va pas être un peu long et laborieux pour faire ça ?

Dysorthographie, tu veux dire que la manière dont j'ai présenté le select case n'est pas optimisée ? Tu parles d'un post que tu as déjà mis concernant cette question, tu aurais le lien par hasard ?

Merci pour vos réponses !

**unparia** · 21/04/2017, 17h19

Unparia, si je comprends bien, tu dis que ce qui fait ralentir pas mal de chose serait le bout de code que tu viens de montrer ?

Pardi ! Tu passe ton temps à parcourir et reparcourir et re-reparcourir toutes tes cellules.
L'utilisation proposée de select case réduirait à un seul tous ces parcours-là !

Invité · 21/04/2017, 18h16

Dysorthographie, tu veux dire que la manière dont j'ai présenté le select case n'est pas optimisée ? Tu parles d'un post que tu as déjà mis concernant cette question, tu aurais le lien par hasard ?

c'est une discussion un peut ésotériques qui démontre ce que j'ai dis dans ce poste!

Sens vouloir de vexé je ne pense pas qu'elle porte grand chose!

Je ne me suis pas vraiment péché sur ton code, de mon téléphone c'est pas bien visible.

Mais si je ne trahis pas la pensée de unparia,il veut dire que multiplier le boucle pour chaque colonnes n'est pas pour réduire le temps se traitement. Alors que peut-être un boucle en qualifiant toutes le colonnes la ligne...

**unparia** · 21/04/2017, 18h25

Bonjour Robert

Alors que peut-être un boucle en qualifiant toutes le colonnes la ligne...

C'est là, son "hic" : toutes ses boucles parcourent exactement le même chemin : même ligne (1) et colonnes
Une seule boucle. Et c'est au sein de cette seule boucle, que doit être utilisé select case.
Je ne vois pas ce qui est "ésotérique" là-dedans.

EDIT : je souhaite qu'une chose soit claire : je n'approuve toutefois pas du tout la méthode *****(La mienne serait totalement autre. Elle n'aurait rien à voir, ni de près, ni de loin, avec celle utilisée ici pour ... finalement "relever" des N°s de colonnes dans des variables ). Je me suis contenté de montrer du doigt la lourdeur dans l'application maladroite de la méthode choisie pour faire ces relevés ...

***** j'aurais répondu différemment si les tenants et aboutissants (que je devine) avaient été exposés (et une idée d'autre solution demandée) au lieu de demander comment "améliorer" le codage de la démarche choisie et présentée .

**klash384** · 21/04/2017, 19h33

Unparia, l'amélioration peut consister en la refonte complète

Mais de ce que j'ai pu comprendre du forum, le but n'est pas de venir et demander à ce que l'on me fasse un truc tout fait comme on viendrait au Mac Drive, mais plutôt de s'enrichir des connaissances des autres. Bref, fini la parenthèse de l'amour

Tu serais parti dans quel sens ? Car vu que je ne suis pas une pointure dans le vba, je n'ai jamais appris d'autres choses pour le moment..

Merci en tous cas

**Pierre Fauconnier** · 21/04/2017, 19h34

Je t'ai donné des pistes, qui évitent les boucles qui sont généralement gourmandes...

**HopPopPop** · 21/04/2017, 21h12

Bonsoir à tous,

Si je peux me permettre, pourquoi ne pas utiliser la méthode Find en remplacement des boucles pour aller directement sur les bonnes colonnes "idcomtxt" et autres ...