Reduction du temps de traitement

**pascal4782** · 11/04/2011, 13h39

Bonjour à tous,

Je débute en VBA et je suis arrivé à la limite de mes compétences. J’ai construis avec l’aide de la communauté un fichier qui me permet de construire des variables synthétiques avec le test Brown.Forsythe. Le problème est qu’il faut plus d’une heure de traitement ce qui me parait vraiment très long.
Si une âme charitable pouvait me trouver une astuce ou me donner une piste pour réduire le temps de travail je suis preneur.
Je suppose que la raison de cette lenteur est le fait que j’associe du VBA et des formules Excel que je n’arrive pas à transcrire totalement en VBA.
Le plus important dans ce fichier est le résultat final des tableaux (BY1 :CL2) et (BY20 :BZ79), le reste des tableaux n’est qu’accessoire.

Merci par avance

**Philippe Tulliez** · 11/04/2011, 14h02

Bonjour,
En parcourant en vitesse le code, j'aurais tendance à abandonner le Copy/PAsteSpecial par un remplissage direct par égalité. Cela ira beaucoup plus vite.
Un petit exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 Dim shtFrom As Worksheet, shtTo As Worksheet
 With ThisWorkbook
  Set shtTo = .Worksheets("Feuil1")
  Set shtFrom = .Worksheets("A")
 End With
 shtTo.Range("A20:J20").Value = shtFrom.Range("T20:AC20").Value

Il y a sûrement d'autres choses à faire mais j'ai lu très rapidement.

**Philippe Tulliez** · 11/04/2011, 15h14

Bonjour,
Une autre chose à faire c'est de bloquer le recalcul automatique pendant que la macro fonctionne parce-que j'ai vu qu'il y a construction de formules dans ce code et qu'évidemment cela oblige Excel à effectuer le calcul de ces fonctions.
Eventuellement désactiver la mise à jour de l'écran

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 Dim CalculMode As Long
 With Application
 .ScreenUpdating = False
 CalculMode = .Calculation
 .Calculation = xlCalculationManual
 End With
 ' ..... Le Code
 ' .....
 With Application
 .Calculation = CalculMode
 .ScreenUpdating = True
 End With

Attention que si le résultat de formules créées dynamiquement pendant l'exécution doit servir lors d'un "copier/Coller" il faut forcer le recalcul.

**pascal4782** · 11/04/2011, 15h37

Merci pour les réponses,

J’ai de quoi bosser pour avancer maintenant. Les tutos sont formidables a conditions de savoir quoi chercher.

Merci encore pour vous être penché sur mon problème

**pascal4782** · 12/04/2011, 08h27

Bonjour à tous,

Je reviens vers vous aujourd’hui parce que je convaincu de la rapidité de traitement grâce aux tableaux. Cependant après une nuit blanche et plusieurs litres de café noir je n’arrive toujours pas a une ombre de début de code.
Si vous pouviez me donner une piste pour la déclaration de mes variables sous forme de tableau j’aurai de quoi bosser sur du concret.

Merci par avance

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
Sub Macro2()
Deb = Timer
   Application.ScreenUpdating = False
 
  For i = 1 To 2
    decal = 14 * i
    Sheets("A").Range("F20:S79").Offset(0, decal).Copy
    Sheets("1").Activate
    Range("A20").PasteSpecial Paste:=xlPasteValues
 
TestABC
 
    Range("BA20:BA79").Copy
    Range("BX20").Offset(0, i).PasteSpecial Paste:=xlPasteValues
    Range("BE17:BR17").Copy
    Range("BY1").Offset(i - 1, 0).PasteSpecial Paste:=xlPasteValues
  Next i
  Application.ScreenUpdating = True
 MsgBox "J'ai bossé " & Timer - Deb & " seconde"
End Sub
 
Sub TestABC()
 
'Deb = Timer
 '  Application.ScreenUpdating = False
Dim A As Long, X As Integer, Str_Val_1 As String, Cel As Range
For X = 1 To 14
    Select Case X
        Case 1
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R"
            Set Cel = Range("BE1")
        Case 2
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R"
            Set Cel = Range("BF1")
        Case 3
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R"
            Set Cel = Range("BG1")
        Case 4
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R"
            Set Cel = Range("BH1")
        Case 5
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R"
            Set Cel = Range("BI1")
        Case 6
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R"
            Set Cel = Range("BJ1")
        Case 7
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+2*SIN(RC[-46]/R"
            Set Cel = Range("BK1")
        Case 8
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R"
            Set Cel = Range("BL1")
        Case 9
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R"
            Set Cel = Range("BM1")
        Case 10
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R"
            Set Cel = Range("BN1")
        Case 11
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R"
            Set Cel = Range("BO1")
        Case 12
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R"
            Set Cel = Range("BP1")
        Case 13
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R"
            Set Cel = Range("BQ1")
        Case 14
            Str_Val_1 = "=RC[-1]+SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R"
            Set Cel = Range("BR1")
 
    End Select
    For A = 20 To 29
 
        Range("BA20").FormulaR1C1 = Str_Val_1 & A & "C56)"
        Range("BA20").AutoFill Destination:=Range("BA20:BA79"), Type:=xlFillDefault
 
    Range("BA12").Copy
        Cel.Offset(A - 1, 0).PasteSpecial Paste:=xlPasteValues
    Next A
Next X
  ' Application.ScreenUpdating = True
' MsgBox "J'ai bossé " & Timer - Deb & " seconde"
End Sub

**Didier Gonard** · 12/04/2011, 08h47

Bonjour,

Une fois compris comment ça marche, ça va mieux, il faut arriver à se représenter la chose :

Optimisation du code avec les variables tableau sous Excel

cordialement,

Didier

**aereno** · 11/04/2011, 15h01

Bonjour,

tu peux transférer le contenu de tes feuilles dans des variables tableau à 2 dimensions. Les gains en performances seront très significatifs.

Un lien utile :

http://silkyroad.developpez.com/vba/tableaux/

Reduction du temps de traitement [XL-2007]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager