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

**bremawemul** · 01/04/2025, 17h36

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 :

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
 
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

**Valtrase** · 01/04/2025, 17h49

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

**bremawemul** · 01/04/2025, 18h12

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

**Nain porte koi** · 01/04/2025, 19h17

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

**Franc** · 01/04/2025, 23h58

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 :

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
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

**Jean-Philippe André** · 02/04/2025, 00h32

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

**tototiti2008** · 02/04/2025, 21h07

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

**tototiti2008** · 02/04/2025, 21h59

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...

**deedolith** · 03/04/2025, 14h44

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.

**jurassic pork** · 04/04/2025, 10h17

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

**bremawemul** · 04/04/2025, 14h05

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.

**laurent_ott** · 04/04/2025, 15h19

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.

**tototiti2008** · 04/04/2025, 17h08

Bonjour,

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

**Nain porte koi** · 04/04/2025, 23h31

Merci Jurassic pork, ça explique bien des choses.

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