Microsoft 365 [MFC Excel] Simplification des Règles

[Hayholten]

Salut à tous,

Alors me voilà tourné vers vous plein d'espoir pour trouver une ou plusieurs solutions pour simplifier quelque peu mes règles de mise en forme conditionnelle.

Je travaille sur une base de donnée partagée très conséquente et impliquant de nombreux paramètres (incluant des requêtes PQ, un peu de macro et surtout beaucoup de données).

À ce stade, je n'ai pas moins de 66 règles... Or, comme la majorité repose sur des conditions et des formules qui s'étendent sur des plages de plusieurs milliers de lignes. Cela rend bien sûr le tout un peu lourd et certaines machines peinent à ouvrir le document, faute de ressources et de puissance.

Or, je constate que malgré tout, soit certaines conditions sont les mêmes mais avec des formats différents, soit ce sont les plages qui sont identiques avec des conditions différentes.

J'ai tenté plusieurs approches :
1. jouer avec les nuances de mise en surbrillance pour regrouper des règles
2. combiner des plages dans une même règle

Et à chaque fois, un pépin finit par survenir (problème de références relatives, etc.).

Bref, je vous partage des copies d'écrans de toutes ces MFC (ça peut donner un peu mal à la tête^^).

Si vous avez des pistes pour m'aider à amaigrir le tout, ce serait top !

Merci tout plein

 

[crocrocro]

Bonjour Hayolten
j'ai mis à disposition un fichier qui pourra peut-être vous aider.
Il est en cours d'approbation et sera disponible dans Téléchargement -> Utilitaires : Aide à la mise au point des Règles de Mise en Forme Conditionnelle (MFC)

 

[Hayholten]

Merci beaucoup crocrocro pour cette contribution.

Hâte de prendre connaissance du document

 

[crocrocro]

Bonjour Hayholten,

À ce stade, je n'ai pas moins de 66 règles... Or, comme la majorité repose sur des conditions et des formules qui s'étendent sur des plages de plusieurs milliers de lignes. Cela rend bien sûr le tout un peu lourd et certaines machines peinent à ouvrir le document, faute de ressources et de puissance.

j'ai bien peur que l'amélioration de l'organisation des MFC n'apporte pas grand-chose au temps d'attente à l'ouverture du fichier.
L'affichage des cellules est toujours très coûteux, c'est un peu le péché d'Excel. C'est pourquoi, dans les macros, il est conseillé de désactiver l'affichage des mises à jour en début de macro et de le réactiver en fin.

Application.ScreenUpdating = False

A voir sur votre fichier s'il n'y a pas des macros exécutées à l'ouverture du fichier et qui pourraient être améliorées.

 

[Hayholten]

Bonjour à tous,

Merci crocrocro pour ces retours et désolé pour le retour tardif.

Le principal souci que je rencontre avec ces MFCs, ce n'est pas tant le temps d'attente pour l'ouverture du fichier, même sur des machines vieillissantes (je précise qu(il est partagé dans une bibliothèque SharePoint), c'est surtout que régulièrement, sans trop savoir pourquoi, lorsque j'ouvre le fichier, Excel se referme dans la foulée et je dois effectuer un "Ouvrir et Réparer" pour pouvoir à nouveau y avoir accès.

Et je pense (sans en être complètement certain) que ce sont ces MFCs et leur complexité qui occasionne ce problème d'ouverture.

Application.ScreenUpdating = False

Cette idée m'interpelle. Je ne suis pas un expert en macro et elles ne sont pas non plus très nombreuses dans mon document (quelques modules simples, une qui me permet d'afficher la date / heure de dernière modification du fichier, une qui désactive dans l'invitation "Chercher et Remplacer" l'option "Totalité du contenu de la cellule" et une autre qui me remplace tout les caractères spéciaux).

Mais si je saisis la suggestion, il faudrait que j'applique cette fonction "Application.ScreenUpdating" dans le code de mes MFCs ? Mais on le trouve où ce code ?

À nouveau merci pour l'aide proposée

 

[crocrocro]

Non, il n'y a pas de code des MFC (sauf si on les créé par VBA) mais juste des formules.
Difficile sans avoir le fichier de connaitre la source des problèmes à l'ouverture.
Si vous pouviez en fournir un en anonymisant les données ...
A voir si sur l'événement WorkBook_Open, il n'y a pas une macro un peu gourmande !

j'ai mis à disposition un fichier qui pourra peut-être vous aider.
Il est en cours d'approbation et sera disponible dans Téléchargement -> Utilitaires : Aide à la mise au point des Règles de Mise en Forme Conditionnelle (MFC)

Maintenant disponible Aide à la mise au point des Règles de Mise en Forme Conditionnelle (MFC)

 

[Hayholten]

Hello crocrocro, hello à tous,

À nouveau merci pour ton retour^^

Non, il n'y a pas de code des MFC (sauf si on les créé par VBA) mais juste des formules.

Et est-ce que les créer par VBA serait plus "propre" ?

Difficile sans avoir le fichier de connaitre la source des problèmes à l'ouverture.
Si vous pouviez en fournir un en anonymisant les données ...
A voir si sur l'événement WorkBook_Open, il n'y a pas une macro un peu gourmande !

Je suis conscient que sans fichier, cela est compliqué d'identifier là où ça pêche... Je vais tenter d'en fournir un mais il s'y trouve tellement de données sensibles, de requêtes PQ vers des BDD externes, etc. que ça risque de prendre un peu de temps.

Toutefois, il y a une macro sur laquelle j'ai un doute, je la joins ici^^

Maintenant disponible Aide à la mise au point des Règles de Mise en Forme Conditionnelle (MFC)

Bravo pour ce travail titanesque !!! Je vais me plonger dedans dès que possible car l'outil m'intéresse sévèrement

 

[crocrocro]

Laisse tomber pour la fourniture du fichier.
Le lien que je t'ai donné contient des macros qui, peuvent remettre de l'ordre dans les MFC mais probablement pas améliorer les performances du point de vue temps d'exécution.
Concernant le code VBA de ton dernier fichier 2 ou 3 remarques :
- .Range("A" & i & ":E" & i).EntireRow.Delete : supprime la ligne entière. Si c'est ce que tu veux, il y a plus simple et plus rapide (0.02secondes contre 7 secondes pour 10 000 lignes. Mais si tu as simplement une dizaine de lignes à supprimer, çà passera inaperçu
- s'il s'agit juste de vider le contenu, .ClearContents serait suffisant.

Le code rectifié avec la macro Remplir qui permet de mettre un a en colonne A ligne 2 à 10 000
Le EntireRow pour supprimer en 1 fois à partir de la ligne 2 jusqu'à la dernière ligne non vide
Le ClearContents (mis en commentaire) pour vider le contenu (seulement colonne A à E) à partir de la ligne 2 jusqu'à la dernière ligne non vide

Sub remplir()
For i = 2 To 10000
    Cells(i, 1) = "a"
    Next i
End Sub

Sub Supprmail()
    Dim n%, i%  'Déclaration des variables
    Dim t1 As Double
    Application.ScreenUpdating = False
    t1 = Timer
    With Worksheets("mafeuil1") 'Nom de la feuille à modifier suivant le cas
        n = .Range("A" & .Rows.Count).End(xlUp).Row 'N° de la dernière ligne remplit
    '      For i = n To 2 Step -1 'Boucle de la dernière ligne remplit vers la ligne 2 (première ligne remplit) par PAS de -1
    '          If .Range("A" & i) > 0 Then .Range("A" & i & ":E" & i).EntireRow.Delete 'si la ligne de la colonne A est = à 0 , suppression de la ligne
    '      Next i
        If n > 1 Then
               .Range("2:" & n).EntireRow.Delete
            '    .Range("A2:E" & n).ClearContents
        End If
    End With
    MsgBox Timer - t1
End Sub

Pour le reste, je te suggère de faire une copie non partagée du fichier et de faire quelques tests d'ouverture, mise à jour, fermeture, réouverture ... et de voir si globalement les problèmes sont les mêmes qu'avec le fichier partagé.

 

[Hayholten]

Re,

À nouveau merci beaucoup crocrocro d'avoir pris le temps^^

Laisse tomber pour la fourniture du fichier.
Le lien que je t'ai donné contient des macros qui, peuvent remettre de l'ordre dans les MFC mais probablement pas améliorer les performances du point de vue temps d'exécution.

Je vais attentivement regarder ton outil. Je suis convaincu d'y trouver quoi qu'il arrive, de meilleures pratiques.

Pour le temps d'exécution, je m'en arrange. C'est surtout la fermeture inopinée et aléatoire d'Excel à l'ouverture du fichier qui me chagrine, avec la potentielle perte de données qui pourrait en résulter.

Concernant le code VBA de ton dernier fichier 2 ou 3 remarques :
- .Range("A" & i & ":E" & i).EntireRow.Delete : supprime la ligne entière. Si c'est ce que tu veux, il y a plus simple et plus rapide (0.02secondes contre 7 secondes pour 10 000 lignes. Mais si tu as simplement une dizaine de lignes à supprimer, çà passera inaperçu
- s'il s'agit juste de vider le contenu, .ClearContents serait suffisant.

Le code rectifié avec la macro Remplir qui permet de mettre un a en colonne A ligne 2 à 10 000
Le EntireRow pour supprimer en 1 fois à partir de la ligne 2 jusqu'à la dernière ligne non vide
Le ClearContents (mis en commentaire) pour vider le contenu (seulement colonne A à E) à partir de la ligne 2 jusqu'à la dernière ligne non vide

Pour être franc, ce code n'est pas le mien, étant encore plutôt novice en VBA. C'est un complément qui permet de désactiver la case "Totalité du contenu de la cellule" qui l'est par défaut lors d'un Ctrl_F ou d'un Ctrl_H (comportement qui m'insupporte et qui à priori n'est toujours pas modifiable dans Excel).

En désactivant ce complément, j'ai toutefois noté une ouverture plus rapide du fichier. Je commence à me convaincre que le souci vient de là.

Je vais suivre tes suggestions en matière de code, réinstaller le complément et voir si le comportement d'Excel n'est pas altéré^^

 

[lav]

Bonjour
j'apporte mon petit grain de sel ...
On peut surement réaliser la mise en forme conditionnelle en VBA, les performances devraient être compétitive. Par ailleurs, si l'on connait les zones mises à jour on peur rafraichir la mise en forme uniquement sur ces zones.
Concernant les Ctrl_F et _H, si l'on crée des macros (éventuellement vides) avec un raccourci clavier correspondant, en principe ça devrait court-circuiter les traitements par défaut