ralentissement sur le temps d'exécution d'une macro

[GiHesse]

Bonjour,

Contexte du problème :

il se passe un truc bizarre sur une macro que j'ai crée.
Cette macro doit traiter de gros fichiers, faire des tris sur les données, créer de nouveaux classeurs avec ces tris....
Pour exécuter un grand nombre de macros d'un coup, j'ai une procédure "enchainement final"(EF) qui regroupe 3 autres procédures "enchainement premiere série"(1S dans la suite), "enchainement deuxième série"(2S), "enchainement troisième série".(3S) Ces 3 procédures font elles-mêmes appel à d'autres procédure.

E2S et E3S doivent nécessairement être appelées après 1S pour fonctionner.

énoncé du problème

-Si j'exécute 1S puis 2S puis 3S, alors j'ai les résultats suivant :
1S : X secondes, 2S moins de 10 secondes, 3S Y secondes.

-Si j'exécute EF, la procédure 1S met X secondes environ, 2S met environ 100 secondes à s'exécuter et 3S met Y secondes environ à s'exécuter

Or EF ne fait qu'une seule chose : exécuter 1S puis 2S puis 3S. Bizarre non ?

Remarques :
Donc tant qu'à faire, je souhaiterais que ma procédure 2S puisse mettre son temps optimum à être exécuté.

NB : dans le cas où il y a un problème, je m'apperçois que la macro semble travailler "pour rien" alors qu'elle a fini son travaille de recopiage. Je vois le petit "recalc" qui avance lentement alors que rien ne semble se passer.

Si vous avez des idées sur cette différence de temps d'exécution, je suis preneur.

Pour le code, je ne sais pas trop quoi vous mettre, donc je mets la procédure qui rassemble les 3 autres (pour voir qu'il n'y a qu'un enchainement de procédures dedans):

Sub EnchainementFinal()


'ma premiere procédure
EnchainementPremiereSerie

'ma deuxième procédure, qui pose un problème si elle est exécutée dans ce contexte mais n'en pose pas sinon
EnchainementDeuxiemeSerie

'ma troisième procédure
EnchainementTroisiemeSerie


End Sub

Restant à votre disposition pour interagir sur vos propositions.

Et merci d'avance,

GiHesse

 

[tototiti2008]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour GiHesse,

ça donne pas trop envie de te lire, ça.
déjà, il faut une bonne heure de disponible pour envisager d'essayer de comprendre ta problématique, et encore faut-il trouver le courage...

Je félicite d'avance les éventuels courageux

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Salut TotoTiti,

je mets ces procédures dans le cas où cela pourrait aider. Puisque cela semble rebuter plus qu'autre chose, je les ai retirées.

En fait, je pense que cela relève plus de la connaissance d'un bug d'Excel et d'une astuce pour le contourner.

Car comme j'ai tenté de l'expliquer, je peux lancer 3 procédures les unes après les autres et cela ne pose pas de problème. Mais si je fais une macro qui effectue cette action (lancer 3 procédures les unes après les autres), alors un des calculs a un temps augmenté de 85 secondes.

Si il n'y a pas de solution possible, et bien je demanderais aux utilisateurs de lancer les procédures indépendamment, et il n'y aura pas mort d'homme.

Merci d'avance si il y a quelqu'un qui s'intéresse au problème, sinon pour une prochaine fois, je saurai qu'il faut attendre avant de mettre son code (je pensais bien faire).

Bonne Journée
GiHesse

 

[Guest]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour le forum,
coucou Tototiti

Ouais sans les macros difficile de dire.
Mais puis qu'on est encore le matin et que j'ai encore plein de bonne volonté.(Attention cela peut ne pas durer)

Essaie donc de glisser ces quelques ligne entre chancune de tes macros:

'Attendre la fin de recalcul après chaque macro
Do
While Not Application.CalculationState = xlDone

A+
bonne journée

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour Hasco,

ayant apris les macros sur le tas depuis peu, je ne connais pas très bien les instructions do...
(j'utilise plutôt les
while(expression)
traitement
Wend )
Donc quand j'ai copié la tienne, j'ai eu une erreur.

En essayant avec

Do While Not Application.CalculationState = xlDone
Loop

Cela n'envoie plus d'erreur, mais je ne sais pas si c'est bien ce à quoi tu pensais, car j'ai toujours un recalc énorme à la fin d'une procédure que je n'ai pas normalement.

Voilà un bout du code, pour le mettre dans le contexte, cette méthode est appelée deux fois d'affilé (et j'ai mis ta proposition avant et après à chaque fois).

Elle applique seulement un filtre automatique sur une colonne d'une feuille et recopie ensuite l'ensemble des données dans une autre feuille.

Sub FiltreDouble(NomClasseurSource As String, NomFeuilleSource As String, _
NomClasseurDestination As String, NomFeuilleDestination As String, NumeroLigneEnTete As Long, _
NomColonneAFiltrer As String, Filtre1 As String, Filtre2 As String)
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'TEST OK : ce fichier permet de filtrer les données d'une feuille en RETIRANT deux conditions de la colonne sélectionnée
'Ensuite il recopie les données restantes dans une feuille DEJA EXISTANTE d'un classeur
'Enfin, il retire le filtre sur la première feuille

'ATTENTION : ce filtre ne copie que les valeurs et pas les formules !!


Dim NumeroColonneAFiltrer As Long
Dim temps0 As Double
Dim temps1 As Double
Dim temps2 As Double
temps0 = Timer

'On recherche le numéro de la colonne que l'on veut filtrer sur la base du nom de celle-ci
Windows(NomClasseurSource).Activate
'NumeroColonneAFiltrer = RecupererNumeroColonneEnFonctionDeLEnTeteBis(NomColonneAFiltrer, _
NomFeuilleSource, NumeroLigneEnTete)

NumeroColonneAFiltrer = RecupererNumeroColonneEnFonctionDeLEnTeteTer(NomColonneAFiltrer, _
NomFeuilleSource, NomClasseurSource, NumeroLigneEnTete)
'Stop

temps1 = Timer
'On se place sur la cellule de l'en tête de la colonne à filtrer
Sheets(NomFeuilleSource).Select
Workbooks(NomClasseurSource).Sheets(NomFeuilleSource).Cells(NumeroLigneEnTete, NumeroColonneAFiltrer).Select

'On effectue un filtre automatique qui évite les deux filtres donnés
Selection.AutoFilter
Selection.AutoFilter Field:=NumeroColonneAFiltrer, Criteria1:="<>" + Filtre1, Operator _
:=xlAnd, Criteria2:="<>" + Filtre2

temps2 = Timer
Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour filtrer"

'On recopie les données filtrées
Cells.Select
Selection.Copy
temps1 = Timer
Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour sélectionner et copier toutes les cellules de la feuille source"

'On sélectionne toutes les cellules de la feuille de desctination
Windows(NomClasseurDestination).Activate
Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination).Select
Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination).Cells.Select
temps2 = Timer
Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour sélectionner toutes les cellules de la feuille destination"
'On colle les données filtrées dans ces cellules
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
temps1 = Timer
Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour coller les données"

Cells(1, 1).Select
Cells.Select
Selection.Columns.AutoFit
temps2 = Timer
Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour mettre en forme les colonnes"

'On retire le filtre initial de la feuille projet
Windows(NomClasseurSource).Activate
Sheets(NomFeuilleSource).Select
Columns(NumeroColonneAFiltrer).Select


ActiveSheet.ShowAllData
Selection.AutoFilter
temps1 = Timer
Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour remettre la position initiale"
'On se replace dans la feuille destination
Windows(NomClasseurDestination).Activate
Sheets(NomFeuilleDestination).Select




Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

temps2 = Timer
Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour remettre la position initiale de Calculation"

Debug.Print "il a fallu " + CStr(temps2 - temps0) + " secondes pour toute la procédure"
End Sub

Et voici ce que donnent les debug.Print

Cas 1 (bug)

il a fallu 0,171875 secondes pour filtrer
il a fallu 0,015625 secondes pour sélectionner et copier toutes les cellules de la feuille source
il a fallu 0,015625 secondes pour sélectionner toutes les cellules de la feuille destination
il a fallu 0,390625 secondes pour coller les données
il a fallu 0,5 secondes pour mettre en forme les colonnes
il a fallu 0,078125 secondes pour remettre la position initiale
il a fallu 87,34375 secondes pour remettre la position initiale de Calculation
il a fallu 88,546875 secondes pour toute la procédure

Cas 2 (non bug)

il a fallu 0,125 secondes pour filtrer
il a fallu 0,015625 secondes pour sélectionner et copier toutes les cellules de la feuille source
il a fallu 0,03125 secondes pour sélectionner toutes les cellules de la feuille destination
il a fallu 0,84765625 secondes pour coller les données
il a fallu 0,53125 secondes pour mettre en forme les colonnes
il a fallu 0,09375 secondes pour remettre la position initiale
il a fallu 0,296875 secondes pour remettre la position initiale de Calculation
il a fallu 1,97265625 secondes pour toute la procédure

 

[liloucmoi]

Re : ralentissement sur le temps d'exécution d'une macro

Hello Gihesse, Hasco , tototiti , tous,

Sheets(NomFeuilleSource).Select
Workbooks(NomClasseurSource).Sheets(NomFeuilleSour ce).Cells(NumeroLigneEnTete, NumeroColonneAFiltrer).Select
Selection.AutoFilter
Selection.AutoFilter Field:=NumeroColonneAFiltrer, Criteria1:="<>" + Filtre1, Operator _
:=xlAnd, Criteria2:="<>" + Filtre2

A remplacer par ceci :

with Workbooks(NomClasseurSource).Sheets(NomFeuilleSour ce).Cells(NumeroLigneEnTete, NumeroColonneAFiltrer)
.AutoFilter
.AutoFilter Field:=NumeroColonneAFiltrer, Criteria1:="<>" + Filtre1, Operator _
:=xlAnd, Criteria2:="<>" + Filtre2
end with

Il y a surement d'autres endroits où il faut faire une modiication similaire.

De manière générale, essaye de proscrire les select tant que tu le peux.

Xldment.

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour liloucmoi,

je ne savais pas que l'on pouvais faire des filtres comme ça, croyant que le select était obligatoire.

Je vous tiens au courant des modifications,

GiHesse

PS : ta devise est schadockienne, ou je me trompe ?

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Re bonjour,

donc j'ai bien mis la méthode de liloucmoi pour le select, ça ne pose pas de problème mais ça ne résout pas celui qui se pose.

Il y a toujours un très long recalc à la fin de la procédure, alors même qu'il n'y a rien à calculer à ce moment...(enfin, il me semble).

Si une autre idée vous vient, elle est la bienvenue.

GiHesse

 

[liloucmoi]

Re : ralentissement sur le temps d'exécution d'une macro

Re,

La seule idée qui me reste c'est d'enlever cela au début :

Application.Calculation = xlCalculationManual

et l'autre à la fin. Tu laisses tout le temps en automatique.

si ca se trouve, c'est le fait de passer en automatique qui déclenche automatiquement un recalcul. Mais je ne vois pas pourquoi il ne le ferait pas dans le cas ou tu déclenche à la main...

Xldment.

PS : Si c'est pas shadockien, ca y ressemble oui
Je n'était pas né lors de la présence des merveilleux shadock dans le poste, je ne peux donc pas l'affirmer.

 

[Bigfish]

Re : ralentissement sur le temps d'exécution d'une macro

Salut,

Voici une version sans select

Sub FiltreDouble(NomClasseurSource As String, NomFeuilleSource As String, _
    NomClasseurDestination As String, NomFeuilleDestination As String, NumeroLigneEnTete As Long, _
    NomColonneAFiltrer As String, Filtre1 As String, Filtre2 As String)
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    'TEST OK : ce fichier permet de filtrer les données d'une feuille en RETIRANT deux conditions de la colonne sélectionnée
    'Ensuite il recopie les données restantes dans une feuille DEJA EXISTANTE d'un classeur
    'Enfin, il retire le filtre sur la première feuille
    
    'ATTENTION : ce filtre ne copie que les valeurs et pas les formules !!
    
    
    Dim NumeroColonneAFiltrer As Long
    Dim temps0 As Double, temps1 As Double, temps2 As Double
    
    temps0 = Timer
    
    'On recherche le numéro de la colonne que l'on veut filtrer sur la base du nom de celle-ci
    With Workbooks(NomClasseurSource)
        'NumeroColonneAFiltrer = RecupererNumeroColonneEnFonctionDeLEnTeteBis(NomCo lonneAFiltrer, _
        NomFeuilleSource, NumeroLigneEnTete)
        
        NumeroColonneAFiltrer = RecupererNumeroColonneEnFonctionDeLEnTeteTer(NomColonneAFiltrer, _
        NomFeuilleSource, .Name, NumeroLigneEnTete)
        'Stop
        
        temps1 = Timer
        'On se place sur la cellule de l'en tête de la colonne à filtrer
        'Sheets(NomFeuilleSource).Select
        .Sheets(NomFeuilleSource).Cells(NumeroLigneEnTete, NumeroColonneAFiltrer).AutoFilter Field:=NumeroColonneAFiltrer, Criteria1:="<>" + Filtre1, Operator _
        :=xlAnd, Criteria2:="<>" + Filtre2
        
        temps2 = Timer
        Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour filtrer"
        
        'On recopie les données filtrées
        .Sheets(NomFeuilleSource).Cells.Copy
    End With
    temps1 = Timer
    Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour sélectionner et copier toutes les cellules de la feuille source"
    
    'On sélectionne toutes les cellules de la feuille de desctination
    'Windows(NomClasseurDestination).Activate
    'Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination).Select
    
    'Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination).Cells.Select
    temps2 = Timer
    Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour sélectionner toutes les cellules de la feuille destination"
    'On colle les données filtrées dans ces cellules
    With Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination)
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        temps1 = Timer
        Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour coller les données"
        'Cells(1, 1).Select
        .Cells.Columns.AutoFit
    End With
    temps2 = Timer
    Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour mettre en forme les colonnes"
    
    'On retire le filtre initial de la feuille projet
    With Workbooks(NomClasseurSource).Sheets(NomFeuilleSource)
        .ShowAllData
        .Cells.AutoFilter
    End With
    temps1 = Timer
    Debug.Print "il a fallu " + CStr(temps1 - temps2) + " secondes pour remettre la position initiale"
    'On se replace dans la feuille destination
    'Windows(NomClasseurDestination).Sheets(NomFeuilleDestination).Select
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    temps2 = Timer
    Debug.Print "il a fallu " + CStr(temps2 - temps1) + " secondes pour remettre la position initiale de Calculation"
    
    Debug.Print "il a fallu " + CStr(temps2 - temps0) + " secondes pour toute la procédure"
End Sub

voici la version sans les lignes qui ne servent à rien

Sub FiltreDouble(NomClasseurSource As String, NomFeuilleSource As String, _
    NomClasseurDestination As String, NomFeuilleDestination As String, NumeroLigneEnTete As Long, _
    NomColonneAFiltrer As String, Filtre1 As String, Filtre2 As String)
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    'TEST OK : ce fichier permet de filtrer les données d'une feuille en RETIRANT deux conditions de la colonne sélectionnée
    'Ensuite il recopie les données restantes dans une feuille DEJA EXISTANTE d'un classeur
    'Enfin, il retire le filtre sur la première feuille
    
    'ATTENTION : ce filtre ne copie que les valeurs et pas les formules !!
    
    
    Dim NumeroColonneAFiltrer As Long
    
    'On recherche le numéro de la colonne que l'on veut filtrer sur la base du nom de celle-ci
    With Workbooks(NomClasseurSource)
        NumeroColonneAFiltrer = RecupererNumeroColonneEnFonctionDeLEnTeteTer(NomColonneAFiltrer, _
        NomFeuilleSource, .Name, NumeroLigneEnTete)
        
        'On se place sur la cellule de l'en tête de la colonne à filtrer
        .Sheets(NomFeuilleSource).Cells(NumeroLigneEnTete, NumeroColonneAFiltrer).AutoFilter Field:=NumeroColonneAFiltrer, Criteria1:="<>" + Filtre1, Operator _
        :=xlAnd, Criteria2:="<>" + Filtre2
        
        'On recopie les données filtrées
        .Sheets(NomFeuilleSource).Cells.Copy
    End With
    
    'On colle les données filtrées dans le fichier de destination
    With Workbooks(NomClasseurDestination).Sheets(NomFeuilleDestination)
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .Cells.Columns.AutoFit
        .Activate
    End With

    'On retire le filtre initial de la feuille projet
    With Workbooks(NomClasseurSource).Sheets(NomFeuilleSource)
        .ShowAllData
        .Cells.AutoFilter
    End With
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Comme tu peux le voir ton code est tout petit.

Pour ce qui est de la lenteur je ne pense pas que cela vienne de ce code. Pour pouvoir resoudre ton probleme le mieux serait que tu nous donnes ton fichier, sans informations confidentielles.

A+

 

[tototiti2008]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour à tous,

Désolé, je ne voulais pas refroidir les ardeurs, hier, mais le post était initialement vraiment trés long...

en effet, plus de 80 secondes de traitements supplémentaires, bizarre....

aurais-tu plusieurs classeur d'ouverts dans un cas et un seul dans un autre ? il faut savoir que le "recalcul" s'applique à l'ensemble des classeurs...

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Bonjour à tous et merci de vos réponses,
au retour du We, je retrouve mon problème de la semaine précédente et vous répond :

-liloucmoi : en fait, j'avais mis le calcul en manuel justement pour essayer d'empêcher le recalcul. J'ai quand même réessayé en le retirant, en vain

-Bigfish, merci pour ton code, je l'ai essayé en parallèle de celui que j'avais épuré de select mais cela n'apporte pas de résultat non plus

Je vais tenter de rendre anonyme le fichier, mais je ne pourrais pas faire ça sur les 45000 lignes, donc forcément les temps de réponse seront meilleurs. Je le poste dès qu'il est prêt.

-tototiti : pas de problème pour "refroidir mes ardeurs", étant un nouveau du forum, je ne sais pas encore trop comment m'y prendre.
Pour te répondre, j'ai le même nombre de classeur ouvert dans les deux cas.

Par contre, je me suis rendu compte d'une chose, ce n'est pas le fait de lancer automatiquement la macro qui lance ce recalc indésirable. En fait, si je lance moi même la macro enchaînement deuxième série après la première, cela pose les même problèmes. Mais puisque toutes les fois suivantes où je relance la seconde macro sans avoir relancé la première (le classeur de données étant toujours ouvert), alors le calcul ne pose pas de problème et se fait sans perte de temps. Vu que je testais à chaque fois en lançant la 2ème macro après avoir constaté une fois le bug, je ne m'en était pas rendu compte.

Ce que tu dis sur les différents classeurs est intéressant. Si j'ai bien compris ce que j'ai trouvé à droite et à gauche, le recalc est le fait de recalculer les formules des différentes cellules. Peut être qu'une fois que j'ai écris mes différentes formules, je pourrais mettre un truc du type
Worksheets(1).EnableCalculation = False
pour tout le reste du code pour empêcher tout problème.


Je vous tiens au courant pour le fichier anonyme et la tentative de blocage du calcul au fur et à mesure.

Bonne journée à tous, et merci pour le temps passé à me répondre

GiHesse

 

[GiHesse]

Re : ralentissement sur le temps d'exécution d'une macro

Re bonjour,

ça y est, problème résolu, merci à tous !

Sur la remarque de tititoto, j'ai bloqué les calcul automatique des autres feuilles, et cela ne bug plus.

Je ne comprends toujours pas pourquoi il y a eu un problème initialement, mais l'essentiel est que ça marche dorénavant.

merci encore,

JS