Autres Lourdeur du fichier excel et lenteur du traitement macro : comment identifier les éléments générant ces lenteurs et lourdeurs et comment optimiser ?

Scoub · 9 Février 2022

Bonjour,

Etant novice en VBA et ayant l'habitude de traiter par étapes les formules excels, j'ai tenté de réaliser un fichier excel mixte entre formule excel et import de données/traitement dont des filtres par VBA.

Le fichier fait 72Mo en xlsm (41 onglets).

Cela inclut :

des imports de données de sources différentes via macro (simple copie coller de colonnes et une demande de sélectionner le fichier et l'onglet souhaité) (nb de lignes des imports pouvant évoluer)
des traitements via des formules excels (avec des formules figées) (sur 145 colonnes dans certains onglets pour construire une base de données regroupant les éléments tout en effectuant des calculs (certaines formules peuvent faire 4 lignes) et des mises en formes conditionnelles
des liens hyperlinks dans un sommaire pour accéder à des onglets au lieu de les chercher manuellement
des macro
- pour étendre des formules depuis la ligne 2 sur les différents imports
- pour effacer et reporter des données sur d'autres onglets (simple copie coller de colonnes)
- pour filtrer plusieurs onglets de base de données et de segment de TCD avec des choix dans un onglet "selection" (noms, catégorie, période dates ou date mini ou date max...) (choix non obligatoire) (macro par type de choix)
- pour affichage ou masquer des onglets suivant le choix de l'utilisateur
- pour bloquer ou activer les calculs (dans certaines macros) ou rafraichissement d'écran (dans certaines macros)
- pour actualiser les TCD
- lister les onglets
- enregistrer le fichier
- voire exporter certains onglet ( à améliorer pour éviter de transmettre des éléments non souhaités. par exemple, ne transmettre que les données Client A avec TCD au client A malgré que la base de données inclus toutes les infos...)

Etant donné que la réalisation a évolué, chaque élément a été fait l'un après l'autre sans optimisation.
Le nombre de ligne des imports et des traitements pouvant évoluer, les formules dans excel ont été inscrites dans le dur jusqu'à la ligne 60000 par sécurité.
Dans certains onglets les formules sont inscrites sur une zone plus importante de ligne que nécessaire en prévision de l'évolution des imports et de la liste de clients pouvant rallonger le nombre de ligne nécessaire.

Principales raisons :

actuellement, je ne sais pas transformer mes formules excels (pouvant être longue : >4 lignes sur 145 colonnes) en langage VBA et comment les imbriquer
- SOMME.SI.ENS
- MIN.SI.ENS
- SOMMEPROD
- SI
- MAX / MIN
- DROITE / GAUCHE
- RECHERCHEV
- INDEX / EQUIV
- (ET/OU)
- ESTVIDE / ESTERREUR...
  - il est surement possible d'optimiser ces formules
  - est-il préférable de répéter une formule sur différents onglets jusqu'à l'onglet source ou est-il préférable de reprendre le résultat de la formule du 1er onglet l'ayant calculé (effet cascade) pour gagner en rapidité ou/et pour réduire lourdeur du fichier ?
en VBA : non optimisée
- utilisation boucle IF ou FOR pour l'instant
- chaque macro construites séparément et récupérées par la fonction call dans les macros lançant le processus si besoin

Par peur de générer des soucis entre formules excel et macro VBA, je n'ai pas utilisé la fonction tableur d'excel pour les bases de données (mettre sous forme de tableau : Excel convertit automatiquement votre plage de données en tableau).

Est-ce que l'import de données VBA dans un tableau excel automatique fonctionne correctement ?
Est-ce que le mixte tableau excel, formule excel et macro VBA font bon ménage ?
Est-ce que cela permet de réduire le volume du fichier ?

Pourriez-vous me dire quels sont les éléments qu'il est préférable d'éviter car consomme beaucoup les capacités excels/vba svp ? et comment les remplacer ?

Quelles sont les pistes pour optimiser un fichier excel / vba ? (réduire son volume, améliorer le délai de traitement...)

Merci d'avance pour vos lumières,
Cordialement,
Scoub

sylvanu · 9 Février 2022

Bonjour Scoub,
Sans fichier test, comment voulez vous avoir une réponse pertinente ?
Il y a tellement de possibilités que ça rame !

Comme vous avez un tas de formules, j'espère que votre macro fige l'écran et se met en calcul manuel, sinon ceci pourrait expliquer cela.

VB:

Début:
Sub xxx
   Application.ScreenUpdating = false
   Application.Calculation = xlCalculationManual
  
Fin:
   Application.ScreenUpdating = true
   Application.Calculation = xlCalculationAutomatic
End Sub

Ensuite évitez les lectures/écritures cellules ça prend beaucoup de temps, passer plutôt par des transferts par arrays.
Evitez aussi les transferts par boucle comme For Next, faites directement des transferts Plage --> Array et Array -> Plage.

Scoub à dit:
Est-ce que l'import de données VBA dans un tableau excel automatique fonctionne correctement ?

La méthode fonctionne. Votre code peut être oui, peut être non. Tout dépend comment il est écrit.

Scoub à dit:
Est-ce que le mixte tableau excel, formule excel et macro VBA font bon ménage ?

Même réponse. Un mix formules et VBA sont ( heureusement ) compatible.

Scoub à dit:
Est-ce que cela permet de réduire le volume du fichier ?

Si toutes les formules sont en VBA et que toutes les cellules ne comportent que des valeurs, alors la taille sera plus faible qu'avec l'équivalent tout en formules.

Pour plus de précisions ... soyez plus précis.

Phil69970 · 9 Février 2022

Bonjour à tous

@Scoub

1)Tout à fait d'accord avec Sylvain

2)Perso j'utilise ce module (que j'avais récupéré sur le net revu un peu à ma sauce) pour faire faire une cure d'amaigrissement à mes fichiers excel

A mettre impérativement dans un module ET faire une copie AVANT du fichier excel

A chaque étape il y a des messages ou tu peux voir l'avancement de chaque feuille
==> *Si tu as 41 onglets tu vas valider 41 messages

*Perso je l'associe à un contrôle de date et quand la date du jour dépasse de 120 jours la date enregistrée dans une cellule spécifique le nettoyage se lance (je l'ai désactivée dans le code ci dessous)

VB:

Sub Nettoie()

Application.EnableEvents = False ' => Desactive les événements

Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String, Avant As Double, Plage As Range
On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de recalcul

Dim Reponse As Byte
'Reponse = MsgBox("L'optimisation du fichier XXX date de plus de 120 jours, une optimisation pourrait être utile ...", 305, "Optimisation requise")
'If Reponse = 2 Then Exit Sub

Reponse = MsgBox("Pour le classeur actif : " & Chr(10) & ActiveWorkbook.FullName & Chr(10) & "dans chaque feuille (onglet)" _
& Chr(10) & "recherche de la zone contenant des données," & Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", 305, "Lancement de l'optimisation") 'vbOKCancel) 'vbInformation

If Reponse = 2 Then Exit Sub

MsgBox "Taille initiale de ce classeur en octets" & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
Dim T1 As Long
T1 = FileLen(ActiveWorkbook.FullName)

With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With

'Le traitement
For Each Sht In Worksheets
Avant = Sht.UsedRange.Cells.Count
Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address

'Traitement de la zone trouvée
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)

'Suppression des lignes inutilisées
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
Set DCell = Nothing
Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)

'Suppression des colonnes inutilisées
If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
End If
Rien = Sht.UsedRange.Address
End If
ActiveWorkbook.Save

'Message pour la feuille traitée
MsgBox "Nom de la feuille de calcul :" & Chr(10) & Sht.Name _
& Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", _
vbInformation, ActiveWorkbook.FullName
Next Sht

'Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
Dim T2 As Long
T2 = FileLen(ActiveWorkbook.FullName)
MsgBox "Taille avant optimisation :" & T1 & " octets" & Chr(10) & "Taille apres optimisation :" & T2 & " octets" & Chr(10) _
& Chr(10) & "Gain de " & T1 - T2 & " octets"

Application.StatusBar = False
Application.Calculation = Calc

'Sheets("Nom_RH").Range("j1").Value = Date

Application.EnableEvents = True ' => Active les événements

End Sub

@Phil69970

Scoub · 9 Février 2022

Merci Sylvanu

même si cela semble des évidences et très général, cela me donne des pistes :

sylvanu à dit:
Comme vous avez un tas de formules, j'espère que votre macro fige l'écran et se met en calcul manuel, sinon ceci pourrait expliquer cela.

VB:

Début: Sub xxx Application.ScreenUpdating = false Application.Calculation = xlCalculationManual Fin: Application.ScreenUpdating = true Application.Calculation = xlCalculationAutomatic End Sub

Ensuite évitez les lectures/écritures cellules ça prend beaucoup de temps, passer plutôt par des transferts par arrays.
Evitez aussi les transferts par boucle comme For Next, faites directement des transferts Plage --> Array et Array -> Plage.

En effet, j'ai utilisé pour certaines macro l'arrêt du calcul automatique et pour d'autre figer l'écran....
je dois généraliser cette utilisation et peut-être vérifier que l'activation/déactivation ne se fasse pas dans une macro qui est utilisée à l'intérieure d'une autre...

Concernant les plages, j'ai utilisé la copie par zone ("range (A1:G20000)" ou range(A:G) par exemple) pour étendre les formules ou copier des colonnes.

Je ne connais pas la fonction "Arrays" comment l'utiliser ?

J'ai utilisé FOR NEXT pour compter les éléments du segments date (de plusieurs onglets) afin de réaliser le filtre via une saisie sur un onglet de sélection (technique puisé sur le net).

Concernant l'utilisation du format tableau automatique d'excel, est-ce un réflexe à utiliser en permanence dès le traitement de base de données tant que tout n'est pas traité 100% en VBA ? (gain de volume et rapidité de traitement)

Merci pour ces précieuses informations

Je vais revoir mes formats conditionnées et ces éléments dans un premier temps avant de revenir avec des précisions (ne sachant pas trop par où commencer et comment présenter un fichier test sans données sauf peut-être fichier bas des macro ).

Scoub · 9 Février 2022

Bonjour @Phil69970

Phil69970 à dit:
1)Tout à fait d'accord avec Sylvain

2)Perso j'utilise ce module (que j'avais récupéré sur le net revu un peu à ma sauce) pour faire faire une cure d'amaigrissement à mes fichiers excel

Pour le point 1 je ne peux être que d'accord avec vous 2

pour le point 2 : merci pour cet outil
si j'ai bien compris les commentaires, ce module permet de supprimer les lignes et colonnes non utilisées.
Est-ce qu'il permet de faire autre chose ?

Etant donné que j'ai des formules excel du style Somme.si.ens(A2:A60000;C2:C60000;"ClientA"), si les lignes de 40000 à 60000 ne sont pas utilisées, cela me changera les formules.
Lors du prochain chargement si les lignes utilisées vont jusqu'à la ligne 45000, la formule ne récupèrera pas les 5000 lignes supplémentaires en l'état sauf si je transforme ma formule excel en vba ou avec une autre technique permettant de s'adapter à la longueur des lignes. Ai-je le bon raisonnement ?
Comment adapter les formules excels en automatiques afin d'éviter ce soucis svp?

Dans tons les cas, cet outils me sera utile
Merci beaucoup

Phil69970 · 9 Février 2022

Bonjour @Scoub

Scoub à dit:
si j'ai bien compris les commentaires, ce module permet de supprimer les lignes et colonnes non utilisées.
Est-ce qu'il permet de faire autre chose ?

Non il ne fait rien d'autre mais très souvent le résultat est très spectatculaire, je ne serais pas surpris si la taille de ton fichier perd 20 à 30 % de sa taille.

N'oublie pas avant :

A mettre impérativement dans un module ET faire une copie AVANT du fichier excel

Donc tu fais le test et tu as une copie en cas de pb (perso cela ne m'est jamais arrivé)

@Phil69970

Scoub · 11 Février 2022

Phil69970 à dit:
Donc tu fais le test et tu as une copie en cas de pb (perso cela ne m'est jamais arrivé)

Bonjour @Phil69970

J'ai fait le test...
le programme a tourné une journée/nuit presque (41 onglets oups)... j'ai peut-être soucis de lenteur dans mon fichier

Malheureusement le résultat ne correspond pas à ce que j'aurais souhaité :

Et étant donné que mes formules incluent des cases de lignes vides en dur et que des lignes vides sont supprimées, cela a modifié mes formules (d'où surement l'alourdissement du fichier) et changé certains format de tableau étant donné qu'il s'agissait que d'esthétisme (cases colorées sans valeur pour encadrer des tableaux.

Le résultat est surement dû à ma construction du fichier.

D'un autre côté, j'ai surtout noté les onglets que le programme avait réduit (11 sur 41)... et j'ai été surpris pour certains onglets.
Par exemple un onglet avait indiqué 20.38% de la taille initiale (et pourtant il n'y a qu'un TCD sur cet onglet...) (et c'est un TCD récupéré dans un fichier d'un collègue)

J'ai donc tenté d'ajuster ton programme pour pouvoir sélection le traitement sur 1 onglet spécifique.
Pour tenter de profiter de ton programme sur les onglets qui n'impacteraient pas mes formules en l'état.

J'ai fait le test de lancer ton programme modifié sur cet onglet... résultat gain de 8952octets uniquement sur un onglet TCD .
D'accord, par rapport à la lourdeur de mon fichier, ce n'est pas beaucoup mais cela donne une piste de réduction.

Je vais peut-être détourner un peu ton programme pour pouvoir l'appliquer onglet par onglet et tenter de bloquer les éléments qui impacte mes formules de calculs (ligne mini ou colonne mini)....

En étudiant ton programme :
Je n'ai pas l'impression de retrouver tous les End if dans ton programme. Est-ce obligatoire d'avoir End If pour chaque If ?

Phil69970 à dit:
If Reponse = 2 Then Exit Sub

pas de end if dans ce cas ?

Phil69970 à dit:
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)

idem pour celui-ci ?

Ensuite, afin de pouvoir utiliser ton programme sans altérer mes formules, j'ai tenté de modifier ton programme en donnant la possibilité :

de sélection 1 onglet spécifique et non tout le fichier
voire de bloquer les lignes ou les colonnes à ne pas toucher quand on choisi un onglet spécifique
car je sais que mes formules incluent des lignes ou colonnes vide en prévision d'évolution des imports.
- l'autre possibilité serait manuelle :
  - ne pas modifier les limites de lignes/colonnes dans le programme
  - et que je marque temporairement une cellule dans la ligne/colonne maximale pour laquelle je ne veux pas que le programme supprime pour ne pas affecter mes formules

Cela me permettrait de profiter de ton outils pour mon fichier le temps que j'arrive à corriger les formules pour qu'elles s'adaptent automatiquement à l'évolution des lignes en imports qui peuvent fluctuer.

Pourrais-tu me dire ce que tu penses de cette modification :
Merci d'avance,

VB:

Sub Nettoie()

Application.EnableEvents = False ' => Desactive les événements

Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String, Avant As Double, Plage As Range

Dim ChoixOnglet As String 'ajout pour sélectionner 1 onglet spécifique

Dim LigneMIN As Variant, ColonneMIN As Variant 'ajout pour sélectionner 1 onglet spécifique
Dim NBLigneMIN As Integer, NBColonneMIN As Integer 'ajout pour sélectionner 1 onglet spécifique


On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de recalcul

Dim Reponse As Byte
'Reponse = MsgBox("L'optimisation du fichier XXX date de plus de 120 jours, une optimisation pourrait être utile ...", 305, "Optimisation requise")
'If Reponse = 2 Then Exit Sub

Reponse = MsgBox("Pour le classeur actif : " & Chr(10) & ActiveWorkbook.FullName & Chr(10) & "dans chaque feuille (onglet)" _
& Chr(10) & "recherche de la zone contenant des données," & Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", 305, "Lancement de l'optimisation") 'vbOKCancel) 'vbInformation

If Reponse = 2 Then Exit Sub

MsgBox "Taille initiale de ce classeur en octets" & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
Dim T1 As Long
T1 = FileLen(ActiveWorkbook.FullName)

With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With

'Ajout choix traitement 1 onglet spécifique ou tout le classeur

ChoixOnglet = InputBox("Quels onglets traiter ?", "Tous ou un seul spécifique", "Tous") 'choix traitement tous les onglets par défaut avec possibilité de choisir 1 seul

If ChoixOnglet = "Tous" Then

    'Le traitement pour tous les onglets
    For Each Sht In Worksheets
        Avant = Sht.UsedRange.Cells.Count
        Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
      
        'Traitement de la zone trouvée
        If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
        Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
      
        'Suppression des lignes inutilisées
        If Not DCell Is Nothing Then
        Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
        Set DCell = Nothing
        Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
      
        'Suppression des colonnes inutilisées
        If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
        End If
        Rien = Sht.UsedRange.Address
        End If
        ActiveWorkbook.Save
      
        'Message pour la feuille traitée
        MsgBox "Nom de la feuille de calcul :" & Chr(10) & Sht.Name _
        & Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", _
        vbInformation, ActiveWorkbook.FullName
    Next Sht
Else
'Ajout choix traitement 1 onglet spécifique ou tout le classeur

LigneMIN = InputBox("Besoin de limiter les lignes à ne pas réduire(impact formule de calcul) ?", "Non ou numéro ligne à ne pas réduire", "Non") 'permet de ne pas réduire en dessous de cette ligne (impact sur formule)

ColonneMIN = InputBox("Besoin de limiter les lignes à ne pas réduire(impact formule de calcul) ?", "Non ou numéro ligne à ne pas réduire", "Non") 'permet de ne pas réduire en dessous de cette colonne (impact sur formule)

        'Le traitement pour un onglet spécifique
        Sht = ChoixOnglet
            Avant = Sht.UsedRange.Cells.Count
            Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
          
            'Traitement de la zone trouvée
            If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
            Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
          
            'Suppression des lignes inutilisées
            If Not DCell Is Nothing Then
                If Not LigneMIN = "Non" Then 'ajout limiter les lignes à supprimer
                    NBLigneMIN = LigneMIN
                    Sht.Range(Sht.Cells(NBLigneMIN, 1), Sht.Cells([A:A].Count, 1)).EntireRow.Delete
                Else
                    Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
                End If
                Set DCell = Nothing
                Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
              
                'Suppression des colonnes inutilisées
                    If Not DCell Is Nothing Then
                        If Not ColonneMIN = "Non" Then 'ajout limiter les colonnes à supprimer
                            NBColonneMIN = ColonneMIN
                            Sht.Range(Sht.Cells(1, NBColonneMIN), Sht.[IV1]).EntireColumn.Delete
                        Else
                            Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
                        End If
                    End If
                Rien = Sht.UsedRange.Address
            End If
            ActiveWorkbook.Save
          
            'Message pour la feuille traitée
            MsgBox "Nom de la feuille de calcul :" & Chr(10) & Sht.Name _
            & Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", _
            vbInformation, ActiveWorkbook.FullName
            End If
  



End If

'Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
Dim T2 As Long
T2 = FileLen(ActiveWorkbook.FullName)
MsgBox "Taille avant optimisation :" & T1 & " octets" & Chr(10) & "Taille apres optimisation :" & T2 & " octets" & Chr(10) _
& Chr(10) & "Gain de " & T1 - T2 & " octets"

Application.StatusBar = False
Application.Calculation = Calc

'Sheets("Nom_RH").Range("j1").Value = Date

Application.EnableEvents = True ' => Active les événements

End Sub

Cordialement,
Scoub

PS : bref j'ai une grande marge de progression dans l'organisation de mon fichier et avoir des réflexes de traitement pour ne pas ralentir/alourdir un fichier lors de sa construction

Phil69970 · 11 Février 2022

Bonjour @Scoub

1)

Scoub à dit:
En étudiant ton programme :
Je n'ai pas l'impression de retrouver tous les End if dans ton programme. Est-ce obligatoire d'avoir End If pour chaque If ?

Plutôt que de refaire une grande explication je te renvoie sur l'excellente explication de @patricktoulon
dans le post ==> https://www.excel-downloads.com/threads/if-then-else-jai-pas-mes-lunettes.20064572/#post-20487862

2)

Scoub à dit:
Ensuite, afin de pouvoir utiliser ton programme sans altérer mes formules, j'ai tenté de modifier ton programme en donnant la possibilité :

Précision ce n'est pas mon programme.

Phil69970 à dit:
Perso j'utilise ce module (que j'avais récupéré sur le net revu un peu à ma sauce) pour faire faire une cure d'amaigrissement à mes fichiers excel

j'ai regardé oui apparemment tu peux cibler 1 onglet en particulier mais le but n'est pas pour sans servir tous les jours mais pour donner une cure de jouvence à un fichier qui mois apres mois prend de l'embonpoint

3)

Scoub à dit:
des mises en formes conditionnelles

Combien ton fichier à de MFC car cela peut causer des ralentissements notamment à l'ouverture de ton fichier

4)

Scoub à dit:
des traitements via des formules excels (avec des formules figées) (sur 145 colonnes dans certains onglets pour construire une base de données regroupant les éléments tout en effectuant des calculs (certaines formules peuvent faire 4 lignes)

A) Des formules sur 4 lignes cela me parait beaucoup ==> ne peut on pas réduire la formule ?
B) Excel est il bien approprier pour faire tout ça et/ou ne peux tu pas couper le fichier en X morceaux ?

5)

Scoub à dit:
Le nombre de ligne des imports et des traitements pouvant évoluer, les formules dans excel ont été inscrites dans le dur jusqu'à la ligne 60000 par sécurité.

Par VBA on peut calculer le nombre de ligne en dynamique et ajuster à tout moment la longueur des lignes au strict nécessaire

6)

Scoub à dit:
Le fichier fait 72Mo en xlsm (41 onglets).

Si j'ai suivi 60 000 lignes max et par sécurité * 41 onglets = 2 460 000 lignes si tous les onglets on 60 000 lignes.
Une rationalisation des onglets et des données pourrait permettre de diminuer fortement ce nombre
Exemple prévoir 1 000 de ...rab peut être mais si c'est 10 000 cela me semble excessif ..... etc....
Et 10 000 * 41 (onglets)= 410 000 lignes de moins à gérer soit ~ 17 % de lignes en moins ....
etc...

Ton fichier met combien de temps à s'ouvrir le matin ?

@Phil69970

patricktoulon · 11 Février 2022

re
bonsoir
72 mega
ouip je rigole

surtout quand on sait que le system alloue a office excel 2 giga de memoire pour fonctionner et pas tout a fait 4 pour les version 64 bit

Attendant voir
donc la mémoire représente 18% du poids du fichier il faut donc réduire les lignes de 82%
oui c'est ça on est bons
en leve un peu des bordures aussi pour alléger

TooFatBoy · 12 Février 2022

Bonjour,

Pour moi, la multiplication des MFC est LE problème de ralentissement sous Excel.
Donc commencer par voir s'il n'y a pas des milliers de MFC suites à des copier/coller.

En suite, peut-être voir si on peut utiliser des tableaux structurés. Leur taille s'adapte automatiquement en fonction du besoin, et les formules, les MFC, et toutes les mises en forme se recopient automatiquement dans la totalité du tableau.

Enfin, remplacer les formules par leur résultat peut faire gagner pas mal de temps, surtout s'il y a des formules matricielles.

Autres Lourdeur du fichier excel et lenteur du traitement macro : comment identifier les éléments générant ces lenteurs et lourdeurs et comment optimiser ?

Scoub

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Phil69970

XLDnaute Barbatruc

Scoub

XLDnaute Nouveau

Scoub

XLDnaute Nouveau

Phil69970

XLDnaute Barbatruc

Scoub

XLDnaute Nouveau

Pièces jointes

Phil69970

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

Discussions similaires