Macro de plus en plus lente au fil des utilisations

Bonjour,

J'ai créé une macro de Name Cleaner pour supprimer tous les noms Excel sauf ceux contenant le chaîne de caractère "!_EXPORT". Trouvant que la macro ralentissait au fil de son exécution, du coup je l'applique sur les 10 000 premiers noms pensant que ça règlerait le problème.

J'ai fait un maximum pour l'accélérer mais je constate que lors de la première exécution la macro met 10/20 secondes, lors de la deuxième 30 secondes, lors de la 3ème 60 secondes etc.

Alors que lorsque je ferme le fichier excel et le rouvre, la macro met de nouveau 10/20 secondes.

Je ne comprends absolument pas pourquoi. La RAM ne bouge pas entre les différentes exécutions de la macro.

Merci pour votre aide !

Voici le code :

Sub NameCleaner ()

Dim CountDown, Nbr As Long

'Désactivation des visuels
Application.Calculation = xlManual
Application.StatusBar = False
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

Nbr = ActiveWorkbook.Names.Count

'Traitement des 10000 premiers noms
For CountDown = 10000 To 1 Step -1
    ActiveWorkbook.Names(CountDown).Visible = True
    If InStr(1, ActiveWorkbook.Names(CountDown).Name, "!_EXPORT", vbTextCompare) <> 0 Then
        ActiveWorkbook.Names(CountDown).Visible = False
    Else
        ActiveWorkbook.Names(CountDown).Delete
    End If
    Application.StatusBar = "Noms restants: " & CountDown & " out of " & Nbr
Next

Set CountDown = Nothing

'Réactivation des visuels
Application.Calculation = xlAutomatic
Application.StatusBar = True
Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True

End Sub

Bonjour

J'ai créé une macro de Name Cleaner pour supprimer tous les noms Excel sauf ceux contenant le chaîne de caractère "!_EXPORT". Trouvant que la macro ralentissait au fil de son exécution, du coup je l'applique sur les 10 000 premiers noms pensant que ça règlerait le problème.

Désolé de ma question mais, vous avez 10000 noms enregistrés ??

Cordialement

Bonjour,

Oui je travaille avec beaucoup de fichier venant de clients, et ils sont souvent vérolés. Je peux avoir jusqu'à 100 000 noms dans mes fichiers.

Vous avez une idée du pourquoi la macro ralentit au fil des exécutions et redevient rapide quand on ferme et rouvre excel ?

Sinon je suis preneur d'une optimisation du code pour accélérer la macro ! :)

Vous avez une idée du pourquoi la macro ralentit au fil des exécutions et redevient rapide quand on ferme et rouvre excel ?

Je suppose que vous parlez de l'exécution lors de la suppression des noms
A quel moment voyez-vous le ralentissement ? A la fin du code ou pendant l'exécution.
Pas facile de vous donner une réponse sans voir le fichier en question

Essayez un peu le code comme ceci :

Sub test()
Dim i As Long
Application.Calculation = xlCalculationManual
With ThisWorkbook
    For i = .Names.Count To 1 Step -1
        With .Names(i)
            .Visible = True
            'MsgBox ActiveWorkbook.Names(i).Name
            If .Name Like "*EXPORT*" Then
                .Visible = False
            Else:  .Delete
            End If
        End With
    Next i
End With
Application.Calculation = xlCalculationAutomatic
End Sub

NB : Attention que le code ne supprime pas les noms attribués pour des tableaux structurés

Si ok -->

Cordialement

Bonjour à tous,

en attendant une explication qui ne viendra peut-être pas (bug potentiel) tu pourrais voir si un enregistrement ne suffit pas pour retrouver la vitesse d'origine.
eric

Merci pour vos retours !

Je teste ta proposition de code Dan.

Eric : Non justement j'ai testé également. J'ai d'autres macro de ce genre qui ralentisse au fil des utilisations mais qui redeviennent rapide lorsque je rouvre l'Excel.

Tu as déjà rencontré ce genre de situation ?

Merci pour votre aide :)

Le souci est toujours de savoir ce qu'excel voit lorsque l'on exécute quelque chose.
Le problème est peut être lié à un souci de mémoire ou alors aux formules. Mais si vous désactivez le calcul pendant l'exécution cela peut aider

Dans le cas présent cela peut être dû à votre déclaration Dim CountDown qui de type Variant et donc la plus mauvaise à utiliser. Mettre Dim countdown as integer serait mieux je pense

Sinon , depuis que j'utilise excel je n'ai connu qu'une seule personne qui passait parfois du temps à faire planter excel pour aller voir ce que les gars de Microsoft avait codé. Trop fort le gars... mais bon il n'est plus des nôtres...

Dans l'attente de votre retour sur ce que je vous ai proposé

Edit modo : quid des formules de politesses d'usage ici en France

Dan, je viens de tester votre proposition mais elle ne fonctionne pas.

J'ai une erreur au niveau du .delete avec le message suivant :

J'ai essayé avec un On error resume next mais dans ce cas la macro se termine donc tout les noms sont traités ainsi.

J'ai vérifié le name des noms avec un name manager mais ils respectent bien ces critères pourtant.

J'ai bien repris ce code mot pour mot.

Le code est bien placé dans le fichier où se trouve les noms à supprimer ?

Puis au moment où avez cette erreur quel est le nom concerné ?

chers collègues, bonjour,

à mon avis, le code ralentit la 2ième, 3ieme, ... fois, parce que les premier x noms sont déjà traité dans l'exécution précédent et la macro les rend visible et directement de nouveau invisible = travail inutile. En plus si on delete un nom, ce n'est pas nécessaire qu'il soit visible avant.

Je pense que cette macro est plus rapide et elle delete 10.000 noms avant d'arrêter.

Option Compare Text 'masjucules = minuscules

Sub Quick(bStatus)
     Application.Calculation = IIf(bStatus, xlCalculationAutomatic, xlManual)
     Application.StatusBar = bStatus
     Application.ScreenUpdating = bStatus
     ActiveSheet.DisplayPageBreaks = bStatus
End Sub

Sub NameCleaner()
     Dim Ptr, Ptr0

     Quick False
     t = Timer
     Ptr = (ActiveWorkbook.Names.Count <> 0) * -1     'vérifier s'il y a des names ===> pointer
     If Ptr = 0 Then MsgBox "no names", vbInformation: Exit Sub
     Do
          With ThisWorkbook.Names(Ptr)     'name numéro "ptr"
               If .Name Like "*!_EXPORT*" Or .Name Like "*_xlfn.IFERROR*" Then       'contient ce texte
                    If .Visible Then .Visible = False     'seulement si visible rendre invisible
                    Ptr = Ptr + 1
               Else
                    On Error Resume Next
                    .Delete
                    If Err.Number > 0 Then s = s & vbLf & .Name
                    Err.Clear
                    On Error GoTo 0
               End If
          End With
          Ptr0 = Ptr0 + 1
          If Ptr0 Mod 1000 = 0 Then Application.StatusBar = "Noms restants: " & ThisWorkbook.Names.Count & " numéro actuel : " & Ptr0 & "  " & Format(Timer - t, "0.00\s"): DoEvents   'une fois tous les 1.000 boucles
     Loop While Ptr < ThisWorkbook.Names.Count And Ptr < 10000     'boucles jusqu'à tous les noms ont passé ou jusqu'à 10.000 noms deleted

     If Len(s) > 0 Then MsgBox Mid(s, 2), vbInformation, "Erreurs avec ces noms"
     MsgBox Ptr0 - Ptr + 1 & " noms deleted  et " & Ptr0 & " noms traité "

     Quick True

End Sub

Dan : Effectivement. Ma macro est dans un Add-in donc fichier .xlam.

Avec un ActiveWorkbook ça marche. Le code est un peu plus rapide mais j'ai toujours ce ralentissement au fil des utilisations.

BsAlv : Bonjour, merci pour ta proposition, je l'essaie et te reviens !

Dan : Effectivement. Ma macro est dans un Add-in donc fichier .xlam.

Ah ben évidemment si cela concerne un Activeworkbook, il faut remplacer Thisworkbook par Activeworkbook

essayez le comme ceci :

Sub test()
Dim i As Long
Application.Calculation = xlCalculationManual
With ActiveWorkbook
    For i = .Names.Count To 1 Step -1
        With .Names(i)
            If Not .Name Like "*EXPORT*" Then .Delete
        End With
    Next i
End With
Application.Calculation = xlCalculationAutomatic
End Sub

bonjour tout le monde,

@Dan, en faisant des testes, j'avais des noms comme "_xlfn.IFERROR", je ne sais pas d'où ils viennent (anciens formules Excel je crois) et je ne pouvais pas les supprimer, ce fameux erreur 1004.

@BsAlv : Oui des fois j'ai aussi eu cette info mais je me demande si ce n'est pas dû au pack de Sébastien qui a été chargé par l'utilisateur

Bonjour à tous,

tu pourrais tester un Maj+Ctrl+Alt+F9 avant de relancer les fois suivantes pour voir si la chaine des dépendances ne serait pas en cause.
Ca force sa reconstruction en plus du recalcul complet
eric

Bonsoir à tous,

Merci pour vos retours et je m'excuse pour mon temps de réponse !

J'ai bien regardé tout ce que vous m'avez proposé.

@BsAlv : j'ai testé ton code, mais il s'avère beaucoup plus lent que celui de @Dan.

@Dan : après modification le code marche bien. Pour autant, la macro ralenti également au fil des exécutions.

@eriiic : j'ai testé le forcefullcalculation, mais ça ne change rien. La macro ralenti quand même avec le temps.

Je vous remets mon code et vous laisse me dire si vous voyez des points à améliorer / changer.

Private Sub NameCleaner()

Dim i, Nbr As Long

Application.Calculation = xlCalculationManual
Application.StatusBar = True
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

Nbr = ActiveWorkbook.Names.Count

With ActiveWorkbook
    For i = 10000 To 1 Step -1
        With .Names(i)
            If Not .Name Like "*EXPORT*" Or Not .Name Like "*Upslide*" Then
                .Delete
            Else
                .Visible = True
            End If
            If i Mod (500) = 0 Then
                Application.StatusBar = "Noms restants: " & i & " out of " & Nbr
            End If
        End With
    Next i
End With

Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True

End Sub

Merci à vous :)

Bonsoir,

@Dan : après modification le code marche bien. Pour autant, la macro ralenti également au fil des exécutions.

Ce n'est pas le code que je vous ai proposé

Pourquoi allez-vous jusque la valeur 10000 au lieu d'utiliser name.count comme je vous ai proposé ?

1. Déclaration de variables : Dim i est en variant au lieu de long --> mettez

Dim i as long, Nbr As Long

2. Votre début de boucle comme ceci

For i = nbr To 1 Step -1

Pour la question du ralentissement, juste pour tester, essayez le code en supprimant la remise du calcul en automatique.

Cela va peut être vous permettre de voir à quel moment cela ralenti

bonjour Dan, Eriiic, Geoffroymax, le fil,

il faut savoir que ce limite de 10.000, ce sont 10.000 (si possible) "names" qui sont conservé !!! Le nombre de "names" qui sont supprimé et le nombre de "names" qui sont vérifié sont indiqués dans le msgbox. Donc si le 2ième chiffre est par exemple 20.000, c'est normal que ma macro prend 2 fois plus de temps.

  MsgBox Ptr0 - Ptr + 1 & " noms deleted  et " & Ptr0 & " noms traité "

Bonjour à tous,

BsAlv : non ce code est vraiment plus lent, 30 secondes pour nettoyer 1,000 names sur une première ouverture du fichier vs 10 secondes avec le code de Dan pour en nettoyer 10,000.

Dan : Oui, je l'ai adapté, mais je conserve votre architecture. J'ai notamment besoin du statusbar pour connaître l'avancer du cleaning et de passer les noms gardés en visible pour pouvoir les consulter dans un name manager. J'ai redéclaré la variable i as long comme vous me l'avez indiqué.

Je n'utilise pas le .name.count parce que j'ai observé que plus l'échantillon de names prit est grand, plus le traitement est long. Je développe, pour un excel avec 75,000 names : pour traiter 10,000 names (première ouverture de l'excel) sur un traitement de 10,000 names, la macro met 10 secondes. En revanche, pour traiter 10,000 names (première ouverture de l'excel) sur un ensemble de 75,000 names, la macro met plus de deux minutes ! Suis-je clair ?

J'ai testé de laisser la macro en manuel à la fin de son exécution, mais ça ne change pas. Son temps d'exécution double à chaque fois que je la relance, mais redevient rapide (10 secondes pour 10,000 names) lorsque je ferme et rouvre le fichier.

Merci pour votre aide !