[VBA-E]Allocation de mémoire aux variables

**DomBourti** · 03/05/2006, 19h24

SAlut tout l'monde,

J'ai un petit problème...J'ai un classeur comprennant 2 feuilles dont je transfert des données d'une feuille à la suivante. Pour transférer les données, j'utilise un bouton(image de bouton en faite

) qui lance le transfert qui comprend plusieurs boucle "Do While ..." et beaucoup de variables. À chaque fois que j'utilise le "bouton", sans avoir fermé Excel, le temps d'éxécution est de plus en plus long. Si je ferme Excel et que je l'ouvre à nouveau, le temps d'éxécution redevient normal(quelques secondes), mais va augmenter avec chaque transfert.

Je me suis dit que c'était probablement pcq les variables ont une partie de mémoire alloué, qui augmente chaque fois que je relance le transfert et ce avec le même nombre de données à transférer.

Donc, ce que je veux savoir c'est si il est possible de libérer la mémoire utilisé par les variables... Quelque chose du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Variable.free

comme en PHP.

Et si le problème n'est pas relié à l'utilisation de la mémoire, je suis ouvert aux suggestions.

**AlainTech** · 03/05/2006, 21h02

Sans ton code, on aura du mal à te répondre.

**DomBourti** · 04/05/2006, 15h33

Je veux simplement savoir si il y a une manière de libérer les variable à la fin du Sub mais bon, voilà mon 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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
Sub Transfert_des_cnp()
' suivi des groupes professionnels
grp0 = 3
grp1 = 5
grp2 = 7
grp3 = 9
grp4 = 11
grp5 = 13
grp6 = 15
grp7 = 17
grp8 = 19
grp9 = 21
cleared = False
ligneaeffacer = 2
' procédure pour effacer la feuille Version imprimable avant chaque transfert
' des offres pour éviter les offres en double
Do While Not cleared
  ligneaeffacer = ligneaeffacer + 2
  eogrp = False
  ' boucle pour effacer groupe par groupe
  Do While Not eogrp
    If Not Worksheets("Version imprimable").Cells(ligneaeffacer, 1) = Empty Then
       temp = ligneaeffacer + 1
       If Worksheets("Version imprimable").Cells(temp, 1).Value = Empty Then
         Worksheets("Version imprimable").Rows(ligneaeffacer).ClearContents
         Worksheets("Version imprimable").Rows(temp).Delete
       Else: Worksheets("Version imprimable").Rows(ligneaeffacer).Delete
       End If
    Else: eogrp = True
    End If
  Loop
 
  If ligneaeffacer > 22 Then
    cleared = True
  End If
Loop
transfert = False
lignet = 3
colonnet = 3
' Boucle pour transferer les offres d'emploi sur la feuille
' pour l'impression
Do While Not transfert
 Vide = False
 colonnec = 1
 lignet = lignet + 1
 
 ' Vérifie si la colonne "CNP" est vide; si oui, fin de
 ' la boucle pour le transfert des ofres d'emploi
 If Worksheets("Saisie").Cells(lignet, colonnet).Value = Empty Then
    transfert = True
 
 Else
 ' Vérifie si la colonne "Fermé" est vide; si oui, on
 ' vérifie si l'offre a expirée
  If Worksheets("Saisie").Cells(lignet, 19).Value = Empty Then
    date2 = Worksheets("Saisie").Cells(lignet, 18).Value
    perime = DateDiff("d", date2, Now)
    ' Si la date de fin d'affichage a expirée, un "X" est écrit
    'dans la colonne "Fermé" de l'offre en cours
    If perime > 0 Then
       Worksheets("Saisie").Cells(lignet, 19).Value = "X"
    End If
  End If
 
  bongrp = 1000
  changerdoffre = False
  Dim tempo As Integer
  Do While Not changerdoffre
  tempo = Worksheets("Saisie").Cells(lignet, colonnet).Value
   If tempo < bongrp Or bongrp = 10000 Then
    changerdoffre = True
    If bongrp = 1000 Then
     lignec = grp0 + 1
    ElseIf bongrp = 2000 Then
     lignec = grp1 + 1
    ElseIf bongrp = 3000 Then
     lignec = grp2 + 1
    ElseIf bongrp = 4000 Then
     lignec = grp3 + 1
    ElseIf bongrp = 5000 Then
     lignec = grp4 + 1
    ElseIf bongrp = 6000 Then
     lignec = grp5 + 1
    ElseIf bongrp = 7000 Then
     lignec = grp6 + 1
    ElseIf bongrp = 8000 Then
     lignec = grp7 + 1
    ElseIf bongrp = 9000 Then
     lignec = grp8 + 1
    ElseIf bongrp = 10000 Then
     lignec = grp9 + 1
    End If
    Do While Not Vide
     ' Boucle pour trouver la première ligne vide de la feuille
     ' d'impression pour inscrire l'offre au bon endroit
     If Worksheets("Version imprimable").Cells(lignec, colonnec).Value = Empty Then
        Vide = True
     Else: lignec = lignec + 1
     End If
    Loop
    ' Si la colonne "Fermé" est vide, transfert de l'offre d'emploi
    If Worksheets("Saisie").Cells(lignet, 19).Value = Empty Then
       toutelaligne = False
       colc = 0
       ' Transfert de chaque cellule de l'offre d'emploi
       Do While Not toutelaligne
        If colc < 18 Then
         colc = colc + 1
         Worksheets("Saisie").Cells(lignet, colc).Copy Worksheets("Version imprimable").Cells(lignec, colc)
        Else
         toutelaligne = True
        End If
       Loop
       prochaineligne = lignec + 1
       ' Insertion d'une nouvelle ligne pour la prochaine offre
       ' d'emploi dans la même tranche de CNP
       Worksheets("Version imprimable").Rows(prochaineligne).Insert
       ' réajustment des sections des groupes professionels qui suivent
       If bongrp = 1000 Then
        grp1 = grp1 + 1
        grp2 = grp2 + 1
        grp3 = grp3 + 1
        grp4 = grp4 + 1
        grp5 = grp5 + 1
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 2000 Then
        grp2 = grp2 + 1
        grp3 = grp3 + 1
        grp4 = grp4 + 1
        grp5 = grp5 + 1
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 3000 Then
        grp3 = grp3 + 1
        grp4 = grp4 + 1
        grp5 = grp5 + 1
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 4000 Then
        grp4 = grp4 + 1
        grp5 = grp5 + 1
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 5000 Then
        grp5 = grp5 + 1
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 6000 Then
        grp6 = grp6 + 1
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 7000 Then
        grp7 = grp7 + 1
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       ElseIf bongrp = 8000 Then
        grp8 = grp8 + 1
        grp9 = grp9 + 1
       Else: grp9 = grp9 + 1
       End If
    End If
   Else: bongrp = bongrp + 1000
   End If
  Loop
 
 End If
Loop
' ouverture de la feuille Version imprimable
Worksheets("Version imprimable").Activate
End Sub

Comme je le disais, lorsque j'exécute cette partie du code plusieurs fois sans fermer le fichier Excel qui le contient, le temps d'exécution est de plus en plus long, et ce avec les mêmes données tranférer à chaque fois.

Bonne chance

**SfJ5Rpw8** · 04/05/2006, 17h10

je sais pas si cela peu aider ... mais tu pourrai déclarer tes variables locales...dans ta procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Sub Transfert_des_cnp()
' suivi des groupes professionnels
 
dim grp0 as integer
dim grp1 as integer
...

**Catbull** · 04/05/2006, 17h15

Tu peux essayer d'optimiser le code, mais abandonne l'idée de libérer de la mémoire. A ma connaissance il n'y a pas de ramasse miettes et l'espace alloué reste alloué tant qu'Excel reste ouvert.

**DomBourti** · 04/05/2006, 20h56

la déclaration des variables ne change rien au temp d'éxécution malheureusment...pour ce qui est de l'optimisation du code, j'ai déja éliminé près de 200 lignes pour arriver à ce que vous voyez... bref, les utilisateurs iront se chercher un café lors de l'éxécution du code.
Merci quant même.

**AlainTech** · 04/05/2006, 21h33

Je n'ai pas eu le temps de tout analyser mais j'ai plusieurs remarques.

Tes déclarations de groupes me semblent lourdes. grp0 = (0 * 2) + 3... grp9 = (9 * 2) + 3. --> Pas besoin de déclarer les variables grpx.
Je ne supprime jamais de lignes dans une feuilles de travail. Je la vide complètement et je la reconstruis après.
Tu peux mettre
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
Application.ScreenUpdating = False
```
au début de ton code et le remettre à True à la fin.

**DomBourti** · 04/05/2006, 21h44

Effectivement, les déclarations de grp sont lourdes et me font un peu honte

mais je n'ai pas trouvé de moyen de faire autrement puisque la déclaration des groupes sert à savoir ou transférer les données selon le grp, se pourquoi je fais leur suivi plusieurs fois, ce qui donne beaucoup de lignes de code.

Est-ce que supprimer une ligne change quelque chose comparé à la vider et la remplir de nouveau?

Finalement, a quoi sert

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Application.ScreenUpdating = False

Merci pour tes efforts

**AlainTech** · 04/05/2006, 21h53

La suppression de lignes demande énormément de recalculs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Cells.Clear

est instantané.

Pour ScreenUpdating, essaye et dis-moi ce que tu en penses.

As-tu compris ce que je proposais pour les groupes?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(NoGrp * 2) + 3

**DomBourti** · 05/05/2006, 14h22

Je vien d'insérer le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = False

et je le remet a True à la fin et je n'ai pas vu de différence...

J'ai aussi compris la formule des grps, le problème est que : Il y a 2 feuille; une de saisie des données et une d'impression. Dans cette dernière, certaines lignes contiennent un nom de groupe professionnel (10 grps au total) où je doit transferer les données de la feuille de saisie selon le bon groupe. Je doit insérer une ligne sous le bon grp; pour chaque ligne transferé, toutes les lignes qui suivent sont décalées. Donc, je ne voit pas comment faire le suivi des grp autrement qu'avec le code que je t'ai montré. Même avec la petite formule (grpX * 2 + 3), je ne voit pas ce que ca va changer plutot que grpX = Y...

Et pour des fins d'impression, je doit vider la feuille d'impression avant le transfert pour éviter les données en double. Si je n'efface pas les lignes vide de trop entre les grp professionnel, la feuille d'impression est gâchée.

**DomBourti** · 05/05/2006, 19h12

je vien de changer quelque peu mon code: plutot que de supprimer des lignes, je les vides et les masques en attendant de la remplir de nouveau, ce qui semble réduire de beaucoup le temps d'éxécution. Par le fait même, j'ai aussi supprimer de mon code les lourdes définitions de variable, qui ne sont plus utiles avec ma nouvelle méthode.

Je ne voit toujours pas ce que la ligne de code suivante(au début du code et je remet la variable à True à la fin) fait???

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Application.ScreenUpdating = False

Donc, on peut dire que mon problème est résolu(même si j'ai encore une intérogation

). Merci de vos conseils

**jmfmarques** · 05/05/2006, 19h19

Sans connaître VBA !
Cette syntaxe inhibe la mise à jour de l'affichage
et un simple F1 sur le mot ScreenUpdating t'aurait conduit à toutes les explications que tu demandes à ce propos ! Il n'est jamais trop tard pour le faire : un geste, c'est tout !

**DomBourti** · 05/05/2006, 19h28

Merci de me le rappeler pour l'aide avec F1, j'ai tendance à l'oublier. Mais est-ce normal quand j'exécute mon macro pas-à-pas, lorsque l'exécution a dépassé la ligne Application.ScreenUpdating = False, quand je met mon curseur sur la ligne, sa écrit : Application.ScreenUpdating = True ??

**jmfmarques** · 05/05/2006, 19h32

Celà veut dire (lis ton code ligne par ligne) que, plus loin, elle a été remise à True (et c'est bien normal sinon l'affichage ne serait jamais mis à jour !)
En inhibant l'affichage pendant certaines opérations (notamment dans des boucles), tu agilises le traitement, à la suite duquel il convient quand même de mettre à jour cet affichage ! (comme il se fait alors d'un seul coup, c'est beaucoup moins gourmand en temps d'exécution).
Lis donc ton code ligne par ligne et tu verras qu'à un moment donné il contient une instruction remettant à True le SceenUpdating !

P¨S : je ne connais pas VBA, mais là...

**DomBourti** · 05/05/2006, 19h38

La ligne remettant le ScreenUpdating à True est la derniere ligne de mon macro.
Ce que je voulais dire : J'exécute mon macro ligne par ligne. Lorsque l'exécution est rendu sur la ligne juste après celle-ci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = False

l'espèce de pop-up qui apparait si je met mon curseur sur cette ligne affiche clairement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = True

Est-ce normal que je lui donne False comme valeur et que celle-ci devienne True avant même d'avoir atteint la fin du macro où se trouve la ligne suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.ScreenUpdating = True

???

En passant, c'est VB tout court, quoi qu'en dise le titre de la discussion

**SfJ5Rpw8** · 05/05/2006, 19h44

oui en mode debug... on ne voit pas le passage à false de screen.. mais :
rajoute par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Sub Test()
 Application.ScreenUpdating = False
  MsgBox Application.ScreenUpdating
 Application.ScreenUpdating = True
End Sub

**jmfmarques** · 05/05/2006, 19h47

Juste en passant, également :
Application.screenUpdating n'est pas du VB mais bien du VBA ...

**AlainTech** · 05/05/2006, 20h34

VBA veut dire Visual Basic for Applications.

C'est le "VB" inclus dans les applications telles que Word, Excel, Power Point, Outlook et bien d'autres.

A la vue de ton code, je peux t'affirmer qu'il s'agit bien de VBA et pas de VB(4, 5 ou 6).

**DomBourti** · 05/05/2006, 21h57

Ahh je vois, c'est bon à savoir. Merci à tous.

[VBA-E]Allocation de mémoire aux variables

Macros et VBA Excel

Discussions similaires

Partager

Partager