Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

[richert90]

Bonjour à toutes et à tous,

Je fais appel à vous pour un petit problème. En effet j'ai un tableau sous Excel qui contient beaucoup de lignes (environ 400 000) et à partir de celui-ci je dois faire des nuages de points : 1 graphique pour chaque "Groupe" (dans le fichier joint j'ai 3 groupes donc 3 graphiques). Le code que j'ai marche très bien mais il est très long à exécuter (compter 2 minutes....).
Est-ce qu'on peut optimiser le code pour générer les graphiques afin de gagner du temps?

Merci d'avance,

PS: Je ne peux pas joindre le fichier complet car il est trop lourd (13000 Ko), du coup je vous joins un extrait de la base de données (avec seulement 25000 lignes au lieu des 400 000) et le code pour générer les graphiques. . A l'ouverture du fichier, il n'y a aucun graphique, cela est fait exprès pour que vous puissiez bien voir le temps que met Excel lorsque vous exécuterez le programme "graph_per_group".
Pour que le test soit réel vous pouvez essayer avec les 400 000 lignes environ pour voir.

Merci d'avance de votre aide;

 

[gmb]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour

Désolé, je ne vois pas comment réduire le temps de travail de ta macro. Mais quand j’ai ce problème, je mets un Beep et un message d’alerte comme dernière instruction. Cela attire mon attention quand la macro a fini .

Beep
MsgBox "Travail terminé !"
End Sub

 

[Robert]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour Richert, bonjour le forum,

Peut être comme ça pour définit compteur_deb et compteur_fin sans boucle :

Public Sub Test()
Dim dl As Long 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)
Dim i As Byte 'déclare la variable i (Incrément)
Dim compteur_deb As Long 'déclare la variable compteur_deb
Dim compteur_fin As Long 'déclare la variable compteur_fin

Sheets("Feuil2").Activate 'sélectionne l'onglet "Feuil2"
dl = Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 1 (=A)
Set pl = Range("A2:A" & dl) 'définit la plage pl
For i = 1 To 3 'boucle de 1 à 3
    Range("A1").AutoFilter Field:=1, Criteria1:=i 'filtre la colonne 1 (=A)du tableau avec i comme critère
    compteur_deb = pl.SpecialCells(xlCellTypeVisible).Cells(1, 1).Row 'définit la première ligne du tableau filtrée
    compteur_fin = pl.SpecialCells(xlCellTypeVisible).Rows.Count + (compteur_deb - 1) 'définit la dernière ligne du tableau filtrée
    Range("A1").AutoFilter 'supprime le filtre automatique
    
    'reste du code

Next i 'prochaine valeur de la boucle
End Sub

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour Gmb, Robert,

Merci pour l'aide. J'ai essayer le code là, à la place du mien (testé sur le fichier avec les 400 000 lignes) mais malheureusement pas d'amélioration en terme de rapidité d’exécution. Bizarrement ça même été plus long (3 min 56 avec ton code, 3 min 26 pour le précédent avec les boucles pour les compteurs....). Ça m'ennuie vraiment car c'est vraiment trop long mais je ne sais pas si on peut y faire quelque chose car si ça vient du nombre de lignes ça va être impossible...

Si vous avez encore d'autres propositions, n'hésitez pas

 

[Robert]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour le fil, bonjour le forum,

Oui tu as raison. J'ai testé sur ton exemple avec ton code initial et la modif que je t'ai proposée et j'obtiens le même résultat : 3,40 secondes ! Heu, faut dire qu'au boulot j'ai une super bécane...

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Même avec un "bonne bécane" cela met beaucoup de temps. C'est assez problématique. Je ne vois plus vraiment de solutions pour arranger ça...

 

[Robert]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour le fil, bonjour le forum,

As-tu essayé d'éliminer les lignes de paramétrage des graphiques en ne gardant que celles dont tu as vraiment besoin. L'enregistreur de macros écrit souvent beaucoup de lignes inutiles pour rien comme :

    With Selection.Format.TextFrame2.TextRange.Characters(1, longueur_titre).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Euh non , je peux essayer mais je pensais que ça venait plus des définitions des plages de données (compteur_deb, compteur_fin).
Je vais essayer voir ce que ça donne.
Je te redis ça.

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Comme prévu, ça n'a pas arranger le temps d’exécution

 

[Robert]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour le fil, bonjour le forum,

Je m'en doutais un peu et je suis perplexe comme toi car je ne vois pas d'autre solution que celle que je t'ai proposée... Je cherche...

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Ok, moi aussi je cherche, j'ai vu que les fonctions API ont souvent un temps d’exécution rapide mais je ne sais pas si c'est utilisable dans notre cas..

 

[PMO2]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour,

On pourrait obtenir un gain de temps significatif
1) en utilisant des variables objet
2) en travaillant toutes les données en mémoire

Je vais essayer de réécrire votre code dans ce sens et vous tiens au courant.
A plus.

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour PMO2 et merci de votre réponse!

Malheureusement je ne connais pas les variables objets sur VBA

Merci pour le temps consacré a mon problème!

 

[Jam]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Salut richert90, PMO2, Robert,

2 petites choses:

Essaye de mettre le classique:

Application.Calculation=xlCalculationManual
Application.Screenupdating=False

Je ne le vois pas dans ton code.

Pour l'amélioration du code, tu peux rajouter des With...End With (avec ActiveChart par ex) qui permettront de grapiller quelques secondes. Je crains, que le problème ne vienne d'Excel et du manque de rapidité des graphiques d'Excel.
Essaye peut-être de créer un modèle de graphique (ie mise en forme déjà faite) plutôt que de la faire en programmation.

Après je ne vois pas trop.

Bon courage,

 

[richert90]

Re : Optimisation du code VBA (graphiques) pour un temps d'éxécution + rapide.

Bonjour Jam et merci pour la réponse.
Même avec le "classique" , ça n'a pas fait grand chose malheureusement.
Je vais essayer de voir avec les With .. end with mais bon si je gagnes que quelques secondes sur 3-4 minutes ça se verra pas.