G
Guest
This error message is an unpleasant shock for those who hit it (and Q213904
is not much help, as it is confined to truisms and contains one major error).
What those who have hit it, and fear hitting it again, need is access to
some of the object model's hidden properties. We need to be able to identify
which format combinations in a workbook's list of (up to about) 4000 are
least used or unique, so that we can choose a few hundred of them to
eliminate. I have written VBA to do this, but it has to evaluate 40 format
aspects for every cell and merged area in each worksheet's UsedRange, so it
can take 2 days to run on my Dell Inspiron 8600 for a large workbook. Inside
the workbook there must be the data structure which records directly or
indirectly what combinations there are, how often each is used, and which
cells are using each. Could we access this, even if it has to be via VBA?
The workbook object could have a new collection of FormatCombinations, with
each one having properties such as NumberOfTimesUsed and a collection of
CellsAndMergedAreasUsing. A Range could have an additional property to point
to the Combination that it is using. I get about one contact per 15 days (on
my web site advertising my software which analyses this) from people who have
hit the error and entered "too many different cell formats" into a search
engine. So the problem is quite often experienced by Excel developers, and
its disastrous effect on the workbook is a source of worry and irritation.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...acaa19f&dg=microsoft.public.excel.crashesgpfs
is not much help, as it is confined to truisms and contains one major error).
What those who have hit it, and fear hitting it again, need is access to
some of the object model's hidden properties. We need to be able to identify
which format combinations in a workbook's list of (up to about) 4000 are
least used or unique, so that we can choose a few hundred of them to
eliminate. I have written VBA to do this, but it has to evaluate 40 format
aspects for every cell and merged area in each worksheet's UsedRange, so it
can take 2 days to run on my Dell Inspiron 8600 for a large workbook. Inside
the workbook there must be the data structure which records directly or
indirectly what combinations there are, how often each is used, and which
cells are using each. Could we access this, even if it has to be via VBA?
The workbook object could have a new collection of FormatCombinations, with
each one having properties such as NumberOfTimesUsed and a collection of
CellsAndMergedAreasUsing. A Range could have an additional property to point
to the Combination that it is using. I get about one contact per 15 days (on
my web site advertising my software which analyses this) from people who have
hit the error and entered "too many different cell formats" into a search
engine. So the problem is quite often experienced by Excel developers, and
its disastrous effect on the workbook is a source of worry and irritation.
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...acaa19f&dg=microsoft.public.excel.crashesgpfs