Some other numbers ...

I really am pleased that the limit of "approximately 4000" on the number of different cell formats (inadequately discussed in support.microsoft.com/default.aspx) is to be increased to 64K.

BTW you refer to this limit as being on "the total number of unique cell styles".  This term is also used in the KB paper listed above, and by several of those who have posted comments about the problem in various places.  The reference to "styles" actually confuses people, and takes attention away from the fact that this limit is on something (format combinations) when most people don't know of its existence.  You will find, if you do a Google search, that some people think the problem can be solved by deleting unused custom number formats, but they don't make the combinations list hit its limit.

I'm pleased the limit is being increased even though this means there will be an end to a steady small income I have been making for a few years from sales of my utility QAid.  Although QAid offers quite a range of useful analyses to improve the efficiency of large Excel workbooks, most of the sales go to people who have run into the "approximately 4000" limit (actually anything from 2300 to 4482 in my experience) and discovered that it makes the workbook _completely_ unmanageable.  When you open the workbook, up comes the error message; whatever you do to it then, up comes the error message again.  (But see my web site for advice on how to get it working, and thus repairable, again).

Since creating my website to publicise QAid some years ago, I have been contacted on average once every 14 days by someone interested in acquiring a QAid licence.  QAid works by identifying and counting occurrences of each of the format combinations used, thus producing a shortlist of those which are used once only and can therefore reduce the problem by being changed to a less unique format.  In the last 18 months QAid has also provided shortlists of the combinations most similar to the one you want to eliminate, thus almost automating the mending process (while leaving to the developer the necessary choice of which feature(s) of the combination to sacrifice).

So hopefully my sales of QAid will begin to dry up once Excel 12 is on the market -- indeed, I will be duty bound to add a note to my web site advising interested potential customers to switch to Excel 12 to sort out their problem.  It seems sad, really; I've had emails from all over the world, with recent sales including the US Department of Treasury, a Hollywood studio, a major oil company, local governments in 3 continents, a New Zealand sawmill, and an Australian bank.  Often we have corresponded at some length as new vagaries in Microsoft's offering have become apparent.

Two main questions:

(a) does the increase to 64K mean that workbooks that hit the new limit will give some warning and not become unmanageable just as their victims discover the problem?  If users could try to deal with the limit without having to worry about whether their workbook can ever be made useable, they might not feel as bad about Microsoft as most of them do (there is a universal feeling among them that this aspect of Excel must have been developed by a Seattle high school student on work experience during the summer vacation, who was inadequately supervised because the managers who should have done so were on vacation themselves -- and that customer complaints have not got through often enough for anyone to consider doing the repair work that has been needed since day 1).

(b) is there any chance that Microsoft might give users some VBA access to the parts of the Excel Object Model that deal with these format combinations?  There must be a hidden list of all the different combinations, with all the relevant format property values for each one, and a pointer system in one direction at least (from cell to list) if not in both directions.  QAid generates such a list (and takes ages, since it has to evaluate 40 different format aspects for every single cell _and_ merged area in the workbook's full UsedRange).  Give us read-only access to the list and the task becomes much less.

The Object Model's list seems not to be particularly often subjected to housekeeping (just as deleting hunks of VBA leaves unused gaps in a workbook, which can only be closed up by exporting all the forms and VBA modules, saving the workbook, and then reimporting them).  When a workbook is close to the ~4000 limit (a state identified by formatting a cell with a previously unused font or colour, and watching it crash), the margin for new formats can best be increased by _both_ saving the workbook _and_ exiting Excel completely.  It looks as though only closing Excel actually tidies up that aspect fully.  Not very easy to prove that point, but I've given that advice frequently, and many people have found it helpful.

Peter – Thanks for your comments.  We believe that increasing the limit roughly 16 fold will dramatically reduce the number of users that ever hit this limit.  Secondly, in a world of more structured formatting with improved cell styles, table styles, and themes (which I will cover in later posts), we believe there will be less need to create one-off formats, so it is possible that the average number of unique formats an Excel 12 workbook will be fewer than an Excel 11 workbook.  Finally, both cell and table styles have their own 64k limits separate from each other and the “unique cell styles” limit in question. The result is that cell styles and table styles take zero slots from the custom format limit.