Macro très lente : possible de l'optimiser ?
Bonjour,
Je suis novice sur VBA et grâce à plusieurs recherches, j'ai pu créer un document pas trop dégueu.
Ce doc permet de saisir l'activité journalière.
L'onglet saisie permet la saisie (oui logique me direz-vous
en cliquant sur sauvegarder les données, il se passe les actions suivantes :
1. envoi vers un onglet spécial pour l'impression
2. envoi vers un fichier qui servira de BD pour des tableaux croisés
Seulement voilà...elle est relativement lente. Ma macro met + de 12 sec (voire parfois 20 sec !)
c'est donc une horreur car attendre devant est trèèèèès dur
voilà comment se déroule ma macro (attention, ce n'est pas très digeste)
Sub Validation_saisies() 'Macro affectée à icône "Sauvegarde des données" de l'onglet Saisie
' Macro qui sert à envoyer les données saisies sur l'onglet BD (base de données) et sur onglet Impression
Application.Calculation = xlCalculationManual 'j'ai testé mais ça reste très long
Dim debut As Date, temps As Date, fin As Date
debut = Time
Application.ScreenUpdating = False 'on ne voit pas le déroulement des étapes de la macro => gain de temps
ActiveWorkbook.Save
ActiveSheet.DisplayPageBreaks = False 'saut page
Application.DisplayStatusBar = False 'barre statut
' a) Début envoi_pour onglet IMPRESSION (permet l'impression des données afin de vérifier les saisies)
Sheets("Impression").Rows("2:32").Insert 'insert plusieurs lignes pour permettre le collage des données
Sheets("SAISIE").Unprotect 'enlève la protection de la feuille Saisie
Sheets("SAISIE").Range("A25:X53").Copy 'Copie la sélection A25 à X53 de l'onglet saisir
Sheets("Impression").Activate 'active la feuille impression
With Range("A2") 'et va coller le tout sur l'onglet Impression, à partir de cellule B2
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.Application.CutCopyMode = False 'fin mode copie
End With
Range("B9:F30").Interior.Color = RGB(255, 255, 255) 'enlève la trame de fond violine
' b) Pour imprimer les données saisies : La macro supprime ensuite les lignes inutiles (vides)
Dim i As Integer 'se positionne sur l'onglet impression et supprime les lignes qui contiennent le mot sup dans la colonne W
With Sheets("Impression")
For i = .Range("W" & Rows.Count).End(xlUp).Row To 2 Step -1
If .Range("W" & i).Value = "sup" Then
.Rows(i).Delete
End If
Next i
End With
' c)Début Envoi sur l'onglet BD (pour saisie dans base de données)
Sheets("BD").Rows("2:22").Insert 'insert des lignes pour pouvoir coller les données
Sheets("BD").Rows("2:22").RowHeight = 15 'les mets en hauteur 15
Sheets("SAISIE").Range("A2:G22").Copy 'copie la sélection A2:G22 de l'onglet saisie
Sheets("BD").Range("A2").PasteSpecial Paste:=xlPasteValues 'et colle les valeurs etc sur onglet BD, en cellule A2
Sheets("BD").Range("A2").PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("SAISIE").Range("U2:U22").Copy 'copie la sélection U2:U22 de l'onglet saisie
Sheets("BD").Range("U2").PasteSpecial Paste:=xlPasteValues 'et va coller "la valeur" en U2 de l'onglet BD
'Début collage formule pour pouvoir modifier plusieurs lignes ultérieurement
Sheets("SAISIE").Range("H2:T22").Copy
Sheets("BD").Range("H2").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
'fin collage formule
Dim j As Integer 'permet de supprimer les lignes inutiles (vides) si "non" se trouve dans colonne J
With Sheets("BD")
For j = .Range("J" & Rows.Count).End(xlUp).Row To 2 Step -1
If .Range("J" & j).Value = "non" Then
.Rows(j).Delete
End If
Next j
End With
'fin envoi sur onglet BD
'classement ordre chrono du tableau BD 'car sinon recherche ne fonctionne pas terrible
Sheets("BD").Activate
ActiveWorkbook.Worksheets("BD").ListObjects("BD").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BD").ListObjects("BD").Sort.SortFields.Add Key:= _
Range("BD[[#All],[N°]]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("BD").ListObjects("BD").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Activate
'd) Remise à 0 de la page Saisie
Sheets("SAISIE").Activate 'retour sur onglet Saisie
' Sheets("SAISIE").Range("D27,D29,H27,B33:C54,F33:F54").ClearContents 'efface le contenu de la sélection (là je l'ai enlevé au cas où vous vouliez faire des tests)
Range("D27").Activate 'et se positionne sur D27
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 'puis replace la protection enlevée précédemment
Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
fin = Time
temps = fin - debut
MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)
End SubJe suis coincée et je ne vois pas comment l'optimiser.
j'ai essayé de trouver pour que la macro supprime les lignes vides que sur une sélection (ex les lignes 2 à 32 de l'onglet BD pour éviter qu'il fasse la recherche sur tous) mais je ne sais pas faire
A NOTER : dans cet exemple, il n’y a pas beaucoup de lignes dans l’onglet BD mais le vrai doc atteint 11000 lignes et du coup, le fichier est trop lourd pour être mis en « entier ».
De ce fait, la macro est rapide (là elle fera 1 sec) mais une fois qu’il y a plein de lignes, j’atteins près + de 12 secondes
Avez-vous des idées pour optimiser tout ça et la rendre plus rapide ?
Par avance, merci beaucoup
Titamulette
Bonsoir,
juste comme ça :
votre feuille BD est constituée d'un tableau nommé sous excel...
Une des options intéressante de ce type de tableau est l'intégration des nouvelles données juste en les copiant sur la première ligne vide qui se trouve sous le tableau, en plus les MFC et les formules sont automatiquement recopiées sur ces nouvelles lignes. Donc peut-être qu'un simple copier/coller de valeur à partir de cette ligne serait plus rapide que de faire une insertion de ligne comme vous le faites.
Pour trouver la première ligne vide de ce tableau :
dim der_lign as long
der_lign = Cells(1, 1).End(xlDown).Row + 1Ensuite vous avez une boucle sur la colonne J qui teste toutes les lignes et si valeur = "" alors suppression de la ligne, mais ce test n'est à faire que sur les nouvelles donc si elles sont ajouter à partir de der_lign alors la recherche des cellules vides est à faire de
dim nouv_der_lign as long, i as long
nouv_der_lign = Cells(3, 1).End(xlDown).Row + 1
for i= nouv_der_lign to der_lign step - 1
next icomme cela votre routine de suppression de ligne est plus rapide
Ce peut être un début de solution...
@ bientôt
LouReeD
Bonjour,
Merci de m'avoir répondu.
J'ai essayé de mettre ce code dans ma macro mais il ne se passe rien. J'ai donc fait une nouvelle macro avec juste ces 2 lignes et idem, rien, il ne se positionne pas sur la dernière ligne et je ne comprends pas pourquoi
j'ai fait une fois comme vous, et une fois en ajoutant With ActiveWorkbook.Worksheets("BD")
Sub derniereligne()
'With ActiveWorkbook.Worksheets("BD") j'avais testé au cas où
Dim der_lign As Long
der_lign = Cells(1, 1).End(xlDown).Row + 1
'End With
End Submais décidément ça ne veut pas
Vous voyez ce que je fais de travers ?
@ bientôt
je précise que cette macro pour dernière ligne...je n'ai jamais réussi à la faire fonctionner donc je dois faire quelque chose de travers
PS : je viens de continuer mes recherches dans ce sens. Je viens de voir qu'il serait possible de mettre ce code :
Range("A" & Rows.Count).End(xlUp).Offset(1).Selectest-il aussi bien ?
Bonsoir,
et attention !
le bout de code que je donne ne sert qu'à mettre "en variable" le numéro de la première ligne vide de votre feuille BD, c'est à dire la première ligne qui se trouve sous le tableau.
Ci-joint une routine qui vous indique le numéro de la dernière ligne utilisée d'un tableau :
Si vous lancer le code, une message boxe vous indiquera 4 donc cette routine trouve bien la dernière ligne du tableau A1 à A4.
Maintenant dans votre code il vous faut intégrer cette valeur dans vos boucles afin de réduire les test que vous faites.
En effet vous avez des for to qui reprenne la totalité de votre tableau alors qu'il suffit de faire le test uniquement sur les nouvelles lignes ajoutées au tableau.
Donc le principe :
pour coller les nouvelles lignes : trouver la dernière ligne utilisée de la feuille BD, ajouter 1 à ce numéro pour trouver la première ligne vide en dessous du tableau, sélectionner la cellule range("A" & der_lign +1), pus faire le "past".
Grâce à l'option tableau, les mise en formes, les formules et autre sont recopiés automatiquement sur les nouvelles valeurs collées, et ces nouvelles lignes sont automatiquement intégrées dans le tableau nommé.
Ensuite pour vos boucles de test pour la suppression de ligne vide, il vous faut "retrouver" la nouvelle ligne de dernière valeur du tableau :
soit c'est der_ligne + le nombre total de ligne que vous avez copiées, soit vous refaites une recherche et affectez cette nouvelle valeur à une nouvelle variable
donc vous avez der_ligne tableau avant collage et nouv_der_lign tableau après collage pour ensuite faire une boucle for to next qui va de nouv_der_lign à der_lign step -1
Ok ?
Les codes que je vous ai donnés ne servent à rien en l'état, il vous faut les intégrer à votre routine afin qu'il finissent par faire ce que vous voulez et voir ainsi s'il y a gain de temps...
@ bientô
LouReeD
Bonjour,
C'est sympa de modifier l'environnement de l'utilisateur.
On enregistre la valeur du paramètre AVANT de le modifier pour pouvoir le restaurer à l'identique, et non pas avec les valeurs qui nous plaisent même si c'est celles par défaut (?).
Qu'est-ce que tu en sais si chez moi Application.DecimalSeparator = "," ????
Idem pour les 7 autres paramètres...
On désactive ces lignes de code pour poster sur un forum. Merci
eric
Merci eriiic ! (Je ne prends donc pas le risque d'aller voir).
Bon ! Déjà on ne mélange pas une impression avec d'autres opérations dans une macro, surtout si on veut optimiser. La mesure du temps dans ces conditions ne me paraît aps vraiment fiable.
S'agissant d'optimiser, il conviendrait d'abord d'éliminer les scories restantes de code enregistré, dissocier l'impression, sans doute séquencer autrement, réduire au max les interactions permanentes entre Excel et VBA...
Ce que je ferai d'abord c'est de virer l'impression de la macro (module à part), éliminer les Activate et autres CutCopyMode ou propriétés par défaut restants, sans doute éviter le copier-coller dans la mesure du possible, changer la méthode de tri...). Après on mesure !
Cordialement.
Bonjour LouReed,
Merci pour l'explication. Je n'avais effectivement pas compris le "der_ligne". (oui c'est ma bête noire celle là) mais là, c'est bon, grâce à vous, j'ai compris que c'est pour mettre en variable.
Je vais essayer de voir ça et de mettre en pratique. Je pense qu'effectivement ça peut me faire gagner du temps
Bonjour Eriiic,
oups, je n'ai pas pensé qu'il fallait désactiver ces lignes là pour mettre sous le forum et effectivement, oui c'est vrai que je ne sais pas comment sont les paramètres par défaut de chacun. (je vais donc modifier de ce pas la pj de mon message initial)
Il existe des lignes spéciales pour enregistrer les paramètres avant les modifications ? ça je ne savais pas qu'on pouvait faire comme ça, je vais regarder sur le net comment faire
Bonjour MFerrand,
c'est pas que je mélange l'impression avec d'autres opérations, en fait c'est que je mets les données sur une autre feuille (que j'appelle impression) qui me servira plus tard à une autre macro qui elle, sera destinée à l'impression (histoire de vérifier si ce que j'ai saisi n'est pas bourré d'erreur)
Par contre, vous pouvez me dire ce que vous entendez par "les scories restantes" ?
En tout cas, merci à tous pour vos réponses
Bonjour,
Rapidement car je n'interviens pas sur ton problème, je n'ai pas le sentiment de pouvoir intervenir fructueusement, et jouer avec l'interface de l'application quand on ne maîtrise pas ça sur le bout des ongles c'est jouer les apprentis sorciers, ça ne donne aucune envie d'aller voir, et je remercie encore chaleureusement Eric d'avoir soulevé cette question et m'avoir permis ainsi de m'abstenir.
Alors ce que j'appelle les "scories issues de code enregistrées", ce sont : tous les Activate (à supprimer, sans autre modification, il sont purement parasites), tous les CutCopyMode (à supprimer sans réfléchir), les paramètres de collage spécial non utilisés, laissés à leur valeur par défaut, inutiles : skipblanks, operation, transpose).
D'autre part : la suppression des sauts de page n'a rien à faire ici, si on n'imprime pas, pas besoin d'intervenir sur la mise en page ; la suppression de la barre d'état pour la rétablir à la fin n'est guère compréhensible ; la déprotection est inutile pour copier, elle ne sert que pour l'effacement.
La multiplicité des insertions et collage spéciaux me paraît relever d'une mauvaise conception de départ... Quant à faire une base de données sur un tableau Excel, tout le monde commence à savoir ce que je peux en penser, mais de plus y introduire des formats, des formules, et même des formats de validation, là je considère que c'est carrément une hérésie, et faire le maximum pour avoir des dysfonctionnement un jour ou l'autre...
Comme je n'ai pas l'intention d'intervenir, je me dispenserai d'entrer dans les détails.
Cordialement.
Bonjour,
Avant de modifier un paramètre tu le sauvegardes dans une variable globale qu'il reste mémorisé.
Tu le restaures avant de quitter avec celle-ci. A condition que ton programme soit bien finalisé et ne plante pas sinon la restauration ne se fait pas
Quand à modifier le correcteur orthographique tu t'abstiens tant que tu ne sais pas le restaurer et encore, aucune raison d'y toucher...
Imagine le temps que va perdre un utilisateur qui aura un soucis qui peut ressortir 1 h ou 3 mois après. Avant qu'il pense au correcteur qu'il n'a jamais touché (à condition qu'il finisse par y penser) il peut s'écouler de l'eau sous les ponts... Et s'il fait le lien avec ton programme tu seras maudite jusqu'à la 5ème génération.
Parce que corriger à la saisie le . en , tu m'expliqueras comment tu fais ensuite pour saisir une fonction telle que nb.si().
Et tu m'expliques aussi comment se servir de word correctement aussi, toutes les applis office sont touchées.
Bonjour et bonsoir,
un coucou à MFerrand qui je le vois est de tous les combats à toutes heures !
Merci à Eriiic pour son astuce (déjà ancienne
Titamulette, ci joint :
une version allégée qui reprend "mes idées" de transformation, juste pour la partie de copier / coller de la feuille "Saisie" à la feuille "BD".
Du nettoyage, comme formulé par MFerrand, j'ai essayé de ne mettre que l'essentiel mais je ne suis pas un "pro".
A vous de voir et de comprendre et @ bientôt
Reste à vous toute la partie de création de la feuille impression...
LouReeD
Salut LouReed ! Tu mets le doigt sur le fait que les sujets sur lesquels j'avais prévu de travailler sont restés au point mort (l'envers du décor)... Rééquilibrage en cours.
Bonne continuation sur le sujet. Soyez prudents !
Houlà!!!
Je met le doigt nulle part...
Mais je constate qu'à toutes heures, quelque soit le jour et le sujet, vous êtes parmi de ceux qui répondent !
Que ce soit "littéraire", scientifique", probabilité, comptabilité ou autre, aucun sujet ne vous fait peur et vos remarques, suggestion et solution sont toujours pertinentes et cela depuis l'été 2015 ! Donc c'était une remarque d'admiration, rien de plus
@ bientôt
LouReeD
Bonjour,
Tout d'abord LouReeD, merci pour tout ! (pour votre explication de der_lign et le temps que vous avez mis pour la version allégée de la feuille BD.
Votre aide m'est très précieuse et va me permettre de perfectionner ce document.
Votre version de la macro a l'air top, je vais donc l'utiliser et voir ce que tout ça peut donner.
Je pense que grâce à votre aide, je vais pouvoir avancer dans le perfectionnement de ce document.
MFerrand, merci pour votre explication sur les scories. quant aux Activate et cupcopymode, je vais voir pour y remédier.
J'ai créé cette macro grâce à l'enregistreur et il m'avait mis des select un peu partout et j'avais lu que select était moins bien que activate
J'ai lu sur plusieurs sites que supprimer les sauts de page et de la barre d'état ralentissait les macro donc j'avais essayé...mais ça n'a rien donné effectivement
Bien d'accord sur la conception de départ mais malheureusement, dans le monde du travail, on ne fait pas toujours ce qu'on veut...du coup, j'ai dû me dépatouiller pour créer ce document dans un temps limité sans aucune connaissance des macros (merci internet). Je ne souhaitais pas le faire sur Excel mais nous n'avons pas le choix là où je travaille (pack office très limité)
Eriiic, en fait, le correcteur orthographique n'était pas mis initialement, mais on a constaté à force d'utiliser ce doc que les "," se transformaient en "." . Donc j'ai dû intervenir de cette façon pour y remédier (car je n'ai trouvé de solution nulle part). A savoir que ce document ne sert plus à inscrire des formules, si telle est le cas, je suis la seule à le trifouiller.
Les quelques utilisateurs (on les compte sur la doigt d'une main) savent que pour fermer ce document, il faut cliquer sur l'icône quitter car cela permet de remettre "avant" (notamment barre de formule et déplacement vers le bas). Depuis un an que nous travaillons dessus, nous avons uniquement le problème de lenteur de macro. Après, vos remarques me font me dire qu'effectivement, ça peut péter à tout moment
J'ai essayé de faire au mieux avec les moyens et le peu de connaissance que j'avais des macros (à savoir rien du tout). Je prends en compte toutes vos remarques et je vais essayer de corriger un peu tout ça.
Bonjour,
le séparateur décimal se gère avec :
Application.International(xlDecimalSeparator) pour la configuration de windows Région et Langue
ou avec :
Application.UseSystemSeparators
True si les séparateurs système de Microsoft Excel sont activés.
et Application.DecimalSeparator (séparateur décimal de Microsoft Excel)
Et non avec le correcteur orthographique. S'il se changent (suite à import je suppose) il faut faire une macro qui les remplace sur la plage concernée.
Et on les sauvegarde avant de les modifier.
eric
Bonsoir,
merci pour votre merci et tenez moi (nous) au courant !
@ bientôt
LouReeD
Bonjour,
Eriiic, merci pour ce détail, je vais regarder ça de plus près
LouReeD, je viens d'appliquer vos modif et effectivement, je gagne quelques secondes non négligeables
Merci
je vais continuer de me pencher sur cette macro, mais je pense que je ne pourrai pas avoir mieux que 8 sec (vu que mon tableau fait plus de 10 000 lignes), mais c'est déjà un bon début pour optimiser tout ça
Bonsoir,
il est vrai que j'ai un vieux souvenir d'un résultat d'étude qui disait que sur Excel les utilisateurs s'impatientaient au bout de 7 secondes de "travail" d'une macro...
A savoir que sur le site il existe des solutions de progressbare qui permet de les faire patienter gentiment...
Mais les 8 secondes c'est pour seulement la copie sur le tableau de la feuille BD ou cela comprend aussi la préparation de la feuille IMPRESSION ?
@ bientôt
LouReeD
Bonjour,
C'est la suppression des lignes qui doit prendre le plus de temps.
Si on peut supprimer les formules de BD!J:J essaie comme ça :
With Sheets("BD")
' For J = Der_Lign + 22 To Der_Lign + 1 Step -1 ' on part d'en bas c'est à dir Der_Ligne + les 22 collées et on remonte
' If .Range("J" & J).Value = "non" Then
' .Rows(J).Delete
' End If
' Next J
.Cells(2, 10).Resize(Der_Lign).Value = .Cells(2, 10).Resize(Der_Lign).Value
If .FilterMode Then .ShowAllData
.ListObjects("BD").Range.AutoFilter Field:=10, Criteria1:="="
.[A2].Resize(Der_Lign).EntireRow.Delete
.ShowAllData
End WithTu devrais tomber vers la seconde.
Et pour Der_Lign au-dessus met plutôt :
' calcul de la valeur de cette ligne
Der_Lign = Sheets("BD").Cells(Rows.Count, 1).End(xlUp).Roweric
Bonjour LouReed,
hmm effectivement, j'irai voir pour la progressbar si je n'améliore pas la durée (ça peut effectivement aider à patienter)
Les 8 secondes comprennent tout : feuille BD et feuille pour l'impression donc mieux que mes 17sec du début
Bonjour Eriiic,
alors pour l'instant, j'ai intégré votre amélioration mais...j'ai quelques soucis car ça ne fonctionne pas, du moins, y'a un problème.
je vais donc essayer de regarder ça et de comprendre d'où ça peut venir car il supprime pleiiiiins de lignes la première fois que je l'utilise et après au choix, soit il fait ce qu'il faut sans supprimer les lignes vides, soit la macro plante au moment du dernier ShowAlldate.
vous pourriez m'expliquer à quoi correspond votre code svp ? car là, je ne comprends pas ce qu'il fait alors peut être que si je comprends, je pourrai corriger le défaut (surement sur un de mes onglets)
Merci d'avance
Bonjour,
C'est un peu vieux. De ce que je me rappelle je n'avais pas trop testé vu que je n'avais pas envie de rouvrir ton fichier, j'ai sans doute fait des erreurs.
Le principe est d'enlever les filtres en cours que tout soit affiché, de filtrer les lignes à supprimer et de les supprimer en bloc.
C'est beaucoup plus rapide que ligne par ligne.
Tu peux encadrer le .ShowAlldata par :
On error resume next (devant)
On error goto 0 (après)
Le tout est de trouver le bon filtre qui te laisse visibles les lignes à supprimer. C'est la ligne :
.ListObjects("BD").Range.AutoFilter Field:=10, Criteria1:="="qui est peut-être à modifier. Tu dois connaitre les critères exacts.
Une formule qui retourne "" n'est pas une cellule vide, d'où le choix de remplacer ces formules par le résultat.
Si tu n'y arrives pas fourni un fichier correct (je pense que tu vois à quoi je fais allusion) et les critères pour supprimer une ligne.
eric