Optimisation de code

Version imprimable

Bonjour,
Voilà, en tant que débutant, j'utilise VBA pour me faciliter la tâche dans l'exploitation de fichier.
J'ai mis en place une macro qui fonctionne très bien mais dont un bout de code pourrait être optimiser mais je ne sais pas comment.

Je vous adresse donc ce bout pour avoir vos retours et me permettre de me perfectionner dans la logique VBA :-)

Code:

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
sub Formula ()
 
Dim DerLig As Long
 
Workbooks("TABLEAU_I.xlsx").Activate
Sheets("BDD_PT").Activate
 
Range("a1").Select
DerLig = Range(Selection, Selection.End(xlDown)).Count
 
Range("b2").Formula = "=IF(RC[1]>1,1,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:b" & DerLig)
 
Range("l2").Formula = "=YEAR(RC[2])"
Range("l2").Select
Selection.AutoFill Destination:=Range("l2:l" & DerLig)
 
Range("m2").Formula = "=WEEKNUM(RC[1])"
Range("m2").Select
Selection.AutoFill Destination:=Range("m2:m" & DerLig)
 
Range("q2").Formula = "=YEAR(RC[2])"
Range("q2").Select
Selection.AutoFill Destination:=Range("q2:q" & DerLig)
 
Range("r2").Formula = "=WEEKNUM(RC[1])"
Range("r2").Select
Selection.AutoFill Destination:=Range("r2:r" & DerLig)
 
Range("x2").Formula = "=YEAR(RC[2])"
Range("x2").Select
Selection.AutoFill Destination:=Range("x2:x" & DerLig)
 
Range("y2").Formula = "=WEEKNUM(RC[1])"
Range("y2").Select
Selection.AutoFill Destination:=Range("y2:y" & DerLig)
 
Range("ah2").Formula = "=IF(RC[-1]>"""",1,0)"
Range("ah2").Select
Selection.AutoFill Destination:=Range("ah2:ah" & DerLig)
 
Range("an2").Formula = "=+RC[-38]-RC[-6]"
Range("an2").Select
Selection.AutoFill Destination:=Range("an2:an" & DerLig)
 
Range("ao2").Formula = "=+IF(RC[-1]>0,RC[-2],0)"
Range("ao2").Select
Selection.AutoFill Destination:=Range("ao2:ao" & DerLig)
 
Range("ap2").Formula = "=+IF(OR(RC[-15]=0,RC[-16]=0),"""",RC[-15]-RC[-16])"
Range("ap2").Select
Selection.AutoFill Destination:=Range("ap2:ap" & DerLig)
 
Range("aq2").Formula = "=+IF(OR(RC[-14]=0,RC[-16]=0),"""",RC[-14]-RC[-16])"
Range("aq2").Select
Selection.AutoFill Destination:=Range("aq2:aq" & DerLig)
 
Range("ar2").Formula = "=+IF(AND(RC[-33]<>""EN COURS"",OR(MID(LEFT(RC[-37]),1,1)=""E"",MID(LEFT(RC[-37],5),1,5)=""JEU I""),TODAY()>RC[-23]-7),""URGENT"","""")"
Range("ar2").Select
Selection.AutoFill Destination:=Range("ar2:ar" & DerLig)
 
End Sub

La plupart des avis expliquent que l'utilisation abusive de select ralentit le code...

Merci pour vos avis.

05/10/2016, 10h48
halaster08
Oui les selection sont inutiles et une perte de temps.

Tu peux remplacer tout tes
Code:

1 2 range().select selection.qqch
Par

Code:

range().qqch

Code:

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
  Sub Formula()
Dim DerLig As Long
With Workbooks("TABLEAU_I.xlsx").Sheets("BDD_PT")
    DerLig = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row
    With .Range("b2")
        .Formula = "=IF(RC[1]>1,1,0)"
        .AutoFill Destination:=Range("B2:b" & DerLig)
    End With
     With .Range("l2")
        .Formula = "=YEAR(RC[2])"
        .AutoFill Destination:=Range("l2:l" & DerLig)
    End With
     With .Range("m2")
        .Formula = "=WEEKNUM(RC[1])"
        .AutoFill Destination:=Range("m2:m" & DerLig)
    End With
     With .Range("q2")
        .Formula = "=YEAR(RC[2])"
        .AutoFill Destination:=Range("q2:q" & DerLig)
    End With
    With Range("r2")
       .Formula = "=WEEKNUM(RC[1])"
        .AutoFill Destination:=Range("r2:r" & DerLig)
    End With
    With Range("x2")
        .Formula = "=YEAR(RC[2])"
        .AutoFill Destination:=Range("x2:x" & DerLig)
    End With
    With Range("y2")
        .Formula = "=WEEKNUM(RC[1])"
        .AutoFill Destination:=Range("y2:y" & DerLig)
    End With
    With Range("ah2")
       .Formula = "=IF(RC[-1]>"""",1,0)"
       .AutoFill Destination:=Range("ah2:ah" & DerLig)
    End With
    With Range("an2")
       .Formula = "=+RC[-38]-RC[-6]"
       .AutoFill Destination:=Range("an2:an" & DerLig)
    End With
    With Range("ao2")
       .Formula = "=+IF(RC[-1]>0,RC[-2],0)"
       .AutoFill Destination:=Range("ao2:ao" & DerLig)
    End With
    With Range("ap2")
       .Formula = "=+IF(OR(RC[-15]=0,RC[-16]=0),"""",RC[-15]-RC[-16])"
       .AutoFill Destination:=Range("ap2:ap" & DerLig)
    End With
    With Range("aq2")
       .Formula = "=+IF(OR(RC[-14]=0,RC[-16]=0),"""",RC[-14]-RC[-16])"
       .AutoFill Destination:=Range("aq2:aq" & DerLig)
    End With
    With Range("ar2")
       .Formula = "=+IF(AND(RC[-33]<>""EN COURS"",OR(MID(LEFT(RC[-37]),1,1)=""E"",MID(LEFT(RC[-37],5),1,5)=""JEU I""),TODAY()>RC[-23]-7),""URGENT"","""")"
       .AutoFill Destination:=Range("ar2:ar" & DerLig)
    End With
End With
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub test()
 
Dim DerLig As Long
 
Workbooks("TABLEAU_I.xlsx").Activate
With Sheets(1) 'Sheets("BDD_PT")
.Activate
DerLig = .Cells(Rows.Count, 1).End(xlUp).Row
 
.Range("b2:B" & DerLig).Formula = "=IF(RC[1]>1,1,0)"
.Range("I2:I" & DerLig).Formula = "=YEAR(RC[2])"
'etc......
End With
End Sub

Bonjour,

Une autre solution parmi celles déjà proposées :

Code:

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
 
 
Sub Formula()
 
Dim DerLig As Long
Dim AireBdd As Range
Dim ShBdd As Worksheet
 
 
       Set ShBdd = Workbooks("TABLEAU_I.xlsx").Sheets("BDD_PT")
 
       With ShBdd
 
            DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
            If DerLig = 1 Then Exit Sub
 
            Set AireBdd = .Range(.Cells(2, 1), .Cells(DerLig, 1))
 
            With AireBdd
                 .Offset(0, 1).Formula = "=IF(RC[1]>1,1,0)"  ' Colonne B
                 .Offset(0, 10).Formula = "=YEAR(RC[2])"     ' Colonne L
                 .Offset(0, 11).Formula = "=WEEKNUM(RC[1])"  ' Colonne M
                 .Offset(0, 16).Formula = "=YEAR(RC[2])"     ' Colonne Q
                 .Offset(0, 17).Formula = "=WEEKNUM(RC[1])"  ' Colonne R
                 .Offset(0, 23).Formula = "=YEAR(RC[2])"     ' Colonne X
                 .Offset(0, 24).Formula = "=WEEKNUM(RC[1])"  ' Colonne Y
                 .Offset(0, 33).Formula = "=IF(RC[-1]>"""",1,0)"      ' Colonne AH
                 .Offset(0, 39).Formula = "=+RC[-38]-RC[-6]"          ' Colonne AN
                 .Offset(0, 40).Formula = "=+IF(RC[-1]>0,RC[-2],0)"   ' Colonne AO
                 .Offset(0, 41).Formula = "=+IF(OR(RC[-15]=0,RC[-16]=0),"""",RC[-15]-RC[-16])"  ' Colonne AP
                 .Offset(0, 42).Formula = "=+IF(OR(RC[-14]=0,RC[-16]=0),"""",RC[-14]-RC[-16])"  ' Colonne AQ
                 .Offset(0, 43).Formula = "=+IF(AND(RC[-33]<>""EN COURS"",OR(MID(LEFT(RC[-37]),1,1)=""E"",MID(LEFT(RC[-37],5),1,5)=""JEU I""),TODAY()>RC[-23]-7),""URGENT"","""")"  ' Colonne AR
            End With
 
            Set AireBdd = Nothing
 
       End With
 
        Set ShBdd = Nothing
 
End Sub

Cordialement.

06/10/2016, 10h10
Cheribay

Bonjour à tous et un grand merci pour la présentation des optimisations possibles.

Je les récupère afin de les étudier et bien comprendre leur architecture.

merci encore !:lol: