Accélérer un code

Wylsonn · 17 Avril 2009

Bonjour,

J'ai un souci avec le code ci-dessous : avec 1000 répétitions de la boucle principale ("AM10=1000"), j'en ai approximativement pour 24 heures à faire tourner la machine (Intel Core 2 duo à 2,2 GHz - Windows XP sur MacBook Pro) !! et je dois faire ça sur une vingtaine de feuilles...

A l'expérience, avez-vous une ou des idées pour gagner du temps de traitement ? quelques fractions de seconde par calcul feront au bout quelques heures en moins... 😱

Sub BBD()

Workbooks("ABONNEMENT.xls").Sheets("Calcul").Activate

Dim A As Currency, B As Currency, C As Currency, D As Integer, E As Integer, F As Integer

For F = 0 To 8 * Range("AM10").Value Step 8

Range("T248").End(xlDown).Select
If Range("T248") = Cells(xlCellTypeBlanks) Then E = 248 Else E = 1 +
Selection.Row - 8
D = 20

Cells(E + F, D).Select
Range("Pour").Value = Cells(E + F, D - 3).Value
Range("cumul1").Value = Cells(E + F, D - 2).Value
Range("retard").Value = Cells(E + F, D - 1).Value
A = -0.8
B = -0.15
C = -0.12

For C = -0.12 To 0.15 Step 0.03
If Range("Seuil").Value > 0.76 Then Range("Seuil").Value = -0.15
Range("Seuil").Value = C

For B = -0.15 To 0.76 Step 0.1
If Range("version").Value > 0.76 Then Range("version").Value =
-0.15
Range("version").Value = B

For A = -0.8 To 0.6 Step 0.2
If Range("cumul2").Value > 0.6 Then Range("cumul2").Value = -0.8
Range("cumul2").Value = A
Cells(E + F, D).Select
Range("H2").Copy
Selection.PasteSpecial Paste:=xlPasteValues
E = E + 1
Next A

E = E - 8
D = D + 1

Next B

Next C

Next F

End Sub

Le résultat génère le tableau joint comme exemple, mais répété près d'un millier de fois. Comme chaque résultat est le produit du traitement d'une base de données, cela entraîne un processus lent (un bloc de 8 lignes est généré en à peu près 1'30").

Tout gain de temps, si minime soit-il, est le bienvenu !!🙂

ROGER2327 · 18 Avril 2009

Re : Accélérer un code

Bonsoir Wylsonn

(...) comment peuvent varier mes variables A, B et C dans le code que tu proposes ? (...)

Ces variables sont des indices de boucle. Dans votre code elles ne servent qu'à sélectionner des cellules. Par exemple (je prends comme référence la procédure BBD2 de cette discussion) lorsque vous écrivez

Code:

               For A = -0.8 To 0.6 Step 0.2
                  .Cells(E + F, D).Value = .Range("H2").Value
                  E = E + 1
               Next A

vous sélectionnez la cellule Cells(E + F, D) et vous lui affecter la valeur contenue en H2. Comme à chaque boucle E augmente de 1 et que D ne change pas, vous recopiez la valeur de H2 dans la même colonne de la ligne suivante. Puisque vous répétez cela pour A = -0.8 To 0.6 avec un pas de 0.2, vous répétez la boucle 8 fois. Puisque c'est toujours la même valeur qui est placée dans la cellule sélectionnée, il est inutile de faire une boucle. Autant remplacer la boucle par

Code:

.Range(.Cells(E + F, D), .Cells(E + F + 7, D)).Value = .Range("H2").Value

qui placera d'un seul coup la valeur de H2 dans les cellules .Cells(E + F, D), .Cells(E +1 + F, D), .Cells(E + 2 + F, D), .Cells(E + 3 + F, D), .Cells(E + 4 + F, D), .Cells(E + 5 + F, D), .Cells(E + 6 + F, D), .Cells(E + 7 + F, D).
D'où la disparition de votre variable A.
Comme vous n'avez pas modifié E, il n'est plus utile d'écrire E = E - 8, et le code devient

Code:

Sub BBD3()
Dim A As Currency, B As Currency, C As Currency, D As Integer, E As Integer, F As Integer
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   With Workbooks("ABONNEMENT.xls").Sheets("Calcul")
      For F = 0 To 8 * .Range("AM10").Value Step 8
         If .Range("T248") = .Cells(xlCellTypeBlanks) Then E = 248 Else E = 1 + .Range("T248").End(xlDown).Row - 8
         D = 20
         For C = -0.12 To 0.15 Step 0.03
            For B = -0.15 To 0.76 Step 0.1
               .Range(.Cells(E + F, D), .Cells(E + F + 7, D)).Value = .Range("H2").Value
               D = D + 1
            Next B
         Next C
      Next F
   End With
   Application.Calculation = xlCalculationAutomatic
End Sub

Je vous laisse le soin de voir que la boucle commandée par For B = -0.15 To 0.76 Step 0.1 est exécutée 10 fois et qu'elle recopie les cellules Range(.Cells(E + F, D), .Cells(E + F + 7, D)) dans les colonnes suivantes. Elle peut donc être supprimée est remplacée par .Range(.Cells(E + F, D), .Cells(E + F + 7, D + 9)).Value = .Range("H2").Value. Continuez le raisonnement pour voir que la boucle commandée par For C = -0.12 To 0.15 Step 0.03 fait la même chose et tirez-en les conséquences.
On en arrive au code que je vous proposais dans mon précédent message. Si vous le testez, vous verrez que vous ne gagnerez pas quelques misérables %, mais que le code s'exécute environ 100 fois plus vite que BBD2.
Voilà le pourquoi du comment...
Programmer, ce n'est pas essentiellement taper sur un clavier. C'est surtout, avant de commencer à taper, prendre papier/crayon/méninges et transpirer sur sa feuille de papier. C'est rarement du temps perdu. Ici, c'est même beaucoup de temps gagné.
Sur ce, bonne nuit !

Cordialement,
ROGER2327

Wylsonn · 18 Avril 2009

Re : Accélérer un code

Merci Roger pour votre explication. Mais A, B et C transmettent des valeurs à des cellules nommées hors tableau (par l'instruction -Range("Seuil").Value = C - p.ex.). Ces cellules paramètrent une base de donnée externe, qui elle-même renvoie un résultat en H2, celui-ci étant repris dans le tableau. C'est pour cela que j'ai utilisé des incrémentations un peu particulière (0,03 - 0,1 - 0,2), selon les besoins de paramétrage de la base de données.

Il m'est donc difficile d'en faire abstraction, sinon tous mes H2 seraient semblables.

Bien cordialement,

ROGER2327 · 19 Avril 2009

Re : Accélérer un code

Re...

Wylsonn à dit:
Merci Roger pour votre explication. Mais A, B et C transmettent des valeurs à des cellules nommées hors tableau (par l'instruction -Range("Seuil").Value = C - p.ex.). Ces cellules paramètrent une base de donnée externe, qui elle-même renvoie un résultat en H2, celui-ci étant repris dans le tableau. C'est pour cela que j'ai utilisé des incrémentations un peu particulière (0,03 - 0,1 - 0,2), selon les besoins de paramétrage de la base de données.

Il m'est donc difficile d'en faire abstraction, sinon tous mes H2 seraient semblables.

Bien cordialement,

Autrement dit, le classeur d'exemple que vous avez fourni initialement ne correspond pas à ce que vous faites : aucune plage nommée, par exemple. S'il y en avait eu, je n'aurais évidemment pas viré les lignes y faisant référence. J'ai bien eu un doute à ce sujet, d'où un précédent message :

ROGER2327 à dit:

Bonsoir à tous

Ce code contient beaucoup de choses inutiles :

Code:

Sub BBD()
Dim A As Currency, B As Currency, C As Currency, D As Integer, E As Integer, F As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Workbooks("ABONNEMENT.xls").Sheets("Calcul")
    For F = 0 To 8 * .Range("AM10").Value Step 8
        If .Range("T248") = .Cells(xlCellTypeBlanks) Then E = 248 Else E = 1 + .Range("T248").End(xlDown).Row - 8
        D = 20
        [U].Range("Pour").Value = .Cells(E + F, D - 3).Value[/U] [COLOR="SeaGreen"]'INUTILE / "Pour" n'est employé nulle part[/COLOR]
        [U].Range("cumul1").Value = .Cells(E + F, D - 2).Value[/U] [COLOR="SeaGreen"]'INUTILE / "cumul1" n'est employé nulle part[/COLOR]
        [U].Range("retard").Value = .Cells(E + F, D - 1).Value[/U] [COLOR="SeaGreen"]'INUTILE / "retard" n'est employé nulle part'[/COLOR]***
        [U]A = -0.8[/U] [COLOR="SeaGreen"]'INUTILE / A sera initialisé ligne *[/COLOR]
        [U]B = -0.15[/U] [COLOR="SeaGreen"]'INUTILE / B sera initialisé ligne **[/COLOR]
        [U]C = -0.12[/U] [COLOR="SeaGreen"]'INUTILE / C sera initialisé ligne ***[/COLOR]
        For C = -0.12 To 0.15 Step 0.03 [COLOR="SeaGreen"]'***[/COLOR]
            [U]If .Range("Seuil").Value > 0.76 Then .Range("Seuil").Value = -0.15[/U] [COLOR="SeaGreen"]'INUTILE / Voir ligne suivante[/COLOR]
            [U].Range("Seuil").Value = C[/U] [COLOR="SeaGreen"]'INUTILE / "Seuil1" n'est employé nulle part[/COLOR]
            For B = -0.15 To 0.76 Step 0.1 [COLOR="SeaGreen"]'**[/COLOR]
                [U]If .Range("version").Value > 0.76 Then .Range("version").Value = -0.15[/U][COLOR="SeaGreen"] 'INUTILE / Voir ligne suivante[/COLOR]
                [U].Range("version").Value = B[/U] [COLOR="SeaGreen"]'INUTILE / "version" n'est employé nulle part[/COLOR]
                For A = -0.8 To 0.6 Step 0.2 [COLOR="SeaGreen"]'*[/COLOR]
                    [U]If .Range("cumul2").Value > 0.6 Then .Range("cumul2").Value = -0.8[/U] [COLOR="SeaGreen"]'INUTILE / Voir ligne suivante[/COLOR]
                    [U].Range("cumul2").Value = A[/U] [COLOR="SeaGreen"]'INUTILE / "cumul2" n'est employé nulle part[/COLOR]
                    .Cells(E + F, D).Value = .Range("H2").Value
                    E = E + 1
                Next A
                E = E - 8
                D = D + 1
            Next B
        Next C
    Next F
End With
Application.Calculation = xlCalculationAutomatic
End Sub

(...)
...et une question : à quoi cela sert-il ?

Bonne nuit !
ROGER2327

Or vous m'avez répondu :

effectivement, la définition des valeurs pour A, B, C est inutile, ainsi que les "If...".

pour dire maintenant :

Mais A, B et C transmettent des valeurs à des cellules nommées hors tableau (par l'instruction -Range("Seuil").Value = C - p.ex.).

Il faudrait savoir !
J'ai l'impression de m'être légèrement fait balader et d'avoir perdu une heure ou deux sur ce coup-là.

Bonne nuit tout de même,
ROGER2327

Bernard_XLD · 19 Avril 2009

Re : Accélérer un code

Bonjour Wylsonn, Roger, le forum

pour gérer l'interruption manuelle ou les erreurs, mets ceci au début du code

Code:

On Error GoTo Gére_Erreurs
With Application
        'désactive affichage écran
        .ScreenUpdating = False
        'désactive affichage messages alerte
        .DisplayAlerts = False
        'redirige echap sur routine
        .EnableCancelKey = xlErrorHandler
End With

et mets ceci à la fin du code à la place du end sub

Code:

Exit Sub
Gére_Erreurs:
    With Application
            'réactive affichage écran
            .ScreenUpdating = True
            'réactive affichage messages alerte
            .DisplayAlerts = True
    End With
End Sub

si les gestions d'erreur t'interesse, regarde les instructions
Err
ou
Error(Err)

tu pourras agir dans la routine de gestion d'erreur en fonction du type d'erreur!

@+

Bernard_XLD · 19 Avril 2009

Re : Accélérer un code

avec la gestion du calcul auto, c'est mieux

Code:

On Error GoTo Gére_Erreurs
With Application
    'désactive affichage écran
    .ScreenUpdating = False
    'désactive affichage messages alerte
    .DisplayAlerts = False
    'redirige echap sur routine
    .EnableCancelKey = xlErrorHandler
    'désactive le calcul automatique
    .Calculation = xlCalculationManual
End With

Code:

Exit Sub
Gére_Erreurs:
    With Application
        'réactive affichage écran
        .ScreenUpdating = True
        'réactive affichage messages alerte
        .DisplayAlerts = True
        'réactive le calcul automatique
        .Calculation = xlCalculationAutomatic
   End With
End Sub

Bernard_XLD · 19 Avril 2009

Re : Accélérer un code

décidément, je ne vais pas y arriver, l'affichage des messages d'alerte est une réponse à un autre post.

au début du code

Code:

On Error GoTo Gére_Erreurs
With Application
    'désactive affichage écran
    .ScreenUpdating = False
    'redirige echap sur routine
    .EnableCancelKey = xlErrorHandler
    'désactive le calcul automatique
    .Calculation = xlCalculationManual
End With

à la fin du code

Code:

Exit Sub
Gére_Erreurs:
    With Application
        'réactive affichage écran
        .ScreenUpdating = True
        'réactive le calcul automatique
        .Calculation = xlCalculationAutomatic
   End With
End Sub

Wylsonn · 19 Avril 2009

Re : Accélérer un code

ROGER2327 à dit:
Re...

Or vous m'avez répondu :
Citation:
effectivement, la définition des valeurs pour A, B, C est inutile, ainsi que les "If...".

pour dire maintenant :
Citation:
Mais A, B et C transmettent des valeurs à des cellules nommées hors tableau (par l'instruction -Range("Seuil").Value = C - p.ex.).

Il faudrait savoir !

ROGER2327

Désolé Roger 😕, je me suis mal fait comprendre : je voulais juste dire que l'attribution en amont de 3 valeurs à A, B et C était inutile, puisque reprise après dans le début de chaque boucle. De même, les "If.." sont superflus puisque la fin des boucles les prévoit. Mais les boucles elles-même restent nécessaires...

Bon dimanche (sous le soleil)

Effectuez une recherche sur Excel Downloads...

Accélérer un code

Wylsonn

XLDnaute Nouveau

Pièces jointes

ROGER2327

XLDnaute Barbatruc

Wylsonn

XLDnaute Nouveau

ROGER2327

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Wylsonn

XLDnaute Nouveau

Discussions similaires

Nous accordons de l'importance à votre vie privée