Insufficient memory when closing unnecessary workbook!?

vschoet

New Member
Joined
Mar 17, 2017
Messages
5
Hey guys,



I'm getting an inexplicable memory error while trying to optimize my code.


My macro is pretty big and totally not optimized, but it shouldn't be exceeding the limits of this computer quite yet.
Even stranger so: the error only occurs when trying to close an unnecessary workbook, in an effort to clear memory!


The involved secondary workbook is used just to extract some array's of data.
In it, there are 4 variable ranges of data that I need to work with, like this:


Set xxwrng = variablerange1 in secondary workbook
Set xxxrng = variablerange2 in secondary workbook
Set xxyrng = variablerange3 in secondary workbook
Set xxzrng = variablerange4 in secondary workbook


After that I compare values in my main workbook against these ranges.
Then the secondary workbook has no more use.


Originally I close this workbook at the very end of my macro. => no memory issues!
But now I tried to optimize my code, by closing this unnecessary workbook right after the comparison.
This is when I get the insufficient memory error!


I tried: Erasing the arrays, setting their values to nothing etc etc.


Can anyone explain this one?


Any referral to a beginner VBA memory management resource would also be greatly appreciated.

Atm I just write line after line of code, without organizing it in any optimized way.

Thank you in advance!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try setting all 4 ranges to nothing before closing the workbook.
Code:
Set xxxrng = Nothing

Determining the cause of your memory error :confused: would be easier if you posted your complete code :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top