Macro très lente et déclarations de variables!

[SETILA]

Bonjour à tous,

Depuis plusieurs semaines j'essaie de trouver la solution ! je suis vraiment novice.
Les codes VBA sont très lent et j'aimerai déclarer les variables pour gagner en rapidité?!
Sur la Sheet1("Scan") soit je scan et la colonne B fait un tronque ou soit je le note manuellement dans la colonne C et se copy en B
mais par exemple quand je copy de la cellule de C2 à B2 Range("C2:C5000").Copy Range("B2:B5000") le tronque s'efface sur toute la colonne(B) donc je ne peux plus scanner (A)???
Je veux que quand je passe en manuelle la cellule qui est en A ce colorie en Noir.
Super compliqué d'expliquer, donc poser moi des questions si besoins.
Pouvez-vous m'aider SVP et m'expliquer afin de comprendre.
Merci d'avance.

Setila

 

[SETILA]

Re

@SETILA
Je viens de comprendre que tu parlais de la fontion TRONQUE.
Voir si en remplaçant tes formules par leur équivalent VBA, ton problème de lenteur disparaitrait.

@Magic_Doctor
As-tu au moins cliquer sur mon lien?

NB: La plus grande part des questions* de ce forum sont d'ordre professionnel.
*:questions que les membres rencontrent avec leur fichier Excel dans leur travail
(rien à voir avec le fun)
(La part congrue étant réservée au fun, voir par exemple le relatif insuccès de mon récent fil dans le salon)

C'est vrai que la netiquette date de 1995 (et que depuis le net a bien changé)
Ci-dessous l'originale dans son jus de l'époque
https://www.ietf.org/rfc/rfc1855.txt

Bonjour Staple1600,
Les formules «countif» sont bien en VBA.
Par contre ma liste est bien de 21000réf! Ne dois-je pas déclarer une variable car + de 20000réf?
Pour synthétiser le fonctionnement:
Lorsque je scan une réf (sheet scan) , la réf avec la désignation produit (sheet liste) va créer une liste de produits scanner car j'ai un filtre automatique (greater than 0).
Merci,
Setila

 

[SETILA]

Re

@SETILA
Je viens de comprendre que tu parlais de la fontion TRONQUE.
Voir si en remplaçant tes formules par leur équivalent VBA, ton problème de lenteur disparaitrait.

@Magic_Doctor
As-tu au moins cliquer sur mon lien?

NB: La plus grande part des questions* de ce forum sont d'ordre professionnel.
*:questions que les membres rencontrent avec leur fichier Excel dans leur travail
(rien à voir avec le fun)
(La part congrue étant réservée au fun, voir par exemple le relatif insuccès de mon récent fil dans le salon)

C'est vrai que la netiquette date de 1995 (et que depuis le net a bien changé)
Ci-dessous l'originale dans son jus de l'époque
https://www.ietf.org/rfc/rfc1855.txt

Re Staple1600,
Quand tu parles d'équivalent VBA c'est la formule là: Sheets("liste").Range("C4:c21084").Formula = "=countif(Scan!$B$2:$B$5000,A4)"
'
Le code VBA va juste mettre la formule mais ne va pas avoir l'équivalent en VBA?

 

[Magic_Doctor]

Décidément, il a du temps à perdre le Patrice...
Il ferait bien de lire ou relire 1984...

Non, je ne pense pas que vous soyez banni de ce site qui demeure, par rapport à d'autres, extrêmement ouvert ; on a rarement affaire à un Torquemada... Dites-vous bien, qu'à part quelques psycho-rigides des bonnes mœurs toiliennes, les autres s'en foutent éperdument que vous ayez posé la même question sur d'autres sites. Il faudrait déjà qu'ils passent leur journée à trainer sur tous les sites...
Dans le pire des cas, allez sur un site anglo-saxon en posant évidemment votre question en anglais (personnellement, quand il m'arrive de le faire -rarement- j'utilise le traducteur de Google, ça suffit amplement).
En dépit du fait que depuis peu je suis Barbamachin, je ne suis pas un grand expert sur le sujet. Je m'intéresse presque uniquement à des problèmes ponctuels personnels, mais en revanche j'aime bien partager le fruit de notre travail (perso + ceux qui m'auront prêté main forte).
Je vais un peu regarder votre feuille, mais je ne vous promet rien.

 

[SETILA]

Décidément, il a du temps à perdre le Patrice...
Il ferait bien de lire ou relire 1984...

Non, je ne pense pas que vous soyez banni de ce site qui demeure, par rapport à d'autres, extrêmement ouvert ; on a rarement affaire avec un Torquemada... Dites-vous bien, qu'à part quelques psycho-rigides des bonnes mœurs toiliennes, les autres s'en foutent éperdument que vous ayez posé la même question sur d'autres sites. Il faudrait déjà qu'ils passent leur journée à trainer sur tous les sites...
Dans le pire des cas, allez sur un site anglo-saxon en posant évidemment votre question en anglais (personnellement, quand il m'arrive de le faire -rarement- j'utilise le traducteur de Google, ça suffit amplement).
En dépit du fait que depuis peu je suis Barbamachin, je ne suis pas un grand expert sur le sujet. Je m'intéresse presque uniquement à des problèmes ponctuels personnels, mais en revanche j'aime bien partager le fruit de notre travail (perso + ceux qui m'auront prêté main forte).
Je vais un peu regarder votre feuille, mais je ne vous promet rien.

Merci c'est gentil

 

[Patrice33740]

Bonjour,

Il n'y a aucune lenteur dans les codes VBA.
Si tu désactives le VBA et que tu agis manuellement tu obtiens la même lenteur.
Ce qui ralenti les macro, c'est le temps que met Excel à calculer ta formule sur 21000 lignes et à appliquer le filtre sur la feuille Liste : c'est indépendant du VBA.

 

[SETILA]

Merci patrice pour ta réponse.
Est-il possible de faire autre chose de manière à gagner en rapidité, exemple: VLookup ou autre code pouvant être plus performant.
Merci d'avance.
Setila

 

[SETILA]

Re,
Est ce que vous connaissez «Quicksort»?
http://rosettacode.org/wiki/Sorting_algorithms/Quicksort

Résultat de la course entre ces trois traitements :

• TriDonnées(MonTableau(), True)dure 14,3 secondes.
  
  
• TriDonnéesBis(MonTableau(), True)dure 2,24 secondes. Effectivement, cela valait la peine de rechercher un algorithme moins gourmand en ressources.
  
  
• QuickSort(MonTableau(), LBound(MonTableau), UBound(MonTableau)) : 0,02 seconde. Il n'y a pas photo. Cet algorithme est 100 fois plus rapide que notre meilleur algorithme.
  

 

[Patrice33740]

Bonjour,
Le lien que tu fournis ne fonctionne pas mais je connais QuickShort.
mais ce n'est qu'un tri, pas un filtre.
Ce qui est très rapide c'est l'utilisation de tableau au lieu de Range.

Il y a aussi les dictionnaires, voici une idée :
1) tu supprimes les formules sur la feuille liste
2) tu supprimes tes formules de la feuille Scan
3) tu utilises l'évènement Change de la feuille Scan pour tronquer la valeur qui vient d'être saisie
4) Au lieu de filtrer la feuille Liste, tu mets le résultat dans une autre feuille
- tu créé un (ou 2) dictionnaire des références
- tu comptabilises, dans le dictionnaire, les références dans la feuille Scan
- tu élimines les références à zéro
- tu transpose ton dictionnaire dans la feuille résultat

Info sur les dictionnaires ici :
http://boisgontierjacques.free.fr/pages_site/Dictionnaire.htm

 

[SETILA]

Bonjour,
Le lien que tu fournis ne fonctionne pas mais je connais QuickShort.
mais ce n'est qu'un tri, pas un filtre.
Ce qui est très rapide c'est l'utilisation de tableau au lieu de Range.

Il y a aussi les dictionnaires, voici une idée :
1) tu supprimes les formules sur la feuille liste
2) tu supprimes tes formules de la feuille Scan
3) tu utilises l'évènement Change de la feuille Scan pour tronquer la valeur qui vient d'être saisie
4) Au lieu de filtrer la feuille Liste, tu mets le résultat dans une autre feuille
- tu créé un (ou 2) dictionnaire des références
- tu comptabilises, dans le dictionnaire, les références dans la feuille Scan
- tu élimines les références à zéro
- tu transpose ton dictionnaire dans la feuille résultat

Info sur les dictionnaires ici :
http://boisgontierjacques.free.fr/pages_site/Dictionnaire.htm

Ça à l'air très intéressant!
Je m'occupe de ça demain et j'aurais sûrement beaucoup de questions pour mettre tout en place.
Merci.
Cordialement,
Setila

 

[Patrice33740]

Re,

Finalement on gardes 1) à 3), mais pas besoin de dictionnaire des références (il y en a trop) .
Edit : voici une 2° proposition :

 

[SETILA]

Bonjour Patrice,

C'est avec une immense joie que je contemple votre travail!
C'est pour ça que j'aime ce genre de site car ici c'est vraiment un travail de professionnel.
J'ai regardé pendant des heures le fichier qui est fait par un expert!
Comme convenu, je vais de poser beaucoup de questions afin d'apprendre...
Bravo...le fichier est d'une rapidité !!!!
Merci
Cordialement,
Setila

 

[SETILA]

Re Patrice,

Pouvez-vous m'expliquer le module mEdit?
'
' Ajouter la référence suivante (Outils/ références) :
' - Microsoft Scripting Runtime
'
Option Explicit
Sub Liste()
' Crée la liste des scans
Const frm$ = "=INDEX(Données!R3C2:R21083C2,MATCH(RC[-1],Données!R3C1:R21083C1,0))"
Dim dSSQ As New Scripting.Dictionary 'Dico des Scan SKU / Quantité
Dim t As Variant
Dim i As Long
' Effacer les données précédentes
shListe.UsedRange.Offset(2).Clear
' Créer les dicos des Scan
With shScan
If .Range("B2").Formula = "" Then Exit Sub
t = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value
For i = LBound(t) To UBound(t)
If dSSQ.Exists(t(i, 1)) Then
dSSQ(t(i, 1)) = dSSQ(t(i, 1)) + 1
Else
dSSQ(t(i, 1)) = 1
End If
Next i
Erase t
End With
' Mettre à jour la liste
With shListe
.Columns("A").NumberFormat = "0"
With .Range("A3").Resize(dSSQ.Count)
.Value = Application.Transpose(dSSQ.Keys)
.Offset(0, 2).Value = Application.Transpose(dSSQ.Items)
.Offset(0, 1).FormulaR1C1 = frm
.Offset(0, 1).Value = .Offset(0, 1).Value
With .Resize(, 3)
' Centrage et bordures
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
End With
End With
dSSQ.RemoveAll
End Sub
Sub EffaceZonesImpression()
' Efface les zones d'impression (pour accélerer le code)
Dim w As Worksheet
For Each w In Worksheets
w.PageSetup.PrintArea = ""
Next w
End Sub

 

[Patrice33740]

Re,

Pour faire simple, la procédure Liste efface et recrée la liste des scans :
Tout d'abord, le dictionnaire est un objet qui permet de stocker des valeurs (item) auxquelles on accède par une clé (key). dans le dico dSSQ la clé sera le SKU et l'item représentera la quantité du SKU.
Pour créer la liste je transfère le contenu de la colonne B, i.e les SKU, dans le tableau t
t = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value
et j'analyse chaque SKU du tableau :
For i = LBound(t) To UBound(t)
...
Next i
Si le SKU (la clé du dico) existe dans le dico (donc à déjà été ajoutée au dico)
If dSSQ.Exists(t(i, 1)) Then
j'ajoute 1 à la quantité du SKU
dSSQ(t(i, 1)) = dSSQ(t(i, 1)) + 1
sinon
Else
je crée une nouvelle entrée du dico avec pour clé le SKU et quantité = 1
dSSQ(t(i, 1)) = 1

Ensuite il n'y a plus qu'a mettre à jour la liste :
je définis la première colonne de plage de cellules qui recevra les SKU (colonne A)
With .Range("A3").Resize(dSSQ.Count)
et j'y transfère tous les SKU contenu dans le dico (i.e. les clés)
.Value = Application.Transpose(dSSQ.Keys)
puis les quantités (les items) dans le 3° colonne (colonne C)
.Offset(0, 2).Value = Application.Transpose(dSSQ.Items)
et enfin dans la seconde colonne (B) la formule qui cherche la désignation correspondante au SKU dans
la feuille Bdd soit "=INDEX(Données!$B$3:$B$21083;EQUIV(A3;Données!$A$3:$A$21083;0))"
.Offset(0, 1).FormulaR1C1 = frm
et je replace les formules par leur résultat
.Offset(0, 1).Value = .Offset(0, 1).Value
et pour finir je met en forme les 3 colonnes alignement et bordures.

Edit : Depuis le VBE, si tu cliques sur un mot du VBA et ensuite tu appuies sur F1, tu obtient l'aide détaillé concernant ce mot

 

[SETILA]

Re,

Un grand merci pour cette explication , c'est plus claire pour moi.
je vais encore analyser votre travail.
Bravo...
Cordialement,
Setila