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

Excel Discussion :

Diminuer le temps d'execution de ma macro [XL-365]


Sujet :

Excel

  1. #1
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut Diminuer le temps d'execution de ma macro
    Bonjour a tous,

    Le temps d'exécution de ma macro dure une éternité... je pense que c'est à cause des formules que j'ai ajouté dans mon tableau (colonnes K/L/M), car avant ces formules, ma maccro était très rapide (environ 1sec).
    Je voulais voir avec vous s'il y avait des possibilités pour réduire le temps d'exécution, mon excel est inutilisable tellement c'est long et je ne sais absolument pas comment faire...
    Je vous joins directement le fichier excel, je pense que ca sera plus parlant !
    Fichier test.xlsm


    Merci de votre aide

  2. #2
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2019
    Messages
    207
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 24
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2019
    Messages : 207
    Points : 128
    Points
    128
    Par défaut
    Sur ce site nous n'ouvrons pas les fichiers tu devrais plutôt mettre ton code avec la balise associée

  3. #3
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    C'est que vous ne pouvez pas ouvrir le fichier ? Ou alors c'est plus pour des mesures de sécurités ?

    En attendant, voici le 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
    Sub Ajout_Data()
     
    Dim i As Integer
    Dim j As Integer
    Dim NBRepere As Integer
    Dim NBVissage As Integer
     
     
    Application.ScreenUpdating = False
     
    Sheets("Data").Activate
    Range("J2:J1048576").ClearContents
     
    NBVissage = Application.WorksheetFunction.CountA(Sheets("Repere").Range("A:A")) - 1
    NBRepere = Application.WorksheetFunction.CountA(Sheets("Data").Range("A:A"))
    i = 2
    j = 2
     
     
    Do While (i <= NBRepere)
     
     
        If Sheets("Data").Range("I" & i).Value Like "" Then
            j = 1
        ElseIf Sheets("Data").Range("I" & i).Value Like "B" & "*" Then
            Sheets("Data").Range("J" & i) = Sheets("Repere").Range("A" & j)
        Else: Sheets("Data").Range("J" & i) = Sheets("Repere").Range("A" & j)
            j = j - 1
        End If
     
        j = j + 1
        i = i + 1
     
        If j <= NBVissage + 1 Then
     
        Else: j = j - NBVissage + 1
     
        End If
    Loop
     
     
    Sheets("Analyse").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    Application.ScreenUpdating = True
    MsgBox ("Les répères ont bien été ajouté")
     
    End Sub
    Pour info, ce code me permet de remplir entre 5 000 et 15 000 lignes en fonction du nombre de données que je vais avoir à traiter.

  4. #4
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 935
    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 : 10 935
    Points : 26 157
    Points
    26 157
    Billets dans le blog
    34
    Par défaut
    Bonjour,
    A la lecture de votre code, il semble que vous remplissez les cellules de la colonne J de la feuille [Data] suivant critères.
    Ne serait-ce pas plus simple d'écrire la formule dans cette colonne ?
    S'il faut vraiment passer par du VBA, j'écrirais la formule directement sur l'ensemble de la plage concernée ce qui vous éviterait d'écrire à chaque ligne une valeur et si la formule doit absolument être bannie, on l'insère dans la colonne et ensuite on fait l'équivalent d'un Copier / collage spécial valeur

    Voir mes billets titrés
    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
    Ma dernière contribution : VBA - Les macros complémentaires

  5. #5
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Peut-être qu'écrire la formule directement dans la cellule est la solution, cependant je pense que cette formule est bien trop compliquée, et je ne vois pas comment ne pas passer par du code...
    Comme vous n'ouvrez pas les fichiers Excel sur ce site, est-ce qu'il y a un moyen de vous partager quelques données pour mieux comprendre le problème ?
    Sinon je peux essayer avec des mots, mais ca risque d'être plus compliqué a comprendre.

  6. #6
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 935
    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 : 10 935
    Points : 26 157
    Points
    26 157
    Billets dans le blog
    34
    Par défaut
    Bonjour,
    J'ai peut-être mal lu mais il me semble que la formule se résume à deux SI imbriqués
    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
    Ma dernière contribution : VBA - Les macros complémentaires

  7. #7
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Deux Si imbriqués mais toutes les 33 lignes (ou plus en fonction du nombre de reprises), il faut reprendre à zéro.
    En gros, les 33 lignes représentent un produit fini. Il faut 33 vissages pour faire un produit fini, d'où les 33 lignes de données.
    Cependant, il se peut que j'ai des reprises lors de mes vissages, donc s'il y a 2 reprises, j'aurais 35 lignes de données. Et je veux qu'à la fin de ces 33 + x reprises, je reprenne ma numérotation à 0 car je passe sur une nouvelle pièce.
    La mon code me permet de donner un nom à chacune de les lignes de données Excel comme par exemple C1 pour le vissage 1, C2 pour le 2, V1 pour le 3, ect.

    Est-ce possible de faire ça uniquement avec une formule dans une cellule ?

  8. #8
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 935
    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 : 10 935
    Points : 26 157
    Points
    26 157
    Billets dans le blog
    34
    Par défaut
    Bonjour,
    Effectivement, c'est plus complexe qu'un simple SI imbriqué.
    Si vous avez une version Excel 2010 ou supérieure, je pense que vous pourriez résoudre ce problème plus simplement et surtout plus rapidement avec Power Query mais il faudrait donner une explication claire de ce que vous attendez avec une image des données concernées
    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
    Ma dernière contribution : VBA - Les macros complémentaires

  9. #9
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Alors voici exactement comment ça se présente :
    Je récupère déjà mes données avec Power Query, et voici les données que j'arrive à extraire :

    Nom : Extraction avec Power Query.PNG
Affichages : 68
Taille : 310,2 Ko

    Ensuite, avec ma macro actuelle, je viens ajouter le nom des répères; voici ce que j'obtiens :

    Nom : Ajout des reperes.PNG
Affichages : 67
Taille : 313,2 Ko

    L'ordre des vissages est toujours le même, on commence par C1 et on fini par V38. Ce qui va changer, c'est quand il y a des reprises ou non. Par exemple, il peut y avoir 5 reprises sur le même repère, nous aurons donc 6 lignes (les 5 reprises + le vissage bon).

    Ensuite, les données que j'ai à ce moment ne me suffisent pas pour faire toutes mes analyses, j'ai donc ajouté 3 colonnes dans mon tableau :

    - La première pour savoir s'il s'agissait soit d'un vissage bon ("B"), soit d'un vissage mauvais ("M"). Cette colonne me sert juste à faciliter l'écriture de la formule de la colonne suivante.
    - La deuxième colonne qui me donne le nombre de vissage qu'il y a eu par pièce et par repère. Si on reprend les photos ci-dessus, j'aurais comme valeur 1 pour la ligne 18, et 2 pour la ligne 19. A chaque fois je dis que si le vissage est bon, alors c'est égale à 1, mais si c'est une reprise, c'est égale à la valeur de la cellule précédente + 1.
    - La troisième qui me donne le statut du vissage. Par exemple, pour certains repères je n'ai pas le droit de faire de reprise, ou alors, sur d'autres repères j'ai le droit de ne faire que 2 reprises maximum. Au final, j'ai 4 possibilités : le vissage est OK, il y a eu reprise mais c'est autorisé, il y a eu trop de reprises, et la reprise est interdite.
    Au final, voici ce que j'ai :

    Nom : Donnees finales.PNG
Affichages : 76
Taille : 76,3 Ko

    Normalement tout fonctionne parfaitement, il y a juste cette histoire de mettre les repères avec la maccro qui prend un temps monstrueux (je parle de plusieurs minutes), et j'ai l'impression que c'est depuis que j'ai ajouté les 3 dernières colonnes... Sauf que j'en ai besoin pour mes analyses.

    J'espère avoir été assez clair.

  10. #10
    Membre expérimenté
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    mars 2007
    Messages
    750
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2007
    Messages : 750
    Points : 1 566
    Points
    1 566
    Par défaut
    Bonjour

    Si j'ai bien compris, la macro publiée au post 3 est celle qui fabrique la colonne J. Est-ce que cela est bien le cas ?
    Est-ce qu'il est possible d'avoir une copie écran de la colonne A de la feuille "Repere" ?

    Est-ce qu'il est possible d'avoir les formules en K, L et M ?

    Bonne fin d'après-midi

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  11. #11
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Bonjour Pierre,

    Oui, il s'agit bien de la macro qui alimente la colonne J.
    Voici la colonne A de la feuille Repere :

    Nom : colonne A feuille Repere.PNG
Affichages : 71
Taille : 5,3 Ko

    Et voici les 3 formules :
    - Colonne K :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI.MULTIPLE(GAUCHE([@CR];1);"B";"B";"M";"M";"";"")
    - Colonne L :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI([@[BON/MAUVAIS]]="M";1+L1;1)
    - Colonne M :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI.CONDITIONS(OU(ET(J3="C27";L3>2);ET(J3="C28";L3>2);ET(J3="V23";L3>2);ET(J3="V24";L3>2));Repere!$B$2;OU(L3=2;L3=3);Repere!$B$3;J3="";"";L3>3;Repere!$B$4;L3=1;Repere!$B$5)
    Bonne fin de journée !

  12. #12
    Membre expert
    Homme Profil pro
    retraité
    Inscrit en
    juin 2012
    Messages
    2 253
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : retraité
    Secteur : Associations - ONG

    Informations forums :
    Inscription : juin 2012
    Messages : 2 253
    Points : 3 773
    Points
    3 773
    Par défaut
    Bonjour,

    En effectuant les calculs dans des tableaux (array), c'est presque instantané.
    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
    Option Explicit
     
    Sub Ajout_Data()
        Dim loData As ListObject, loRep As ListObject
        Dim i As Integer, j As Integer
        Dim NbRepere As Integer, NbVissage As Integer
        Dim Repere As Variant, CR As Variant, Rep As Variant
        Dim Rng As Range, sT As String
        Dim t As Single
        t = Timer
        Sheets("Data").Activate
        Set loData = Worksheets("Data").ListObjects("Donnees")
        Set loRep = Worksheets("Repere").ListObjects("t_repere")
        Set Rng = loData.ListColumns("Rep").DataBodyRange   '--- Rep = titre colonne J
        Rng.ClearContents
        Repere = Application.Transpose(loRep.ListColumns("Repere").DataBodyRange)
        CR = Application.Transpose(loData.ListColumns("CR").DataBodyRange)
        NbVissage = loRep.DataBodyRange.Rows.Count
        NbRepere = loData.DataBodyRange.Rows.Count
        Debug.Print NbVissage, NbRepere
        ReDim Rep(NbRepere)
        i = 1
        j = 1
        Do
            If CR(i) Like "B*" Then
                Rep(i) = Repere(j)
            ElseIf CR(i) = "" Then
                j = 0
            Else
                Rep(i) = Repere(j)
                j = j - 1
            End If
            j = j + 1
            i = i + 1
        Loop Until i > NbRepere
        Set Rng = loData.ListColumns("Rep").Range
        sT = Rng(1)                                 '--- récupère le titre
        Rng.Value = Application.Transpose(Rep)      '--- colle les valeurs calculées
        Rng(1) = sT                                 '--- remt le titre (écrasé à l'instruction précédente)
        'Sheets("Analyse").PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
        Debug.Print Timer - t
        MsgBox ("Les répères ont bien été ajouté en " & (Timer - t) & " sec.")
    End Sub
    Il y a juste les lignes 34 à 36 de votre code qui n'est pas repris, n'en ayant pas compris l'objet.

    Cordialement.
    Fichiers attachés Fichiers attachés

  13. #13
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Bonjour Eric,

    Merci beaucoup pour ta solution. Le temps d'exécution est quasi instantané. C'est top !

    J'aurais juste une dernière question, je n'arrive pas trop à comprendre à quoi servent les lignes 16/17/20, soit celles-ci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    Repere = Application.Transpose(loRep.ListColumns("Repere").DataBodyRange)
    CR = Application.Transpose(loData.ListColumns("CR").DataBodyRange)
    .
    .
    Debug.Print NbVissage, NbRepere
    Je débute en VBA, est-ce que tu pourrais m'expliquer un peu plus précisément ce qu'est cette fonction Transpose, et à quoi sert ce Debug.Print stp ?

    Mais encore une fois merci beaucoup pour ton aide !

  14. #14
    Membre expert
    Homme Profil pro
    retraité
    Inscrit en
    juin 2012
    Messages
    2 253
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : retraité
    Secteur : Associations - ONG

    Informations forums :
    Inscription : juin 2012
    Messages : 2 253
    Points : 3 773
    Points
    3 773
    Par défaut
    Bonjour,

    Pour les explications, le mieux est de faire des recherches sur internet.

    Transpose sert essentiellement à passer d'une orientation verticale à une orientation horizontale, ce qui est nécessaire lorsque que l'on remplit une table (array), qui est en quelque sorte par défaut "horizontale", à l'aide de ce qui se trouve dans une plage (range) qui est verticale.

    Le Debug.Print ne sert qu'à afficher des informations dans la fenêtre Exécution de l'éditeur VBA (Ctrl-g)

    Un petit exemple en attaché.

    Cordialement.
    Fichiers attachés Fichiers attachés

  15. #15
    Membre à l'essai
    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    juillet 2020
    Messages
    24
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2020
    Messages : 24
    Points : 17
    Points
    17
    Par défaut
    Bonjour,

    Très bien, je comprend mieux.

    Merci pour tout et bonne journée !

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

Discussions similaires

  1. Réponses: 15
    Dernier message: 25/11/2016, 13h49
  2. Diminuer le temps d'execution d'un script perl
    Par Isabella83 dans le forum Langage
    Réponses: 16
    Dernier message: 24/02/2012, 12h46
  3. Compteur automatique pendant le temps d'execution de la macro.
    Par Flo3301 dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 14/02/2011, 13h56
  4. Diminuer le temps d'execution d'une application
    Par javatar dans le forum Général Java
    Réponses: 6
    Dernier message: 27/11/2010, 14h44
  5. [AC-97] Optimiser une requête pour diminuer le temps d'execution
    Par Milyshyn76 dans le forum Requêtes et SQL.
    Réponses: 3
    Dernier message: 31/05/2010, 13h22

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