[RESOLU] Optimisation d'une compilation de données en VBA

[Jenovae]

Bonjour à tous,

Je cherche actuellement à optimiser le code suivant, afin de gagner du temps. En effet pour executer ce code, Excel met (sur mon ordinateur) environ 10sec. Cependant ce code pouvant être répété de 1 à 7 fois, on peut parfois poireauter durant une bonne grosse minute devant son écran pendant l'execution.

Ci dessous le code en question :

    Sheets("Données Cout Total").Range("B4").Value = .Cells(14, Col - 3).Value + .Cells(14, Col - 2).Value + .Cells(14, Col - 1).Value
    Sheets("Données Cout Total").Range("B5").Value = .Cells(15, Col - 3).Value + .Cells(15, Col - 2).Value + .Cells(15, Col - 1).Value
    Sheets("Données Cout Total").Range("B6").Value = .Cells(16, Col - 3).Value + .Cells(16, Col - 2).Value + .Cells(16, Col - 1).Value
    Sheets("Données Cout Total").Range("B7").Value = .Cells(17, Col - 3).Value + .Cells(17, Col - 2).Value + .Cells(17, Col - 1).Value
    Sheets("Données Cout Total").Range("B8").Value = .Cells(18, Col - 3).Value + .Cells(18, Col - 2).Value + .Cells(18, Col - 1).Value
    Sheets("Données Cout Total").Range("B9").Value = .Cells(20, Col - 3).Value + .Cells(20, Col - 2).Value + .Cells(20, Col - 1).Value
    Sheets("Données Cout Total").Range("B10").Value = .Cells(21, Col - 3).Value + .Cells(21, Col - 2).Value + .Cells(21, Col - 1).Value
    Sheets("Données Cout Total").Range("B11").Value = .Cells(22, Col - 3).Value + .Cells(22, Col - 2).Value + .Cells(22, Col - 1).Value
    Sheets("Données Cout Total").Range("B12").Value = .Cells(23, Col - 3).Value + .Cells(23, Col - 2).Value + .Cells(23, Col - 1).Value
    Sheets("Données Cout Total").Range("B13").Value = .Cells(24, Col - 3).Value + .Cells(24, Col - 2).Value + .Cells(24, Col - 1).Value
    Sheets("Données Cout Total").Range("B14").Value = .Cells(25, Col - 3).Value + .Cells(25, Col - 2).Value + .Cells(25, Col - 1).Value
    Sheets("Données Cout Total").Range("B15").Value = .Cells(26, Col - 3).Value + .Cells(26, Col - 2).Value + .Cells(26, Col - 1).Value
    Sheets("Données Cout Total").Range("B16").Value = .Cells(27, Col - 3).Value + .Cells(27, Col - 2).Value + .Cells(27, Col - 1).Value
    Sheets("Données Cout Total").Range("B17").Value = .Cells(28, Col - 3).Value + .Cells(28, Col - 2).Value + .Cells(28, Col - 1).Value
    Sheets("Données Cout Total").Range("B18").Value = .Cells(29, Col - 3).Value + .Cells(29, Col - 2).Value + .Cells(29, Col - 1).Value
    Sheets("Données Cout Total").Range("B21").Value = .Cells(33, Col - 3).Value + .Cells(33, Col - 2).Value + .Cells(33, Col - 1).Value
    Sheets("Données Cout Total").Range("B22").Value = .Cells(34, Col - 3).Value + .Cells(34, Col - 2).Value + .Cells(34, Col - 1).Value
    Sheets("Données Cout Total").Range("B23").Value = .Cells(35, Col - 3).Value + .Cells(35, Col - 2).Value + .Cells(35, Col - 1).Value
    Sheets("Données Cout Total").Range("B25").Value = .Cells(38, Col - 3).Value + .Cells(38, Col - 2).Value + .Cells(38, Col - 1).Value
    Sheets("Données Cout Total").Range("B26").Value = .Cells(39, Col - 3).Value + .Cells(39, Col - 2).Value + .Cells(39, Col - 1).Value
    Sheets("Données Cout Total").Range("B27").Value = .Cells(40, Col - 3).Value + .Cells(40, Col - 2).Value + .Cells(40, Col - 1).Value
    Sheets("Données Cout Total").Range("B28").Value = .Cells(41, Col - 3).Value + .Cells(41, Col - 2).Value + .Cells(41, Col - 1).Value
    Sheets("Données Cout Total").Range("B29").Value = .Cells(42, Col - 3).Value + .Cells(42, Col - 2).Value + .Cells(42, Col - 1).Value
    Sheets("Données Cout Total").Range("B30").Value = .Cells(43, Col - 3).Value + .Cells(43, Col - 2).Value + .Cells(43, Col - 1).Value
    Sheets("Données Cout Total").Range("B31").Value = .Cells(44, Col - 3).Value + .Cells(44, Col - 2).Value + .Cells(44, Col - 1).Value
    Sheets("Données Cout Total").Range("B33").Value = .Cells(47, Col - 3).Value + .Cells(47, Col - 2).Value + .Cells(47, Col - 1).Value
    Sheets("Données Cout Total").Range("B34").Value = .Cells(48, Col - 3).Value + .Cells(48, Col - 2).Value + .Cells(48, Col - 1).Value
    Sheets("Données Cout Total").Range("B36").Value = .Cells(51, Col - 3).Value + .Cells(51, Col - 2).Value + .Cells(51, Col - 1).Value
    Sheets("Données Cout Total").Range("B39").Value = .Cells(54, Col - 3).Value + .Cells(54, Col - 2).Value + .Cells(54, Col - 1).Value
    Sheets("Données Cout Total").Range("B40").Value = .Cells(55, Col - 3).Value + .Cells(55, Col - 2).Value + .Cells(55, Col - 1).Value
    Sheets("Données Cout Total").Range("B41").Value = .Cells(56, Col - 3).Value + .Cells(56, Col - 2).Value + .Cells(56, Col - 1).Value
    Sheets("Données Cout Total").Range("B42").Value = .Cells(57, Col - 3).Value + .Cells(57, Col - 2).Value + .Cells(57, Col - 1).Value
    Sheets("Données Cout Total").Range("B44").Value = .Cells(59, Col - 3).Value + .Cells(59, Col - 2).Value + .Cells(59, Col - 1).Value
    Sheets("Données Cout Total").Range("B45").Value = .Cells(60, Col - 3).Value + .Cells(60, Col - 2).Value + .Cells(60, Col - 1).Value
    Sheets("Données Cout Total").Range("B47").Value = .Cells(62, Col - 3).Value + .Cells(62, Col - 2).Value + .Cells(62, Col - 1).Value
    Sheets("Données Cout Total").Range("B49").Value = .Cells(64, Col - 3).Value + .Cells(64, Col - 2).Value + .Cells(64, Col - 1).Value
    Sheets("Données Cout Total").Range("B50").Value = .Cells(65, Col - 3).Value + .Cells(65, Col - 2).Value + .Cells(65, Col - 1).Value
    Sheets("Données Cout Total").Range("B51").Value = .Cells(66, Col - 3).Value + .Cells(66, Col - 2).Value + .Cells(66, Col - 1).Value

Ici, une variante (fais exactement la même chose), mais qui n'a pas montré ses preuves questions gain de temps...

    For i = 4 To 52
        If (i = 19) Or (i = 20) Or (i = 24) Or (i = 32) Or (i = 35) Or (i = 37) Or (i = 38) Or (i = 43) Or (i = 46) Or (i = 48) Then
        ElseIf i < 9 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 10, Col).Value + .Cells(i + 10, Col + 1).Value + .Cells(i + 10, Col + 2).Value
        ElseIf i < 19 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 11, Col).Value + .Cells(i + 11, Col + 1).Value + .Cells(i + 11, Col + 2).Value
        ElseIf i < 24 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 12, Col).Value + .Cells(i + 12, Col + 1).Value + .Cells(i + 12, Col + 2).Value
        ElseIf i < 32 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 13, Col).Value + .Cells(i + 13, Col + 1).Value + .Cells(i + 13, Col + 2).Value
        ElseIf i < 35 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 14, Col).Value + .Cells(i + 14, Col + 1).Value + .Cells(i + 14, Col + 2).Value
        ElseIf i < 37 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 15, Col).Value + .Cells(i + 15, Col + 1).Value + .Cells(i + 15, Col + 2).Value
        ElseIf i < 43 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 15, Col).Value + .Cells(i + 15, Col + 1).Value + .Cells(i + 15, Col + 2).Value
        ElseIf i < 46 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 15, Col).Value + .Cells(i + 15, Col + 1).Value + .Cells(i + 15, Col + 2).Value
        ElseIf i < 48 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 15, Col).Value + .Cells(i + 15, Col + 1).Value + .Cells(i + 15, Col + 2).Value
        ElseIf i < 52 Then
            Sheets("Données Cout Total").Range("B" & i).Value = .Cells(i + 15, Col).Value + .Cells(i + 15, Col + 1).Value + .Cells(i + 15, Col + 2).Value
        End If
    Next i

Voili voilou donc je cherche à optimiser ça mais je ne vois pas vraiment comment.

Pour information j'ai donc un tableau rempli par un utilisateur qui contient des coûts travaux (1 type de travaux par ligne Ex : Gros oeuvre, plâtrerie...) identiques pour différents "morceaux" d'un immeuble (1 "morceau" = Cage A, Cage B, infrastructure...) et chaque morceau d'immeuble étant ensuite subdivisé en 3 domaines : Logements, Commerces, Bureaux.
Le code plus haut doit me permettre en cliquant juste sur un petit bouton, de créer sur une autre feuille une colonne récapitulative du coût total pour chaque morceaux (donc en additionnant les 3 colonnes de "infrastructure", puis en se décalant et en additionnant les 3 colonnes de "Cage A" ... par exemple).
Une petite illustration est fournie pour mieux comprendre.


De mon côté je fais une petite pause et je reprendrai ma réflexion sur le sujet demain matin.
Je remercie d'avance tout âme charitable qui aura quelques minutes pour me donner une idée ou une astuce

Jenovae.

PS : Le code s'affiche de façon assez moche... J'en suis navré.

 

[Staple1600]

Re : Optimisation d'une compilation de données en VBA

Bonjour à tous

Jenovae
Le fichier exemple attends que tu le postes dans ce fil, camarade
(Comme la charte du forum te le conseille entre autres judicieux conseils)

Les images c'est joli, surtout avec des chatons ou des fleurs mais on peut pas tester de code VBA dessus
alors qu'un petit fichier exemple mitonné aux petits oignons par le demandeur, piaffe d’impatience qu'on lui colle dans le bide du code VBA à tire-larigot.

Donc camarade, à ton clavier, à ton Excel, et nous on attends ici dans ce fil ton fichier Excel anonymisé bien sur et zippé pourquoi pas.

 

[Robert]

Re : Optimisation d'une compilation de données en VBA

Bonsoir Jenovae, JM, bonsoir le forum,

Oui je suis d'accord avec Staple, c'est bien plus facile avec un fichier exemple...
Peut-être comme ça :

Dim x As Byte

For x = 4 To 51
    Sheets("Données Cout Total").Cells(x, 2).Value = .Cells(x + 10, Col - 3).Value + .Cells(x + 10, Col - 2).Value + .Cells(x + 10, Col - 1).Value
Next x

 

[Jenovae]

Re : Optimisation d'une compilation de données en VBA

Bonjour à tous,

En déplacement hier toute la journée je ne vous lis qu'aujourd'hui.
Je vous concocte un petit fichier exemple alors, et vous le poste le plus vite possible.

@Robert :
La subtilité est que le code ne se déroule pas simplement de la ligne 4 à 51. Il saute certaines lignes aussi bien sur la feuille qui récupère les données que dans la feuille où il les prend.

Bien à vous,
Jenovae.

 

[Jenovae]

Re : Optimisation d'une compilation de données en VBA

Re-Bonjour à tous,

En vous créant mon fichier d'exemple (que j'ai du simplifier pour éviter de divulguer des données confidentielles), je me suis aperçu de plusieurs redondances dans mon code initial. Du coup en corrigeant ça l'éxecution du code ci dessus se fait une quelques centièmes de secondes maintenant

Je ne vais donc pas vous embêter plus longtemps avec ça et je remercie Robert et Staple de m'avoir réclamé ce ficher d'exemple.

Au cas où certains souhaiteraient quand même essayer de rendre ce morceau de code plus propre, voici le fichier que j'avais concocté.

Merci à vous!
Jenovae.

EDIT : Je passe la discussion en "résolu" en fin de journée, histoire que les éventuels curieux passent y jeter un oeil en attendant.

 

[Staple1600]

Re : Optimisation d'une compilation de données en VBA

Bonsoir à tous

Jenovae
Merci pour le feedback et le partage.

Effectivement ta PJ servira peut-être à quelqu'un un jour, tu as donc bien fait de revenir poster ton exemple final ici.