Discussion :

[Patrice740]

Je cherche de l'aide sur le message d'erreur Excel : Nombre de formats de cellule différents trop élevé (avec Excel 2002 et Excel 2003)

Depuis 8 mois, je jongle avec cette erreur. Je travaille sur un fichier Excel de taille importante : 6 Mo, 260 feuilles, environ 2500 liens hypertexte internes, des dizaines de milliers de formules complexes, beaucoup de formats conditionnels mais aucune macro et aucune liaison externe.

Pour mettre à jour ce fichier j'utilise une macro complémentaire personnelle (longue mais assez simple) qui permet entre autres de régénérer et d'harmoniser les formats utilisés. Cette macro traite au choix, soit une seule feuille de calcul, soit à l'aide d'une boucle, la totalité des feuilles du classeur. C'est en général dans ce dernier cas que l'erreur se produit.

Je précise tout de suite que mon niveau en Visual Basic pour Application est plutôt moyen.

Je ne peux pas scinder mon fichier en plusieurs fichiers plus petits pour éviter cette erreur. Ce fichier est justement le résultat de la fusion de plusieurs fichiers élémentaires qui posaient d'autres problèmes (nombre de liaisons externes considérable, complexité des formules, les temps de calculs excessifs, ...).

Au début, j'ai appliqué les recommandations trouvées sur le site Support Microsoft: j'ai réduit au strict minimum le nombre de formats différents, notamment en utilisant l'excellent utilitaire gratuit MZTools.

Ensuite je me suis inspiré de cet utilitaire et de la macro "xlsclean" de Microsoft Download Center pour développer une macro un peu plus complète qui liste et localise les principaux formats utilisés dans mon fichier : formats des nombres, des polices et des motifs. Je peux alors éliminer les formats superflus. A chaque fois, je repousse pour quelque temps l'apparition de l'erreur.

Aujourd'hui, malgré toutes les simplifications que j'ai pu apporter à mon classeur Excel pour le faire survivre, l'erreur continue de se produire de façon épisodique. Je suis dans une impasse mais j'ai acquis quelques connaissances sur cette erreur :

• Elle est liée à tous les formats définissables avec la boite de dialogue "format de cellule" et uniquement à ceux-là (en Visual Basic, tous les formats de cellule qui sont des propriétés de l'objet CellFormat). La quantité de formats différents correspond au nombre de combinaisons utilisées.
  
• D'après Microsoft ce nombre de formats différents est limité à environ 4000. Dans mon fichier il y a 19 formats de nombre, 98 formats de police (police, taille, couleur, type) et 24 formats de motif (couleur de fond, motif, couleur de motif), tous différents. Je n'ai pas comptabilisé les nombreuses autres combinaisons (bordures, alignements, protection) qui sont utilisées car je suis obligé de les conserver. Je constate que la "limite" des 4000 combinaisons est trés très largement dépassée! (19 x 98 x 24 x ...)
  
• Cette erreur ne semble pas directement liée au nombre de formats utilisés dans le fichier mais plutôt au nombre de formats différents qui ont été modifiés depuis que l'on a lancé l'application Excel. Il se produit un genre d'effet cumulatif et c'est souvent une sorte de saturation qui finit par provoquer l'erreur.
  
• L'insertion de ligne(s) à l'intérieur d'une plage contenant déjà de nombreux formats provoque l'erreur alors que cette opération ne crée pas de format supplémentaire. Dans les macros, le problème arrive souvent lors du changement de format d'une cellule alors que ce format est déjà utilisé dans le classeur.
  
• Dès que l'erreur s'est produite, on ne peut plus ajouter d'autre format, même si auparavant on en a supprimé quelques uns de façon à libérer de la place pour les nouveaux. Après avoir supprimé les formats, il faut enregistrer le fichier, tout fermer et surtout arrêter Excel. Lorsque l'on relance Excel, on peut alors utiliser cette place libérée pour ajouter d'autres formats en prenant soin d'enregistrer fréquemment avant que l'erreur ne réapparaisse.

Cette analyse me fait penser qu'Excel "oublie" de remettre à zéro une sorte de buffer ou de pile interne. Mes connaissances du VBA et d'Excel sont trop élémentaires pour me permettre de trouver une solution. Mes recherches sur le net francophone sont épuisées (je suis malheureusement pas très doué pour la langue anglaise).

Si quelqu'un a une idée ou une piste, elle sera la bienvenue.
Merci d'avance.

[helios77]

Moi ce qui me parait clair, le problème vient de ton nombre de feuilles
Tu en as 260 tu dis ??
Bizarre, il me semblait que c'était limité à 256

N'y a-t-il pas des msies en forme dont tu pourrais te passer par hasard ??

Pourquoi autant de feuilles ?t'as pas moyen de faire un peu le ménage ou de faire des archives ?
Les 260 feuilles sont constamment remsies à jour ?
L'appli a été quand, comment et par qui ?
C'est un peu du travail de cochon de faire çà......

Les liens entre fichiers fonctionnent très bien du moment que le lien ne fait pas appell à un fichier inexistant ou un fichier sur un serveur inaccessible

[Invité]

Bonjour,

Moi ce qui me parait clair, le problème vient de ton nombre de feuilles
Tu en as 260 tu dis ??
Bizarre, il me semblait que c'était limité à 256

Le nombre de feuilles est limité à la capacité de l'ordinateur (voir spécifications dans l'aide Excel).

Patrice740 : Par curiosité que fait tu avec toutes ces feuilles, et tout ces formats ?

Starec

[helios77]

Merci pou' l'info
on m'avait toujours dit que Excel était limité à 2^8 feuilles = 256 (comme pour les colonnes)

Je le saurais pour une prochaine fois

[ouskel'n'or]

A défaut d'une solution plus sioux, j'essaierais de chercher "ailleurs"
Une question : Quand l'erreur se produit, cela provoque-t-il un appel au débugger (ligne de code surlignée en jaune) ? Ou ça plante Excel ?
Si oui (pour la ligne jaune...) tu gères l'erreur avec un on error goto Erreur::
Et dans Erreur:: tu enregistres ton fichier, et sur les lignes suivantes, tu mets simplement

Code :

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

1
2
3
DoEvents
Err.clear
Resume

La macro reprendra sur la ligne ayant provoqué l'erreur.
Tu peux au moins essayer.

[alsimbad]

Je suppose que tu les connais déja, mais je te les mets quand même
http://www.developpez.net/forums/arc...p/t-63521.html
http://support.microsoft.com/kb/213904/fr
pourquoi es ce si difficile de réduire le nimbre de format de ton ton classeur.
je veux bien croire que ta mise en forme soit importante, mais quelques action sont surement possible comme :
harmoniser la police ne demande qu'un petite boucle de code, et ne change surement pas grand chose
reprendre les principaux tableaux, les selectionner et supprimer l'encadrement, puis le remettre. cela évite les célulle ou chacune a été encadrée, et pour lequel excel comptabilise les bordure gauche sur une cellule et droite sur la cellule adjacente.
également, il peut etre interessant de sélectionner l'ensemble des ligne et colonne vide et de les supprimer car les formats restent même si la cellule est vide. je ne parle pas des cellules de tes tableaux mais celle a droite et au dessous de tes tableaux.
et puis il y a surement a creuser

[Currahee]

Bonjour,

Petite question complémentaire à ce vieux sujet : comment connaître simplement le nombre de formats utilisés dans un classeur donné ?

Merci d'avance pour vos réponses,

Cordialement,

Currahee

[Currahee]

Bon ma question n'inspire pas grand monde, tant pis et faisons confiance aux logiciels existants...

http://xlsgenreduction.arstdesign.com/index_fr.html