XL 2016 Procédure très lente : boucle pour insérer des colonnes et des formules

[Daniel38]

Bonjour les Excelliens

La procédure est vraiment lente ...

Je cherche à insérer des colonnes et des formules dans des tableaux variables en nombre de ligne et colonnes
- l'insertion des colonnes (2 par entreprise) s'effectue dans une boucle sur chaque onglet (relativement rapide)
- les formules sont déterminées dans la feuille Couleur et ensuite fait un copié collé (je ne pense pas que ce soit la bonne solution)
- les formats des colonnes avec bordure s'effectue aussi avec une boucle (ça semble assez lent)

Y aurait il un moyen de rendre plus rapide l'exécution de cette procédure ? (je sais que c'est pas le top mais elle fonctionne et pendant que cela s'exécute on a le temps d'aller boire un café mais bon ...)

A bientôt

 

[Staple1600]

Re

C'est vrai qu'à choisir question temps, mieux vaut une procédure très lente que de prendre le même temps long pour compulser des exemples prêt à l'emploi (qui au mieux pourrait correspondre à ton corps de métier ou au pire te donner des pistes d'améliorations)

 

[Daniel38]

Oui voilà c'est un peu ce que je fais avec tes conseils
en fait cette procédure est acceptable en temps jusque 15 lots (1 minute) et quand c'est supérieur ça se met à ramer...
bizarre sous Excel 2007 et 2016 ...
va comprendre !!!

 

[Staple1600]

Re

Sous ma latitude personnelle et par choix politique, pour moi, une minute c'est parfait!
Et cela en plus permets ce petit bonus

 

[Daniel38]

Ahahahaha !!!
tu es excellent
alors tu acceptes les select ...
mais bon j'améliore aussi avec tes conseils qui me branchent bien

 

[Staple1600]

Re

La dernière sélection que j'ai accepté, c'était celle du Reader's Digest
Et là, je viens de m'auto-sélectionner pour aller rejoindre Morphée.

Bonne nuit.

 

[Daniel38]

Have a goodnight
petit film pour t'endormir, souhaitant que tu as peut-être connu ce pays

 

[Staple1600]

Bonjour le fil,

Daniel38
Toujours dans la perspective de :" Pas de Select dans VBA, même le week-end )
Ouvres un classeur vierge (dans lequel tu ne gardes qu'une feuille)
1) Lance la macro nommée MacroPreparation

Sub MacroPreparation()
'Cette macro ne doit être lancée qu'une seule fois
'Elle ne sert qu'à créer des feuilles et données de test sur un classeur vierge
Dim i&, vNoms
vNoms = Array("Menu", "Couleurs", "Lot 1", "Lot 2", "Lot 3", "Lot 4", "Lot 5")
For i = 0 To 6
Sheets.Add(After:=Sheets(i + 1)).Name = vNoms(i)
Next
With Sheets("Couleurs").Range("A1:F30")
  .Borders.LineStyle = 1: .Formula = "=ADDRESS(ROW(),COLUMN(),4)"
  .Font.ColorIndex = Asc("Staple1600") - 77: .Font.Bold = True
  .Interior.Color = RGB(255, 166, 79): .HorizontalAlignment = -4108: .VerticalAlignment = -4108
End With
End Sub

2) Supprimes la feuille que tu avais gardé au point 1)
(Il ne reste plus alors que les feuilles: Menu, Couleurs, Lot 1, Lot 2, Lot 3, Lot 4 et Lot 5)
3) Lance alors la macro ci-dessous

Sub PetitTest()
'Déclarations variables
Dim ws As Worksheet, rngSource As Range, vArr, i&
'Définition du modéle (plage sur la feuille Couleurs)
Set rngSource = Sheets("Couleurs").Range("A1").CurrentRegion
'création de la liste des feuilles (Menu exclue) dans le tableau vArr (tableau <-> Array)
With CreateObject("System.Collections.ArrayList")
  For Each ws In Worksheets
  If InStr("Menu", ws.Name) = 0 Then .Add ws.Name
  Next
  vArr = .toArray
End With
On Error Resume Next ' au cas où
'recopie du modèle (formule et format inclus) sur toutes les feuilles présentes dans vArr
Worksheets(vArr).FillAcrossSheets rngSource
End Sub

Tu constates alors que les formules, les formats de la feuille Couleurs ont bien été recopiées sur les feuilles Lot
(mais pas sur la feuille Menu)
(sans que dans le code VBA on utilise Select ou Activate)

NB: Ce ne sont là que des macros illustratives
(pas des macros à utiliser dans ton classeur original)

PS: Sauf erreur de ma part, dans ton dernier message pas vu de lien vers un film.

 

[Daniel38]

Hello Staple
OK je viens de faire une feuille avec les 2 Sub ci-avant
dans le 1er module, tu utilises une boucle pour ajouter feuille par feuille je fais la même chose mais avec un select encore car il faut que je récupère le nom de la feuille ce qui permettra de sélectionner la feuille plus tard pour copier les formules ...
For O = 1 To Sheets.Count
If Sheets(O).Name = "Données" Or Sheets(O).Name = "Menu" Or Sheets(O).Name = "Couleurs" Then
Else
ActiveCell.Value = Sheets(O).Name
Application.StatusBar = "Veuillez patienter, copie des feuilles : " & Sheets(O).Name & " ... " & CInt((O / Max) * 100) & "%"
ActiveCell.Offset(1, 0).Select
End If
Next O

dans le 2 ème je vois que les boucles for to sont remplacées par for each mais comment faire pour insérer les colonnes au bon endroit avant dans le tableau de base avant de copier ou écrire les formules ?
en fait j'en suis là
la 1ere colonne à insérer se situe juste après la colonne PU (pour la formule Ecart %/PU)
la 2ème colonne se situe juste après la colonne Total en € (pour la formule Ecart Montant / Px Tot)
les 2 colonnes de référence pour ce formules sont E et F

Merci pour cet exemple qui m'inspire bien
Je te remets le lien du film

Bon ap

 

[Staple1600]

Re

Re

Si j'étais moi, et si je devais triturer ton fichier, je changerai mon fusil d'épaule.
C'est à dire que je reverrai complètement la structure de mon fichier.

Ceci explique pourquoi je me borne à verser dans l'illustratif.
Je suis réfractaire à cette insertion de colonne
(Ça sent l'usine à gaz (sans vouloir te heurter) )

PS: Toujours pas vu de lien vers le film.

 

[Daniel38]

Staple

Oui clair c'est un peu usine à gaz mais ces colonnes doivent être insérées dans ces positions sinon ce serait illisible.
D'autant plus que les 4 dernières colonnes sont occupées par d'autres formules (Min,Moyenne, Mediane, Max) ...

Avec tout ça + une page de synthèse qui sépare les lots et les options et tout le traitement prend moins d'1 minute environ pour 15 lots ensuite ça varie jusqu'à 4 minutes maximum avec 25 lots et moins de 100 offres (ça dépend aussi du nb de ligne par lot) ...

J'ai cré" un fichier avec 50 lots et 500 offres traitement total en 28 minutes (oops !!!)

j'ai mis plein de messages dans la barre des taches, pour faire visualiser l'avancement du traitement, j'aurai bien aimé mettre une barre de progression mais impossible de le faire sur plusieurs sub à la suite du moins je n'ai pas tout compris aussi ...

Enfin bref 1 à 28 minutes de traitement au lieu de 1 à 5 jours à la main je suis preneur ...

Prochaine étape les graphiques dans la synthèse

Lien du film ici -->

peut-être que le serveur censure ?
J'ai essayé de t'envoyer le lien en MP mais visiblement tu acceptes pas les conversations !

Merci encore pour tes conseils et bon film j'espère

 

[Staple1600]

Re

Nul besoin de stocker le nom des feuilles sur une feuille
(ici autre syntaxe que celle message#22 pour remplir un array avec le noms des feuilles Lot*)

Sub NomsFeuilles()
Dim ws As Worksheet, chaine$, tabFeuilles() As String, i
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Lot N°*" Then
ReDim Preserve tabFeuilles(i)
tabFeuilles(i) = ws.Name
i = i + 1
End If
Next ws
'///////////////////// TEST  ///////////////////////////////////////////////////////////////////
'ci dessous lignes uniquement pour test
MsgBox tabFeuilles(0) '<- 1ère feuille dont le nom commence par Lot
MsgBox tabFeuilles(1) '<- 2ième feuille dont le nom commence par Lot
MsgBox "Nombre de feuilles Lot: " & Chr(13) & UBound(tabFeuilles) + 1, vbInformation, "Test"
chaine = Join(tabFeuilles, Chr(10))
MsgBox chaine
'///////////////////// TEST  ///////////////////////////////////////////////////////////////////
End Sub

PS: OK pour le lien du film

 

[Daniel38]

OK vu
sympa comme procédure mais je construits une liste d'onglets dans le menu clic droit de l'appli.
donc je pense avoir besoin d'écrire et de nommer cette liste de feuille
A moins que ?

 

[Staple1600]

Re

sympa comme procédure mais je construits une liste d'onglets dans le menu clic droit de l'appli.
donc je pense avoir besoin d'écrire et de nommer cette liste de feuille

Oui, ça j'avais bien vu
Mais comme je viens de l'écrire c'est complément inutile
Et comme il semble inutile de tenter de te convaincre de la chose, je te laisse développer ton usine à gaz à ta guise
Bonne fin de week-end

 

[Daniel38]

Lol OK merci
j'ai quand même bien simplifié grâce à toi
@+ Staple

 

[Staple1600]

Re

[aparté]
J'ai du mal à comprendre la démarche qui consiste à demander aide et assistance sur un forum
et suivre son fil directeur (sans prendre en compte les suggestions et conseils donnés)

[/aparté]

PS: Tu n'as pas vu que tu avais un MP?