Optimisation de traitement en masse des propriétés d'une plage

**Fred_Fred** · 22/01/2018, 12h30

Bonjour à tous,

Pour mettre en valeur une plage donnée, je renforce ses couleurs de fond comme ceci :

Code :

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

1
2
3
For Each Cellule In MaPlage
     Cellule.Interior.Color = Cellule.Interior.Color + 328965
Next Cellule

Ca fonctionne très bien, à condition que la plage ne soit pas trop étendue, le For Each étant assez gourmand en ressources.

J’ai donc eu l’idée de tester s’il était possible de transiter par un tableau, à la manière dont on le fait avec les données d’une plage

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Dim MonTableau As Variant
 
MonTableau = MaPlage.Interior.Color
 
For i = LBound(MonTableau) To UBound(MonTableau)
     For j = LBound(MonTableau) To UBound(MonTableau)
          MonTableau(i, j) = MonTableau(i, j) + 328965
     Next j
Next i
 
MaPlage.Interior.Color = MonTableau

Sauf que, comme on pouvait s’y attendre, ça ne fonctionne pas…

Quelqu’un aurait une idée géniale pour optimiser les mises à jour de propriétés sans utiliser le For Each ?

Merci,

**Marc-L** · 22/01/2018, 12h38

Bonjour,

désactiver au moins l'affichage avant la boucle, voir dans l'aide VBA interne la propriété ScreenUpdating …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**pijaku** · 22/01/2018, 12h50

Bonjour,

Salut Marc,

Ou encore, comme ton code ne comporte aucun test If :

Code :

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

1
2
3
4
5
6
 
Sub demo()
Dim MaPlage As Range
Set MaPlage = Range("A1:Z200000")
MaPlage.Interior.Color = MaPlage.Interior.Color + 328965
End Sub

==> Moins de 1 seconde pour cette grande plage...

**Fred_Fred** · 22/01/2018, 14h13

Bonjour à tous les deux et merci de vos propositions,

Pijaku :

Oui c’est exact, moins d’une seconde ça n’est pas beaucoup lorsque l’action est unique
En revanche, sur actions multiples par exemple déclenchées sur Worksheet_SelectionChange, ça peut ralentir les déplacements dans la feuille jusqu’à les saccader.
Ca fonctionne, mais pour l’utilisateur, ça peut être désagréable
C’est pourquoi je cherche tous les moyens d’optimisations possibles

Marc :

Même chose pour le ScreenUpdating, dans le principe c’est ce qu’il faut faire.
Il faut néanmoins le tester, parce que lors de sa réactivation, il peut se révéler plus gourmand que s’il n’avait pas été sollicité.
En fait, ça dépend, faut vérifier…

Et puis, c’est aussi que ça me titille intellectuellement :

Je suis sûr qu’il doit y avoir un moyen de récupérer toutes les valeurs des propriétés d’un objet d’un seul coup.
Elles sont forcément stockées quelque part en mémoire sous la forme de tableau ou d’Array…

Mais merci à vous quand même,

**eriiic** · 22/01/2018, 14h21

Bonjour,

Je suis sûr qu’il doit y avoir un moyen de récupérer toutes les valeurs des propriétés d’un objet d’un seul coup.

C'est ce que fait la proposition de pijaku il me semble. Tu n'auras pas plus rapide, et dans tous les cas plus rapide qu'une boucle.
A toi de revoir le fonctionnement prévu de ton appli en baissant les fonctionnalités.

Même chose pour le ScreenUpdating, dans le principe c’est ce qu’il faut faire.
Il faut néanmoins le tester, parce que lors de sa réactivation, il peut se révéler plus gourmand que s’il n’avait pas été sollicité.

Un exemple concret pour étayer serait le bienvenu.

T'es bizarre toi, tu demandes des améliorations et tu les rejettes toutes :-)
eric

**patricktoulon** · 22/01/2018, 14h25

bonjour
si ta plage est a la base et avant changement de la meme couleur alors pijaku t'a repondu

si tes cellules ne sont pas de la meme couleur alors c'est rapé a part le screenupdating=false il n'y a rien d'autre
surtout si on parle de milliers de cellules
voila

**Fred_Fred** · 22/01/2018, 15h29

Bonjour Patrick,

Hé non, les différentes cellules de mes plages peuvent être de couleurs différentes
Sinon, je n’aurais effectivement eu aucun souci :

Code :

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

MaPlage.Interior.Color = MêmeCouleurPartout

De plus, je souhaite pouvoir revenir en arrière et rétablir les couleurs d’origine
D’où la technique suivante par ajout ou retrait sur les couleurs

Mise en valeur de la plage :

Code :

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

1
2
3
For Each Cellule In MaPlage
     Cellule.Interior.Color = Cellule.Interior.Color + 328965
Next Cellule

Retour à la normale :

Code :

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

1
2
3
For Each Cellule In MaPlage
     Cellule.Interior.Color = Cellule.Interior.Color - 328965
Next Cellule

Mais si tu me confirmes que la boucle en For Each … Next est la meilleure solution, alors je vais la conserver.
Elle fonctionne très bien, c’était juste pour chercher des optimisations possibles…

Merci à tous,

**patricktoulon** · 22/01/2018, 15h36

ben en fait c'est pas que c'est la meilleure solution c'est la seule

si couleur differente

cela dit un peu lourd pour juste de la couleur
j'avais eu une idée mais je n'ai pas persévérer a savoir une mfc ou la couleur serait en numerique dans une colonne caché qui evolurait avec le change

et au change tu met par vba la mfc a la valeur de la colonne caché pour chaque cellule mais je sais pas si on y gagnerait ni meme si ca serait significatif
ce que je ne comprends pas c'est le besoins de changer toute la colonne sur l'evenement change pourquoi pas que les target ???

**Fred_Fred** · 22/01/2018, 16h30

Mais si, je teste le Target, c’est d’ailleurs la raison pour laquelle j’ai besoin du Worksheet_SelectionChange

L’idée est de simuler une surbrillance, soit toute la ligne, soit la colonne, soit un ou plusieurs tableaux dédiés afin d’attirer l’attention de l’utilisateur

J’ai testé plusieurs solutions, dont les tiennes, avec mémorisation de l’état antérieur des couleurs de la plage dans une colonne masquée et/ou mises en forme conditionnelles
Sauf que j’ai plutôt préféré déclarer un tableau en variable globale, plus rapide à traiter et plus discret qu’une colonne masquée

Avec les mises en forme conditionnelles (fixées toujours Vrai), on peut éviter les mémorisations antérieures, donc l’utilisation du tableau
Mais il faut se résoudre à n’avoir qu’une seule couleur de surbrillance et leur gestion est délicate en présence d’autres mises en forme conditionnelles

Au final, l’ajout d’une valeur constante dans la couleur de chaque cellule est la solution qui m’a paru la plus élégante
Ceci parce que chaque cellule mémorise elle-même son état antérieur, sans besoin de tableau, ni de calculs sur conditions (même très léger, puisque toujours Vrai)

Le fin du fin aurait été de pouvoir récupérer les valeurs de la propriété .Color dans un tableau, comme c’est possible avec la propriété .Value de la plage.

Mais si on peut pas, on peut pas…

En tout cas, merci à toi,

**patricktoulon** · 22/01/2018, 16h39

re
si on peut
cells(1,1).interior.color
le probleme c'est que l'on ne peut pas les recupérer en mase que (un par un )
t en a beaucoup de couleur differente ?

**Fred_Fred** · 22/01/2018, 17h02

Oui, c’est ça, on peut récupérer les couleurs une par une mais pas en masse, d’où le For Each

Sachant que je souhaite écrire une procédure générique réutilisable, je peux effectivement avoir des classeurs et des feuilles avec des plages de couleur assez variées

Mais tu sais, comme le soulignait Pijaku, le For Each fonctionne très bien, même sur plage importante
C’est juste que c’est un tout petit peu plus lent sur changement de ligne lorsque les utilisateurs se déplacent au clavier

**Marc-L** · 22/01/2018, 17h23

Il y aurait moyen en regroupant les adresses par couleur dans un dictionnaire par exemple
puis traiter chaque couleur sur l'union de ses adresses.
Et avec un peu d'imagination charger une fois pour toute ce dictionnaire dans une variable globale
évitant ainsi de tout recalculer à chaque fois; mais bon de la p'tite usine à gaz pour si peu d'intérêt …

**Fred_Fred** · 22/01/2018, 20h09

Ha oui, c’est très astucieux, ça !…

Bon d’accord, on ne gagnerait rien en écriture, ce qui est malheureusement le plus coûteux
Mais il n’y aurait qu’une seule lecture, réalisée une fois pour toute à l’ouverture du fichier
Cela dit, tu as raison, c’est quand même un tantinet compliqué...

Curieux que Microsoft n’ait pas pensé à cette possibilité de mise en surbrillance
C’est pourtant un truc que l’on trouve dans beaucoup de progiciels et que l’on a envie d’offrir à ses utilisateurs
Surtout lorsque le nombre de colonnes est important et que l'on ne sait plus sur quelle ligne on se trouve

En tout cas, merci à tous, c'était sympa

**eriiic** · 22/01/2018, 20h32

Bonjour,

si tu connais tes couleurs à remplacer (on peut le supposer) tu peux le faire par paquets avec .ReplaceFormat:

Code :

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

1
2
3
4
5
6
7
8
Sub couleurFond()
    With Application
        .FindFormat.Clear
        .FindFormat.Interior.Color = [D15].Interior.Color
        .ReplaceFormat.Interior.ColorIndex = 3
    End With
    [D:D].Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
End Sub

Ta boucle ne sera plus que du nombre de couleurs et non de celui des cellules une par une.
eric

**patricktoulon** · 22/01/2018, 20h35

bien vu eriic

**Fred_Fred** · 23/01/2018, 08h24

Ah oui, je confirme, le truc du ReplaceFormat d'Eriiic, ça aussi c'est pas mal !

Du coup :

Avec la proposition de Marc, on optimise la lecture des couleurs en entrée de fichier
Et avec la proposition d'Eriiic, on optimise l'écriture des valeurs de couleurs

Hé, hé... On progresse, on progresse !...

Va falloir que je teste, sauf que dans les jours qui viennent, je vais manquer de temps
Ne vous formalisez pas trop si je ne réagis plus tout de suite...

Merci à vous,