Big List Sort. Not Enough Memory. Help Run my Code. ( Or fix it Please ! )
VBA has a Memory of the Memory it used. And I cannot clear it
I have been on this a few days now, and am going really mad!!
I am working privately on home on my old computers ( PC Vista, XL2007 ). I am trying to make a large reference list of Foods and their Nutrients to help in organising Diets.
I am hitting a Brick wall.
I do not get far before Excel “bombs out” with error “Not enough Memory”
I am keen to get to the bottom of this before I reluctantly may have to invest in a new computer, especially as I do not want to do that only to find a small improvement.
So I am asking for help here as follows.
_1) Could someone if they have access to a fairly “state of the current art” computer have a go at running a code for me and tell me how far you get. ( An Error handler section will chuck out a message as well as you will see how many rows get filled in ) . If you can give me some details of the computer you are using that will help me.
_2 ) Anyone have any other ideas how I can change the code to get over this problem. Have I missed something?. ( In parallel I will now rewrite the program without Arrays to see if that gets me anywhere.. )
So, in order for you to help me, .....
I upload two Files_....
_ “BDSept2016.xlsx” ( A couple of links to different share filing sites )
German Telekom: https://www.magentacloud.de/share/jqlc8w278a#$/
_... This is the File to be filled in. This is at the outset just a File with Headers ( There are 20 Rows for each Header (Nutrition Type Name ) to allow different spellings of the Nutritions and other information about the Nutritions ). So Data rows will be filled in from Row 21.
_.....and data File with code ( Sub USDAToDB()
_ “NutritionalValues2016.xlsm” ( A couple of links to different share filing sites )
German Telekom https://www.magentacloud.de/share/6t-0t1ao1v#$/
_ This has a large amount of data. Only the following Columns are of interest:
_ J Names of Food Products. ( Like Butter, Hamburger .. etc.. etc.. )
_ K Nutrition Names ( Like Fat , Protein etc.. etc... )
_ N Values of the Nutritions in standardised Units
So here the first Part of the only used Worksheet ( “NutritionalValues” ) in data File _ “NutritionalValues2016.xlsm”
Using Excel 2007 32 bit
Row\Col J K L M N 1 Food Product Nutrion Name Nutrition Value 2 Butter, salted |Protein g 0.85 0.85 3 Butter, salted |Total lipid (fat) g 81.11 81.11 4 Butter, salted |Carbohydrate, by difference g 0.06 0.06
On average the Food Products have a bit under 80 Nutrition Values. There are 8790 Food products. There are approximately then 680000 Rows in this data File !!
So in this file the code I would like you to run for me when both Workbooks are open is.
Code is also Posted here:
and in the following post
is also the same File as .xlsx, (“NutritionalValues2016.xlsx” ) should you prefer not to download a .xlsm File )
When I run this code, I, get to between 340-347 rows filled in worksheet “Tabelle1” in File “DBSept2016.xlsx” before the code errors. ( I need finally nearly 9000 !!!, so I am not getting very far.. Lol... ) ( Edit: I just managed to borrow a WINDOWS 7 XL2010 .. and only got a few more rows ?? )
Some further points:
_(i) Often , once the code “Bombs out” , the next run does not get past the first row!! So I have to close Excel and start again ??
_(ii) I added a code line to erase three large Arrays after the code no longer needed them. That had no noticeable effect.
_(iii ) My Files are very large. But I thought I had an efficient code. (_... I have gone to great lengths in Threads to optimise an efficient combination of Worksheet Functions and use of Arrays.
I use extensively 1 Dimensional Arrays which I have found is very efficient ( at least from the Speed point of view ). _... )
_(iv) The following Info is not necessary to know to help me, but may be of interest for anyone interested the code I have posted.
Code Notes ( and Files ) to the code given Here:
(_... Code in this post