Lenteur à l'ouverture du fichier

Le 13/07/2020 à 23:00

Bonjour,

Étant novice en VBA, mais ayant réussit à me dépatouiller pour diverses macros...

J'ai un problème avec ce fichier d'essai de macro, celui-ci met beaucoup de temps pour s'ouvrir, sachant que là il n'y a qu'un onglet...

Mais que sur mon fichier final, il en y aura 13 onglets "installation / équipement" (avec une périodicité de 1 mois ou 1 trimestre ou 1 semestre ou 1 an suivant la réglementation) + 1 onglet "Accueil" + 1 onglet "Data" + 2 onglets "Synthèses". Le nombre de lignes n'est pas fixe et va pour l'instant de 2 à 200.

En pièce jointe: fichier d'essai allégé avec un onglet "TableauSimplifié" + un onglet "TableauFinal" pour avoir un ordre d'idée du résultat final.

Si quelqu'un a une idée pour accélérer son ouverture et l'optimiser, je suis preneur.

Merci d'avance pour votre aide.

B. A.

11formatcolor13-07-20.xlsm (36.87 Ko)
Le 14/07/2020 à 00:00

Bonjour,

Plusieurs Choses à dire.

Si tu ne boucle que sur ce classeur la ne dit pas à Excel de boucler sur tous les classeurs ouverts...

'Définit une variable qui va représenter le classeur
    Dim Wb As Workbook
'Définit une variable qui va représenter une feuille de calcul
    Dim Ws As Worksheet

'Boucle sur le classeur
    'For Each Wb In Workbooks        'Inutile et encombrant. Peu aussi affecter tes autres classeurs qui n'ont rien demandé.
'Boucle sur chaque feuille de chaque classeur
        For Each Ws In ThisWorkbook.Worksheets       'Remplace le Wb par thisWorkbook

Ensuite pour chaque cellule tu lui refait chercher dans toutes les cellules de toutes les feuilles.

Sort ta fonction FormatColor de la boucle !

            Next Cell
        Next Ws

'Appel de la macro FaitNonFait
Call FormatColor
End Sub

Enfin, pourquoi faire chercher deux fois à excel dans toutes les cellules du même range ?

De plus dans FormatColor 2 fois tu lui fait rechercher si une valeur = "Fait" ou "Non Fait" Pareil, Combine les.

Combine les deux programmes :

Resultat :

Private Sub Workbook_Open()

'Fonctionne pour tous les onglets avec l'appel de call

'Définit une variable qui va représenter une feuille de calcul
    Dim Ws As Worksheet
'Définit une variable qui va représenter une cellule
    Dim Cell As Range

'Boucle sur chaque feuille de chaque classeur
        For Each Ws In ThisWorkbook.Worksheets
'Boucle sur chaque cellule de la plage A4:d29
            For Each Cell In Ws.Range("A3:L29")

'ETUDE DATE
'Si la date est dans les 6 jours (60 jours pour version finale), alors
                If Cell.Value < Date + 6 And Cell.Value <> "" Then
                    Cell.Interior.Color = RGB(255, 255, 156) 'Arrière-plan de couleur jaune
                    Cell.Font.Color = RGB(255, 192, 0) 'Police de couleur orange
                End If
'Si la date est dans les 3 jours (30 jours pour version finale), alors
                If Cell.Value < Date + 3 And Cell.Value <> "" Then
                    Cell.Interior.Color = RGB(255, 192, 0) 'Arrière-plan de couleur orange
                    Cell.Font.Color = RGB(255, 255, 156) 'Police de couleur jaune
                End If
'Si la date est dépassée, alors
                If Cell.Value <= Date And Cell.Value <> "" Then
                    Cell.Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                    Cell.Font.Color = RGB(156, 0, 6) 'Police de couleur noirrouge foncé
                End If

'FAIT / NON FAIT
'Si la valeur de la cellule est "Fait", alors
                If Cell.Value = "Fait" Then 'Si la valeur = "Fait" alors
                    Cell.Interior.Color = RGB(198, 239, 206) 'Arrière-plan de couleur vert clair
                    Cell.Font.Color = RGB(0, 97, 0) 'Police de couleur vert foncé

                    Cell.Offset(0, -1).Interior.Color = xlNone 'Fond cellule pas de couleur
                    Cell.Offset(0, -1).Font.Color = RGB(0, 0, 0) 'Police en noir
                End If
'Si la valeur de la cellule est "Non fait", alors
                If Cell.Value = "Non fait" Then 'Si la valeur = "Non fait" alors
                    Cell.Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                    Cell.Font.Color = RGB(156, 0, 6) 'Police de couleur rouge

                    Cell.Offset(0, -1).Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                    Cell.Offset(0, -1).Font.Color = RGB(156, 0, 6) 'Police de couleur rouge
                End If
''Si la valeur de la cellule est "En service", alors
                If Cell.Value = "En service" Then  'Si la valeur = "En service" alors
                    Cell.Interior.Color = RGB(198, 239, 206) 'Arrière-plan de couleur vert clair
                    Cell.Font.Color = RGB(0, 97, 0) 'Police de couleur vert foncé
                End If
''Si la valeur de la cellule est "Hors service", alors
                If Cell.Value = "Hors service" Then  'Si la valeur = "Hors service" alors
                    Cell.Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                    Cell.Font.Color = RGB(156, 0, 6) 'Police de couleur rouge
                End If

            Next Cell
        Next Ws

End Sub

Ouverture immédiate.

Dit moi si tu rencontre un problème !

Le 14/07/2020 à 00:24

Bonjour à tous,

autre version qui ne lit pas les cellules unes par unes (très chronophage) :

Sub FormatColor()
    ' Fonction appelée par Call de Workbook et Feuil1

    'Définit une variable qui va représenter un classeur
    Dim Wb As Workbook
    'Définit une variable qui va représenter une feuille de calcul
    Dim Ws As Worksheet
    'Définit une variable qui va représenter une cellule
    Dim Cell As Range

    '***************
    Const plage As String = "A3:L29" ' voir si tu ne pourrais pas utiliser .UsedRange (plage utilisée)
    Dim datas, lig As Long, col As Long, c As Range

    'Boucle sur le classeur ' ****** non, là tu boucles sur tous les classeur ouverts !!!!!! *****
    'For Each Wb In Workbooks

    'Boucle sur chaque feuille de classeur
    For Each Ws In ThisWorkbook.Worksheets ' ThisWorkbook : feuilles de ce classeur ayant la macro
        'Boucle sur chaque cellule de la plage A3:L29
        '********************
        datas = Ws.Range(plage).Value
        'raz couleurs
        Application.ScreenUpdating = False
        Ws.Range(plage).Interior.ColorIndex = xlNone
        For lig = 1 To UBound(datas, 1)
            For col = 1 To UBound(datas, 2)
                Set c = Ws.Range(plage)(1).Offset(lig - 1, col - 1)
                Select Case datas(lig, col)
                    Case "Fait", "En service" 'Si la valeur = "Fait" ou "En service" alors
                        c.Interior.Color = RGB(198, 239, 206) 'Arrière-plan de couleur vert clair
                        c.Font.Color = RGB(0, 97, 0) 'Police de couleur vert foncé
                    Case "Non fait"  'Si la valeur = "Non fait" alors
                        c.Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                        c.Font.Color = RGB(156, 0, 6) 'Police de couleur rouge
                    Case "Hors service"   'Si la valeur = "Hors service" alors
                        c.Interior.Color = RGB(255, 199, 206) 'Arrière-plan de couleur rose
                        c.Font.Color = RGB(156, 0, 6) 'Police de couleur rouge
                End Select
            Next col
        Next lig
    Next Ws
    'Next Wb
End Sub

J'ai ajouté des commentaires à lire...
eric

Le 14/07/2020 à 00:31

Bonsoir @eriiic ,

Je ne connais que trop peu les Ubound / LBound, donc je ne m'aventure pas trop la dedans encore ...

Le fait de RAZ les couleurs est important, je n'y ai pas pensé.

Mais il y a aussi du code dans le workbook_open.

Et ce qui ralentissait grandement le programme était le lancement de FormatCellule a chaque cellule analysée... (Excel est "puissant" mais quand même )

Bonne soirée !

Le 14/07/2020 à 08:12

Bonjour,

Mais il y a aussi du code dans le workbook_open.

Ah oui, je n'avais pas fait attention.
Et bien même principe, lire en bloc dans une variable tableau qu'on parcourt ensuite avec 2 For.
Ca lui fera un exercice d'application
Pas de Select Case possible, conserver le If. Par contre remplacer les If suivants par des ElseIf (et supprimer les Endif devenus inutiles) et les mettre dans le bon ordre, ça fera toujours 1 seul test au lieu de 3.

eric

Le 14/07/2020 à 08:23

Bonjour Moul et Eriiic ainsi qu'aux autres lecteurs,
Merci pour vos réponses... Je vais privilégier la solution de Moul qui pour moi est la plus simple à comprendre et à modifier le cas échant, vu que je suis vraiment un parfait débutant en VBA.
Eriiic, ta solution est encore trop difficile pour moi, pour comprendre toutes les subtilités des termes employés genre les "UsedRange" et les "Ubound", etc. Mais, je vais m'y pencher de dessus.
Je découvre à chaque manipulation...
Merci encore à vous deux.
B.A.

Le 14/07/2020 à 10:21

Bonjour,

je t'encourage à approfondir.
Tout lire en une fois est 60 à 100 fois plus rapide que de multiples accès aux cellules.
Ne reste que la mise en couleur qui ne peut se faire qu'individuellement, mais que sur les cellules concernées.
eric

Rechercher des sujets similaires à "lenteur ouverture fichier"