Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Available resource limits, memory leaks and linked files
I have not read your entire post but here is a link to an excellent resource
on memory, speed and optomization... http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "Paulortrick" wrote: I am working on a VBA/Excel app to manage my company's inventory and projects, it has been largly okay, but to allow multiple users without taking a step up to a real database I split my workbook into three separate workbooks, one of them is a "User interface module" saved as read-only and the other two are the data sets saved as Shared workbooks. The data sets have some simple calculations and a number or named ranges but no links. The user module has a number of worksheets, some of which include cell and range references to the data set workbooks. To make the links work consistently I needed to load the data set workbooks before loading the user module workbook. The VBA code periodically saves the datasets and so far, that has been without incident. Where my problem arrises is when I close the files. Either running a close macro I wrote, or by using the close icon on the userinterface module workbook (this event triggers the other two worksheets to be closed in the beforeclose workbook event), I receive an error message for each workbook as it closes: "Excel cannot perform this task with the available resources. Select less data or close other applications." When I click "okay" in the dialog, the workbooks close and its a wrap. Because I do not save the files when I close, there has not been any data loss related to this, but as I am launching this as a company wide application, it might be good to avoid these kinds of ugly faults. In my search around, so far, relating to this error most of the references are to a memory leak in Excel 2002 which was fixed (apparently) by one of the service packs. The conditions of that error are nothing like what I am encountering so I am fairly aggravated. I've updated my excel 2003 with the recommended service packs and patches without affect. Other useful notes: the PC I'm on is fully capable of managing the files' sizes (4 MB for the user module, 3.5MB for the core dataset, 1MB for the transactions dataset) I have no other apps running, so memory should not be an issue. However during operation, the =INFO("memused") is 10MB and =INFO("totmem") is 11.5MB - on a 1GB computer this seems a fairly low number, and does not appear to change more than a few KB from one load to the next or while in operation... I presume (but may well be wrong) that because memory is allocated dynamically, this should increase at need and available resources are not actually at issue. The problem is also only showing now that I have cell references to other shared workbook files. The previous version of the application was all-in-one workbook and never a bogus moment was seen. Any advise is much appreciated. Thanks in advance for your help, -- Paulortrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Available resource limits, memory leaks and linked files
Thanks Jim for the input,
I did take some time at the descisionmodels website - where I discovered just how much data Excel can manage (allaying my concerns that I might be pushing the excel envelope boyond the limit). Unfortunately, the only references to the error I am getting is the one I mentioned in excel 2002. Cheers, -- Paulortrick "Jim Thomlinson" wrote: I have not read your entire post but here is a link to an excellent resource on memory, speed and optomization... http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "Paulortrick" wrote: I am working on a VBA/Excel app to manage my company's inventory and projects, it has been largly okay, but to allow multiple users without taking a step up to a real database I split my workbook into three separate workbooks, one of them is a "User interface module" saved as read-only and the other two are the data sets saved as Shared workbooks. The data sets have some simple calculations and a number or named ranges but no links. The user module has a number of worksheets, some of which include cell and range references to the data set workbooks. To make the links work consistently I needed to load the data set workbooks before loading the user module workbook. The VBA code periodically saves the datasets and so far, that has been without incident. Where my problem arrises is when I close the files. Either running a close macro I wrote, or by using the close icon on the userinterface module workbook (this event triggers the other two worksheets to be closed in the beforeclose workbook event), I receive an error message for each workbook as it closes: "Excel cannot perform this task with the available resources. Select less data or close other applications." When I click "okay" in the dialog, the workbooks close and its a wrap. Because I do not save the files when I close, there has not been any data loss related to this, but as I am launching this as a company wide application, it might be good to avoid these kinds of ugly faults. In my search around, so far, relating to this error most of the references are to a memory leak in Excel 2002 which was fixed (apparently) by one of the service packs. The conditions of that error are nothing like what I am encountering so I am fairly aggravated. I've updated my excel 2003 with the recommended service packs and patches without affect. Other useful notes: the PC I'm on is fully capable of managing the files' sizes (4 MB for the user module, 3.5MB for the core dataset, 1MB for the transactions dataset) I have no other apps running, so memory should not be an issue. However during operation, the =INFO("memused") is 10MB and =INFO("totmem") is 11.5MB - on a 1GB computer this seems a fairly low number, and does not appear to change more than a few KB from one load to the next or while in operation... I presume (but may well be wrong) that because memory is allocated dynamically, this should increase at need and available resources are not actually at issue. The problem is also only showing now that I have cell references to other shared workbook files. The previous version of the application was all-in-one workbook and never a bogus moment was seen. Any advise is much appreciated. Thanks in advance for your help, -- Paulortrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP - Not enough Memory, Out of system resource! | Excel Discussion (Misc queries) | |||
HELP - Not enough Memory, Out of system resource? | Excel Discussion (Misc queries) | |||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data | Excel Programming | |||
documented memory leaks in Excel 2003 or OWC11? | Excel Programming |