Accélération code

Le 29/11/2017 à 20:25

Bonjour à tous, j'ai fait le code ci dessous et je voudrais savoir si cette méthode est correct niveau optimisation de la vitesse d'éxécution ou une autre méthode plus rapide existe ?

For i = 4 To 53 Step 13

        Range(Cells(i, n + 1), Cells(i, n + 1)).Formula = (Cells((i - 1), 7) + Cells((i - 1), n) - Cells((i - 1), n - 1)) / ((Cells((i + 10), 4) + Cells((i + 10), n) - Cells((i + 10), n - 1)))
Next i

Merci à vous.

Le 29/11/2017 à 20:54

Salut,

Que vaut ton n dans ton code? à quoi sert exactement ta boucle (il y a peut être plus simple si on comprends le tout)?

Pourquoi veux-tu optimiser la vitesse? cela te prends combien de temps à exécuter cette boucle?

ECG

Le 29/11/2017 à 21:08

Merci de ta réponse j'ai réalisé un fichier excel qui comporte énormément de macro, et la vitesse d'éxécution de l'ensemble des macros est de 1 minutes et 15 secondes.

J'essaye de gratter un peu partout, en enlevant des "select" et en re structurant certain code dont celui ci afin de gagner quelques secondes par ci par là.

Voici mon code en entier:

Private Sub MG12()
Dim i As Integer
Dim j&, jj&

With Sheets("Cout")

'Création du 12Mg, ce code repère si il y a 2 "YTD" auquel cas il ajoute une colonne après 
For n = Cells(1, Columns.Count).End(xlToLeft).Column To 3 Step -1
   If Left(Cells(1, n), 10) Like "YTD *" And Left(Cells(1, n - 1), 10) Like "YTD *" And Left(Cells(1, n), 11) = Left(Cells(1, n - 1), 11) Then
        Columns(n + 1).Insert Shift:=xlShiftToRight

        jj = Cells.Find("*", , , , xlByColumns, xlPrevious).Column 'Dernière colonne
    For j = 3 To jj
    If Cells(1, j).Value = "Réel " & Right(Cells(1, n - 1), 4) Then Exit For
    Next

'Écriture 12Mg en entête de la colonne insérée
        Cells(1, n + 1).Value = "12mg " & Right(Cells(1, n), 7)

'Calcul des cellules de la colonne 12Mg
For i = 2 To 53
        Range(Cells(i, n + 1), Cells(i, n + 1)).Formula = Cells(i, j) + Cells(i, n) - Cells(i, n - 1)
Next i

' Calcul du critete % ASS de la colonne 12Mg
For i = 4 To 53 Step 13
        Range(Cells(i, n + 1), Cells(i, n + 1)).Formula = (Cells((i - 1), 7) + Cells((i - 1), n) - Cells((i - 1), n - 1)) / ((Cells((i + 10), 4) + Cells((i + 10), n) - Cells((i + 10), n - 1)))
Next i

' Calcul du critere % E/R de la colonne 12Mg
For i = 8 To 53 Step 13
        Range(Cells(i, n + 1), Cells(i, n + 1)).Formula = (Cells((i - 3), 7) + Cells((i - 3), n) - Cells((i - 3), n - 1)) / ((Cells((i + 6), 4) + Cells((i + 6), n) - Cells((i + 6), n - 1)))
Next i

   End If
Next
End With

End Sub
Le 30/11/2017 à 23:18

Question bête, je vois des formulas, ce n'est pas possible de calculer directement ou tu veux forcément que cela soit "dynamique"?

Sinon tu remplies que la première ligne et tu fais un

Selection.AutoFill Destination:=Range("XX:XX")

, cela sera plus rapide que de faire une par une les case surement

Le plus simple sinon pour un gain de temps est de passer par des tableaux surement, à tester ^^

ECG

Le 30/11/2017 à 23:29

Bonsoir,

Pour commencer mettre un bloc With, c'est bien, l'utiliser c'est mieux, sinon c'est tout à fait inutile et des risques d'erreurs demeurent.

Toutes les expressions Range ou Cells ou Columns ou Rows représentant des plages ou cellules de la feuille mise sous bloc With doivent être précédées d'un point. C'est cela qui les raccorde à la feuille.

Le 01/12/2017 à 00:37

Ton code, juste réécrit (et indenté).

Private Sub MG12()
    Dim i%, n%, j&, jj&
    With Sheets("Cout")
    'Création du 12Mg, ce code repère si il y a 2 "YTD" auquel cas il ajoute une colonne après
        jj = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For n = jj To 3 Step -1
            If Left(.Cells(1, n), 10) Like "YTD*" And Left(.Cells(1, n - 1), 10) Like "YTD*" _
             And Left(.Cells(1, n), 11) = Left(.Cells(1, n - 1), 11) Then
                .Columns(n + 1).Insert: jj = jj + 1
                For j = 3 To jj
                    If .Cells(1, j).Value = "Réel " & Right(.Cells(1, n - 1), 4) Then Exit For
                Next j
                'Écriture 12Mg en entête de la colonne insérée
                .Cells(1, n + 1).Value = "12mg " & Right(.Cells(1, n), 7)
                'Calcul des cellules de la colonne 12Mg
                For i = 2 To 53
                    .Cells(i, n + 1) = .Cells(i, j) + .Cells(i, n) - .Cells(i, n - 1)
                Next i
                'Calcul du critete % ASS de la colonne 12Mg
                For i = 4 To 53 Step 13
                    .Cells(i, n + 1) = (.Cells(i - 1, 7) + .Cells(i - 1, n) - .Cells(i - 1, n - 1)) / _
                     (.Cells(i + 10, 4) + .Cells(i + 10, n) - .Cells(i + 10, n - 1))
                Next i
                'Calcul du critere % E/R de la colonne 12Mg
                For i = 8 To 53 Step 13
                    .Cells(i, n + 1) = (.Cells(i - 3, 7) + .Cells(i - 3, n) - .Cells(i - 3, n - 1)) / _
                     (.Cells(i + 6, 4) + .Cells(i + 6, n) - .Cells(i + 6, n - 1))
                Next i
            End If
        Next n
    End With
End Sub

Ça allège le texte côté parenthèses, et expressions hyper-redondantes style : Range(Cells(i, n), Cells(i, n)).Formula

Tu calcules la valeur à affecter à une seule cellule, donc : .Cells(i, n) suffit ! Et réserve Formula et ses variantes pour insérer ou lire une formule. Dans le cas général on peut toujours se contenter de Value, et le plus souvent l'omettre car c'est la propriété par défaut de l'objet Range.

Maintenant, si on a allégé un petit peu le texte, ce n'est pas ça qui va optimiser la procédure... Je ne pense pas qu'on aura un résultat tangible tant qu'on laisse tes 3 ou 4 boucles à l'intérieur de la boucle initiale. Je ne vois pas a-priori comment faire tes insertions autrement, il me semble donc qu'il faudrait d'abord les faire en bloc, et reprendre les calculs ensuite, en essayant d'opérer au maximum sur tableaux. Je n'ai pas plongé dans le détail des calculs que tu opères, car sans fichier, c'est plutôt pénible. Il faudra aussi essayer de voir si on peut trouver quelques raccourcis de nature le nombre global d'opérations élémentaires...

Je n'ai pas compris ce fragment :

                For j = 3 To jj
                    If .Cells(1, j).Value = "Réel " & Right(.Cells(1, n - 1), 4) Then Exit For
                Next j

On parcourt les colonnes, on sort éventuellement avant la fin, mais on ne fait strictement rien, et ce n'est même pas pour réutiliser la valeur de j après sortie de la boucle... Il s'agit donc d'un fragment de code totalement inutile, parasite (puisqu'il s'exécute pour rien), et dont on ne voit pas la raison. Si une raison existait dans les intentions, elle n'est pas menée à bien (l'exécution est sans aucun effet), et ce qui était prévu manquera peut-être ensuite...

Cordialement.

Le 01/12/2017 à 13:33

Bonjour et merci à vous pour votre aide,

MFerrand : Merci pour l’allègement du code, pour ce qui est de la valeur J, en fait elle me sert à la ligne ci dessous :

For i = 2 To 53
                    .Cells(i, n + 1) = .Cells(i, j) + .Cells(i, n) - .Cells(i, n - 1)
                Next i

je récupéré le numéro de la colonne ou ce situe la valeur ""Réel " & Right(.Cells(1, n - 1), 4)" .

Encore merci à vous

Le 01/12/2017 à 13:52

Ok ! La réutilisation de j m'avait échappée...

Rechercher des sujets similaires à "acceleration code"