VBA optimiser vitesse d'exécution macro
Résolu
Phoenellion
Messages postés
116
Date d'inscription
dimanche 14 septembre 2008
Statut
Membre
Dernière intervention
30 août 2012
-
Modifié par Phoenellion le 4/01/2012 à 03:39
michel_m Messages postés 16602 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 -
michel_m Messages postés 16602 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 -
Bonjour,
tout d'abord, je sais que excel n'est pas initialement conçu pour de la gestion de bdd, et que MySQL ou Access seraient plus adaptés mais je n'ai pas le choix des armes pour cette bataille.
je codé un macro dans le but suivant :
je dispose d'un classeur "détail" qui contient le détail simplifié des facturations d'un magasin, soit 1 ligne par article avec :
colonne 1 : le numéro d'article
colonne 2 : le numéro de facture
colonne 3 : la vendue
colonne 4 : la date de vente
le but de la macro est de créer une nouvelle ligne pour chaque article et d'y reporter :
colonne 1 : le numéro d'article
colonne 2 : le total des mouvements de stock
colonne 3 : la dernière date de vente
Là où Excel patine à mort, c'est pour la colonne "total des mouvements de stock".
A savoir pour mieux comprendre :
la macro affronte au minimum 30 000 lignes (si si)
le classeur a préalablement été mis en forme de la manière suivante :
- les articles sont triés par ordre croissant
- pour chaque article, la première ligne correspond toujours à la ligne où le total doit être reporté.
En résumé, la macro :
- repère la première occurence pour chaque article
- repère la plage contenant les mouvements de stock de l'article repéré
- calcule le total de cette plage
- efface les lignes devenues obsolètes
(1) : sort de la boucle avant LigMax car la suppression de ligne raccourcit le tableau
(2) : les cellule devant contenir le total sont les seules à être vides, je les repère donc ainsi
(3) : la plage de calcule est donc (x1 ; x2)
(4) : j'anticipe les plages d'une seul ligne sinon -> erreur sur la méthode range à venir
(5) : supression des lignes en temps réel pour alléger le classeur et donc libérer de la ressource : résultat constaté
Comme vous le voyez, j'ai déjà :
- isolé la boucle fatidique dans une sub (si je la laisse dans la macro de départ c'est le plantage assuré)
- inhibé le recalcul automatique du classeur (encore qu'il ne contient aucune formule... mais au cas où...)
- figé la fenêtre excel pour limiter la perte de ressource.
Malgré cela, la macro ne traite que 2 à 3 lignes par seconde au début, et comme je supprime des données au fur et à mesure, la excel pédale de moins en moins et arrive à étaler 10 à 15 lignes par seconde vers la fin (oui oui j'ai tout calculé...)
Je suis donc à la recherche d'autre moyens d'acélérer lexécution de cette boucle.
Merci de vos conseils car cette boucle n'est qu'une petite partie d'un programme qui lui même est répété 10 fois (car 10 magasins à traiter) et d'après mes estimations, cela porterait la durée total des traitements à entre 3 et 5 heures... !!!
tout d'abord, je sais que excel n'est pas initialement conçu pour de la gestion de bdd, et que MySQL ou Access seraient plus adaptés mais je n'ai pas le choix des armes pour cette bataille.
je codé un macro dans le but suivant :
je dispose d'un classeur "détail" qui contient le détail simplifié des facturations d'un magasin, soit 1 ligne par article avec :
colonne 1 : le numéro d'article
colonne 2 : le numéro de facture
colonne 3 : la vendue
colonne 4 : la date de vente
le but de la macro est de créer une nouvelle ligne pour chaque article et d'y reporter :
colonne 1 : le numéro d'article
colonne 2 : le total des mouvements de stock
colonne 3 : la dernière date de vente
Là où Excel patine à mort, c'est pour la colonne "total des mouvements de stock".
A savoir pour mieux comprendre :
la macro affronte au minimum 30 000 lignes (si si)
le classeur a préalablement été mis en forme de la manière suivante :
- les articles sont triés par ordre croissant
- pour chaque article, la première ligne correspond toujours à la ligne où le total doit être reporté.
En résumé, la macro :
- repère la première occurence pour chaque article
- repère la plage contenant les mouvements de stock de l'article repéré
- calcule le total de cette plage
- efface les lignes devenues obsolètes
Sub B_Compil_Dernvte() Dim x, x1, x2, z, LigMax As Long Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual [...] For x = 1 To LigMax If Not Cells(x, 1) <> "" Then Exit For ' (1) If Cells(x, 2) = "" Then ' (2) x1 = x + 1 For z = x1 To LigMax If Cells(z, 1) = Cells(x, 1) Then x2 = z Next z (3) If x1 = x2 Then ' (4) Cells(x, 2) = Cells(x + 1, 2) Else: Cells(x, 2).Value = WorksheetFunction.Sum(Range(Cells(x1, 2).Address & ":" & Cells(x2, 2).Address)) End If Range(Cells(x1, 1), Cells(x2, 1)).EntireRow.Delete ' (5) End If Next x [...] Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic ActiveWorkbook.Save End Sub
(1) : sort de la boucle avant LigMax car la suppression de ligne raccourcit le tableau
(2) : les cellule devant contenir le total sont les seules à être vides, je les repère donc ainsi
(3) : la plage de calcule est donc (x1 ; x2)
(4) : j'anticipe les plages d'une seul ligne sinon -> erreur sur la méthode range à venir
(5) : supression des lignes en temps réel pour alléger le classeur et donc libérer de la ressource : résultat constaté
Comme vous le voyez, j'ai déjà :
- isolé la boucle fatidique dans une sub (si je la laisse dans la macro de départ c'est le plantage assuré)
- inhibé le recalcul automatique du classeur (encore qu'il ne contient aucune formule... mais au cas où...)
- figé la fenêtre excel pour limiter la perte de ressource.
Malgré cela, la macro ne traite que 2 à 3 lignes par seconde au début, et comme je supprime des données au fur et à mesure, la excel pédale de moins en moins et arrive à étaler 10 à 15 lignes par seconde vers la fin (oui oui j'ai tout calculé...)
Je suis donc à la recherche d'autre moyens d'acélérer lexécution de cette boucle.
Merci de vos conseils car cette boucle n'est qu'une petite partie d'un programme qui lui même est répété 10 fois (car 10 magasins à traiter) et d'après mes estimations, cela porterait la durée total des traitements à entre 3 et 5 heures... !!!
A voir également:
- Optimiser code vba
- Code ascii - Guide
- Optimiser son pc - Accueil - Utilitaires
- Code puk bloqué - Guide
- Comment déverrouiller un téléphone quand on a oublié le code - Guide
- Code activation windows 10 - Guide
7 réponses
michel_m
Messages postés
16602
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
16 décembre 2023
3 315
Modifié par michel_m le 5/01/2012 à 09:37
Modifié par michel_m le 5/01/2012 à 09:37
0,0625 secondes pour 10000 lignes
https://www.cjoint.com/?3AfjCKxYaHA
avec 512 Mo RAM DDR et 3 ghz proc
j'ai considéré que la dernière date était celle de la 1° ligne d'une ref
La restitution se fait à coté pour vérification (essais) à modifier enn définitif ( voir macro)
Tu dis...
Pour les tutos:
https://silkyroad.developpez.com/
Michel
https://www.cjoint.com/?3AfjCKxYaHA
avec 512 Mo RAM DDR et 3 ghz proc
j'ai considéré que la dernière date était celle de la 1° ligne d'une ref
La restitution se fait à coté pour vérification (essais) à modifier enn définitif ( voir macro)
Tu dis...
Pour les tutos:
https://silkyroad.developpez.com/
Michel
5 janv. 2012 à 10:27
Phoenellion >> Pas de panique, ça fonctionne impec, change juste la ligne de place
Pour avoir..
Michel voulais prolonger le suspense :-))
A+
5 janv. 2012 à 11:32
effectivement pour le false
mais on peut supprimer
Application.ScreenUpdating = True ' provisoire pour essai de rapidité (vieille histoire);-D
ca venait d'un autre projet où j'étais obligé de réactiver l'écran avant d'indiquer la durée (me souviens plus pourquoi)