IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Macro beaucoup trop longue [XL-2007]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre éclairé Avatar de Orhleil
    Homme Profil pro
    Intégrateur fonctionnel
    Inscrit en
    Mai 2011
    Messages
    81
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Intégrateur fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2011
    Messages : 81
    Par défaut Macro beaucoup trop longue
    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

  2. #2
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    13 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 13 173
    Billets dans le blog
    53
    Par défaut
    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.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  3. #3
    Membre éclairé Avatar de Orhleil
    Homme Profil pro
    Intégrateur fonctionnel
    Inscrit en
    Mai 2011
    Messages
    81
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Intégrateur fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2011
    Messages : 81
    Par défaut
    Citation Envoyé par corona Voir le message
    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 ?

  4. #4
    Membre chevronné
    Profil pro
    Inscrit en
    Juillet 2011
    Messages
    141
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2011
    Messages : 141
    Par défaut
    Bonjour Orhleil,

    Citation Envoyé par Orhleil Voir le message
    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 :
    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.

    Citation Envoyé par Orhleil Voir le message
    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.

  5. #5
    Membre éclairé Avatar de Orhleil
    Homme Profil pro
    Intégrateur fonctionnel
    Inscrit en
    Mai 2011
    Messages
    81
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Intégrateur fonctionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2011
    Messages : 81
    Par défaut
    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


  6. #6
    Expert confirmé
    Profil pro
    Inscrit en
    Décembre 2007
    Messages
    6 814
    Détails du profil
    Informations personnelles :
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations forums :
    Inscription : Décembre 2007
    Messages : 6 814
    Par défaut
    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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Améliorer requête SQL beaucoup trop longue
    Par faulk dans le forum Langage SQL
    Réponses: 4
    Dernier message: 05/06/2014, 12h12
  2. [IDE] E2222 : Expansion de macro trop longue
    Par Didier44 dans le forum C++Builder
    Réponses: 2
    Dernier message: 27/01/2012, 09h05
  3. [XL-2007] Macro trop longue, simplification
    Par laduche31 dans le forum Macros et VBA Excel
    Réponses: 13
    Dernier message: 12/12/2011, 14h40
  4. [AC-2003] Tuer une macro trop longue à s'exécuter
    Par reeenooo dans le forum VBA Access
    Réponses: 5
    Dernier message: 30/10/2009, 18h43
  5. Réponses: 5
    Dernier message: 14/03/2008, 11h57

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo