Excel 32 and 64 bit memory limitations

[mgCulver]

Hi.

I have a strange problem. In an xlsm workbook, I seem to be encountering a memory limitation problem on both 32 and 64 bit Excel (2016 and 365).

If I run only the xlsm Workbook with no other sheets (xlsx) open, it works fine. If I open 1 or 2 xlsx files and run the xlsm Workbook, some code code executes, however, it will eventually throw up an error. Odd things like Application.EnableEvents will seemingly automatically reset from True to False.

Not sure what to look for. Any suggestions would be appreciated.

Thanks,

Mike

 

[StephenCrump]

What makes you think it's a memory problem? I assume you're not getting an Out of Memory message?

I suspect it's more likely that your code doesn't fully qualify the workbook/worksheets it's using, and hence it's defaulting to the active workbook/worksheet, causing errors.

Application.EnableEvents won't reset by itself. Your code is probably setting to False then crashing before it can reset to True.

But this is just speculation, without seeing your code. Can you post your code so we can take a look? Ideally, can you identify the line that's erroring, and the error message you're getting.

 

[mgCulver]

Hi Stephen.

Thanks for your reply. In answer to your last question first, this is generally where it crashes only if there are several other workbooks open:

file_path = = "P:\Productivity\Master Work Books\Master Templates\Master Templates.xlsx"
Set wbMasterList = Workbooks.Open(Filename:=file_path, ReadOnly:=False)

file_path returns ""

If the one workbook is open, this executes correctly; no problems.

The reason I think it may be a memory problem is because the error only occurs when there are several other workbooks open; as soon as they are closed, the code works fine. From what I have read, if Excel does run into a memory problem, it can behave abnormally.

I'll double check that the workbook and sheets are fully qualified, however, I haven't had an error indicating they are not. I'll also try to locate where the events gets turned off.

I'd love to post the code, however, because of the amount of code, it would be prohibitive.

Thanks again,

 

[RoryA]

Why are there two = signs in the first line?

 

[mgCulver]

Typo

 

[StephenCrump]

Thanks for your reply. In answer to your last question first, this is generally where it crashes only if there are several other workbooks open:

file_path = = "P:\Productivity\Master Work Books\Master Templates\Master Templates.xlsx"
Set wbMasterList = Workbooks.Open(Filename:=file_path, ReadOnly:=False)

file_path returns ""

When you say crashes, can you be more specific? If I try Workbooks.Open with file_path = "", I get a Run-time error 1004. Sorry we couldn't find [blank]. Is this what you're getting?

As your code lines are posted, it implies that file_path had the value P:\Productivity\Master Work Books\Master Templates\Master Templates.xlsx on one line, but "" one line later, which doesn't make sense. It seems more likely that there is intervening code, with conditions, and that the value of file_path wasn't set as you intended.

But again, this is just speculation unless you can post more code?

Otherwise I can only suggest you put in some breakpoints and step through your code to test it's behaving as you envisaged.

 

[mgCulver]

Yes. It's a 1004 error. even though the file_path is set as shown a couple of lines before. Thx Sorry about the terminology - old habits die hard.

 

[StephenCrump]

Are you sure this line is executing?

file_path = "P:\Productivity\Master Work Books\Master Templates\Master Templates.xlsx"

Put in a breakpoint and test.

If the value is set correctly at this line, what happens if you keep stepping through the code, i.e. why does file_path subsequently take the value ""?