Discussion :

[Orhleil]

Bonjour à tous,
Je réalise une macro qui traite des données provenant d'un autre fichier Excel (et à terme d'un troisième fichier...). Ledit autre fichier contient environ 27000 lignes de données. Mon traitement fonctionne bien, mais la macro actuelle (qui est amenée à se complexifier un peu) met déjà près de 1h30 à s'exécuter... Je vous mets juste après le code de la macro en question, dans les (...) des bouts de codes sans importance sur la durée d'exécution, et je vous joins aussi le code de macros auxquelles je fais appel.
Si quelqu'un a une vague idée de ce qui fait ramer à ce point, je suis preneur... Merci d'avance.
Le ScreenUpdating est désactivé, ainsi que le calculation, idem pour les Events et même l'écran de veille (bah oui, en 1h30 l'écran de veille se déclenche...)

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
Private Sub MajDonnees()
    '(...)
    ActiveScreenSaver False
    'Ouverture du fichier ExtractionDPN.xls
    On Error GoTo TraitementOuvFich
    Dim ClasseurDPN As Workbook
    Set ClasseurDPN = Workbooks.Open("C:\Users\Moi\Desktop\ExtractionDPN.xls", , True)
    ClasseurDPN.Windows(1).Visible = False
    GoTo OuvFichOk
TraitementOuvFich:
    MsgBox ("L'application n'a pas pu ouvrir le fichier ExtractionDPN.xls, vérifiez que le fichier est bien dans le même dossier que l'application.")
    Err.Clear
    GoTo Suite
OuvFichOk:
    ClasseurDPN.Saved = True
    'Traitement des données DPN
    On Error GoTo 0
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim ListeCommandesDPN As Range
    Set ListeCommandesDPN = ClasseurDPN.Worksheets("Feuil1").Range(ClasseurDPN.Worksheets("Feuil1").Cells(65, gpNumCommande), ClasseurDPN.Worksheets("Feuil1").Cells(65, gpNumCommande), End(xlDown))
    NbCommandes = ListeCommandesDPN.Rows.Count
    For Each NumCommande In ListeCommandesDPN
        'Processus d'ajout des commandes non existantes
        If NumCommande.Formula = NumCommande.Offset(-1, 0).Formula Then GoTo FinAjoutDPN 'Pour accélérer un peu la recherche des nouvelles commandes
        If Not CommandeExiste(NumCommande.Formula) Then
            AjouterCommande (NumCommande.Formula)
        End If
FinAjoutDPN:
        'COLONNES AUTOMATISEES
        Dim LigneContrats As Variant
        LigneContrats = Intersect(Contrats.Rows(IndexLigne(NumCommande.Formula, Contrats, AutoCont_NumCommande)), Contrats.UsedRange)
        Dim LigneSurveillance As Variant
        LigneSurveillance = Intersect(Surveillance.Rows(IndexLigne(NumCommande.Formula, Surveillance, AutoSurv_NumCommande)), Surveillance.UsedRange)
        Dim LigneExtract As Variant
        LigneExtract = Intersect(ClasseurDPN.Worksheets("Feuil1").Rows(NumCommande.Row), ClasseurDPN.Worksheets("Feuil1").UsedRange)
        'TODO : On Error Resume Next => On garde toutes les automatisations mais ne s'effectuent que celles des colonnes présentes
        On Error GoTo ErreurAuto
        CCSContrats LigneContrats, LigneExtract 'CONTRATS : CCS
        NumContratContrats LigneContrats, LigneExtract 'CONTRATS : N° du marché
        NumContratSurveillance LigneSurveillance, LigneExtract 'SURVEILLANCE : N° du marché
        IntituleContratContrats LigneContrats, LigneExtract 'CONTRATS : Intitulé
        TitulaireContrats LigneContrats, LigneExtract 'CONTRATS : Titulaire
        PrestataireSurveillance LigneSurveillance, LigneExtract 'SURVEILLANCE : Prestataire
        DateDebutContrats LigneContrats, LigneExtract 'CONTRATS : Date de début
        EtatTrancheContrats LigneContrats, LigneSurveillance 'SURVEILLANCE : État et tranche
        DPNDINSurveillance LigneSurveillance, True 'SURVEILLANCE : DPN/DIN
        ServiceContrats LigneContrats, LigneExtract 'CONTRATS : Service
        ServiceSurveillance LigneSurveillance, LigneExtract 'SURVEILLANCE : Prestataire
        GoTo SuiteCommandeDPN
ErreurAuto:
        MsgBox ("Le processus d'entrée automatique des données pour la commande " & NumCommande.Formula & " a dû être arrêté pour la raison suivante :" & vbNewLine & """" & Err.Description & """")
        Err.Clear
        Resume SuiteCommandeDPN
SuiteCommandeDPN:
        '(...)
    Next NumCommande
    'Fermeture du fichier ExtractionDPN.xls
    On Error GoTo TraitementFerFichDPN
    ClasseurDPN.Close
    GoTo Suite
TraitementFerFichDPN:
    MsgBox ("L'application n'a pas pu fermer correctement le fichier ExtractionDPN.xls en raison d'une erreur inconnue.")
    Err.Clear
Suite:
    '(...)
    'On réactive l'écran de veille
    ActiveScreenSaver True
End Sub

Il est bon de préciser que ma macro rentre très rarement dans "AjouterCommande".
Contrats et Surveillance sont les noms de deux Worksheets.
Dans la partie COLONNES AUTOMATISEES, toutes les macros sont de la forme suivante :

Code :

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

1
2
3
Public Sub DPNDINSurveillance(LigneSurveillance As Variant, DPN As Boolean)
    If DPN Then LigneSurveillance(1, AutoSurv_DPNDIN) = "DPN" Else LigneSurveillance(1, AutoSurv_DPNDIN) = "DIN"
End Sub

A part une ou deux, mais qui n'expliquent pas un énorme décalage de durée.
Si je commente toute la partie COLONNES AUTOMATISEES, je diminue environ de moitié le temps d'exécution. Ce qui fait quand même pas loin de 45 minutes pour un pauvre parcours d'une colonne au final...

J'espère que quelqu'un saura m'indiquer comment accélérer un peu (beaucoup...?) cette macro...
Je tourne sous Excel 2007, avec Windows Vista.

Merci par avance !

EDIT : j'avais oublié la macro CommandeExiste, qui elle est répétée quasiment à chaque fois :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Public Function CommandeExiste(Numero As String) As Boolean
    On Error GoTo Fin
    CommandeExiste = False
    Dim ZoneRecherche As Range
    Set ZoneRecherche = Intersect(Contrats.Columns(IndexColonne("N° de commande", Contrats, 1)), Contrats.UsedRange)
    Dim Valeur As Double
    Valeur = CDbl(Numero)
    Dim Trouve As Double
    Trouve = Application.WorksheetFunction.Match(Valeur, ZoneRecherche, 0)
    If 0 < Trouve Then CommandeExiste = True
Fin:
    Err.Clear
End Function

[Philippe Tulliez]

Bonjour,

(...) des bouts de codes sans importance sur la durée d'exécution

Es-tu sûr que c'est sans importance ?
Parce-qu'à la première lecture, je ne vois rien qui puisse ralentir ta procédure.
Petite remarque tout de même mais qui n'a aucune incidence sur la vitesse, évite les Goto.

[Orhleil]

Bonjour,

Es-tu sûr que c'est sans importance ?
Parce-qu'à la première lecture, je ne vois rien qui puisse ralentir ta procédure.
Petite remarque tout de même mais qui n'a aucune incidence sur la vitesse, évite les Goto.

En l'occurence oui je suis sûr, c'est en fait des bouts où je mets à jour une barre de progression, mais j'ai instauré cette barre de progression après avoir constaté la lenteur du code, donc je suis formel.
C'est quoi le souci avec les GoTo ?

[MattChess]

Bonjour Orhleil,

Je réalise une macro qui traite des données provenant d'un autre fichier Excel (et à terme d'un troisième fichier...). Ledit autre fichier contient environ 27000 lignes de données.

A ce niveau de complexité, la précision est requise.
Précisez qu'il s'agit du ClasseurDPN dans la feuille Excel ExtractionDPN.xls, de traitement de mise à jour de n° de commande.
____________

Code :

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

1
2
 Private Sub MajDonnees()
 '(...)

Le type de NumCommande serait utile ainsi que les Constantes de rangées et colonnes importantes

1. Optimisation locale

Il y a à première vue des petites optimisations locales à tester mais ce n'est pas cela qui va révolutionner la performance globale à ce niveau de prise de contact.

Voir 3. For Each Next vs. For Next concernant NumCommande In ListeCommandesDPN.

Code :

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

1
2
3
4
5
Private Sub MajDonnees()
Dim indCmd As Long ' Car 27000 est trop proche de la limite d'un Integer
 
For indCmd = 1 To NbCommandes
    Set NumCommande = ListeCommandesDPN(indCmd)

____________

2. Structurer en procédures courtes

Le titre de la discussion "Macro beaucoup trop longue" a un double sens fort pertinent !

Comme le cœur de la boucle sur NumCommande a droit au commentaire :

Code :

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

'COLONNES AUTOMATISEES

cela mériterait de créer une procédure:

Code :

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

1
2
Sub ColAuto(ByVal NumCommande as ?, Contrats, Surveillance, ClasseurDPN As Workbook)

Cela permettrait de mieux cibler les efforts d'optimisation et réduire la complexité de MajDonnees().
____________

3. Eviter le Variant

Les lignes de plus de 70 caractères sont sujettes à optimisation.

Code :

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

1
2
Dim LigneExtract As Variant
LigneExtract = Intersect(ClasseurDPN.Worksheets("Feuil1").Rows(NumCommande.Row), ClasseurDPN.Worksheets("Feuil1").UsedRange)

Regroupez les variables locales en tête de procédure plutôt que lors de leurs premières utilisations.
Evitez absolument le Variant peu performant. Vous ne voulez pas traiter du multi-type générique ?

De ce que l'on peut comprendre, LigneExtract ressemblerait à l'intersection de Range.
Si c'est un Range alors ne le typer pas comme étant un Variant.
____________

4. Factoriser avec With ... End With

L'accès par le nom de feuille "Feuil1" est plus long que par son indice probablement 1 à définir en constante :

Code :

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

1
2
Option Explicit
Public Const indSheetCmd = 1 ' ClasseurDPN.Worksheets("Feuil1")

Autant définir une seule fois wsheetDpn en dehors de la boucle sur le n° de commande.

ClasseurDPN a une portée MajDonnees() mais n'est peut-être pas utile dans ColAuto() si on passe son wsheetDpn pointant sur sa première feuille.

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
Private Sub MajDonnees()
Dim wsheetDpn As Worksheet, NumCommande As cmdType
 
    Set wsheetDpn = ClasseurDPN.Worksheets(indSheetCmd) ' was Worksheets("Feuil1")
    For indCmd = 1 To NbCommandes
        Set NumCommande = ListeCommandesDPN(indCmd)
        ColonnesAutomatisees NumCommande, wsheetDpn ', ...
    Next
End Sub
 
Private Sub ColonnesAutomatisees(ByVal NumCommande As cmdType, ByVal wsheetDpn As Worksheet, ...)
Dim rngLigneExtract As Range '? was Variant
 
    With wsheetDpn
        Set rngLigneExtract = Intersect(.Rows(NumCommande.Row), .UsedRange)
    End With
End Sub

Notez la factorisation With ... End With qui optimise l'accès à la feuille.
____________

5. Décrire les rangées et colonnes clés de chaque feuille par des Constantes

La question que l'on se pose est que cela sert à quoi cette intersection avec .UsedRange ?
Donnez un exemple simplifié de rngLigneExtract et comparez avec .Rows(NumCommande.Row).
Peut-on réellement avoir une colonne de n° commande en partie en dehors de .UsedRange ?

Si l'objectif est de réduire la rangée entière en une portion de colonnes connues à l'avance,
alors il faut décrire chaque feuille en terme de Constantes pour les rangées et colonnes concernant des éléments significatifs à mettre à jour :

Code :

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

1
2
3
With wsheetDpn
    Set rngLigneExtract = .Range(.Cells(NumCommande.Row, colCmdStart), .Cells(NumCommande.Row, colCmdEnd))
End With

Avec les constantes à définir en début de module par type de feuille :

Code :

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

1
2
Public Const colCmdStart =  2 ' Commentaire significatif de la 1ere colonne
Public Const colCmdEnd =  20 ' Commentaire significatif de la dernière colonne de commande

____________

6. Pour mieux structurer, supprimez les Goto.

C'est quoi le souci avec les GoTo ?

Voir la conclusion de cet article.

Code :

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

1
2
3
4
5
6
7
Private Sub MajDonnees()
    If NumCommande.Formula = NumCommande.Offset(-1, 0).Formula Then GoTo FinAjoutDPN 'Pour accélérer un peu la recherche des nouvelles commandes
    If Not CommandeExiste(NumCommande.Formula) Then
        AjouterCommande (NumCommande.Formula)
    End If
FinAjoutDPN:
End Sub

devient :

Code :

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

1
2
3
4
5
6
7
Private Sub MajDonnees()
    If NumCommande.Formula <> NumCommande.Offset(-1, 0).Formula Then
        If Not CommandeExiste(NumCommande.Formula) Then
            AjouterCommande (NumCommande.Formula)
        End If
    End If
End Sub

Idem pour Resume SuiteCommandeDPN juste au-dessus de l'étiquette SuiteCommandeDPN:
____________

7. Gestion d'erreur

Code :

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

1
2
3
4
TraitementFerFichDPN:
    MsgBox ("L'application n'a pas pu fermer correctement le fichier ExtractionDPN.xls en raison d'une erreur inconnue.")
    Err.Clear
End Sub

devient :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
TraitementFerFichDPN:
    Warning "9000: L'application n'a pas pu fermer correctement le fichier ExtractionDPN.xls en raison d'une erreur inconnue."
End Sub
 
Sub Warning(ByVal strMsg As String) 'Common error management
Const lenErr = 4 ' Number of digits of the error code beginning the message
 
    MsgBox Mid(strMsg, lenErr + 3), vbExclamation, "MajDonnees warning " + Left(strMsg, lenErr)
    Err.Clear
    ' Other common features after an error
End Sub

____________

8. Conclusion

En réduisant le nombre de lignes par procédure, on réduit la complexité même s'il y a davantage de paramètres et de procédures.
Cela permettra de cerner ultérieurement le problème majeur de performance.
___________

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.

[Orhleil]

Wouaw... Je m'attendais pas à une réponse aussi détaillée et bien construite o_o
Bon là j'ai survolé ce que tu as dit (il se fait tard), je détaillerai tout ça demain au boulot, ton analyse me parait bougrement pertinente.
Je vais corriger mon code en tenant compte de tous tes conseils et je reviendrai pour débriefer.
Sois sûr que tu as d'ores et déjà toute ma gratitute

[el_slapper]

Pour le "pourquoi pas de GoTo", la réponse usuelle est "parcequ'on a tendance à faire su code spaghetti avec les GoTo".

Le code spaghetti, ça marche, mais dès qu'il s'agit de le faire évoluer(pour prendre en compte un nouveau fichier, ou pour optimiser, par exemple), ça devient très compliqué. Alors qu'un programme avec de petites procédures se lit mieux(sans aller à l'extrême et se limiter à 10 lignes par sub.....j'ai essayé, on a aussi des problèmes ).

Pour le reste, je m'incline devant la skill de MattChess.