Optimisation boucle for (temps d’exécution trop important)

Version imprimable

Bonjour à tous et à toutes.

J'ai un grand problème avec mon code VBA, qui se lance lors de la création d'une feuille Excel.
La variable "resultat" est entrée par l'utilisateur à la création d'une feuille qui prend sa valeur comme nom.

Je voudrais que les valeurs d'une colonne de cette feuille (la C) soient copiées dans ma feuille "Bon de commande".
De même avec les valeurs venant d'un autre fichier.
Code:

1 2 3 4 5 6 7 8 9 10 With Sheets("Bon de commande") For i = 5 To 1677 .Cells(i - 2, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C" & i .Cells(i - 2, j - 4).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!G" & i .Cells(i - 2, j - 3).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!H" & i .Cells(i - 2, j - 2).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!I" & i Next i End with
Une grande partie de ces valeurs resteront vides, mais je ne trouve pas de solution autre que celle-ci car la feuille "resultat" et "fichier de base" seront modifiés plus tard par l'utilisateur et ces modifications doivent être prises en compte...

Ma boucle est beaucoup trop longue et je ne trouve pas de moyen d'optimisation. J'espère avoir été clair dans mon explication.

En espérant que vous pourrez m'aider! :roll:

Cordialement,

Antoine

20/07/2016, 10h55
kolodz

Le problème c'est la condition de fin de ta boucle for To 1677. Celle-ci est arbitraire... et bien trop grande sans raison valable.
Il est possible d'obtenir la dernière cellule non vide via : Range.End, propriété (Excel)
Sans faire de VBA moi-même, je pense que la solution est de ce côté. En ayant la boucle for limité à ce qu'il y a vraiment à copier tu va réduire le temps de traitement.

Cordialement,
Patrick Kolodziejczyk.
20/07/2016, 11h04
Al__22

Déjà je suis d'accord avec kolodz et ensuite cela doit venir aussi du fait que à chaque fois tu mets dans tes cases une formule d'appel de données venant d'un autre fichier... Donc ton excel doit ouvrir ce fichier en cache chercher les données voulue puis copié celle-ci.
Ce genre de cas peut ne pas être gênant quand c'est dans une ou deux cases mais vu le nombre de fois ou tu l'as met ton fichier doit être saturé :mouarf:
Pourquoi ne pas faire simplement un copié/collé des données voulues sans une formule d'appel ?
20/07/2016, 11h06
barbeaua

Fin de la boucle

Bonjour Patrick,

Merci pour ta réponse.

Le problème auquel je fais face, c'est que l'utilisateur va aller modifier le "fichier de base" après.. Et ce fichier contient 1677 lignes.

Par conséquent, je ne peux pas prévoir à l'avance quelles lignes seront vides ou non...

Cordialement,

Antoine
20/07/2016, 11h14
casefayere

Bonjour,

et si tu montrais le code complet, que représente "j" ?
20/07/2016, 11h18
jeanmidudu

Bonjour,

Déterminer la dernière ligne remplie :

Code:

.Range(.Cells(Rows.Count, j),.Cells(Rows.count, j)).End(xlUp).row

et
Ainsi tu es certain de ne pas faire de traitement inutiles

Ensuite, ça ne serait pas plus simple de faire seulement une instruction? Un simple copié-collé de l'ensemble de ce qu'il te faut?
20/07/2016, 11h27
Philippe Tulliez

Bonjour,

Citation:

Ma boucle est beaucoup trop longue et je ne trouve pas de moyen d'optimisation. J'espère avoir été clair dans mon explication.

D'après la lecture du bout de code, tu copies une formule de la ligne 5 à la ligne 1677 et ce sans condition.
Dans ce cas pourquoi une boucle alors que tu peux écrire en une seule ligne sur la plage complète
Exemple d'une formule (addition) écrite sur une colonne

Code:

Range("C2:C1000").Formula = "=A2+B2"

Cette ligne évite une boucle de 2 à 1000

Bonjour,

Je pense que kolodz et Al__22 apporte des pistes très intéressante.
Dans le bout de code que tu donnes ce qui est long c'est l'ouverture en arrière plan du classeur qui est sur un emplacement réseau...
Si quand tu souhaites ouvrir ce fichier toi même ça te prends 0,5 seconde ça te prends 14 minutes pour faires les 1167 ouvertures ... :roll:

A mon sens il faudrait aller lire le moins souvent ton [Fichier_de_base.xlsx] en mettant tout son contenu dans un dictionnaire puis en le refermant.
Travailler sur ton fichier de bon de commande

Je me suis fait cette fonction pour ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 'Remplir un dictionnaire avec le contenu d'une feuille '@PARAM ligne_debut {Integer} Première ligne de la feuille '@PARAM colonne_debut {Integer} Première colonne de la feuille '@PARAM ligne_fin {Integer} Dernière ligne de la feuille '@PARAM colonne_fin {Integer} Dernière colonne de feuille '@RETURN {Dictionnary} tableau rempli Function remplir_dictionnaire( _ ligne_debut As Integer, _ colonne_debut As Integer, _ ligne_fin As Integer, _ colonne_fin As Integer _ ) As Dictionary Dim dic As New Dictionary Dim item As String i = 0 j = 0 k = 0 For i = ligne_debut To ligne_fin For j = colonne_debut To colonne_fin If j = 1 Then item = Cells(i, j).value Else item = item & ";" & Cells(i, j).value End If Next j dic.add k, item k = k + 1 Next i Set remplir_dictionnaire = dic Set dic = Nothing End Function
J'appelle cette fonction comme ci après (tout ce trouve dans le même classeur) :
Code:

1 2 3 4 5 6 7 8 ' Récupérer les informations dans les feuilles ' jobs.infos, TIR.infos, UNLOPC Sheets(shJob).Select lDebut = Cells.Find(what:="JOB_NAME").Row + 1 lFin = ActiveSheet.UsedRange.Rows.Count cDebut = 1 cFin = Cells.Find(what:="PRCT_JOB_ETAPE").Column Set Jobs = remplir_dictionnaire(lDebut, cDebut, lFin, cFin)
Ainsi j'accède une seule fois à ma feuille, tous mes travaux et tests se font dans mon dictionnaires qui s'appelle ici Jobs.

Dans ton cas on pourrait imaginer rajouter le fichier et la feuille à consulter, lire le contenu et refermer le tout.

Teste si le classeur est ouver, et l'ouvre en lecture seule si besoin
Code:

1 2 3 4 5 planning = excelOuvert(fplan) If Not planning Then 'Si le classeur du planning n'est pas ouvert Workbooks.Open ch_p & "\" & fplan, ReadOnly:=True 'Il s'ouvre en lecture seule Workbooks(fplan).Sheets(sa).Activate 'Active une feuille spécifique End If
Et la fonction qui contrôle si le classeur est ouvert
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 'Contrôler si un classeur est ouvert '@PARAM {Variant} nom du classeur à contrôler '@RETURN {Boolean} Function excelOuvert(Classeur As Variant) As Boolean Dim wb As Excel.Workbook Dim Appli As Excel.Application On Error Resume Next Set Appli = GetObject(, "Excel.Application") excelOuvert = False For Each wb In Appli.Workbooks If wb.Name = Classeur Then excelOuvert = True Exit Function End If Next wb End Function
Une fois que tu as lu le contenu de la feuille, tu refermes le classeur

Code:

Application.Workbooks(fplan).Close SaveChanges:=False

A adapter évidement.

20/07/2016, 11h55
jeanmidudu

@Bragu Demon

Je crois que ta solution n'est pas vraiment gagnante. Elle l'est par rapport à son code initial, mais quand on voit ce qu'il fait, il est assez évident (même s'il subsiste un très léger riquiqui minuscule micro doute) que tout peut se faire par copier-coller. 4 instructions maximum (et encore, je me demande si on ne peut pas faire moins) au lieu de plus de 1600...

Enfin le demandeur choisira la solution qui lui convient le mieux.
20/07/2016, 13h08
barbeaua

Evolution fichier

Bonjour à tous et merci pour votre implication dans mon projet.

La variable "j" est un simple compteur qui me permet de créer 4 colonnes à chaque nouvelle feuille créée.

Le problème, c'est que les modifications effectuées sur une feuille après le lancement de la procédure doivent avoir une répercution sur la feuille "bon de commande".
Je suis donc obligé de créer une sorte de lien entre celles-ci.

Je vais essayer de tout placer dans ce même classeur pour éviter le lien vers un autre fichier, en espérant que ça améliore le temps d’exécution.

Je reviendrais vers vous sur la suite.

Nouvelle proposition

Voilà,

Alors j'ai copié le contenu de mon fichier externe dans une nouvelle feuille appelée "prix".

Voici le code:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Commande()
 
 
With Sheets("Bon de commande")
    .Range(.Cells(1, j - 5), .Cells(1, j - 2)).Merge 'Fusion des 4 cellules de la nouvelle colonne ligne 1
    .Cells(1, j - 5) = WorksheetFunction.VLookup(resultat, Sheets("ListeIG").Range("A4:B2000"), 2, False) 'Attribution d'un nom à cette colonne (nom récupéré grâce au code entré "resultat" qui va rechercher sur une autre feuille le nom associé au code)
 
    .Cells(2, j - 5).Value = " Quantité " 'Attribution des 4 sous colonnes avec un nom ligne 2
    .Cells(2, j - 4).Value = " Fournisseur "    'Affichage des titres colonnes
    .Cells(2, j - 3).Value = " Code "
    .Cells(2, j - 2).Value = " N° Article "
 
    For i = 5 To 1677 'boucle qui parcourt la plage de données
 
    .Cells(i - 2, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C" & i 'lie les cellules de la nouvelle feuille (nomée avec la variable resultat) a celles de la feuille bon de commande "quantité"
    .Cells(i - 2, j - 4).FormulaLocal = "=Prix!G" & i 'lie les cellules de la colonne G de la feuille prix à celles de la colonne fournisseur dans "bon de commande"
    .Cells(i - 2, j - 3).FormulaLocal = "=Prix!H" & i 'lie les cellules de la colonne H de la feuille prix à celles de la colonne code dans "bon de commande"
    .Cells(i - 2, j - 2).FormulaLocal = "=Prix!I" & i 'lie les cellules de la colonne I de la feuille prix à celles de la colonne n° article dans "bon de commande"
 
    Next i
 
End With
 
End Sub

Le temps d'exécution est nettement plus rapide mais toujours de quelques secondes...

Voici donc le but recherché, avec un temps d'exécution plus court.
Si l'utilisateur modifie la colonne "quantité" dans une des feuilles du classeur, la modification se fera automatiquement sur la colonne associée dans "bon de commande" etc..

N'hésitez pas à me poser des questions si je n'ai pas encore été assez clair, et merci pour votre aide.

~~La meilleurs utilisation ici serait un dictionnaire selon moi~~

EDIT :
Non en fait mieux tu créé un tableau avec les fonctionnalités d'excel tu auras pas besoin de à chaque fois remettre la formule suffit de la mettre dans la première cellule puis de redimensionner le tableau !!!! :mouarf:
Code:

1 2 3 4 5 6 7 8 9 10 '....Ton histoire d'entête (nom de colonne) .ListObjects.Add(xlSrcRange, Range("Ton Range entête"), , xlYes).Name = "Tableau1" .Cells(3, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C5" .Cells(3, j - 4).FormulaLocal = "=Prix!G5" .Cells(3, j - 3).FormulaLocal = "=Prix!H5" .Cells(3, j - 2).FormulaLocal = "=Prix!I5" .ListObjects("Tableau1").Resize Range("Le début de ton range:la colonne de fin et 1677")

Problème code

Merci beaucoup, je pense que ça peut fonctionner. Cependant, j'ai du mal à comprendre l'histoire de bornes...

Code:

1
2
3
4
5
6
7
8
    ListObjects.Add(xlSrcRange, Range(.Cells(2, j - 5), .Cells(2, j - 2)), , xlYes).Name = "Tableau1"
 
    .Cells(3, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C5"
    .Cells(3, j - 4).FormulaLocal = "=Prix!G5"
    .Cells(3, j - 3).FormulaLocal = "=Prix!H5"
    .Cells(3, j - 2).FormulaLocal = "=Prix!I5"
 
    ListObjects("Tableau1").Resize Range(.Cells(2, j - 5), .Cells(1667, j - 2))

J'ai pas compris ta question par contre tu as oublié les points devant listobject

Code:

1
2
3
4
5
6
7
8
    .ListObjects.Add(xlSrcRange, Range(.Cells(2, j - 5), .Cells(2, j - 2)), , xlYes).Name = "Tableau1"
 
    .Cells(3, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C5"
    .Cells(3, j - 4).FormulaLocal = "=Prix!G5"
    .Cells(3, j - 3).FormulaLocal = "=Prix!H5"
    .Cells(3, j - 2).FormulaLocal = "=Prix!I5"
 
    .ListObjects("Tableau1").Resize Range(.Cells(2, j - 5), .Cells(1667, j - 2))

20/07/2016, 15h24
barbeaua

MERCI!

ça fonctionne merci pour tout!
20/07/2016, 15h25
Al__22

Citation:

Envoyé par barbeaua

ça fonctionne merci pour tout!

Tout le plaisir est pour moi :mouarf: