VBA - problème d'initialisation ?

[Pyje]

J'ai plusieurs macros de mise en forme de tableaux qui durent normalement très peu de temps, moins de 30 secondes. Mais après une première exécution, si je les relance, sans avoir entre temps fermé et réouvert la feuille EXCEL où elles se trouvent, elles peuvent durer plusieurs minutes, jusqu'à 5 ou 6. C'est très agaçant !
N'y aurait-il pas un problème d'initialisation, genre quelque chose à mettre au début du code VBA pour réinitialiser la mémoire ou quelque chose de ce genre ?
Précision : je fonctionne systématiquement avec
"Application.ScreenUpdating = False"
Merci à tous ceux qui pourront me venir en aide !

 

[skoobi]

Re : VBA - problème d'initialisation ?

Bonjour Pyje, bienvenue sur XLD,

As-tu déclaré des variables?
Si oui, sont elles "Public", "Privé"?
D'une manière générale, en mettant "End" à la fin d'une macro tu arrêtes le code et toute la mémoire des variables excel se vide.
Mais je suis pas sur que ça résout le problème, à voir donc.

 

[Pyje]

Re : VBA - problème d'initialisation ?

Merci skoobi pour ta réponse.
Je n'ai pas pour habitude de déclarer les variables. Peut-être est-ce une mauvaise habitude?
Qui pourrait me fournir des explications sur ce point ?
Je vais faire des essais suivant ta suggestion.

 

[Dan]

Re : VBA - problème d'initialisation ?

Merci skoobi pour ta réponse.
Je n'ai pas pour habitude de déclarer les variables. Peut-être est-ce une mauvaise habitude?
Qui pourrait me fournir des explications sur ce point ?
Je vais faire des essais suivant ta suggestion.

Ben tout à fait, le fait de ne rien déclarer revient à ce que toutes tes variables soient de type VARIANT, donc la plus mauvaise.
La déclaration de variables (chère à mon ami @+thierry. Lol ! s'il me lit... ) te permet de gérer la mémoire et d'éventuellement accélerer l'exécution d'un programme.

Maintenant ton pb ne vient peut être pas de là.

Si je dois te donner un conseil :
- déclare tes variables
- évite les SELECT et ACTIVATE
- évite les boucles si cela est possible
- L'organisation de tes macros

Amicalement
@+ Dan

edit : vois ce lien pour t'aider --> https://www.excel-downloads.com/threads/vba-types-de-variables.81052/

 

[Pyje]

Re : VBA - problème d'initialisation ?

La déclaration des variables ne change rien au problème !
En faisant différents essais, j'ai isolé la partie posant problème dans une macro qui me sert de test, la voici ci-dessous, c'est la boucle "For x ...." :

Dim der_lig As Integer
Dim x As Integer
'
Application.ScreenUpdating = False
der_lig = ActiveSheet.UsedRange.Rows.Count + 1
'
MsgBox (der_lig)
For x = der_lig To 2 Step -1
If IsEmpty(Cells(x, 15)) Then
Rows(x).Delete
End If
Next x
-----------------------
Je comprends que ça puisse être long, car on supprime des lignes, donc on décale tout à chaque fois (pour mes essais, 5005 lignes au départ, seulement 105 à l'arrivée).
Par contre pourquoi un temps très court la première fois (environ 15 secondes) et 4 à 5 minutes ensuite, là est le mystère !

 

[Gael]

Re : VBA - problème d'initialisation ?

Bonsoir à tous,

N'y a-t-il pas un mode de calcul différent entre les 2 tests? si des formules font appel à la feuille dans laquelle les lignes sont supprimées ou des calculs internes à la feuille elle-même, le temps d'exécution risque d'être très long. de même pour les évènements

essaye en rajoutant en début de procédure:

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

@+

Gael

 

[Pyje]

Re : VBA - problème d'initialisation ?

Merci Gaël,
Il semble que tu aies vu juste, avec tes 2 instructions, ça va beaucoup mieux !
Il y a effectivement dans le classeur des calculs.
Etrange quand même cette affaire car je maintiens, je n'ai jamais eu ce problème lors d'une première exécution après ouverture du classeur.

Pour l'histoire des calculs, je suppose qu'il faut ensuite rétablir la situation avec
"Application.Calculation = xlCalculationAutomatic"

Par contre, pourrais-tu m'en dire un peu plus sur les évènements, de quelle sorte d'évènements s'agit-il ? et faut-il rétablir la situation ?

Un grand merci à tous ceux qui se sont penchés sur mon problème.

 

[Gael]

Re : VBA - problème d'initialisation ?

Bonsoir Pyge,

L'activation d'une feuille, la sélection d'une cellule, le changement de valeur sont des évènements auxquels on peut associer une macro.

La désactivation des évènements permet de ne pas exécuter une macro liée à un évènement pendant l'exécution d'une autre macro.

Exemple: si tu as une macro liée au changement de valeur de la cellule A1, et que tu veux éviter que cette macro ne se déclenche si tu modifie la valeur de A1 dans une autre macro.

Le mode de calcul correspond à outils - options et calcul sur ordre ou automatique, tu peux le rétablir facilement par ce biais, mais c'est mieux de le faire dans la macro.

Pour les évènements, cela ne peut se faire, à ma connaissance, que par macro, il ne faut donc pas oublier de le réactiver en fin de macro, sinon les procédures évènementielles ne s'exécuteront plus. Dans ton cas, je pense que le calcul en manuel devrait suffire à rétablir la situation.

@+

Gael

 

[Pyje]

Re : VBA - problème d'initialisation ?

Encore merci pour toutes ces informations.

 

[sousou]

Re : VBA - problème d'initialisation ?

Bonjour Pyje et Gael

En regardant vos messages, je voulais vous faire partd'une méthode que j'utilise lorsqu'il y a un grand nombre de lignes ou de colonnes à supprimer ou inserer par exemple.
Cette méthode consiste à ne faire l'opération qu'une fois en selectionnant l'ensemble des lignes.
Pour cela, j'utilise l'union pour définir une seule zone.
le gain de temps est considérable.

cordialement


exemple:
Sub deb()
zone = Null
For Each i In Selection
If i = "" Then
If IsEmpty(zone) = False Then Set zone = Union(i, i)
Set zone = Union(zone, i)
End If
Next

zone.Delete
End Sub

 

[Gael]

Re : VBA - problème d'initialisation ?

Bonjour à tous,

Et merci sousou, j'ai gardé l'exemple en bibliothèque.

@+

Gael