Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding 65K of defined names within workbook causes workbook to go into repair mode when it is opened
Okay, I'm at it again, but only with further testing on this defined names
issue. I have seen different posts stating that with excessive number of range names, it causes the files to process significantly slower, which I have seen no such symptom within my own files. However, I have noticed a certain pattern and now I must raise another question that I seem to have noticed? What is the maximum number of items any one collection can have without causing issues? Why do I ask this? It seems to me that the collection is using 4 bytes as addresses to get an index number. If this is the case, then when an item exceeds 65536 different items, you no longer have a unique ID number, which is what seems to be causing me these issues that I seem to have ran into. This 65536 number also made me think of rows instantly, which then makes me wonder if this is also the reason why MS has made 65536 as the maximum number of rows one can have within a single worksheet. What had me think in this sort of direction? Remember the early Windows 95 edition, and you could only have up to 2GB of HD Space on a per letter drive basis, thus if you had a larger drive size, it had to be partitioned into multiple partitions to be able to use the space on it? Well that issue related back to the fact that the original FAT filing system could only handle up to the 2GB of space per drive/partition for addressing purposes. In this case, if the Names collection has more than the 4 bytes of indexed values, the workbook goes into repair mode when it is opened, which then makes the workbook almost useless as it stripes a lot of stuff from it. While the application specifications says it's limited to the amount of RAM on the system, I'm no where near reaching this limit. Not only that, but supposedly, Excel 2002 should be able to handle up to about 160MB of RAM usage, which according to the task manager, when I ran into this issue, it was only using 67MB of RAM on a system that has 512MB of RAM. According to the Task Manager, I still have 130MB of RAM usage currently not in use and available to use along with 768MB of swap file available to use for a total working memory usage availability being 1.28GB, which only up to 853MB of that has been used. Now that I seemed to have found the real limit of defined names, it's now time to start thinking in other directions to address the issues that I been facing. I would dare to venture to claim that the specification limit of defined names is in deed 65536 just like the maximum number of rows is 65536. The "Cells" object has a 2 byte by a 1 byte index method, similar to a 2 dimensional array. To help me find out the issues, I used the watch window to look at the count value on the Names collection object and after several testings and watching that value along with seeing other things, it's what has had me draw that conclusion. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defined Names in Workbook | Excel Worksheet Functions | |||
How to delete all defined names from a workbook? | Excel Worksheet Functions | |||
How to delete all defined names from a workbook? | Links and Linking in Excel | |||
Deleting unused Defined Names in a workbook? | Excel Programming | |||
Correct way to use names defined globally in a workbook, in VBA | Excel Programming |