temps d'exécution croissant pour écriture dans un tableau structuré

Bonjour,

je me forme à l'usage d'Excel et à la programmation VBA, et je suis en train d'explorer le bon usage des tableaux structurés en VBA.

Voici le contexte :
- un tableau structuré à 8 colonnes déjà existant dans une feuille de calcul,
- une macro VBA qui vide ce tableau avec ...ListObject.DataBodyRange.Delete
- puis qui remplit ce tableau à l'aide d'une boucle For qui tourne 579 fois (nombre arbitraire)
- dans une itération de la boucle, il y a une insctruction Add pour ajouter la ligne, puis une instruction Count pour récupérer le numéro de la nouvelle ligne, puis 8 instructions ...DataBodyRange(ligne,colonne)=cpt pour écrire la valeur du compteur de la boucle dans les 8 colonnes de la ligne

Voici ce que j'observe :
- à la 1ère exécution de la macro, le temps d'exécution est de l'ordre de 0,8s
- à chaque exécution supplémentaire, le temps d'exécution augmente de l'ordre de 0,1s
- si je ferme le fichier puis que je le rouvre, ça recommence à partir de 0,8s (puis ça augmente progressivement de 0,1s)

Voici mes questions :
- est-ce que c'est normal que ça augmente à chaque exécution ? (alors que le traitement est le même !)
- à votre avis, qu'est-ce que je fais de travers ?

Je ne comprends pas ce qui se passe, et je n'ai pas trouvé d'infos à ce sujet jusque là.

Merci d'avance à ceux qui se pencheront sur la question.

[EDIT : voici le code de la procédure]

Code:

---

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

Sub creer_ts_v1()   ' Fonction ADD dans boucle FOR pour ajouter 579 lignes   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''   ' désactiver les MAJ cf affichage & recalcul pour gagner du temps à l'exécution Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Application.Calculation = xlCalculationManual   ' récupérer l'heure courante au début (pour messure du temps d'exécution) tempsDebut = Timer   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''   ' nom du ts à manipuler LDD = "ts_v1"   ' RAZ TS If Not Range(LDD).ListObject.DataBodyRange Is Nothing Then Range(LDD).ListObject.DataBodyRange.Delete End If   ' boucle FOR For cpt = 1 To 579   ' ajouter 1 ligne dans le TS Range(LDD).ListObject.ListRows.Add   ligne = Range(LDD).ListObject.ListRows.Count   ' compléter la ligne 1 avec le n° du cpt Range(LDD).ListObject.DataBodyRange(ligne, 1) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 2) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 3) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 4) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 5) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 6) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 7) = cpt Range(LDD).ListObject.DataBodyRange(ligne, 8) = cpt   Next   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''   ' récupérer l'heure courante à la fin (pour messure du temps d'exécution) tempsFin = Timer   ' réactiver les MAJ cf affichage & recalcul à la fin Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.Calculation = xlCalculationAutomatic   ' affichage de fin pour prévenir l'utilisateur (avec ou sans mesure du temps d'exécution) MsgBox "FIN - " & tempsFin - tempsDebut & "s" 'MsgBox "FIN"   End Sub

---

Bonjour,
Pouvez-vous joindre le code de la procédure, en utilisant bien le bouton # ?
Cela nous serait utile.

Bien sûr, je viens de mettre à jour mon post ci-dessus.

Hello,

juste pour faire avancer de shimili...
je constate la même chose mais dans une moindre mesure (PC plus puissant ?)
FIN - 0,2890625s
FIN - 0,34375s
FIN - 0,3984375s
FIN - 0,4453125s
FIN - 0,5s
FIN - 0,5546875s
FIN - 0,609375s
FIN - 0,65625s
FIN - 0,703125s

Effectivement, le temps d'exécution augmente

Salut, teste comme ceci. Chez moi le temps varie entre 0 et 0.03, quel que soit le nombre d'itérations. J'utilise une tableau en mémoire pour y écrire les valeurs et ensuite copie vers le listobject en une seule opération plutôt que ligne par ligne.

Code:

---

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

Sub creer_ts_v1()   Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Application.Calculation = xlCalculationManual   Dim tempsDebut As Double tempsDebut = Timer   Dim LDD As String LDD = "ts_v1"   Dim lo As ListObject Set lo = Range(LDD).ListObject   If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete End If   ' Ajouter toutes les lignes en une seule fois Dim nombreLignes As Long nombreLignes = 579 lo.Resize lo.Range.Resize(lo.Range.Rows.Count + nombreLignes)   ' Remplir les données dans un tableau VBA Dim data() As Variant ReDim data(1 To nombreLignes, 1 To 8) Dim cpt As Long For cpt = 1 To nombreLignes data(cpt, 1) = cpt data(cpt, 2) = cpt data(cpt, 3) = cpt data(cpt, 4) = cpt data(cpt, 5) = cpt data(cpt, 6) = cpt data(cpt, 7) = cpt data(cpt, 8) = cpt Next cpt   ' Écrire les données dans le tableau structuré lo.DataBodyRange.Value = data   Dim tempsFin As Double tempsFin = Timer   Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True Application.Calculation = xlCalculationAutomatic   MsgBox "FIN - " & tempsFin - tempsDebut & "s"   End Sub

---

Salut,

je suis d'accord avec cette suggestion, un tuto l'expliquant est disponible ici : https://didier-gonard.developpez.com...a/#LVIII-B-2-c

Hello Franc et Jean-Philippe André,

merci pour cette solution qui est un contournement, mais ça n'explique pas pourquoi le temps d'exécution de la procédure de bremawemul augmente à chaque lancement et revient à la normale en fermant et réouvrant le fichier.

Est-ce qu'il n'y aurait pas une instruction pour vider la mémoire de la dernière procédure ?
J'ai lu (je ne sais plus où) que la suppression des variables utilisées n'était pas utile car le "end sud" les supprimaient automatiquement. Ne faudrait il pas les supprimer avant le end sub (bien qu'ici il y en ai très peu) ?

Une piste possible:

Code:

---

Range(LDD).ListObject

---

Tu rappelle sans cesse cette instruction, en conséquence, VBA doit résoudre toutes les références avant d'accéder a l'objet ou méthode désirée.
Factorise, soit en déclarant une variable, soit avec le mot clef With:

Code:

---

1 2 3

Dim Lo As Excel.ListObject Set Lo = Range(LDD).ListObject Lo.DataBodyRange(ligne, 1) = cpt

---

Code:

---

1 2 3

With Range(LDD).ListObject .DataBodyRange(ligne, 1) = cpt End With

---

De plus, sauf erreur de ma part, la méthode ListRows.Add renvoie un objet ListRow qui lui même contient un objet Range, on peut l'exploiter:

Code:

---

1 2 3 4 5 6 7 8 9

Dim Lo As Excel.ListObject Set Lo = Range(LDD).ListObject   Dim Ligne As Excel.ListRow Set Ligne = Lo.ListRows.Add   Dim Rng As Excel.Range Set Rng = Ligne.Range Rng(1).Value = Cpt

---

Enfin, garde en tête que le fonctionnement interne des objets Office est extrêmement complexe (forte heureusement, pas notre problème), qu'ils ont également besoin de mémoire (gérée à leur sauce) pour fonctionner.

Bonjour à tous,

Citation:

---

De plus, sauf erreur de ma part, la méthode ListRows.Add renvoie un objet ListRow qui lui même contient un objet Range, on peut l'exploiter:

---

Pour ma part j'ai eu fait le test, et cela ne change rien au Schmilblick. :(

Bonsoir,

En effet, c'est troublant
J'ai pensé que ça pouvait venir de la QueryTable liée potentiellement au ListObject qui en quelque sorte garderait des traces des données sauvegardées au fur et à mesure mais si la source d'un ListObject est une plage de cellule, l'objet QueryTable semble ne pas exister (ou en tout cas n'est pas atteignable)

Pas trouvé la source de la fuite de mémoire
Peut-être est-elle dans la conception même des ListObject et pas accessible en VBA sur l'objet, ou en tout cas je ne vois pas où chercher

Re,

Peut-être le filtre automatique qui retient au fur et à mesure des données supplémentaires ?
mais si c'est ça je n'ai pas trouvé où "purger" le cache du filtre auto...

La meilleur solution reste de passer par un tableau 2D en mémoire, à conditions d'être pleinement conscient de tout ce que cela implique:
- Le tableau doit être de type Variant.
- Le tableau et la plage ciblée doivent être de la même taille.
- Perte de lisibilité du code source pour un neofit.
- Complexification du code source.
- Perte de compréhension du code source pour un neofit.
- Besoin élevé de performances.

La plus part du temps, les performances ne sont pas une priorité en VBA, on préfèrera d'abord obtenir les comportements attendus et résultats escompté.
Si le besoin de performances se fait sentir, c'est une solution envisageable, sinon mieux vaut rester sur du fonctionnel pure et dur.

Hello,

Lors de l'utilisation de la méthode ListObject.ListRows.Add dans Excel VBA, il y a eu des rapports de fuites de mémoire, en particulier lorsqu'il s'agit d'un grand nombre de lignes. Ce problème peut se produire si les cellules sous le tableau ne sont pas correctement traitées, conduisant à une situation où elles ne sont pas nettoyées comme prévu.
Pour atténuer ce problème, assurez-vous de gérer correctement le cycle de vie de vos objets et envisagez de vous désabonner des gestionnaires d'événements et de nettoyer les ressources lorsque celles-ci ne sont plus nécessaires. Par exemple, lorsque vous ajoutez une nouvelle ligne, vous pouvez spécifier AlwaysInsert paramètre pour éviter de déplacer les cellules existantes, ce qui pourrait aider à gérer la mémoire plus efficacement.
De plus, lorsque vous travaillez avec de grands ensembles de données, il peut être avantageux d'utiliser des tableaux au lieu de manipuler directement les lignes de la feuille de calcul, car cela peut être plus efficace et réduire la probabilité de fuites de mémoire.

Ami calmant, J.P

Bonjour à tous,

je reviens vers vous après avoir faits quelques essais supplémentaires à la lumière de vos réponses.

- @Valtrase et @Nain porte koi :

Merci pour vos réponse, je suis rassuré de voir que d'autres font la même observation que moi, je suis par contre inquiet de voir que l'explication reste assez mystérieuse.

- @Franc et @Jean-Philippe André :

Merci pour la solution alternative avec un tableau directement en mémoire, qui s'avère beaucoup plus efficace et stable en temps d'exécution.

- @deedolith :

Merci pour tes commentaires et les modifications suggérées, mais ça ne règle pas le problème du temps d'exécution qui augmente progressivement.

- @tototit2008 et @jurassik pork :

Merci pour vos commentaires et explications concernant de potentielles fuites de mémoire.

Petit commentaire perso : je trouve assez incroyable que des outils existant de longue date, comme Excel, VBA et les tableaux structurés, soient encore sujet à des fuites de mémoire !?!?!

- @jurassik pork :

Merci pour la suggestion : un essai avec l'option AlwaysInsert:=False permet de stabiliser le temps d'exécution à hauteur d'environ 1s sur mon ordinateur. Mais je trouve quand même que c'est trop long pour 579 lignes alors que j'envisage de manipuler plusieurs dizaines de milliers de lignes.

- @Franc, @Jean-Philippe André, @deedolith et @jurassik pork :

Suivant vos conseils et recommandations, je vais m'orienter vers l'usage d'un tableau en mémoire suivie d'une écriture unique en 1 seul bloc dans la feuille de calcul. Cela me semble plus raisonnable, étant donné que l'application envisagée pourra nécessiter de manipuler jusqu'à plusieurs dizaines de milliers de lignes dans des tableaux structurés.

- @TOUS : Merci, et je reviens vers vous dans quelques instants avec un nouveau post concernant un nouveau casse-tête à élucider.

Citation:

---

Envoyé par bremawemul

Suivant vos conseils et recommandations, je vais m'orienter vers l'usage d'un tableau en mémoire suivie d'une écriture unique en 1 seul bloc dans la feuille de calcul. Cela me semble plus raisonnable, étant donné que l'application envisagée pourra nécessiter de manipuler jusqu'à plusieurs dizaines de milliers de lignes dans des tableaux structurés.

---

Bonjour,
Dans ce cas cette documentation (en cours de publication officielle) pourrait vous aider : Fonctions en VBA pour gérer de gros volumes de données

Bonne continuation.

Bonjour,

Citation:

---

Petit commentaire perso : je trouve assez incroyable que des outils existant de longue date, comme Excel, VBA et les tableaux structurés, soient encore sujet à des fuites de mémoire !?!?!

---

Excel et les produits Office sont en constante évolution, avec des mises à jour régulières
Une nouveauté peut entraîner des impacts imprévus sur des outils existants, et la gestion mémoire n'est pas la partie la plus simple à gérer des programmeurs, surtout si de nouveaux outils interagissent avec d'anciennes fonctionnalités
Pour optimiser un programme et être sûr qu'on évite des fuites mémoire, on l'optimise et ensuite le mieux est qu'il reste stable, pas qu'il évolue en permanence

Merci Jurassic pork, ça explique bien des choses.

Citation:

---

La plus part du temps, les performances ne sont pas une priorité en VBA, on préfèrera d'abord obtenir les comportements attendus et résultats escompté.
Si le besoin de performances se fait sentir, c'est une solution envisageable, sinon mieux vaut rester sur du fonctionnel pure et dur.

---

et bien d'accord avec deedolith

Salut

Citation:

---

étant donné que l'application envisagée pourra nécessiter de manipuler jusqu'à plusieurs dizaines de milliers[ de lignes]

---

Je ne sais pas quels traitements seront faits sur tes données mais regardes peut-être du coté de PowerQuery.

++
Qwaz