FichierObèse-Commentaire-procédure-Laurent Longre
Je crois que dans la plupart des cas, les classeurs qui font de la
surcharge pondérale à l'insu de leur plein gré sont victimes du
phénomène suivant:
- Crée un classeur tout neuf, avec 5 feuilles vides.
- Sélectionne toutes les feuilles, et mets le nombre 1 dans leur cellule
A1
- Enregistre le classeur => sa taille devrait être de 20 K.
- Maintenant, toujours avec les 5 feuilles sélectionnées, va sur la
cellule A65536 et mets sa police par exemple en couleur rouge.
- Enregistre le classeur => sa taille est maintenant de 142 K.
Multipliée par 7.
Comme la surcharge pondérale est dûe à une seule cellule, qui plus est
ici "invisible" (elle ne contient rien du tout, seul son format est
modifié), difficile de voir a priori par quoi elle est causée.
La solution ? Utiliser cette macro :
Sub Nettoie()
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String
On Error Resume Next
Calc = Application.Calculation
With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = False
End With
For Each Sht In Worksheets
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Clear
Set DCell = Nothing
Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(,2)
If Not DCell Is Nothing Then _
Sht.Range(DCell, Sht.[IV1]).EntireColumn.Clear
End If
Rien = Sht.UsedRange.Address
End If
Next Sht
Application.StatusBar = False
Application.Calculation = Calc
End Sub
Maintenant, pourquoi ce phénomène ? Parce qu'Excel stocke les données
des cellules sous la forme suivante:
- Pour chaque feuille, il mémorise les numéros de ligne de la première
cellule et de la dernière cellule utilisées, au sens large (cellule
contenant une valeur ou ayant un format particulier).
- Toutes les lignes se situant entre ces deux cellules sont divisées en
blocs de 32 lignes maxi.
- L'adresse de départ de chacun de ces blocs est mémorisée dans un
enregistrement dénommé INDEX. Ces adresses sont stockées sur 4 octets.
- Chaque bloc comprend au moins un enregistrement (DBCELL) de taille
variable contenant les adresses des enregistrements décrivant les lignes
de données (ROW) et les contenus des cellules (champs RK, ARRAY, BLANK,
BOOLERR, FORMULA, NUMBER, MULRK etc selon le type de valeur). Si le bloc
ne contient rien, cad si les 32 lignes sont complètement vides,
l'enregistrement DBCELL correspondant fait une taille de 8 caractères
(descripteur d'enregistrement + adresse du premier bloc suivant).
Je vais prendre l'exemple d'une feuille contenant une valeur (ou
simplement un format particulier) en A1, une autre en A65536, et à part
ça du vide total, pour déduire par calcul la taille nécessitée:
- Nombre de lignes de la plage "utile" : 65536
- Nombre de blocs de 32 lignes :
65536 / 32 = 2048 blocs
- Stockage des adresses (4 octets) de ces 2048 blocs dans
l'enregistrement INDEX:
2048 * 4 = 8 192 octets
- Stockage des enregistrements DBCELL des 2048 blocs (8 octets chacun):
2048 * 8 = 16 384 octets
- Total nécessaire : 8192 + 16384 = 24 576 octets, soit 24 K.
Toutes les lignes vides entre A1 et A65536 vont donc entraîner une
surcharge pondérale d'environ 24 K.
Si je reprends mon premier exemple (les 5 feuilles), le compte est bon:
classeur sans lignes vides = 20 K, classeur avec cellule A65536 remplie
= 142 K, différence = 122 K, divisée par 5 feuilles = 24,4 K.
Conclusion : quand la taille du classeur paraît démseurée, il est
impératif de vérifier au moins qu'il ne traîne aucune cellule isolée
quelque-part très bas dans une des feuilles de calcul, contenant soit
une valeur, soit un format particulier. Et de supprimer le cas échéant
toutes les lignes qui vont de cette cellule à la dernière ligne
réellement utilisée dans la feuille. Ce que fait automatiquement la
macro ci-dessus, sur l'ensemble du classeur.
C'est une raison possible d'obésité parmi d'autres, il y en a d'autres
possibles que je détaillerai peut-être dans un prochain chapitre. ;-)
Laurent