Acceleration et Optimisation de code

Bonjour à tous,

J'ai crée un code avec plusieurs USF qui m'affiche des infos a partir d'une BDD. Tous fonctionne trés bien. Mon seul soucis c'est que les infos sont récuperées sur une BDD de plus de 46000 lignes et donc cela prend du temps. J'imagine que le code que j'ai écris doit pouvoir être modifié, optimisé de maniére a fonctionner plus rapidement.

Je souhaite avoir des conseils de personnes experimentées pour m'orienter sur la maniére de pouvoir reduire le temps de fonctionnment des macro.


Je transmet en PJ le fichier excel. Les données de bases etant confidentielles, je l'ai ai donc remplacer. (Le classeur n'est plus trop comprehensible du coup lol). J'ai du reduire la BDD a 20000 lignes pour pouvoir le transferer sur le forum. Mais le fichier tourne sur plus de 46000.

Je ne souhaite pas de solution "clé en main" comme on a pu me le reprocher sur d'autre demande d'aide. Je souhaite des conseils et des orientations pour avancer.


Merci d'avance pour votre aide.

Bonjour,

Citation:

---

Envoyé par stephb084

Je ne souhaite pas de solution "clé en main" comme on a pu me le reprocher sur d'autre demande d'aide. Je souhaite des conseils et des orientations pour avancer.

---

Bon j'ai survolé ton code et je pense que pour l’accélérer, il faudrait passer par des tableaux mémoire, au lieu de

Code:

---

1 2 3 4 5 6 7 8

For Each Cell In Sheets("BDD").Range("C1:C" & i) 'Stocke les données dans une collection '(La collection n'accepte que des données uniques et permet donc ' de filtrer facilement les doublons). If ListBox1.Text = Cell.Offset(0, -1) And Cell.Offset(0, 1) = "En cours" And Cell.Offset(0, 2) < Date Then Unique.Add Cell, CStr(Cell) End If Next Cell

---

par exemple mettre un code dans ce style :

Code:

---

1 2 3 4 5 6

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count) For i = 1 To UBound(tbd) If ListBox1.Text = tbd(i, 2) And tbd(i, 4) = "En cours" And tbd(i, 5) < Date Then Unique.Add Cell, CStr(Cell) End If Next i

---

Ainsi tu supprimes les adressages feuille qui sont bien plus longs

bonsoir anasecu

relis ce que tu a proposé Svp!

Code:

---

1 2 3 4 5 6

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count) For i = 1 To UBound(tbd) If ListBox1.Text = tbd(i, 2) And tbd(i, 4) = "En cours" And tbd(i, 5) < Date Then Unique.Add Cell, CStr(Cell) End If Next i

---

dans ta boucle c'est quoi "cell" !!!!!??????:mouarf:

et ceci est dangereux et faux 'erreur 438"

Code:

---

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)

---

et meme suposont que ca marche et que je veux la plage c1:d10 :10 etant la derniere ligne utilisée sur une feuille rempli jusque colonne F par exemple

voyons voir ta ligne devrait me donner

Code:

---

Sheets("BDD").UsedRange.Cell(1, 1).Resize(10, 6) on est loin de la plage C1:Cx

---

tu vois le chmilblick;)
resizer avec le columns.count oui!! sauf !!! si la plage ne commence pas en colonne A

;)

Bonsoir Patrick,

Tu as raison j'ai recopié la ligne qui aurait dû être

Code:

---

Unique.Add tbd(i, 3), CStr(tbd(i, 3))

---

Citation:

---

on est loin de la plage C1:Cx

---

Oui mais là c'est tout à fait normal car il faut plus que la colonne C pour tester ! ;-)

Comme je n'avais pas testé (honte à moi) il manquait aussi un 's'

Code:

---

tbd = Sheets("BDD").Cells(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count).Value

---

Merci de ta rectification mais elle était partielle...

reste que ceci n'est pas bon pour 2 raisons

Code:

---

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)

---

veux tu savoir pourquoi?

re
tien c'est pour toi annasecu
c'est ce qui est voulu je crois il me semble
rempli quelque ligne sur un sheets("BDD") en metant au moins une cellule pleine en colonne A et va jusqu'a a F ou plus copmme tu veux

apres teste ca

Code:

---

1 2 3 4 5 6 7

Sub testx1() With Sheets("BDD") 'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count) Set tbd = .Range([c3], .Range([A3], .UsedRange)).Columns("C:C") MsgBox tbd.Address End With End Sub

---

ou ca

Code:

---

1 2 3 4 5 6 7

Sub testx2() With Sheets("BDD") 'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count) Set tbd = .Range([c3], .UsedRange).Columns("C:C") MsgBox tbd.Address End With End Sub

---

ou ca

Code:

---

1 2 3 4 5 6 7

Sub testx3() With Sheets("BDD") 'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count) Set tbd = .Range([c3], .Range([A3], .UsedRange)).Columns("C:C") MsgBox tbd.Address End With End Sub

---

ou ca

Code:

---

1 2 3 4 5 6 7

Sub testx4() With Sheets("BDD") 'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count) Set tbd = .Range([A3], .UsedRange).Columns("C:C") MsgBox tbd.Address End With End Sub

---

maintenant vide la colonne A et reteste surprise surprise :oops:
;)
le dernier exemple etant explicite de part l'address de colonne en string (et pourtant!!... comme dit la chanson)

perso le cells(rows.count,y) .end(xlup) reste le plus sur

et pour en revenir a steph comme elle boucle sur les valeurs sans selectionner c'est pas la qu'elle va gagner en velocité

Bonjour a tous,

Merci pour vos réponses.

Alors si je te suis bien Patrick, je dois boucler sur des sélections ? Le fait de passer par un tableau ne sert a rien sur une optimisation de code ?

Bonjour steph
non c'est pas ce que j'ai dis
j'ai dis que tel que l'a presenté anasecu c'est adire
creer un tableau avec une plage et boucler dessus contre boucler simplement sur une plage sans selectionner n'avais aucun signification reelle de velocité

ci dessous ces deux boucles iront aussi vite l'une que l'autre par contre en memoire la 2d est plus gourmande puisque l'on met en memoire le tableau dansdis que la boucle sur cell ne modifie rien a la memoire

Code:

---

1 2 3

for each cell in plage ' reste du code next

---

Code:

---

1 2 3 4

tableau=plage.value for i= 1 to ubound(tableau) ' reste du code next

---

boucler sur des tableaux est vraiment interessant quand on doit moifier les valeurs la oui en effet c'est plus rapide
si tu modifie pas les valeur c'est pas utile
tu comprends ?

D'accord j'ai bien compris . Effectivement je n'ai aucune modif , juste un renvoi de valeurs . Alors il y a t'il d'autres possibilités ?

oui peu etre les filtres avancés surement meme

c'est ICI

Salut.

Il y a plusieurs solutions. Les filtres avancés proposés par Patrick (:plusser:) en sont une, probablement très rapide.

Question que je me pose: Pourquoi remplir une collection puis vider la collection dans la listbox? Pourquoi ne pas directement alimenter la listbox au moment du test?

Autre solution, qui dépend de ta version d'Excel (il faut au moins la 2010). Utiliser PowerQuery qui ferait un peu office de filtre avancé mais qui ne nécessite comme programmation qu'une actualisation de la table produite par PowerQuery.

Autre chose: Savoir ce que tu souhaites réaliser pour te proposer, peut-être, une meilleure conception de ton classeur et de ton code.