[VBA-E]Nombre de formats de cellule différents trop élevé

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.

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

Bonjour,

Citation:

---

Envoyé par 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

---

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

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

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:

---

1 2 3

DoEvents Err.clear Resume

---

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

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

Ouskel'n'or : Effectivement le débogueur me rend la main lors de l'erreur (ligne surlignée en jaune).

Je peux alors terminer ma macro d'erreur en erreur en ignorant toutes les mises en forme qui provoquent l'erreur.

Ton idée de gérer l'erreur est intéressante mais je n'arrive pas au résultat espéré.

Je me heurte toujours au problème de la réinitialisation d'Excel :

• si j'enregistre le fichier lorsque l'erreur s'est déjà produite, à la réouverture, l'erreur se reproduit immédiatement dès les premières modifications de format d'où mon sentiment d'un effet d'accumulation.
  
• Dans tous les cas il faut anticiper et enregistrer avant que l'erreur ne se déclenche.
  
• Il faudrait pouvoir arrêter Excel sans interrompre la macro. Est-ce possible ?
  
• Sinon, comment réinitialiser Excel sans l'arrêter ?

Je précise à Alsimbad qu'effectivement sa méthode permet de diminuer de façon conséquente le nombre de formats, car il reste en général beaucoup de formats "invisibles". C'est une des premières solutions que j'ai appliqué et à l'époque elle m'avait permis d'obtenir un sursis très appréciable.

Comme je le disais précédemment ce n'est pas vraiment le nombre de formats existants dans le classeur qui provoque l'erreur mais plutôt le nombre de format que l'on a modifié dans le classeur depuis l'ouverture d'Excel.

Pour les curieux, il s’agit d’une application de « Dialogue de gestion » et plus précisément du Tableau de bord d’une grosse entreprise dont je ne suis qu'un modeste employé. Je reçois des informations de tous les principaux intervenants, je les centralise dans ce fichier pour les synthétiser et les présenter, d’où le nombre de feuilles, surtout le très grand nombre de calculs et un nombre de formats irréductible. Il n’y a aucune feuille inutile et toutes les feuilles sont liées par des calculs plus ou moins complexes (statistiques, matrices…)
Il y a certainement des logiciels mieux adaptés qu’Excel pour ce genre d’application mais je fais avec les moyens dont je dispose (surtout ne me parlez pas de Powerpoint, pour les liaisons avec Excel c’est pas top et pour les calculs on fait mieux !).

Pour répondre aux affirmations Helios77, les cochons trouvent que les calculs effectués par des liaisons vers des fichiers externes (même ouverts) fonctionnent nettement moins bien que ceux effectués entre les feuilles d’un même classeur, en particulier au niveau de la rapidité de calcul. S’il connait une solution pour obtenir des temps de calculs équivalents, nous sommes preneurs.

Citation:

---

si j'enregistre le fichier lorsque l'erreur s'est déjà produite, à la réouverture, l'erreur se reproduit immédiatement dès les premières modifications de format d'où mon sentiment d'un effet d'accumulation.

---

Essaie en mettant un DoEvents après la ligne d'enregistrement.
Cela donne le temps au système d'enregistrer le fichier avant que ne soit relancée l'instruction sur laquelle le programme a buggé. Sinon, le programme se poursuit avant la fin de l'enregistrement.
Tu fais un boucle

Code:

---

1 2 3

For z = 1 to 10000 '100000... si ça ne suffit pas DoEvents Next

---

SilkyRoad a un syntaxe plus sioux mais je l'ai oubliée. Consiste à vérifier que l'enregistrement soit bien terminé.
A+ et bonne chance.
Tiens-nous au courant, si le bug se perpétue, autant le savoir ;)

Merci à tous,
l'enregistrement du fichier s'effectue pendant la boucle mais excel refuse toujours de formater des cellules avec des formats existants déjà, dans ce cas un Resume Next dans la gestion d'erreur me permet de terminer la macro. C'est pas top.

J'ai du mal à identifier précisement la manière dont Excel totalise le nombre de formats utilisés. D'après [URL="http://support.microsoft.com/kb/213904/fr"] il faut comptabiliser les polices, bordures,motifs de cellule, mises en forme des nombres, alignements, protection, mais dans ce cas j'arrive à des quantités énormes qui dépassent largement les 4000 formats. Certaines propriètès n'entrent probablement pas dans les comptes mais j'ai du mal à savoir lesquellles.

J'ai constaté que pour certaines formules (par exemple avec des dates) Excel affecté automatiquement un format numérique (ex : date) à la place du format "standard". Celà compte pour un format supplémentaire même si on remet la cellule au format standard.

Finalement je me suis résolu à économiser des formats de police, en supprimant 2 tailles, j'ai retrouvé une petite marge de manoeuvre.

Bonjour Patrice740,

J'ajouterai à ce qui a déjà été dit :

faites des sauvegardes régulières de votre fichier, car le gros risque est qu'un jour vous ne puissiez même plus l'ouvrir.

J'ai eu l'occasion de dépanner plusieurs entreprises dont une de distribution (70 magasins dont 10 à l'étranger) qui à ma grande surprise utilisait Excel pour enregistrer les données du bilan, du compte de résultat et des tableaux annexes
avec un classeur de consolidation et encore là il n'était pas question de nombreux formats mais comme vous le dîtes aussi de plusieurs dizaines de milliers de formules par feuilles.

A toutes fins utiles.

salut.
je lance l'idée en l'air, mais as tu éliminé les options liés a ton ordinateur proprement dit. le fichier a t'il le même comportement sur une autre bécane?. désinstallé et réinstallé excel? nettoyé les fichiers temps de l'ordi? plus (+) de pannes si une autre apli tourne en même temps? si le probleme viens d'une sorte de mémoire qui serais saturée, créer une macro de format avec un fichier plus léger et voir après la panne si elle bloque aussi. certaines bécanes ont des comportements un peu stranges. la mienne par exemple bloque systemetiquement, si quand je renomme une textbox ou autre dans un usf, je la batise avec un nom qui existe déja. si je n'ai pas enregistré juste avant, c'est le plantage, et je perd tout, si j'ai enregistré, elle ne bloque pas (grrrr), j'en ai pris on parti, j'enregistre. t'as peut etre quelque chose du genre.
sinon, pour la panne elle même, aucune idée désolé.

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

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

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