P
Peter Quarrell
It is quite easy to get an Excel workbook into an almost unusable
state and generate this message. The Knowledge Base says that there
is a limit of "about 4000" on the number of different format
combinations that a workbook can have. Although the Excel object
model must have in it a fairly complex data structure to manage access
to these combinations, it offers no comfort at all to any user who has
just hit the limit.
What that user needs is some way of identifying those format
combinations which are only used in one or two of the workbook's
cells.
To bridge the gap Microsoft have left, I have a commercially available
set of routines called QAid. They sit in a free-standing Excel
workbook. The jewel in its crown is an analysis of all the UsedRanges
in a rtarget workbook, listing all of the different format
combinations it uses, with a count of how many cells use each one, a
pink highlight for the unique ones, and a note of the address of the
first cell found to be using each.
A few clever tools to take the hard work out of eliminating unique
formats while keeping the results accurate, and some gems of advice on
how to cope with the situation when a workbook hits the 4000 limit
make QAid a worthwhile option. I sell it (cheaply) as a commercial
product to cover my costs, but feel the need to publicise it more
widely. At present I get one enquiry per 3 or 4 weeks from people in
the English-speaking world who type "too many different cell formats"
into a search engine and find my name comes up.
For more details, see www.quarrell.demon.co.uk/QAid/
Peter Quarrell
state and generate this message. The Knowledge Base says that there
is a limit of "about 4000" on the number of different format
combinations that a workbook can have. Although the Excel object
model must have in it a fairly complex data structure to manage access
to these combinations, it offers no comfort at all to any user who has
just hit the limit.
What that user needs is some way of identifying those format
combinations which are only used in one or two of the workbook's
cells.
To bridge the gap Microsoft have left, I have a commercially available
set of routines called QAid. They sit in a free-standing Excel
workbook. The jewel in its crown is an analysis of all the UsedRanges
in a rtarget workbook, listing all of the different format
combinations it uses, with a count of how many cells use each one, a
pink highlight for the unique ones, and a note of the address of the
first cell found to be using each.
A few clever tools to take the hard work out of eliminating unique
formats while keeping the results accurate, and some gems of advice on
how to cope with the situation when a workbook hits the 4000 limit
make QAid a worthwhile option. I sell it (cheaply) as a commercial
product to cover my costs, but feel the need to publicise it more
widely. At present I get one enquiry per 3 or 4 weeks from people in
the English-speaking world who type "too many different cell formats"
into a search engine and find my name comes up.
For more details, see www.quarrell.demon.co.uk/QAid/
Peter Quarrell