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

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