Discussion :

[sebyin]

Bonjour à tous,

Je suis nouveau sur le forum et je galère avec un programme VBA qui fonctionne mais est très long.
Je m'explique cette "moulinette" applique des tas de mise en forme mais à chaque fois en balayant cellule par cellule, ligne par ligne .... avec un tableau pouvant atteindre les 20000 lignes, je vous laisse imaginer le temps que ça prend ...

J'aimerai utiliser les tableaux à priori bien plus rapide, j'ai trouvé une aide ici : http://silkyroad.developpez.com/vba/tableaux/ mais je n'ai pas tout compris, cela manque d'exemple pour débutants, d'explications ....

Actuellement j'utilise un code de ce type (je balaye des lignes 6 à la dernière ligne (dans l'exemple, j'ai remplacé les variables par des chiffres pour que ce soit plus parlant par exemple dernièreligne est devenu 8000) :
le code colorie en jaune les demandes en attente, en rouge les refusées, applique des bordures si l'opération est de nuit (N) ...

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
 
'----CODE COULEUR----
couleurcouvert = 5296274
couleurarb = 49407
couleuratt = 65535
couleuracouvr = 15773696
couleurrefus = 255
couleurnuit = -10477568
'---COLORATION---
For i = 6 To 7367
    If Cells(i, 4).Value = "J" Then
        If LCase(Cells(i, 18).Value) = "en attente" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then Cells(i, j).Interior.Color = couleuratt
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "validée" And Cells(i, 22) = "" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then Cells(i, j).Interior.Color = couleuracouvr
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "validée" And Cells(i, 22) <> "" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then Cells(i, j).Interior.Color = couleurcouvert
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "en arbitrage" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then Cells(i, j).Interior.Color = couleurarb
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "refusée" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then Cells(i, j).Interior.Color = couleurrefus
            Next j
        End If
 
    ElseIf Cells(i, 4).Value = "N" Then
        If LCase(Cells(i, 18).Value) = "en attente" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then
                    Cells(i, j).Interior.Color = couleuratt
                    With Cells(i, j).Borders
                    .Weight = xlThick
                    .Color = couleurnuit
                    End With
                End If
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "validée" And Cells(i, 22) = "" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then
                    Cells(i, j).Interior.Color = couleuracouvr
                    With Cells(i, j).Borders
                    .Weight = xlThick
                    .Color = couleurnuit
                    End With
                End If
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "validée" And Cells(i, 22) <> "" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then
                    Cells(i, j).Interior.Color = couleurcouvert
                    With Cells(i, j).Borders
                    .Weight = xlThick
                    .Color = couleurnuit
                    End With
                End If
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "en arbitrage" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then
                    Cells(i, j).Interior.Color = couleurarb
                    With Cells(i, j).Borders
                    .Weight = xlThick
                    .Color = couleurnuit
                    End With
                End If
            Next j
        ElseIf LCase(Cells(i, 18).Value) = "refusée" Then
            For j = 23 To 57
                If Cells(i, j).Value = 1 Then
                    Cells(i, j).Interior.Color = couleurrefus
                    With Cells(i, j).Borders
                    .Weight = xlThick
                    .Color = couleurnuit
                    End With
                End If
            Next j
        End If
    End If
Next i
End Sub

Je voudrais utiliser une méthode plus rapide, que le balayage ligne par ligne, et j'ai entendu parler des tableaux en VBA qui sont nettement plus rapide, le problème c'est que je n'y comprend rien.
J'ai pu lire que

Lors de la manipulation de larges plages de données, le recours au tableau peut permettre des gains de temps significatifs

J'ai trouvé un code exemple concernant des additions de données sur un tableau mais je n'ai pas trouvé d'exemple basé sur une mise en forme, un colorisation des cases en fonction du contenu ?

LE code que j'ai essayé de prendre pour exemple (sans succès):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub addition_avec_tableau()
    Dim montableau As Variant, i As Long, j As Long
    montableau = Range("R1:AZ65000").Value
 
    For i = LBound(montableau, 1) To UBound(montableau, 1)
        For j = LBound(montableau, 2) To UBound(montableau, 2)
            montableau(i, j) = montableau(i, j) + 10
        Next j
    Next i
 
    Range("A1:Z455000").Value = montableau
End Sub

Merci d'avance pour l'aide afin d'optimiser ce code et le rendre plus rapide

[joe.levrai]

Bonjour,

as-tu pensé aux mises en forme conditionnelles ?

Utiliser une variable tableau, n'empêchera pas la mécanique de "traiter chaque élément séparément", même si ici ça ira plus vite de les traiter/évaluer
Il est également préférable d'utiliser un bon Select Case plutôt qu'un If/End If dans ta situation, où les conditions les plus récurrentes sont placées en premier, ce qui diminue les évaluations logiques à faire de façon drastique

Sachant que tu as 6 couleurs différentes, il devrait être possible de construire une structure de 5/6 Case et d'un Case Else.

Et puis, tu pourrais également t'intéresser aux filtres automatiques, qui te permettront de travailler sur des plages filtrées, plus faciles pour du traitement en bloc
car du côté des feuilles de calcul, on pourrait aussi par formule préparer le terrain pour VBA. Les outils ne manquent pas, les idées parfois.

[sebyin]

Bonjour,

as-tu pensé aux mises en forme conditionnelles ?

Déjà merci pour ta réponse, c'est sympa.

La mise en forme conditionnelle est impossible pour moi, déjà car il y a plusieurs tableaux et donc ce serait fastidieux de redéfinir les règles à chaque fois, sur des tableaux de taille différentes.
Et la macro colorise des cases en fonction du contenu d'une case.

Par contre diminuer le nombre de condition ou d'utiliser les filtres, est une bonne idée. Je vais essayer de travailler là dessus. Je l'ai déjà faitsur d'autres macro, je devrais pouvoir m'en sortir.

Néanmoins l'idée du tableau reste dans ma tête car j'ai des tas de processus qui balayent les lignes à la recherche de valeurs. Mais je ferai sans si je ne le maitrise pas, mon objectif est surtout de réduire la durée d’exécution de la macro dont l'exemple ci-dessous n'est qu'un petit exemple.

[joe.levrai]

Tu peux bien sûr utiliser un tableau pour analyser chaque élément

Mais le préalable pour gagner du temps, c'est de réduire le point de tes tests

un bloc If/End If a pour problème que la procédure analyse chaque bloc conditionnel, contrairement à un Select Case où dès que le bon Case est trouvé, les Case suivants ne sont pas analysés

d'où la stratégie de mettre en premiers Case les cas les plus fréquents

Ensuite seulement, le gain de temps en passant par un tableau devient significatif puisque le reste sera lui optimisé

Je te laisse préparer la conversion en Select Case. Quand ce sera fait, travailler sur un tableau ne sera pas bien différent d'un travail depuis un Range
Puisque le tableau sera utilisé pour faire les test dans ton Select Case ... et les ranges correspondants seront utilisés uniquement pour appliquer la mise en forme

Et ne pas oublier la piste du filtre automatique, qui pourrait s'occuper de traiter les lignes ayant en colonne 4 la lettre "J", puis ensuite traiter les lignes ayant en colonne 4 la lettre "N" en ne travaillant que sur les lignes filtrées

[riaolle]

Bonjour,
Je pense qu'avec un tableau et les filtres automatiques tu y arriveras plus facilement. Pour savoir comment utiliser les filtres en VBA : utilise l'enregistreur de macro pendant que tu filtres le tableau "à la main".
Ensuite, une fois le tableau filtré, tu peux utiliser la propriété SpecialCells pour ne prendre en compte que les cellules visibles après filtre :

Code :

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

Range("A1:A1000").SpecialCells(xlCellTypeVisible)