"Excel cannot complete this task with available resources"

[MDW]

I've got an Excel file that links to two other files, runs some calculations
using VBA, and then creates an output file. Today, I started getting the
message "Excel cannot complete this task with available resources. Choose
less data or close other applications." during my run.

I know this isn't a physical memory issue (and this is borne out by looking
at the Task Manager), and I've read the article on Excel's inherent memory
limits. However, what I'm doing isn't new - I ran the exact same process with
different source files this morning and had no problems. It's worked fine for
months. I'm using the same PC and there've been no software changes (XL2003,
XP Pro).

When I run this process, I have no other Excel instances open.

Any ideas as to what else might be causing the issue? TIA.

 

[Jim Thomlinson]

Physical memory and Excel Addressable memory are two different things. xl2003
can however address a whole pile of memory. Give this link a look and ti
might be able to help...

http://www.decisionmodels.com/memlimits.htm

 

[MDW]

Unfortunately, that doesn't tell me very much, other than the fact that I'm
well within the parameters of what should be good performance (512 MB RAM and
765MB virtual memory).

The article talks about "large" Excel files, but doesn't really quanify
that. What does it mean by "large"?

Here are the three files I'm using:

Source File 1 -
Size: 128 K
1 Worksheet, 213 rows, no formulae

Source File 2 -
Size: 1,571 K
9 worksheets, largest contains 5,875 rows, some formulae, but nothing too
advanced. Some VBA, but nothing too advanced.

Model File (the one generating the errors) -
Size: 2,875 K
8 worksheets, lowest row used is 368 rows, out to column AK. Some advanced
formulae, including about 8 columns with arrays. Some pretty extensive VBA
and a userform.

Given that in the example they talked about 80 MB files and the like, I
don't consider these three files (even combined) to be "large"....is there
some other measure of largeness that I'm not taking into account?

What's troubling is, as I said, there's been no significant changes to my
system or to any of these files since this morning (when everything was
working fine), but every time I run my code this afternoon, I'm seeing this
error.

 

[NickHK]

There is the INFO worksheet function to give you data on Excel. I can't say
how accurate they are, but this is what Excel thinks it is using :
=INFO("memused")
=INFO("memavail")

From your description of your WBs, they seems within "normal" bounds, but
without any idea of what your code is actually, who can tell.
If you are creating/using a lot of resources and not releasing them
correctly, you may have a problem with a memory leak.

NickHK

 

[MDW]

As near as I can tell, this block of code is when the error occurs most often:

objLoan.Close False
Set objLoan = Nothing

' Clear the file info for use next time
shtInput.Range("export_name").Value = ""
shtInput.Range("export_path").Value = ""

End If

Next

Next

objHedge.Close False
Set objHedge = Nothing

Which seems counter-intuitive since it's when I release the resources.
objLoan and objHedge are the only Excel objects ceated in my code. They're
both workbooks. I don't create any Worksheet, Range, Chart, objects. etc.