Lenteurs de macros VBA sous Excel 2007 vs Excel 2003

**nicotab** · 03/08/2007, 15h10

Bonjour,

J'utilise une boucle "FOR" pour effectuer des traitements de remplissage dans un fichier d'environ 26000 lignes. Ce développement VBA programmé sous Excel 2003 prend environ 40 secondes. Sur le même PC sous Excel 2007, le même traitement prend 5 minutes.

Certaines fonctions sous VBA 2007 auraient elles changées au profit d'autres ?
Quelqun peut il m'éclairer svp ? Merci par avance.

Ci-dessous la boucle "FOR" en cause :

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
For i = 1 To NombreLignes - 1
    If ActiveCell.Offset(i, 0).Value = "Base" Then
    Puissance = ActiveCell.Offset(i, 14).Value
    Fixe = Application.WorksheetFunction.VLookup(Puissance, PlageST, 5, False)
    PF_CU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 6, False)
    PF_MU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 8, False)
    PF_LU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 10, False)
    Conso_CU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 7, False)
    Conso_MU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 9, False)
    Conso_LU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 11, False)
 
    PF_CU = Fixe + (PF_CU_bareme * Puissance)
    Conso_CU = (Conso_CU_bareme * ActiveCell.Offset(i, 7).Value) / 100
    TURPE2_CU = PF_CU + Conso_CU
    MOYENNE_CU = TURPE2_CU / (ActiveCell.Offset(i, 10).Value / 1000)
    CTA_CU = 0.21 * PF_CU
 
    PF_MU = Fixe + (PF_MU_bareme * Puissance)
    Conso_MU = (Conso_MU_bareme * ActiveCell.Offset(i, 7).Value) / 100
    TURPE2_MU = PF_MU + Conso_MU
    MOYENNE_MU = TURPE2_MU / (ActiveCell.Offset(i, 10).Value / 1000)
    CTA_MU = 0.21 * PF_MU
 
    PF_LU = Fixe + (PF_LU_bareme * Puissance)
    Conso_LU = (Conso_LU_bareme * ActiveCell.Offset(i, 7).Value) / 100
    TURPE2_LU = PF_LU + Conso_LU
    MOYENNE_LU = TURPE2_LU / (ActiveCell.Offset(i, 10).Value / 1000)
    CTA_LU = 0.21 * PF_LU
 
    TURPE2_OPTIMISE_BASE = Application.WorksheetFunction.Min(TURPE2_CU + CTA_CU, TURPE2_MU + CTA_MU, TURPE2_LU + CTA_LU)
    If TURPE2_OPTIMISE_BASE = TURPE2_CU + CTA_CU Then OPTIMISE = "CU"
    If TURPE2_OPTIMISE_BASE = TURPE2_MU + CTA_MU Then OPTIMISE = "MU"
    If TURPE2_OPTIMISE_BASE = TURPE2_LU + CTA_LU Then OPTIMISE = "LU"
    If OPTIMISE = "CU" Then
        PF_OPT = PF_CU
        Conso_OPT = Conso_CU
        TURPE2_OPT = TURPE2_CU
        MOYENNE_OPT = MOYENNE_CU
        CTA_OPT = CTA_CU
    End If
    If OPTIMISE = "MU" Then
        PF_OPT = PF_MU
        Conso_OPT = Conso_MU
        TURPE2_OPT = TURPE2_MU
        MOYENNE_OPT = MOYENNE_MU
        CTA_OPT = CTA_MU
    End If
    If OPTIMISE = "LU" Then
        PF_OPT = PF_LU
        Conso_OPT = Conso_LU
        TURPE2_OPT = TURPE2_LU
        MOYENNE_OPT = MOYENNE_LU
        CTA_OPT = CTA_LU
    End If
    ' CU
    ActiveCell.Offset(i, 16).Value = Round(PF_CU, 2)
    ActiveCell.Offset(i, 17).Value = Round(Conso_CU, 2)
    ActiveCell.Offset(i, 18).Value = Round(TURPE2_CU, 2)
    ActiveCell.Offset(i, 19).Value = Round(MOYENNE_CU, 2)
    ActiveCell.Offset(i, 20).Value = Round(CTA_CU, 2)
    ' MU
    ActiveCell.Offset(i, 22).Value = Round(PF_MU, 2)
    ActiveCell.Offset(i, 23).Value = Round(Conso_MU, 2)
    ActiveCell.Offset(i, 24).Value = Round(TURPE2_MU, 2)
    ActiveCell.Offset(i, 25).Value = Round(MOYENNE_MU, 2)
    ActiveCell.Offset(i, 26).Value = Round(CTA_MU, 2)
    ' LU
    ActiveCell.Offset(i, 28).Value = Round(PF_LU, 2)
    ActiveCell.Offset(i, 29).Value = Round(Conso_LU, 2)
    ActiveCell.Offset(i, 30).Value = Round(TURPE2_LU, 2)
    ActiveCell.Offset(i, 31).Value = Round(MOYENNE_LU, 2)
    ActiveCell.Offset(i, 32).Value = Round(CTA_LU, 2)
    ' minimum TURPE2 BASE optimisé
    ActiveCell.Offset(i, 40).Value = OPTIMISE
    ActiveCell.Offset(i, 41).Value = Round(PF_OPT, 2)
    ActiveCell.Offset(i, 42).Value = Round(Conso_OPT, 2)
    ActiveCell.Offset(i, 43).Value = Round(TURPE2_OPT, 2)
    ActiveCell.Offset(i, 44).Value = Round(MOYENNE_OPT, 2)
    ' CTA BASE optimisée
    ActiveCell.Offset(i, 46).Value = Round(CTA_OPT, 2)
    ActiveCell.Offset(i, 47).Value = Round(CTA_OPT / (ActiveCell.Offset(i, 10).Value / 1000), 2)
    End If
    If ActiveCell.Offset(i, 0).Value = "Double" Then
    Puissance = ActiveCell.Offset(i, 14).Value
    Fixe = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 5, False)
    PF_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 6, False)
    ConsoHP_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 7, False)
    ConsoHC_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 8, False)
 
    PF_MUDT = Fixe + (PF_MUDT_bareme * Puissance)
    ConsoHP_MUDT = (ConsoHP_MUDT_bareme * ActiveCell.Offset(i, 7).Value) / 100
    ConsoHC_MUDT = (ConsoHC_MUDT_bareme * ActiveCell.Offset(i, 8).Value) / 100
    TURPE2_MUDT = PF_MUDT + ConsoHP_MUDT + ConsoHC_MUDT
    MOYENNE_MUDT = TURPE2_MUDT / (ActiveCell.Offset(i, 10).Value / 1000)
    CTA_MUDT = 0.21 * PF_MUDT
    ' MU DT
    ActiveCell.Offset(i, 34).Value = Round(PF_MUDT, 2)
    ActiveCell.Offset(i, 35).Value = Round(ConsoHP_MUDT + ConsoHC_MUDT, 2)
    ActiveCell.Offset(i, 36).Value = Round(TURPE2_MUDT, 2)
    ActiveCell.Offset(i, 37).Value = Round(MOYENNE_MUDT, 2)
    ActiveCell.Offset(i, 38).Value = Round(CTA_MUDT, 2)
    ' TURPE2 DOUBLE (optimisé)
    ActiveCell.Offset(i, 40).Value = "MU DT"
    ActiveCell.Offset(i, 41).Value = Round(PF_MUDT, 2)
    ActiveCell.Offset(i, 42).Value = Round(ConsoHP_MUDT + ConsoHC_MUDT, 2)
    ActiveCell.Offset(i, 43).Value = Round(TURPE2_MUDT, 2)
    ActiveCell.Offset(i, 44).Value = Round(MOYENNE_MUDT, 2)
    ' CTA DOUBLE
    ActiveCell.Offset(i, 46).Value = Round(CTA_MUDT, 2)
    ActiveCell.Offset(i, 47).Value = Round(CTA_MUDT / (ActiveCell.Offset(i, 10).Value / 1000), 2)
    End If
Next i