Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
WXP, SP2
XL2K2, SP3 What is the maximum number of range names one can have within a file without running into issues to the point that Excel decides to say that the file has been corrupted and goes into repair mode? According to the specification help file, the only thing to limit Excel with range names is the amount of memory, which I know I still have plenty of RAM for the range names that were added to the various workbooks. The HD isn't that full either as far as extended memory is concerned. The first wave of 3 waves dealing with range names, the workbooks only showed as being at a size between 8MB and 10MB, even after saving, which after the first wave, it has something like 26k range names in it. The second wave and the third wave both added another 26k or so of range names each, which opened fine after the second wave, but then claims the workbooks are damaged after the 3rd wave when you try to reopen those files. The files now shows a size of between 11MB and 13MB, so it's still well below the 160MB of MS's arbituary RAM usage limitation. What brought on the need to put in range names? With the changes that took place, it took the numbers out of alignment cause cell refences within code doesn't adjust like cell references within formulas does. Guess to overcome this issue, I will just have to name rows and columns, then use long variables within VBA to get around both issues (cell references not adjusted within VBA when rows/columns are inserted/deleted and apparently to avoid reaching the maximum number of range names allowed within a single workbook that seems to be somewhere between 52k and 78k). Sincerely, Ronald R. Dodge, Jr. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
Hi Ronald,
'----------------------- WXP, SP2 XL2K2, SP3 What is the maximum number of range names one can have within a file without running into issues to the point that Excel decides to say that the file has been corrupted and goes into repair mode? According to the specification help file, the only thing to limit Excel with range names is the amount of memory, which I know I still have plenty of RAM for the range names that were added to the various workbooks. The HD isn't that full either as far as extended memory is concerned. The first wave of 3 waves dealing with range names, the workbooks only showed as being at a size between 8MB and 10MB, even after saving, which after the first wave, it has something like 26k range names in it. The second wave and the third wave both added another 26k or so of range names each, which opened fine after the second wave, but then claims the workbooks are damaged after the 3rd wave when you try to reopen those files. The files now shows a size of between 11MB and 13MB, so it's still well below the 160MB of MS's arbituary RAM usage limitation. What brought on the need to put in range names? With the changes that took place, it took the numbers out of alignment cause cell refences within code doesn't adjust like cell references within formulas does. Guess to overcome this issue, I will just have to name rows and columns, then use long variables within VBA to get around both issues (cell references not adjusted within VBA when rows/columns are inserted/deleted and apparently to avoid reaching the maximum number of range names allowed within a single workbook that seems to be somewhere between 52k and 78k). Sincerely, Ronald R. Dodge, Jr. '----------------------- You have inadvertently arrived at the Italian language Excel NG. I suspect that you would be better served by the corresponding English language Excel pragramming group: microsoft.excel.programming However, as you have seen in the Specification limits file, the number of names is limited only by memory. I have, on occaision used vast numbers of names without experiencing any discernable problems. I would suspect, therefore, that the source of your problem may lie elsewhere, --- Regards, Norman Microsoft Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
Hi Ronald
Please ignore my response - a case of crossed wires and two PC's! --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
No problem. I also had to modify the name within the newsgroup account
(where it shows as "microsoft.com" as it being from them, but now that's been modified to reflect my name.) as the system that I'm working on had been totally reimaged, which means I have had to go through and reset everything just about. No fun to have to go through all of the settings and everything else to get things back to normal, which was as a result of the DS patch that wasn't marked as for XP only (as provided from one of our IT guys who claimed it was marked as such, but our local IT guy confirmed it wasn't marked as such) and I was working on a W2K Pro at the time, thus why I had to get the system completely reimaged. If you try to use the DS patch that's meant for WXP Pro, and used it on W2K Pro, the system will go into a constant state of rebooting itself and never actually get to the login screen. Sincerely, Ronald R. Dodge, Jr. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
I only started doing this after the system was completely reimaged, and this
morning, I realized that one of the things that I had to do was to take the number of UNDO's in the registry from the default 16 down to 4. I completely forgot about this aspect as I have had the system for just about 9 years now, and I had to deal with that very issue back then when it stated that my resources were low even though everything was being ran through VBA. Why Excel seems to retain the information when you copy and paste large amounts of data, but yet, it doesn't allow you to undo your changes cause it was ran through VBA (By default anyhow), that doesn't seem to make sense to me. Had to put in the registry setting when I was under W2KPRO, XL2K; W2KPRO, XLXP; and now WXPPRO, XLXP. We shall see if this resolves the issue, which I suspect it will. Sincerely, Ronald R. Dodge, Jr. "Norman Jones" wrote in message ... Hi Ronald, '----------------------- WXP, SP2 XL2K2, SP3 What is the maximum number of range names one can have within a file without running into issues to the point that Excel decides to say that the file has been corrupted and goes into repair mode? According to the specification help file, the only thing to limit Excel with range names is the amount of memory, which I know I still have plenty of RAM for the range names that were added to the various workbooks. The HD isn't that full either as far as extended memory is concerned. The first wave of 3 waves dealing with range names, the workbooks only showed as being at a size between 8MB and 10MB, even after saving, which after the first wave, it has something like 26k range names in it. The second wave and the third wave both added another 26k or so of range names each, which opened fine after the second wave, but then claims the workbooks are damaged after the 3rd wave when you try to reopen those files. The files now shows a size of between 11MB and 13MB, so it's still well below the 160MB of MS's arbituary RAM usage limitation. What brought on the need to put in range names? With the changes that took place, it took the numbers out of alignment cause cell refences within code doesn't adjust like cell references within formulas does. Guess to overcome this issue, I will just have to name rows and columns, then use long variables within VBA to get around both issues (cell references not adjusted within VBA when rows/columns are inserted/deleted and apparently to avoid reaching the maximum number of range names allowed within a single workbook that seems to be somewhere between 52k and 78k). Sincerely, Ronald R. Dodge, Jr. '----------------------- You have inadvertently arrived at the Italian language Excel NG. I suspect that you would be better served by the corresponding English language Excel pragramming group: microsoft.excel.programming However, as you have seen in the Specification limits file, the number of names is limited only by memory. I have, on occaision used vast numbers of names without experiencing any discernable problems. I would suspect, therefore, that the source of your problem may lie elsewhere, --- Regards, Norman Microsoft Excel MVP |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
Well the undo registry hack didn't seem to make a difference in this issue.
With about 1/4 of the range names put into the 12 different machine center files, both before and after putting in the registry hack for the Undo Feature, I got the low resource error message as it attempted to open the 11th file (Note: The first 10 files has already been opened, processed, and closed up to this point). Even though each machine center file has something like 31k range names, I also got rid of a little more than 30k "SUMIF" functions thinking that was the culprit. There was nothing changed on the VBA side. I initially put in the "SUMIF" function as a short-term fix, and that's cause I had to get my numbers back into alignment after the other changes took place as what ended up happening with regards to the lean manufacturing team now that our parent company is pushing more things unto us. However, I don't like using the SUMIF function as a long-term solution cause it's a huge memory and CPU hog. Anyhow, with these adjustments that I put in (added range names and converted the SUMIF function to formulas refering to range names, instead of it getting better (reduced processing time), it got worse (getting the low resource error message). With the SUMIF functions, at least I could have all of the 12 files and other files processed all the way through the reporting system in one go, but with the 1/4 of the needed range names, the process had to be split up into 2 different passes to get all the way through the production reporting system. Process via the VBA code is to open a set of summary files, open the first individual machine center file, open it's raw data file (that file only retains up to the last 45 days of data), update the raw data into the individual machine center file, convert all data that's older than the previous 2 weeks as of the last date of the previous fiscal week (Monday through Sunday is our fiscal week), close out the raw data file, then calculate the data through. After all of the data has been calculated through and processed via the VBA code, the individual machine center file is closed out and the next file is then opened up as specified within the control file. Here's the weird thing about it, if I setup in my control file to process just the first 8 files of the 12, everything works as expected, and then I have that same control file then process the remaining 4 files, and then also process other production files, though not tied to machine centers, it process all of those files just fine too provided everything else is working properly as expected. Sincerely, Ronald R. Dodge, Jr. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
I haven't read the thread but this caught my eye
Even though each machine center file has something like 31k range names FYI, Charles Williams answered this question earlier this year - Q. "is there a limit to the number of named cells or named ranges you can create?" A. "there is no real limit that I know of, but things can get a bit slow if you get above 5-10000 names." his site www.DecisionModels.com Regards, Peter T "Ronald Dodge" wrote in message ... Well the undo registry hack didn't seem to make a difference in this issue. With about 1/4 of the range names put into the 12 different machine center files, both before and after putting in the registry hack for the Undo Feature, I got the low resource error message as it attempted to open the 11th file (Note: The first 10 files has already been opened, processed, and closed up to this point). Even though each machine center file has something like 31k range names, I also got rid of a little more than 30k "SUMIF" functions thinking that was the culprit. There was nothing changed on the VBA side. I initially put in the "SUMIF" function as a short-term fix, and that's cause I had to get my numbers back into alignment after the other changes took place as what ended up happening with regards to the lean manufacturing team now that our parent company is pushing more things unto us. However, I don't like using the SUMIF function as a long-term solution cause it's a huge memory and CPU hog. Anyhow, with these adjustments that I put in (added range names and converted the SUMIF function to formulas refering to range names, instead of it getting better (reduced processing time), it got worse (getting the low resource error message). With the SUMIF functions, at least I could have all of the 12 files and other files processed all the way through the reporting system in one go, but with the 1/4 of the needed range names, the process had to be split up into 2 different passes to get all the way through the production reporting system. Process via the VBA code is to open a set of summary files, open the first individual machine center file, open it's raw data file (that file only retains up to the last 45 days of data), update the raw data into the individual machine center file, convert all data that's older than the previous 2 weeks as of the last date of the previous fiscal week (Monday through Sunday is our fiscal week), close out the raw data file, then calculate the data through. After all of the data has been calculated through and processed via the VBA code, the individual machine center file is closed out and the next file is then opened up as specified within the control file. Here's the weird thing about it, if I setup in my control file to process just the first 8 files of the 12, everything works as expected, and then I have that same control file then process the remaining 4 files, and then also process other production files, though not tied to machine centers, it process all of those files just fine too provided everything else is working properly as expected. Sincerely, Ronald R. Dodge, Jr. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Names Limitation
Well in this situation, the files aren't being processed any slower, but
rather resources seem to be eaten up at a much faster rate than what would have been expected, which based on the symptoms, I will have to look at my object variables within VBA. "Peter T" <peter_t@discussions wrote in message ... I haven't read the thread but this caught my eye Even though each machine center file has something like 31k range names FYI, Charles Williams answered this question earlier this year - Q. "is there a limit to the number of named cells or named ranges you can create?" A. "there is no real limit that I know of, but things can get a bit slow if you get above 5-10000 names." his site www.DecisionModels.com Regards, Peter T "Ronald Dodge" wrote in message ... Well the undo registry hack didn't seem to make a difference in this issue. With about 1/4 of the range names put into the 12 different machine center files, both before and after putting in the registry hack for the Undo Feature, I got the low resource error message as it attempted to open the 11th file (Note: The first 10 files has already been opened, processed, and closed up to this point). Even though each machine center file has something like 31k range names, I also got rid of a little more than 30k "SUMIF" functions thinking that was the culprit. There was nothing changed on the VBA side. I initially put in the "SUMIF" function as a short-term fix, and that's cause I had to get my numbers back into alignment after the other changes took place as what ended up happening with regards to the lean manufacturing team now that our parent company is pushing more things unto us. However, I don't like using the SUMIF function as a long-term solution cause it's a huge memory and CPU hog. Anyhow, with these adjustments that I put in (added range names and converted the SUMIF function to formulas refering to range names, instead of it getting better (reduced processing time), it got worse (getting the low resource error message). With the SUMIF functions, at least I could have all of the 12 files and other files processed all the way through the reporting system in one go, but with the 1/4 of the needed range names, the process had to be split up into 2 different passes to get all the way through the production reporting system. Process via the VBA code is to open a set of summary files, open the first individual machine center file, open it's raw data file (that file only retains up to the last 45 days of data), update the raw data into the individual machine center file, convert all data that's older than the previous 2 weeks as of the last date of the previous fiscal week (Monday through Sunday is our fiscal week), close out the raw data file, then calculate the data through. After all of the data has been calculated through and processed via the VBA code, the individual machine center file is closed out and the next file is then opened up as specified within the control file. Here's the weird thing about it, if I setup in my control file to process just the first 8 files of the 12, everything works as expected, and then I have that same control file then process the remaining 4 files, and then also process other production files, though not tied to machine centers, it process all of those files just fine too provided everything else is working properly as expected. Sincerely, Ronald R. Dodge, Jr. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
How to unlock the cell range limitation? | Excel Discussion (Misc queries) | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Excel Formula Range Limitation | Excel Worksheet Functions | |||
Range Address Memory Variable Limitation | Excel Programming |