Big List Sort. Not Enough Memory. Help Run my Code. Or help fix or change code Please :)

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

Hi
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 )
box https://app.box.com/s/1vjlvdm1n4bmlcu61f2dyxrzx9lh92oh
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 )
box https://app.box.com/s/o77euubc1kaz11kb0ajg5o4ux8s3wi5a
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

NutritionalValues

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.
Sub USDAToDB()
Code is also Posted here:
http://www.excelforum.com/showthread...29#post4481078
and in the following post
http://www.excelforum.com/showthread...36#post4481236
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 ?? )

Thanks,

Alan

_.....................................................
_...............................

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.
http://www.excelforum.com/showthread...45#post4326245
http://www.eileenslounge.com/viewtop...176056#p175302
http://www.excelforum.com/tips-and-t...ml#post4380613
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:
http://www.excelforum.com/showthread...36#post4481236

(_... Code in this post
http://www.excelforum.com/showthread...36#post4481078
_.....)

Hello Doc.AElstein,

Sorry, I am a bit tied up with other Threads, and could not check out all your references, but have you considered including in your Code the following?

Please Login or Register  to view this content.

at the start of your code, and at the end of your Code

Please Login or Register  to view this content.

Regards.

Originally Posted by Winon

...........have you considered including in your Code the following?
......Application.Calculation = .......

Hi Winon
_ Thanks for the Reply.
_ I confess I had not tried that as I am not too familiar with what it is about

_ I just did try

_ It doesn’t seem to make any difference

Alan

Thank you for the feedback Doc.AElstein,

How much RAM do you have on your Computer?

Originally Posted by Winon

..
How much RAM do you have on your Computer?

Hi,
4 Ghz on my main Vista Computer ( 32 Bit ) XL 2007
On a Windows 7 ( 64 bit ) XL 2010 which I can sometimes borrow is also 4GHz - On that machine I only got on avarage a couple of extra rows compared to my main Vista.
Every run is a few row difference in the Range 340 to 350 before the code "bombs out"
Alan

Hi Doc.AElstein,

4 Ghz on my main Vista Computer ( 32 Bit ) XL 2007 is more than ample, but to see how much ram you have, Click on Start, Bottom Left>right Click on Computer>Properties, and check how much Ram is available.

Originally Posted by Winon

....t to see how much ram you have, Click on Start, Bottom Left>right Click on Computer>Properties, and check how much Ram is available.

Hi,
I think that is what I did.. ..? ..
Ram.JPG

Alan

Thank you Alan,

No problem with you Ram capacity. Try closing all other apps, programs,Web access etc. and try running your Excel as the one and only program. See if that helps.

Hi Winon
I think when I ran the codes I usually had very little else going on in my computer.
So to test your idea, I did the other extreme a few times:...
I opened loads of things , even a few new instances of Excel and opened some massive Files,. I also let a few You tube videos run in the background, etc, etc..
Then I ran the code. It took a lot longer.. In fact everything was running at a snail’s pace. – The You Tube songs were not too coherent either as they kept hanging up.. Lol..
But...
_ eventually the code stopped, that is to say “bombed out”, at about the same number of rows.
So no change

I confess I have no Idea how my Computer or Excel works. But my “gut feeling” is that the problem is either
the use of Arrays in the code has hit some limitation that I have not pinned down yet.
Or
Some Worksheet size limit..

_....

I have just modified my code to do a very crude “Spreadsheet interaction type” code which pastes out directly at every matched cell from the data Worksheet to the sorted list Worksheet.
http://www.excelforum.com/showthread...t=#post4481608
http://www.excelforum.com/showthread...t=#post4481611
This code seems to work but will probably take all night to run. So I will set that off in a minute or so, then take another look tomorrow...

If that code works, I will do a two prong approach :
_1 ) keep trying to figure out where my Array code “bombs out”
_2) try and get a more efficient “spreadsheet interaction” code

If the code “bombes out” at a similar point in the Worksheet, then a different two prong approach :
_1 ) stay in bed depressed for a while and / or swear a lot
_2) hope somehow to get some enlightenment from God or possibly someone here as to what the Fuk is going on...


Thanks
Alan

_.....


P.s. I updated the box link to my main data code to include the new Code and did a few typo corrections:
“NutritionalValues.xlsm”
https://app.box.com/s/o77euubc1kaz11kb0ajg5o4ux8s3wi5

( _... the pasted to, “sorted List” File, DBSept2016.xlsx”, has not changed
https://app.box.com/s/1vjlvdm1n4bmlcu61f2dyxrzx9lh92oh

even though you said it doesn't help i noticed something not right

Please Login or Register  to view this content.

thinking it should be

Please Login or Register  to view this content.

and your right it doesnt help

have found in the past that the out of memory message does not actually relate back to physical memory itself
so getting a new computer will not solve your issue unfortunately

If you work with large workbooks, external data or charts sooner or later you will get one of the dreaded messages “Out of Memory” or "Excel cannot complete this task with available resources" or "Not enough System Resources/Memory to Display Completely" , regardless of how much RAM or how big a swap-file you have.
This is because Excel has its own memory manager and its own memory limits.

i ran your code on my computer

Processor Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz, 2601 Mhz, 2 Core(s), 4 Logical Processor(s)
Installed Physical Memory (RAM) 8.00 GB
64-bit Microsoft Windows 7 Enterprise
32-bit Excel 2010

ran few times
Bombs out 232, 235 when i turned calculate to manual 236
ill dig some more see if i can find anything that helps

the quote i had above is from
http://www.decisionmodels.com/memlimitsc.htm

have not digested most of this material as it is pretty dense for my understanding
however it does run more deep into memory usage and limitations of excel

i ran process explorer and captured the usage for Excel

opening just DBSept2016.xlsx shows approx 53,000 kB
then opening NutritionalValues2016.xlsm it jumps to approx 272,000Kb

putting a toggle on

Please Login or Register  to view this content.

running the code one thru to 1090
excel reads ~550,000 kb

running 50 times
excel reads 754,072 kb

estimated....4000kb per each successive run thru

running till it bombs
it hits ~1,245,592 KBytes

i think your just hitting the limit of excel memory
which is probably why the 64-bit Excel runs a lot more lines

VBA Memory Limits
32-bit versions of Excel seem to have a memory limit of about 500MB for VBA (arrays, code etc).
Excel 2010-2016 64 bit seems to have a memory limit of about 4GB for VBA.
I do not know if these limits are reduced if you also have large workbooks open at the same time as the VBA.

Doesnt fix your situation but helps you understand why it is happening

Hi,

I ran your USDAToDBArrays routine to completion on Excel 2016 64bit. Win 10 64bit 8GB RAM.

I do not really understand why you make string copies of the arrays you load from the worksheets- I very much doubt it is helping your memory usage. Nor do I see the benefit of UniqueFoodNamesList when the same information is already available in the array of dictionary keys.

You might also consider moving the code into a third workbook so as to be able to close the large source workbook after loading the arrays from it. Merely opening that workbook raised the RAM usage to some 350MB when I tested it.

@ NoSparks

Originally Posted by NoSparks

Processor: Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz 3.00 GHz
4.00 GB (3.50 GB usable)
64-bit Windows 7 Pro
64-bit Excel 2010
Macro Sub USDAToDB()....4 minutes.
Application-defined or object-defined error
Code "bombed out" at Row 3595
Bottom row of DBSept2016.xlsx is 8810

Hi Sparks
Thanks very much. This is very helpful as it seems to be finally saying that the computer is having some effect.


Very Minor point I expect the 8810 you mentioned is in column A ? – this is a unique product List done earlier in the program before the main looping to produce the long sorted rows, which is where the problem arises.
Questions:
_ can you then confirm that the row of DBSept2016.xlsx which bombed out was 3595. That is to say you had data up to 3595 from column K
_Is it at all possible to get me a copy of the File DBSept2016.xlsx that you were able to produce ?
It would be very interesting to see if I could open such a File. I would have then half the problem solved, that is to say half of the File I am trying to produce.

I am very confused just now at where the resource issue is, the file size, or in the attempt to create that File - or probably a combination of both!! _....._..
_..............leading on from that

_...................@ xlnitwit, humdingaling, Thankyou both very much for you inputs. I am trying very hard to digest and understand all that you have said, as well as making further experiments based on what I am learning.
I am quite out of my depth here, but have an important project so am very grateful for your inputs. Your suggestion and comments are gold worth to me just now

I am having some big problems with my computer just now... I fear I may have overworked it.. I hope I am able to reply !! I certainly intend to give some feedback to what you have said.

Alan

Hello Doc.AElstein,

It could help if you clear the Cache. How I cannot remember, but there are a lot of tips on the Web of how to go about it. I had a similar situation years ago and after clearing the Cache, the problem was solved.

Regards.

Originally Posted by xlnitwit

Hi,
You might also consider moving the code into a third workbook so as to be able to close the large source workbook after loading the arrays from it. Merely opening that workbook raised the RAM usage to some 350MB when I tested it.

this is what i would suggest you do also in order to complete the task
i dont know how you can effectively "clear the cache" (not sure this is the correct term but it fits the meaning) if you are constantly still referring to it

A further thought- if you have a database program such as Access or SQL Server Express, you may consider moving the data into it. It will be a better place to store so much data and you can query it from Excel as required. Although you can do the same with a workbook as the data storage, it does not work well unless your data is of consistent type within each column.

@ Winon ( and humdingaling )

Hi Winon ( and humdingaling )
Re Cache, and clearing it.. This is what I do frequently: ( In Google Chrome, and IE )
_.. -----...Click up at Top Right of the browser Window...-----.. .... then usually you have such options like in the first two screen shots here:
http://www.excelforum.com/showthread...t=#post4482053
_------ ..Then after a few selections you get something like in the last screenshot there

_.______________________-
_..........

Along those line of “clearing stuff”...
I have heard that this “CCleaner” thing is good
http://www.excelforum.com/the-water-...-ccleaner.html
http://www.eileenslounge.com/viewtop...155679#p155679
_..possibly a “AdwCleaner” thing also
http://www.eileenslounge.com/viewtop...22413&start=20

I am still a long way from getting clued up on all the help and suggestions given by you guys. But just an initial “gut feeling” I have is that it is “Excel” limits which are the issue here. They are possibly influenced by the actual computer you have. But what is going on elsewhere in the computer does not appear directly affect my limits in Excel ( unless I do something stupidly extreme as I noted in Post #9, in which case everything can hang up or crash.. Lol.. ) . So I expect clearing the cache has no effect. ( I just cleared it again, and ran those two “clearing things"... Then I did another run of Sub USDAToDBArrays().----_--- It had no effect on my where my code approximately bombs out )

Thanks again for this great support Guys. I am doing my best to digest it all ( when my ailing computer allows.. Lol.. ), and I will feedback anything I find ( or if I may, come back with some follow up questions )

Alan

P.s. I put some ice packs under my computer.. I have a feeling it is running now a bit faster now !! . It was incredibly Hot in the last few days )

i didnt think i was using the correct term by clearing cache
clearing internet cache would not help you

its more like memory dump

more along the lines of these suggestions
http://stackoverflow.com/questions/1...r-in-excel-vba


i do believe the issue is excel limitations
which is probably the reason why the 64-bit excel versions have better luck running your code for longer

@ NoSparks, xlnitwit, humdingaling

Originally Posted by NoSparks

Processor: Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz 3.00 GHz
4.00 GB (3.50 GB usable)
64-bit Windows 7 Pro
64-bit Excel 2010
Macro Sub USDAToDB()
_...
Code Errored! Code Description from Err Object is Application-defined or object-defined error
Code "bombed out" at Row 3595....

Originally Posted by xlnitwit

I ran your USDAToDBArrays routine to completion on Excel 2016 64bit. Win 10 64bit 8GB RAM.....

Originally Posted by humdingaling

...i think your just hitting the limit of excel memory
which is probably why the 64-bit Excel runs a lot more lines....

Originally Posted by humdingaling

....
Processor Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz, 2601 Mhz, 2 Core(s), 4 Logical Processor(s)
Installed Physical Memory (RAM) 8.00 GB
64-bit Microsoft Windows 7 Enterprise
32-bit Excel 2010
Bombs out 232,......l

Originally Posted by humdingaling

....
I do believe the issue is excel limitations
which is probably the reason why the 64-bit excel versions have better luck running your code for longer

I have mostly 32 Bit computer and only 32 Bit Excel ... I can get some access to this...
http://imgur.com/cbuDZCE
That is a 64 Bit machine, but I only have 32 Bit Excel

So it would appear that we are saying , that, to get the improvement I need 64 Bit Excel. So in total I would need a large investment of both a new 64 Bit machine and a 64 Bt Excel. In addition I am sure I will have teething / compatibility problems etc.. etc... when I upgrade...

If I buy a new computer for example, it will have Windows 10. Then I can probably chuck away all my Printers etc... and look forward to a lot of other headaches as things no longer “work” lol.....

So for now I will investigate further how to improve my code / and or File..

Alan

P.s. @ xlnitwit

Originally Posted by xlnitwit

I ran your USDAToDBArrays routine to completion on Excel 2016 64bit. Win 10 64bit 8GB RAM.....

_...completion ???? - How far did you get? How many Rows ??? . Is it possible for you to get a copy of the “BDSept2016.xlsx” that You made to me ??
Thanks

It ran with no errors at all. Output is a little shy of 9000 rows.

Hi xlnitwit

Originally Posted by xlnitwit

It ran with no errors at all. Output is a little shy of 9000 rows.

wow , Brilliant, I have now from you the complete File I was struggling to get. It opens very quickly for me.

I am still keen to get to the bottom of this , to understand**, and so as to make such a File myself, but this will allow me to get further in my work in parallel..

**Clearly all this is a very complicated thing. The file I have from you is very manageable and iI think will give me probably no great problems to access and use. But I had great problems with ( presumably Worksheet size ) Excel limits when running the code...


_.........................
I am working now on this:

Originally Posted by xlnitwit

....
You might also consider moving the code into a third workbook so as to be able to close the large source workbook after loading the arrays from it. Merely opening that workbook raised the RAM usage to some 350MB when I tested it.

Originally Posted by humdingaling

this is what i would suggest you do also in order to complete the task
....

I will let you know how I get on...

Thanks so much...this is great help once again.
Alan

( P.s. Now I know it can be done... I may have to reconsider my idea about investing in a new machine and Excel 64 Bit.... .. But why did NoSpark only manage half – he has Win 7 , not Win 10 like you.. but I was getting the “feel” that the only thing effecting was the Excel 32 Bit v Excel 64 Bit ??? )

P.s. I expect the .xlsb may have some effect on all this. I have never used .xlsb. Oh no, another variable for me to consider and try to understand.. Lol..

I only converted the workbook to xlsb format in order to upload it here. For some reason the allowable file size here is much larger for xlsb than other Excel formats. The actual code ran with an xlsx format as you provided it.

I notice that NoSpark only has 4GB RAM whereas the computer I ran it on had 8GB. I suspect that is what made the difference.

Originally Posted by xlnitwit

I only converted the workbook to xlsb format in order to upload it here. For some reason the allowable file size here is much larger for xlsb than other Excel formats. The actual code ran with an xlsx format as you provided it..

...
Thanks for that, it is helpful to clear it up. ( I will have to note that - i keep being caught out by the 1MB limit for .xlsx and .xlsm files )

Originally Posted by xlnitwit

....I notice that NoSpark only has 4GB RAM whereas the computer I ran it on had 8GB. I suspect that is what made the difference.

Hmm .. I was getting the "feeling" that RAM had no effect, or at least 4Ghz should have been OK ... I guess that is a point that will finally be cleared up once this Thread is done..
Very Naively looking at it .... You got 8790, NoSpark got 3595
So very very , very approoximately, 4000/8000 is in the same Ball Park ( sort of almost ) as 3595/8790

Alan

P.s. if anyone else out there has a go we can get a nice spread of results. ( If someone had 6 GB RAM with 64Bit XL ... but that maybe is an unusual number though for a computer to have ?? )
At least if someone else managed to get the full File using 64 Bit XL , then that would be useful to know.
It seems that the 32 Bit XL is around 340 – 350 for most of us... would do no harm to have anyone else with 32 Bit XL to confirm..

Hi, A bit of an update:.....
So regarding

Originally Posted by xlnitwit

....
You might also consider moving the code into a third workbook so as to be able to close the large source workbook after loading the arrays from it. Merely opening that workbook raised the RAM usage to some 350MB when I tested it.

Originally Posted by humdingaling

this is what i would suggest you do also in order to complete the task
....

_.. I have started doing that.. I do not seem to see any noticeable improvement. ?? I need to think about this a bit more. I am wondering if Excel has a weird “memory, of the memory” it used, so to speak.. so it only “clears” when the Excel itself is completely closed ???

But I will keep looking at this.. maybe I missed something_...
_..... for this I am using 3 Workbooks:

_ the big data file ( .xlsx version with no code in it )
“NutritionalValues2016.xlsx”
Box https://app.box.com/s/rr9poitdmxits6a10g7k5c7ujz0lkylf
German Telekom https://www.magentacloud.de/share/yx0ycx9.kq

_and the File to have sorted rows filled in
_ “BDSept2016.xlsx” box https://app.box.com/s/1vjlvdm1n4bmlcu61f2dyxrzx9lh92oh
German Telekom: https://www.magentacloud.de/share/jqlc8w278a#$/

_and the new code_.....
Sub USDAToDBArraysThirdWB()
_....in a new Workbook ( with only codes in it ) uploaded ( attached ) in this Post
“MacrosOnly.xlsm”

_....................
_.------_--------------------


Some other points

Originally Posted by humdingaling

even though you said it doesn't help i noticed something not right

Please Login or Register  to view this content.

thinking it should be

Please Login or Register  to view this content.

....

Oops, thanks for catching that humdingaling!
So I checked a few times with and without erasing those THREE Arrays. Then I did on average notice a small improvement, of about 10 extra rows . Not a great difference, but good to be aware of the effect. So thanks, it was good that you caught that one-. Always when a different set of eyes looks through a cod to catch the bits the Author misses
( And I will have then another think about XLNitWit’s comments about the Arrays..


_...
_.__________________

Originally Posted by Winon

Please Login or Register  to view this content.

at the start of your code, and at the end of your Code

Please Login or Register  to view this content.

Originally Posted by humdingaling

....
Bombs out 232, 235 when i turned calculate to manual 236
....

I checked that again doing a lot of runs with and without = manual
I have not noticed a difference yet. But I will check again... Best would be to automate ,and let a code try both but loads of times... bit difficult as I need to close and reopen Excel completely after every bomb out. I am not sure how to do that.. ( I guess I can get a code to open a new instance of Excel , open the files and alternately run a code with and without it ?? - maybe a later project if i get time to write that code – would be handy for all the testing actually as it is a bit time consuming opening and closing Excel and running codes on big files, - am not too sure how to do that. – could get tricky handling when Excel “bombs out”.. ). )


_......
_..___________________

From late last night....

Originally Posted by Doc.AElstein

....
I have just modified my code to do a very crude “Spreadsheet interaction type” code which pastes out directly at every matched cell from the data Worksheet to the sorted list Worksheet.......
If that code works, I will do a two prong approach :
_1 ) keep trying to figure out where my Array code “bombs out”
_2) try and get a more efficient “spreadsheet interaction” code

If the code “bombes out” at a similar point in the Worksheet, then a different two prong approach :
_1 ) stay in bed depressed for a while and / or swear a lot
_2) hope somehow to get some enlightenment from God or possibly someone here as to what the Fuk is going on...
....

So I swore quite a bit as it made no great difference. .....but did not swear too long as thanks to the help here I am getting through this one now
Thanks again everyone

Alan

Hi Doc.AElstein,

Thank you for the elaborate Feedback.

Something else I have forgotten to mention. Sometimes Excel plays silly bugger with you. What I mean is that it never seems to really Clear the ranges you have "cleared", if any, and it "hangs on to them" whatever you try, until I discovered this solution. Works most of the time. Select all the Columns to the Right of the last column with Data or Formulas, right to the end, and delete them.
Repeat the same procedure, but this time with the Rows.

Try it, and if that does not work, perform an Office Diagnostics procedure, and see if that maybe fix the problem.

HTH

Regards

Hi Winon

Originally Posted by Winon

Thank you for the elaborate Feedback. ..

I try to give back what I can when people try to help... .. as well as trying to prevent someone going through the agony I am going through trying to get the problem solved.. Lol...
_................................

Originally Posted by Winon

... it never seems to really Clear the ranges you have "cleared", if any, and it "hangs on to them" whatever you try, until I discovered this solution. Works most of the time. Select all the Columns to the Right of the last column with Data or Formulas, right to the end, and delete them....
_.. Sometimes Excel plays silly bugger ..

_..This sounds like something similar to what I have stumbled across a few times when trying to reduce the size of a File and/ or when dealing with .UsedRange_...
http://www.excelforum.com/excel-new-...ml#post4137312
_.....and .SpecialCells
http://www.mrexcel.com/forum/excel-q...t-anomale.html
_. For example, it appears if you manually empty a cell, then it is similar to ClearContents in VBA. So if things like fonts or font size have been changed, you do not “Clear” them , so Excel will still include them in the UsedRange, so the Ranges are not “cleared”. If you carefully change those Formats back to the original it is like Clear in VBA which does clear the UsedRange. But I have found, like you probably, that it is easier just to delete the Rows or columns that I am “finished with” and be done with it.
_ . It may have been in your case that Excel was “hanging on” to the format changes that may have been done intentionally , or unintentionally - when Excel did that other annoying thing of changing number types / formats etc.. (It can get confusing as not all formats ( like background color ) are taken as in the UsedRange.)
_ But you are quite right that Excel plays silly Bugger....
_ . another point that came out from the .SpecialCells Thread was that .SpecialCells used a memory that VBA had of the UsedRange rather than the actual UsedRange. In this case I found a trick to clear that memory which was to use any property of the UsedRange. This had the by-product of refreshing the VBA Memory. I am unfortunately not finding a way to get Excel to clear the memory it Used and so “free it”

Anyway, I think deleting things will have no effect – I just tried a quick run, deleting all cells in the large data file after I had filled the Arrays from it.
303 WBBigACCESS.Worksheets.Item(1).Cells.Delete
It had no effect. I still bomb out at about the same point.
I repeated this with saving the file also after deleting the Cells, and there was a small improvement of about 15 more rows – weird that !!

I expected no significant improvement This was because the idea of xnNitwit and humdingaling to close the File had not had any effect. It would appear that VBA is playing silly bugger and remembering ( and not “clearing” ) any memory it used. )

BTW. the fact that no improvement occurs when I close the Bid data File ties up with my observations noted at the end of Post #1 : “...

Originally Posted by Doc.AElstein

....
_(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 ??
....

..”

So the current Big problem still remains that Excel is remembering any memory it used and not “clearing it”

The idea from humdingaling in Post #19 ( possibly what you meant in #15 ? ) to clear the Excel cache seemed worth a try.. But I have googled several hours.. you are correct there is loads of info out there, that is to say the question of how to do that is asked. I have not found any answers , other than the changes we have found in this thread to make minor improvements.
Fundamentally “clearing” the “Excel Cache” or “Memory of its Memory used” is either not possible , ( here is suggested ther isn’t one ??..
http://www.mrexcel.com/forum/excel-q...ml#post2342496 ...)
or it is privilege that I am not privy to...


There are some Files in the cache of a Workbook.. ( "Files Cached in the Workbook" ) If you change your File extension to .zip, you can see them, like in the you Tube video referenced here:
http://www.excelforum.com/excel-prog...ml#post4319174
and for example with the big data file_.........
http://www.excelforum.com/showthread...91#post4482591


_......_.....But doubt if deleting any of those will do any better than deleting and or closing the whole file !!


_.......................
On ward with experiments...
Alan

P.s

Originally Posted by Winon

.... perform an Office Diagnostics procedure,....

I have no idea how to do that... and not sure exactly what you are referring to

Hello Doc.AElstein,

Quote Originally Posted by Winon View Post
.... perform an Office Diagnostics procedure,....

I have no idea how to do that... and not sure exactly what you are referring to ....

Sometimes the Office installation loses "Control" of what it is supposed to do. That is when an "Office Repair" comes to the rescue.

Check out, and try the steps in this link: http://www.slipstick.com/tutorial/re...installations/

You don't necessarily have to select "Outlook" as in the Demo, you may select your version of Office "whatever", and continue with the prompts thereafter.

HTH

Best Regards.

Hi Winon,

Originally Posted by Winon

Sometimes the Office installation loses "Control" of what it is supposed to do. That is ......

OK, thanks..
I will bear it in mind if I “lose control..”
I maybe will not try it just yet. – I am nervous of repairing what is not ( i think ) broken .. Lol
Thanks
Alan

P.s. _.. Typically my Excel seems to “repair” itself.
http://www.mrexcel.com/forum/general...l-crashes.html

_.. In the Course of work to do with this Thread I may have use the wrong word ( if I did ) by “crashing”. Typically the code “bombs out”, saying that it has not enough memory. I can then go on to use Excel, although usually then when I try to do much it tells me pretty quickly again that it has not memory. Hence I have to close Excel and reopen Excel to “clear”..

Hi, A bit of an update again:.....
_.. I wanted to try out getting the data from the large data File (“NutritionalValues2016.xlsx”
) from it whilst closed to see if that helped.

To make a good comparison for that, I thought I would first re do the comparison between the Array Code and the simple spreadsheet interaction code. The reason for this being as , depending on which method I chose in the closed Workbook case it may compare better with one or the other.


_. To recap: My original Code uses Arrays in the main looping “down” all rows in the data File (“NutritionalValues2016.xlsx” ) . These are three captured long single “column” Arrays from the three columns J K and N representing Food Products , Nutrient Names and Nutrition Values. The various comparisons are then done in the main Loopings from Rem4) in which a long one Dimensional Array is produced at the middle of the looping_.....
990 Let arrDBlxRow(MtchHed) = arrNutValues(JayRow - 2) ' Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name
_........
___ For every output row in the output workbook, “BDSept2016.xlsx” , that Array of the sorted order “row” is pasted out
1080 Let wsDBlx.Range("K" & rDBlx & "").Resize(1, UBound(arrDBlxRow())).Value = arrDBlxRow() 'We may assign a 1 D Array as "pseudo" Horizontal
_......
_._________
Then

_. A simple spreadsheet interaction code does away with the data Arrays, and the final 1 D output Arrays. Direct progression and interaction is done going down the data Worksheet, similar looping is done to find each position of every Nutrition Value in the Output Worksheet, and each matched value is pasted then directly into the Output Worksheet.

Average Results: ( row number pasted out before code "bombs out" )
Sub USDAToDBArraysThirdWB() '---------------- 348
Sub USDAToDBSpreadsheetThirdBook() '--------- 374
_ A little difference, probably the difference between having or not the three long column Arrays..
_...
_ Codes ( given in two parts to fit in Post, but both parts a single code – second part to be copied directly under the first in the same Code Module )
Sub USDAToDBArraysThirdWB()
http://www.excelforum.com/showthread...t=#post4482934
http://www.excelforum.com/showthread...t=#post4482937



Sub USDAToDBSpreadsheetThirdBook()
http://www.excelforum.com/showthread...t=#post4482938
http://www.excelforum.com/showthread...t=#post4482939


Codes in File "MacrosOnly.xlsm" attatched
_....................................

Next post gets on with the closed Workbook idea......

So two closed workbook code ideas I thought about to be tried...,

First attempt at code to get data from Big Closed Workbook
_ follow similar idea to , Sub USDAToDBArraysThirdWB() , - but instead of capturing the three columns of data, string reference Link to that range is made and added as a CSE link in a spare column in the macro Workbook. ( One need to know the last row.. _ that could be got through a code, _.. but for the first simple experiments it is hardcoded )


Second attempt at code to get data from Big Closed Workbook
_ following similar to Sub USDAToDBSpreadsheetThirdBook() but instead of referencing each cell through .Value property of each Cell in the open Worksheet, the Perform the reference” or “Get the reference” or "Get / Perform Reference" idea, which is an alternative to getting the data from a cell in a closed worksheet. Is used..( In this method you make a String reference again , and then use that reference in a _.....
= “ExecuteExcel4Macro(here the reference string) “
_... thing, to return the cell value )

_..........................................................

Brief Code Description and Initial Results

First attempt at code to get data from Big Closed Workbook
Sub USDAToDBArraysThirdWBClsdWorkbook() '
_ The main working change to the Sub USDAToDBArraysThirdWB() is in this code section

Please Login or Register  to view this content.

Previously, The capture of the Arrays for the three deep column Ranges J K and N -- Food Products arrCapFoodNames() , Nutrition Names arrCapNutNames(), and Nutrition Values arrCapNutValues(), was done in Sub USDAToDBArraysThirdWB() through the .Vaue Property applied to that Range in the open large data Workbook, “NutritionalValues2016.xlsx”
This is now replaced by the Function LValsClsdWB( ... which puts a CSE Formula of the reference ( Link ) in the macro Workbook ( temporarily ) to the deep column Ranges in the closed Workbook. The Function itself returns the same Array of values as in Sub USDAToDBArraysThirdWB()
Function LValsClsdWB( ... takes as its arguments
_ the Full File Path And Name to File “NutritionalValues2016.xlsx” ( so you would need to modify this to reflect a path you had downloaded that file to ) ,
_ the Top ( left ) of where the Links are temporarily pasted to ( I use the first column in the macro Workboook, “MacrosClsdWorkbook.xlsm” ,
_ the string of the Cell reference in form like “A2:$A2398753” ,
_ ( optional**) worksheet name, ( **note this could be omitted and the last optional argument used which will take the Worksheet index number )

So code here
http://www.excelforum.com/showthread...t=#post4483102

And here ( One code – just needed to be pasted in two posts due to Post size limits ! )
http://www.excelforum.com/showthread...t=#post4483121

And required Function here
http://www.excelforum.com/showthread...t=#post4483122


Codes also in uploaded (attached file “MacrosClsdWorkbook.xlsm” )

The code works.. Eventually.. it may appear to hang up at the pasting in of the links, but it does actually eventually do it: You can see the progress at the bottom of the Window..
FillingCells.jpg
http://imgur.com/2VgGHxp

But.. the initial results are tat the code bombs out at around 340 rows ... ... OH Poo! .. presumably again Excel has been a pain in the bum and remembered the memory it needed for pasting in the three deep column Ranges.

_.........................
_.________________






Second attempt at code to get data from Big Closed Workbook

I will think about the Second attempt at code to get data from Big Closed Workbook with the “.........
_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!

_.......................
_.........................................................





'Rem Ref
http://www.excelforum.com/excel-prog...ing-excel.html
http://www.excelforum.com/tips-and-t...heet-item.html

Solely for information, I have run the original code on my laptop at work (Win7, Excel 32bit, 4GB RAM, and a few things open) and only managed 279 rows before the error message appeared.

Update

I obtained an increase to 306 rows by closing all other applications.

Originally Posted by xlnitwit

Solely for information, I have run the original code on my laptop at work (Win7, Excel 32bit, 4GB RAM, and a few things open) and only managed 279 rows before the error message appeared.
Update
I obtained an increase to 306 rows by closing all other applications.

Thankyou xlnitwit
This additional Info is very helpful
I am trying as many ways as I can to look at this.

Currently a code using the _.....

Originally Posted by Doc.AElstein

...
Second attempt at code to get data from Big Closed Workbook
I will think about the Second attempt at code to get data from Big Closed Workbook with the “.........
_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...

_.... has been running on my computer all night long!!

_..... I have borrowed a second Vista 32 Bit machine, Excel 32 Bit 2010. That bombs out currently at about 300 Rows for the
First attempt at code to get data from Big Closed Workbook
Sub USDAToDBArraysThirdWBClsdWorkbook () '
from Post #31.

_... I also noted your comments about trying to use different File Types for the data. ACCESS and SQL things are outside my Physical and mental resources !!! But I do have some experiences with handling text Files and am looking at that possibility in parallel
_... All the extra Feedback is helpful in steering me to a final solution ( - the solution To being able to produce the File myself - I already have a copy of the final File now thanks to you giving me the one that worked completely on your 64 Bit Excel.. )

Thanks again for the continued support.

Alan

Originally Posted by Doc.AElstein

...
Second attempt at code to get data from Big Closed Workbook
I will think about the Second attempt at code to get data from Big Closed Workbook with the “.........
_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...

-..So I looked at this at some length yesterday.... "Forays Down the Excel Range Referencing
( Addressing the Member Property .Address on referral Wonks)" ...)
http://www.excelforum.com/showthread...t=#post4483230
http://www.excelforum.com/showthread...t=#post4483344
http://www.excelforum.com/showthread...t=#post4483347
_.......
_..............And finally came up with a Function to replace a call to the spreadsheet like ( pseudo code )_..
StringValue = OpenWorkbook_Cell( r, c )_Value
_...
StringValue = FunctionGetFrmClsdWorkbook(ClosedWorkbook_Cell( r, c )

http://www.excelforum.com/showthread...t=#post4483417

_ It was quite an involved and detailed discussion and final code. It did all eventually work.. But I gave up waiting for the code to work through. So I redid the code, and hardcoded , rather than using the Function. As I understand it, Functions are only for convenience, and tidiness. In this case I had some extra lines checking that the closed File existed etc... etc.. So I thought if there was any mileage at all in this method I would strip it down to the minimum, so why I was at it I did away with the Function all together.
To replace the “tidiness” of a Function and for clarity, one just needs to very carefully write out a “Blue Print type code line, showing the direct equivalent of a “Spreadsheet Cell call interaction” with an Open Workbook with the equivalent = ExecuteExcel4Macro( code line.
_ Full details and explanations are given in the above referenced Threads. Here just summarised: ( using the actual Code lines for my code example )

Code lines Preliminary Stuff for open Workbook

Please Login or Register  to view this content.

Code lines Preliminary Stuff for closed Workbook

Please Login or Register  to view this content.

_...............................................

Typical “spreadsheet interaction” code bit for open workbook

StringValue = wsBgAcs.Range("J" & JayRow & "").Value

Typical equivalent using = ExecuteExcel4Macro(

StringValue = ExecuteExcel4Macro( RefClsdws & Range("J" & JayRow & "").Address (, , xlR1C1) )

_.. with JayRow = 2 the string for the ExecuteExcel4Macro argument looks like
'H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\Sept2016\[NutritionalValues2016.xlsx]NutritionalValues'!R2C10

_.......................................

So I wrote the code _.... started it running_.... A day later it is still running, .. and hasn’t yet filled in the first 1 Dimensional String type Array ( 0 To 679238 ) .....

_...............

So while I am waiting I am looking along simplified things along these lines:

Originally Posted by xlnitwit

A further thought- if you have a database program such as Access or SQL Server Express, you may consider moving the data into it. It will be a better place to store so much data and you can query it from Excel as required. ...

ACCESS I do not have and SQL stuff is a big mystery to me my – sounds something to do with Computer stuff.. which I know nothing about, lol... I have a bit of experience in the VBA type stuff of the form like_...
Open FullTextFilePathAndName For Input As___
Open strFullPathAndFileNameForPrint For Output As ___
_.......and looping to read in or print out a line at time_...
_........... so i did a code for that, but I expect that is not the best way to do that...

Originally Posted by xlnitwit

.. Although you can do the same with a workbook as the data storage, it does not work well unless your data is of consistent type within each column.

Not quite sure what that last bit is saying..._..
_...But anyway I have started other Thread in parallel to look into this text file idea.. in particular I am thinking there is some way to efficiently get a whole column of data from a text file “in one go” rather than looping as I am in my Read ( Input ) and write ( Output ) text File attempts. Ideally I would want the columns put directly in an Array
http://www.excelforum.com/showthread...44#post4484344


Onward !
Alan
Bollox

By way of an update, I did some testing yesterday and I believe the memory issue lies in the application.match loop and its use of Range objects. The solution I think is to use an array of data and loop through it (match does not seem to like arrays with long text in them, which you have). It will be slower but use far less memory. My initial tests were around 30minutes but I have not verified the data as yet because I had made numerous other changes first.

Originally Posted by xlnitwit

By way of an update, I did some testing yesterday and I believe the memory issue lies in the application.match loop and its use of Range objects. The solution I think is to use an array of data and loop through it (match does not seem to like arrays with long text in them, which you have). It will be slower but use far less memory. My initial tests were around 30minutes but I have not verified the data as yet because I had made numerous other changes first.

Hi xlnitwit,
That is all very interesting, thanks
That sorting part of the code and the use Application.Match with a Range Object as second argument came out from this Thread
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
The original reason for posting that Thread was that I had previously got in the habit of using Arrays in most sorting codes. But hen suddenly I then got caught out when I found that VBA .Match worked differently for second argument Array of Elements Variant Types or String Types for Strings of over 255 Characters!! ( To do a simple conversion of the Range Object to an Array using_.,.
Range.Value
_..would return me Variant Elements )

( Note the problem only occurs for Variant Element types, String Element types are fine ) That curiosity was never really explained, but_..

_..I then went on to make many numerous test, and found that the use of Application.Match with a Long Row Range Object as second argument was by far the most efficient. It also did not have the 255 String limit.
http://www.eileenslounge.com/viewtop...=22512#p175343


After that experience I changed my opinion slightly about Arrays_...
http://www.excelforum.com/showthread...95#post4380613
_... I still do favour them, but note that there are occasions when a Worksheet Function with a Range object works better. My gut feeling is that that is the case here

I cannot do a direct comparison here with a substitution of an Array through
Array() = Range.Value
as I am limited to the 255 column Limit when using Arrays in Worksheet Functions( I have a bit under 2000 )

What approach did you use in the test which took 30 minutes?

The codes I used for those speed test here
http://www.eileenslounge.com/viewtop...=22512#p175343
had an extra complexity, ( - The initial Heading String used as the search first argument in the .Match first argument ( my Nutrition Name , ) had also 20 variations to be gone through. In this example there is just the one in the data File and 20 Header spelling possibilities in the File to have the final sorted rows pasted in). Using a very simple Arrays only code in that previous work changes the time per row from 3.1 secs to 52secs!! - So it might still be a possibility here. 30 minutes may bot be too bad

What was approximately your time taken to produce the Full File you sent me ?

_.. It is very helpful you taking the time to look at this my resources are a bit tied down....

_...... As way of an update on

Originally Posted by Doc.AElstein

...
Second attempt at code to get data from Big Closed Workbook
_.... Second attempt at code to get data from Big Closed Workbook with the “_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...

_.. Day 3 : The first long column Array has not been filled yet ! ( In the spreadsheet interaction codes I still use an Array initially to get the Product Names from which to get a unique list with the MSR Dictionary )
( I assume the code is still going – I could put a _...
Application.DisplayStatusBar =
_...in it , - but I think that often increases the time as well – maybe not significantly here Lol.!
It may be time for me to stop that experiment ..)
_...


_.. So many possibilities to try.. a bit overwhelming!


Thanks again for you help.

Alan

The following version of your code, placed in a separate workbook, runs in approximately 15 minutes on my laptop and seems to produce the correct results. I have not done a full comparison with the previous code however. The memory usage never exceeds 500MB as best I can determine.

Please Login or Register  to view this content.

I would toggle your code, if I could..--... Ooh er missus !! ;-) :-)

Thanks xlnitwit
_... I see you have done a number of changes, thanks for taking the time again to help.

So _.. Comparing your uploaded code form last post_...
Using My 32 Bit Vista with 32 Bit XL 2007

First rechecked current codes of mine ( In Third ( separate ) Workbook )

My Array Code ( Still with Long Row Range Object Sort sort )
Sub USDAToDBArraysThirdWB()
After about 35 seconds, bombs out around [COLOR="#008000"]345 rows ( Results approximately the same If I “close “NutritionalValues.__ “ after filling Arrays

My Spreadsheet interaction Code ( Still with Long Row Range Object Sort sort )
Sub USDAToDBSpreadsheetThirdBook()
After about 40 seconds, bombs out at around 370 rows (“NutritionalValues.__ “ must stay open for spreadsheet access to the Cells )

_......................
Now your code version_..

I note in passing...:

_1) you have done away with my extra 1 D Arrays, and just use the Initial Captured ones . Fair enough, .. I do not think that makes much difference after I had erased the Captured Arrays, but it is probably just a bad habit of mine to make extra 1 Dimension String Arrays.. but a minor point I think..

_2) You set the Dik to Nothing, - I think / thought Diks , Collections and the Like used a memory outside Excel.. but again I expect this is not a major contributor.. _... Interestingly, I stopped using things like
Set Dik = Nothing
After advice from experienced people saying only use them when you need to ( argument being you do not need to , so do not mask a situation when you may need to ).. This could be one of the few cases where it may be relevant to do it

3) I have not come across
Dik.RemoveAll
_.. can you explain the reasoning behind that?

_ 4) see you make the whole output in a large Array ( Multi “row” ) , then fill that in the middle of the Loop and only at the end paste the large Array out. I make a 1 Dimensional ( 1 “row” ) Array at each loop and paste that out each loop. Your way is what i always did do. But
_4(i) for files not much bigger My code crashed as my excel could not handle such big Arrays, and
_4(ii) just my tendency to work with 1 Dimensional Arrays as I was finding them on average very fast..

_ 5) You transpose the Dik.Keys() rather than my building of a 2 D 1 “column” Array and pasting that out. Personal choice again, I mentioned my preference there before..

_ 6) You have taken out my “ “Bodge” to allow Loop to overshoot 1 row “ and replaced it with

Please Login or Register  to view this content.

_... which jumps out at the extra row. I thought that idea added an extra If in the Loop, which I thought best to avoid, . – but a minor point – your way is probably tidier, more professional etc..

Anyway.....
In general your code is “doing it all in Arrays” , what I personally prefer, and allways did do, and mostly still do, and only occasionally go away from for the reasons noted in post #36
_......................
_._______________--

I had a feeling that I might get some problem due to that large Array for output - , so I put a stop there at the point where it is about tp be pasted out to “DBSept2016.__”

After about 35 Minutes The code got there..
Hovering on the Array arrDBlxRowHolder reveals this
StopBeforePasteAttempt.jpg
http://imgur.com/wzNU0a9

.. or so I thought..
and setting a watch in the watch Window gives similarly:
ArrayTooBig.jpg
http://imgur.com/rRHCeF2

_... or so i thought !!
So I guessed what would might happen -.... I hit F8 expecting the worse.. and

But ... I was wrong !!!! :-) :-) :-)

After about 3 minutes I appear to get all the rows pasted out !!!!

Wow. I would never have guessed that this solution might work !!!!!!!

I have now two complete Files , one from you done on your computer with my Array code, and one made with your code on my computer. They appear the same, and have a similar memory :-)
NitWiit2results.JPG
http://imgur.com/krkyJ57


What on earth is this telling me about what Excel is doing????

Is it that at every Long Row Range Object call, Excel set aside Memory for that Range Object, and never refreshed cleared it, as most of the results to date in this Thread are suggesting.

Maybe this shot in the dark is the answer ???

_.. We know we can go a long way back with the backward symbol to get at an earlier point. Is it that for every point and change, Excel holds an entire Worksheet in the Memory it sets aside for use. This will not show up on a File size – the record of previous states is not held in the File stuff – we know that what we have in front of us is not a File, but just a copy of it.
http://www.excelfox.com/forum/showth...-Name#post9949
For that Copy ( and all other files “open” ) it has a record of all the previous states. .....
This maybe the point of what Humdingeling meant with his Toggle ( Post #10 )
http://www.excelforum.com/excel-prog...ml#post4481685
If I knew how, I would “Toggle” your code... ;-) ( in the nicest possible way :-) – don’t want to rub any one up the wrong way and get infracted... )



_._-------_______________________________-
_.________________________________________--

_..
Update on my

Originally Posted by Doc.AElstein

...
Second attempt at code to get data from Big Closed Workbook
_.... Second attempt at code to get data from Big Closed Workbook with the “_....................“perform the reference “ or “Get the reference” method or GetValue function way
GetReferrence = ExecuteExcel4Macro(FullReferrencecell)
Where GetReferrence , a String variable gets given the string of the held for the cell value..

Let GetValueOfClosedWBCell = ExecuteExcel4Macro(StrTemp)
.............................” ........idea !!...

So I gave up after almost 4 Days and stopped the code ...

_... I Added a Status Bar to give me Cnt ( I had not got past the initial Column J “row by row capture” yet .. )
_ I started again using 2 different computers
A 32 Bit Vista with 32 Bit XL 2010
and
A 64 Bit Win 7 with 32 Bit XL 2010

_...Interestingly they both consistently got “hung” up at 50 Cnts. Also if I stepped through in Debug ( F8 ) they did not appear to get hung up. Further interesting, - Say I step through up to several hundred Cnts. Then I hit F5 to let it continue the rest running normally – I then find it gets hung up again at about 50 extra Cnts from where I had reached in debug F8

_. So I added a 1 second wait at each Cnt Loop
_.... That was a few hours ago:
_... The Vista is now at about 7000 Cnts now, and the Win 7 machine at about 10000
Maybe this is telling me that Excel is “tripping up” or not waiting for the ExecuteExcel4Macro( to do its stuff or finish properly or something along those lines.
Possibly there is some more professional Wait thing like ( pseudo code )

Do while something is not quit finished yet
Something here I am not sure about
Loop

I have seen code bits like this before in some Internet scrapping codes,
I have never been able to get a straight answer about what is going on there
http://www.excelforum.com/showthread...t=#post4479320

I have also found and heard that adding a wait empirically works better..

In this code of course, adding a fixed wait of a second is a bit undesirable !! It would then take at least ( just for the first long Column J Capture ) about
679238/( 60 x 60 ) = 105 Hours !!!!
Hmmm.....maybe I should stop and try less, like 1/10 second.. I did it hung up at 24 !? , then it went on after i hit Escape ?!
Oh dear.. a lot of time consuming experiments here..

Onward___
Making good , even if a bit slow, --- progress :-)
:-)


Alan

Hello

I just read some of what I said in the last Post, .... some of it could be bollox.
.. or so I thought
The bit about Excel having a non clearable record after every long line paste may have been OK,

But i am not ( yet ! ) convinced that the Range Object as second argument in a .Match is necessarily a bad idea. But I could be wrong, - I am not interested in so much in what I end up doing but the Why and What is happening is important to me.

So to that end,- current experiment
Background.
I always used to build up a big final Output Array within, as it were, VBA to then paste out in one go. In answering many Sorting Threads I still do. I had gone a bit off it with my own work as I have worksheets I use daily for me and my Wife’s Diets that are from about 2 – 4 times as big as the final Output Array here. I have experienced Problems , that is to say it just don’t work capturing and pasting out Arrays of those size. ( In addition when I modify or add in one bit of stuff we eat, it is better to just update a row and then add the increased Nutritional values to to the current total etc.. etc..
In any case I got a bit side tracked from the idea of filling a large Array for output as an alternative for this particular problem.

So what to do for this post
As a first step I will simply modify my_..
Sub USDAToDBArraysThirdWB()
_...just so as to fill a big Array for output. See what happens then take it from there...

So the key to it working, I am ( was ) thinking is this bit from xlnitwit code: ( and the subsequent one off pasting of it at the end )

Dim arrDblxRowHolder() As Variant
ReDim arrDblxRowHolder(1 To UBound(UniqueFoodNamesList) + 1, 1 To lcDBlx - 10)

I think I might like to keep this a Variant, as I did my Long Row Output Row Array, arrDBlxRow() as I prefer to leave Empty Cells as Empty, rather than them converting to vbNullString for strings, ( worse, - 0 , for Numbers – as that can really screw up average calculations when = are included rather than, as required, blanks being ignored )

My Row Output Row Array,arrDBlxRow() Dim and Re Dim is now commented out. Note I will not now replace it at that point with Dim and ReDim arrDblxRowHolder(). The reason for this being that arrDBlxRow() was deliberately REDim ed without Preserve at the start of the =Main Outer Food Produkts Loop=
‘4a
so as to empty it for the next rDBlx ( the for Output Worksheet , in DBSept2016.__ File ) . rDBlx had defined the “K” & rDBlx output Top Left for each Long Row Output Row Array, arrDBlxRow(). Correspondingly it started at 20, being incremented just before the_...
“K” & rDBlx = arrDBlxRow()
_..bit.

Now in the new code and as in xlnitwit's rDBlx is free to use for our “row” in the big Output Array, arrDblxRowHolder() . A good point somewhere back up in the code to Dim ( and Re Dim only once now ) for arrDblxRowHolder() would be , for example , in the
490 Rem 2) Dogs Bollox Workbook
_.. like what xlnitwit did. There we have lcDBlx, the Last column in the for Output Worksheet , in DBSept2016.__ File and just before this at
‘1f ) we have the unique Diktionary Keys ( Unique Produkt names effectively ) and can get the total then for output data row number from something of the form, pseudo,
UBound(arrKeys() = Dik.Keys())-LBound(arrKeys() = Dik.Keys()) +1
So
522 Dim arrDblxRowHolder() As Variant
524 ReDim arrDblxRowHolder(1 To UBound(arrKeys()) + 1, 1 To lcDBlx - 10)

So for this simply first Big Output Array example we need to comment out the Long Row Output Row Array Paste line which just inside at the end of the
1090 ===Main Outer Food Produkts Loop
and just outside this have a new line only to be done once right at the end
1095 Let wsDBlx.Range("K21").Resize(UBound(arrDblxRowHolder), lcDBlx - 10).Value2 = arrDblxRowHolder()
which is exactly what xlnitwit did.

All that remains now is to modify the central bit of the code to add things to this Array rather than the Long Row Output Row Array, arrDBlxRow(). So the Value of Found Nutrition is put in Array for output at the position of the matched Nutritional Heading Name as before, but also now at the appropriate rDBlx ( rDBlx used now as starting at 1, - our intention being to paste the final Array out from Top left at K21 , as seen above line 1095.

So I did all that.... ran the code...
Sub USDAToDBArraysThirdWBarrOutLongRangeRow()

Poo ! It bombed out again at 348 rows.. I did not expect that !!!

Have to think again !!

Bollox ( well at least some of what I said in the last post was not as much Bollox as I thought at the start of this post ?!? )..... Bo....

I am determined to get to the bo..ttom of this....

Later..
Alan



_._________________________________________-

_...............................................
( Code Sub USDAToDBArraysThirdWBarrOutLongRangeRow()
http://www.excelforum.com/showthread...=1#post4485268
and here
http://www.excelforum.com/showthread...66#post4485266


And in this file
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

Sub USDAToDBArraysThirdWBarrOutLongRangeRow() '
Sub USDAToDBArraysxlnitwit()
And the original ( bombing out in 32 Bit )
Sub USDAToDBArraysThirdWB()
Sub USDAToDBSpreadsheetThirdBook()

Hi
Adding some further Experiments along the way to solving this Thread..

Doing it one stage at a time, so_...
_.....Next step is the idea just to take out the “.Match , , second argument Long Range Row” search.
( so for this first experiment still Build Paste Long 1 D Array (DBlx "row") in each Loop So 8790 Long row paste outs in a run )

As mentioned previously a simple .Value tacked on the end, like_..
Arr()=RangeLongRow.Value
_.. will not work for us, due to the 255 column Limit for an Array in Worksheets Functions
( _....It has to be restricted ( hence limit lengths 255 also in complex offset Brushes , correspondingly “housed” in Variants to Call byVal and as such also necessarily a String length truncated to the smallest length 255 therein , wonks... ))
http://www.excelforum.com/excel-prog...ml#post4238685
http://www.mrexcel.com/forum/excel-q...ml#post4375354
http://www.mrexcel.com/forum/excel-q...ml#post4376533
_....
http://www.mrexcel.com/forum/excel-q...ml#post4336751
_.....)

So we go for a full Array nested Loop search. We require a completely new Array Variable, ( vData xlnitwit code )

Please Login or Register  to view this content.

Later we do a full array match when __ = __ nested pseudo

__For rDBlxHed ‘ rDBlxHed was used as vertical indicia 1 To 18 for a long Range Row of each heading row, now as the Header Array arrDBlxHead( rDBlxHead , _____ ) ,
____For nixHd ‘ nixHd new variable stepping “along” like what .Match did looking for a match – we do it “manually, stopping at condition is
______IfSearchdHeading = arrDBlxHead( rDBlxHead , nixHd ) Then
_______ 'Fill in “Horizontal” position “along” in 1 D long “row” Array

So Thats it_...

_ ... Tried it

Bingo!

Works! ( All be it somewhere between 5 to 8 hrs !!!!! )####

So
_1) it would appear xlnitwit post #35

Originally Posted by xlnitwit

.... believe the memory issue lies in the application.match loop and its use of Range objects. ....

.. may have something in it
_2 ) Possibly puts the mockers a bit on my Theory about the redo thing, - my idea was that every paste out causes Excel to make a complete back up copy of the entire Spreadsheet, ( which for some strange reason it never clears – (well it wouldn’t in such a “storing for an undo” case I suppose) ..)

_ I cannot figure out why VBA is throwing a memory Paddy after referencing ( presumably [color = blue]ByVal[/color] ) the Long Range Row Object in the .Match. After all it is referencing the same Object. Maybe it gets its knickers in a twist and thinks it is “doing” something to the spreadsheet so makes an erroneous back up copy that it never needs.. Or every call to the range object puts a whole lot of data to be used in that ,Match somewhere and that “place gets over filled. I note again here my earlier findings here:
http://www.eileenslounge.com/viewtop...175115#p175343
_....... that replacing the Range Object with a simple arr()=RangeLongRow.Value ( not possible here as discussed in last Post , but if it was anyway ) in the Worksheet .Match Function does not improve the time, and is infact worse – my Theory there was the Workksheet Fuctions are optimised for use on spreadsheets and that they may “pseudo” conver to a Spreadsheet range and work on that causing increase time, and who knows, then making VBA do its weird store that at every call of range in memory thing.. )

_ #### The very long time is sort of understandable, I guess. Although I did think a long row Paste out once in the loop was fairly efficient, even for 8790 rows I did not expect that such a large time difference would occur. The difference to about 30 minutes for adding to a 8790 “row” Array at each loop and pasting that out once at the end surprised me a bit. ( The main difference to xlnitwits code and this latest one ) ( Possibly just now I will do that next mod to this code and post the results of that shortly |||| I expect then similar results to xlnitwits code, - if not then I will need to look again at the other mods which I was only expecting a small improvement from..


_ I must learn to Toggle, do some more accurate speed tests and do a few of the other changes we discussed here to optimise*** and possibly learn a bit more of what is going on. ( ***This code took a lot longer than xlnitwits so maybe the big output Array build may be more appropriate, for example. Exactly how long the code took I am not sure – after an hour I went away and left it running, went to bed , when I went for a **** it was running 5 hrs, and 8 hrs late it was finished - need to put a time in for all these long runsxxx )

__ I will try to do that if i have time and wrap the Thread up. .. later

_ I will “leave it “ open to go off at a tangent for a bit on the other two outstanding ideas..( But I might just quickly do the next code |||| with a big 8790 “row” Array fill per loop to be pasted out at the end )

_ the text file solution thing
http://www.excelforum.com/excel-prog...al-arrays.html

_ the closed Workbook idea
http://www.excelforum.com/excel-prog...arge-file.html

_..............................



Latest code used for the experiments of this post here: ( two parts to fit in due to Post size restrictions, but as usual it is all just one file )
http://www.excelforum.com/showthread...t=#post4486014
http://www.excelforum.com/showthread...t=#post4486015
I did notice a stupid mistake on all my codes in the error handle section: I had my Message box after the .ScreenUpdating = True which meant I was looking at a black screen Message box sometimes! So I changed that. While I was at it put a timer in... xxx ####

Please Login or Register  to view this content.

_.................................

Files as before , updated where appropriate::
The Third Workbook File ( which also has the code in it ) is here:
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

The massive data Workbook is here
NutritionValues.xlsx
https://app.box.com/s/rr9poitdmxits6a10g7k5c7ujz0lkylf

The File to be filled with sorted data is here
DBSept2016.xlsx
https://app.box.com/s/1vjlvdm1n4bmlcu61f2dyxrzx9lh92oh

_..
Getting there


Alan

Originally Posted by Doc.AElstein

_ I cannot figure out why VBA is throwing a memory Paddy after referencing ( presumably [color = blue]ByVal[/color] ) the Long Range Row Object in the .Match. After all it is referencing the same Object.

Indeed- this is the part that is most interesting to me.

I am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. Thus at worst there should be 18 blocks of memory used. My testing leads me to believe that this is not happening with your code but I have not as yet determined the reason for that- assuming my belief is correct, of course.

Originally Posted by xlnitwit

.. am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. Thus at worst there should be 18 blocks of memory used. My testing leads me to believe that this is not happening with your code but I have not as yet determined the reason for that- assuming my belief is correct, of course.

Interesting. I have no idea how these things work, but am still puzzled why Referencing the Range Object ( rnglongSrcRow ) so many times effects memory ( BTW I meant in the last Post to say 18 times, not once, for the 18 long Range Rows of Header Names ( Nutrition names ) )

Of course if that is the problem then , we are talking I think of theoretically up to 18 x 679238 = about 12 Million calls of those Range Objects. As it catches the header often in the first half of the rows then maybe roughly 5 Million in the practice ( we both sensibly Exit For when the match is found)

A naive idea was to continually after use Set rnglongSrcRow = Nothing

Has no effect, did not really expect it as I guessed the next Set rnglongSrcRow = Something must do similar anyway, sort or, resetting, as it were.

(_.... And just for fun I did a UsedRange ..910 Set rnglongSrcRow = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(rDBlxHed, 11), wsDBlx.UsedRange.Cells(rDBlxHed, lcDBlx))... well - I thought naively it might reset some other memory coincidentally as well as the UsedRange memory ... , Lol...
http://www.excelforum.com/showthread...t=#post4487154
_.......) .. still bombed out at about 350 rows.

Time was bit difficult to see as it sometimes has not enough memory left to display the message Box with that info. So I paste that out also for the Error handling section ending of the code. Time averaged over a few runs was 20 minutes compared with 23 minutes for no extra .UsedRange on 64 Bit Win 7 machine with Xl 2007 32 Bit -------

_................................

Anyway, an update.

That long last run of my code that only replaced the .Match with second argument Long Range Row with a neted For For on ta capture Array of the headers) would appear to have been erroneous from the time point of view ..
I have redone the tests, adding a timer in the codes and now have averages of

xlnitwit Code Full Arrays including building a large Array() for output of all sorted rows and pasting that out in one go. ( includes the nested 2 For Loops shown below for my fist code that worked )
Sub USDAToDBArraysxlnitwit()
32 Bit Vista Xl 2007 32 Bit ------- 38 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 29 Minutes

My very first Array code just modified to replace the .Match with second argument Long Range Row with a nested 2 For Loops,
Sub USDAToDBArraysThirdWBMtchArr() __For rDBlxHed ‘ rDBlxHed was used as vertical indicia 1 To 18 for a long Range Row of each heading row, now as the Header Array arrDBlxHead( rDBlxHead , _____ ) ,
____For nixHd ‘ nixHd new variable stepping “along” like what .Match did looking for a match – we do it “manually, stopping at condition is
______IfSearchdHeading = arrDBlxHead( rDBlxHead , nixHd ) Then
_______ 'Fill in “Horizontal” position “along” in 1 D long “row” Array

Sub USDAToDBArraysThirdWBMtchArr()
32 Bit Vista Xl 2007 32 Bit ------- 85 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 47 Minutes

So actually not too bad. That was more than what I was expecting - - The little bit of experience that I have suggested to me that pasting a long row of info out is not too inefficient.

I am still a little apprehensive of having such a massive Array, not as I do not like them, I love them, - but they are close to the current size at which my computer has refused to have anything to do with other such Arrays
Also in the case of a bomb out or crash I still have some data.

But just for completeness I will go one step further with my code and fill a big Array for final output at each loop rather than pasting the row out each time.

So_...
Sub USDAToDBArraysThirdWBarroutMtchArr()
_...this will be Full Arrays Code, that is to say, - Match Loop Header Array For For - Fill Big Output Array in each Loop - Paste that out in one go. Basically similar to that from xlnitwit, but without the many other refinements / simplifications to my original code. So basically this code is xlnitwits code with a lot of extra 1 Dimensional Array building and extra unnecessary steps with extra variable , not putting the Dik = Nothing or the Dik.RemoveAll etc... stc... _-......

Latest Code Basic code idea and description:
_.. copy my last code and change it a bit... What is missing is the bits relating to the large Output Array

_ Dimensioning a new Big Output Array, arrDblxRowHolder(), ( Variant Elements are chosen to allow me to paste out empty rather than unwanted null Strings or 0’s )

_ A Long type variable used in the count of rows in output Worksheet, set to 20 originally to be increased to 21 just before first output, is now set to 1 and will be increased at the end of the Loop for each Food Product, once the “row” in the Big output Array has been filled. Then this will mean that the Array is pasted finally towards the end of the Code at Top Left of K21

_ ' Comment out the Single “row” Array dimensioning

_ ' Comment out the filling line of the 1 Dimensional Long “row” Array just inside at the end of the
1090 ===Main Outer Food Produkts Loop
and just outside this have a new line only to be done once right at the end
1095 Let wsDBlx.Range("K21").Resize(UBound(arrDblxRowHolder), lcDBlx - 10).Value2 = arrDblxRowHolder()

_...........................

So, I did all that

Code:
Here: _..
http://www.excelforum.com/showthread...19#post4487619
_.. and here:
http://www.excelforum.com/showthread...=1#post4487621



_ So after a while it worked. ( it was Less of a surprise this time. - It is basically a version of the xlniwit code )
Sub USDAToDBArraysThirdWBarroutMtchArr()
32 Bit Vista Xl 2007 32 Bit ------- 85 Minutes
64 Bit Win 7 Xl 2007 32 Bit ------- 47 Minutes

The times are a bit longer than I expected, the many other modifications / simplifications may be a bit more significant than I thought. So I will try to look at that at more detail sometime and optimise it a bit.

Also in parallel still not giving up on the .Match ( , secondargumentLongRangeObject , ) as previous results suggested that way ( for single runs on similar sorting codes then ) was very efficient.

_...................

Perhaps for now I will look again at one of the parallel Ideas: _..
_... the text file solution thing
http://www.excelforum.com/excel-prog...al-arrays.html

Alan

Ps. Edit...
Just adding a short bit on the way to solution for this Thread

Originally Posted by xlnitwit

_....I am aware that a new object reference will be returned each time a call to Range or Cells is performed but objects already on the heap and no longer in use should be reused. ....

Originally Posted by Doc.AElstein

.......still not giving up on the .Match ( , secondargumentLongRangeObject , ) as previous results suggested that way ( for single runs on similar sorting codes then ) was very efficient. .....

_ I had a last few ideas that I thought were highly unlikely to have any effect, but in my short computer career I have found it best to empirically check, as weird things can happen. Also Sod’s Law says if I do not try a particular idea , then that will be the one that would have worked.. So best I spend a lot of time to make sure I do not have a solution


I am actually Setting my Range every time in the looping, which amounts to about 5 Million times.

Please Login or Register  to view this content.

_...............
I thought I should try some variations on that, to reduce the Settings. The reason I hold little hope of this working is that when you Set an Object you just “write in” a lot of “pointers” type things saying “where” the “things” ( Methods, Properties etc ) are, but in actual effect you do not store at that point a lot of big Arrays of the ranges cell Members values, the Range cell Members Colors etc.. etc.. In fact you just have a lot of references to the spreadsheet. It is this last point that normally means referencing the spreadsheet was slow, ( but for my particular example, from previous work
http://www.eileenslounge.com/viewtop...175184#p175343
I had found the .Match( , secondArgumentLongRangeObject, ) very quick..

_.......
So 3 things I thought to try

_1) Just substituting the Range in the .Match Formula direct:

Please Login or Register  to view this content.

No noticeable difference in the Results at all with this, .. I guess VBA is still Setting somehow the range every time.
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 330 rows at 22 Minutes

_..........................................

_2 ) Set One Range and just offset that 18 ( 17 ) times. ( In fact the offset code version
http://www.eileenslounge.com/viewtop...175184#p175343
came out particularly good, ( my guess being Excel as we “see it” is all about offsetting from one call that is Excel )

So Dim and Set right outside all Loops

Please Login or Register  to view this content.

And then in the Loop for the 18 Ranges ( or 1 offset by 18 ( 17 ) different amounts )

Please Login or Register  to view this content.

Results for
Sub USDAToDBArraysThirdWB18RngOffsets()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 340 rows at 29........ Seconds
.. Well bugger me with a barge pole.. I am still Setting each Range, just as many times as before.. but what a difference offset makes... somehow supports my wild ideas about how Excel works. Somehow this makes Excel very efficient... It is making / forcing Excel always use the one Range .. and offsetting i expect does work very efficient as ( my theory ) everything Excel does is based on offsetting, so just changing the offset ‘values’ a bit makes no great difference to what is was doing anyway.
_.. Still does not help with the “bombing out due to ( presumably ) memory problem
_.........

_3) A very tedious idea ... set 18 specific Ranges outside all loops and go through each one. Effectively taking out the Inner most Loop For rDBlxHed = 1 To 18, and going down a list of trying 18 Ranges ( In the unlikely event that this did work, I could put those ranges in an Array ot a VBA Collection Object and then loop again. But at this stage I thought it was sensible not to add anything else that ( shouldn’t but ) might have strange unexpected effect.. ( But first I wrote a code to wipe out my code Lines_...
http://www.excelforum.com/showthread...39#post4488539
_...– I naff myself off with my own weird habits sometimes.. )

So outside all the Loops I have

Please Login or Register  to view this content.

_.. and equally boring at the point where the inner most Loop is we now have 18 similar code lines

Please Login or Register  to view this content.

Results
Sub USDAToDBArraysThirdWB18RngObjects()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 342 rows at 22....Seconds
Again a very respectable time !! ( even a bit better than 1 range with 18 ( 17 ) offsets – need to recheck a bit and average to confirm....)


_............................

So some very interesting results on the time side..

But still something very weird going on, apparently Excel eating up ( permanently ) a chunk of its memory for each Range Object call....

_........

Alan

p.s Updated Macro File
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

VBA is a weird Bugger when it Sets Ranges. It like Offsets. It holds ‘em differently

Just a quick update / recap before I move on or lose what is left of my sanity...

Codes looping a Line pseudo like
= .Match( , SecondArgumentRangeObject , )
or
= .Match( , rnglongSrcRow_x , )
Where x can vary for a different “Long Row” Range Object.

Codes where SecondArgumentRangeObject changed in each Loop by different Offset to a specific Range
Sub USDAToDBArraysThirdWB18RngOffsets()
Set rnglongSrcRow1 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(1, 11), wsDBlx.UsedRange.Cells(1, lcDBlx))
Set rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed, 0)
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 348 rows at 24 Seconds

Sub USDAToDBArraysThirdWB18RngOffsets()
Set rnglongSrcRow1 = wsDBlx.Range(wsDBlx.Cells(1, 11), wsDBlx.Cells(1, lcDBlx))
rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed, 0)
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 343 rows at 25 Seconds
_.....................

Codes where .Match code line is repeated so that each one uses a fixed Range Object
Sub USDAToDBArraysThirdWB18RngObjects()
Set rnglongSrcRow4 = wsDBlx.UsedRange.Range(wsDBlx.UsedRange.Cells(4, 11), wsDBlx.UsedRange.Cells(4, lcDBlx))
x 18 etc ( just “row” 4 given here )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 336 rows at 23 Seconds


Sub USDAToDBArraysThirdWB18RngObjects()
Set rnglongSrcRow4 = wsDBlx.Range(wsDBlx.Cells(4, 11), wsDBlx.Cells(4, lcDBlx))
x 18 etc ( just “row” 4 given here )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 23 Seconds

Some slight variations on the couple above
Sub USDAToDBArraysThirdWB18RngObjects2()
Set rnglongSrcRow4 = wsDBlx.Range(wsDBlx.Cells(4, 11), wsDBlx.Cells(4, lcDBlx))
With the entire 18 Set Long Row Ranges within the Loop
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 341 rows at 25 Seconds

Sub USDAToDBArraysThirdWB18RngObjectsFrmOfst()
( The range Objects are made ( Set ) from Offset from first Long Range Row Object )
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 348 rows at 24 Seconds

Back to the original Codes for a check:
Sub USDAToDBArraysThirdWB()
Here a code line like
Set rnglongSrcRow = wsDBlx.Range(wsDBlx.Cells(rDBlxHed, 11), wsDBlx.Cells(rDBlxHed, lcDBlx))
Sets the range each time. The same Long type variable, rDBlxHed, used in previous codes is used to specifically reference the Range of interest each time in the Loop
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 21.... .. Minutes !!

Sub USDAToDBSpreadsheetThirdBook()
This is a simple "spreadsheet interaction code, - as a matched heading is found, the Nutrition value is pasted directly to the Output Workbook ( DBSept2016.xlsx ) - so no building of Array of multiple values to Paste out in one go
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 368 rows at 25...... seconds!.. This is a bit wiered.. the code is basically the same as the one that takes 21 minutes ??
Because of this last bit, I added a line to paste out arbritrarily to Workbook DBSept2016.xlsx in the code Sub USDAToDBArraysThirdWB(). So this effectively theoretically does away with the advantages of doing it in Arrays then pasting out in one go..
_... the results were then
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 342 rows at 25 Seconds. Another mad result!!

_.. Just putting all that in words

If I have a code line, pseudo

= .Match( , SecondArgumentRangeObject , )
and I have it in a Loop.

It takes in my codes
_1 ) Minutes if
SecondArgumentRangeObject
is Set at each loop to a different Range Object ( specifically like Set = Range(“___”) )

_2 ) seconds if

_either
SecondArgumentRangeObject
Is always the same Range Object
_ or ( and this is the amazing thing )
SecondArgumentRangeObject
is Set at each loop to a different Range Object via some offset to a fixed Range.

One could say, VBA does not like to change the Range Object that is used in a = .Match( , SecondArgumentRangeObject , ), unless that changed Object is changed via an Offset to a single Fixed Range Object ( But if you interact with the Worksheet it settles down !!! )

In other words if I have _...

rngX = Range(“A2”)

and

rngY = Range(“A1”).Offset(1, 0)

_.... they both refer to Cell A2

But Excel VBA handles them differently

As long as all my different ranges are Set as an Offset ( that Offset can vary each time ) to any Range, then my code will run about
60 times quicker than if I Set each range to the specific Range I want.

That is weird!!! ( and The last bit, is either weird , or an amazing discovery !! )*** And to take the Insanity one stage further- Do the Orange Bit, then put Sub USDAToDBArraysThirdWB() back as it was, save, close, open the file.. run Sub USDAToDBArraysThirdWB(), and it is no longer slow !! AARRRGGGHHHH . ..and the orange phenomena does not always work.... I am losing the will to live : (

Hi
The results from the last Post were so mental, I am encourage to keep as much away from Excel as I can in this project.. then to that end , up the idea from the end of Post #34_...
http://www.excelforum.com/showthread...=1#post4484346
_..... Here the idea is to prevent having to open the large data File, NutritionalValues2016.xlsx , those long columns are put previously in a Text File. Excel is then closed and reopen ( to clear the strange memory of memory used – one of the main findings of this thread ). Then that is got from the text File and put into 3 VBA Arrays ( those already used in the main codes discussed in the last few posts. )
Some preliminary work has already been done for that:
http://www.excelforum.com/excel-prog...al-arrays.html

_... For now, until I can get a more efficient Text File column to VBA Array Code ( Using something like ADODB stuff ) , the code I wrote here will have to do , Sub TextToVBAArray(),
http://www.excelforum.com/showthread...t=#post4484332

_.. They will be incorporated into a code of the form
Sub USDAToDBArraysThirdWB18RngObjects()

_ Briefly what is done to convert that code to_....
Sub USDAToDBArraysThirdWB18RngObjectsFrmText()

_..............is

Rem 1) was all to do with the Big data File, ( what we think is giving at least some of the memory problems regardless of whether we close it later or not – the opening of it is the problem.. ). We use that code section now to bring in the Data from the Text File .
( _..... Here is the text File I made earlier
https://app.box.com/s/q7z0vyu8fm1pd4mchgpw5f90hfbj7do9
_..and the contents are shown here:
http://www.excelforum.com/showthread...=2#post4484311
_...)
So we copy the “innards” from the Text code , Sub TextToVBAArray() , into that code section. That is to say, paste it over all up to the unique Food Produkt Diktionary section

_ That’s it really, the rest of the code stays for now the same for better comparison..

_ Done all that

Results
Sub USDAToDBArraysThirdWB18RngObjectsFrmText()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 446 rows at 46 Seconds

To compare with the similar code which required opening of large XL data File, NutritionalValues.xlsx
Sub USDAToDBArraysThirdWB18RngObjects()
64 Bit Win 7 Xl 2007 32 Bit -------Bombed out 344 rows at 23 Seconds

The improvement does show that opening of the size of the large data File, NutritionalValues.xlsx
, was an issue. But well below the issue the mysterious memory of memory caused by the use of a Range Object in = .Match( , SecondArgumentRangeObject , )
That still dominates.

Oh well, never mind

Alan


Code here ( split to fit in, but all one code )
http://www.excelforum.com/showthread...t=#post4490364
http://www.excelforum.com/showthread...t=#post4490366
http://www.excelforum.com/showthread...t=#post4490368
http://www.excelforum.com/showthread...t=#post4490371


Also in File with macros
MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

File For sorted data output
DBSept2016.xlsx
https://app.box.com/s/rr9poitdmxits6a10g7k5c7ujz0lkylf

TextFile ( Big data File no longer needed )
NutValues.txt
https://app.box.com/s/q7z0vyu8fm1pd4mchgpw5f90hfbj7do9

To help quantity and to clarify the current problem , and as a prerequisite for a simple code specifically for looking at the phenomena, I have included a count for the number of times a Range Object is referenced within_....
= .Match( , RangeObject , )_... and repeated the results. Also , one problem encountered was that when the arousal if the Error Erection Exceptional Situation occurs, the Error handler_..
60 On Error GoTo Lend
_.. does its job going to the error handler section towards the end of the code, but the Memory problem can sometimes prevent the message box appearing giving performance info. However it was seen that these performance values could be pasted out to an arbitrary range in a Worksheet. So the code gives out , just before ending, in the first four cells in the First Worksheet info for:

_ the time taken,
_ the Rows filled ( or the “row” in the big for output Array in such a code ) ,
_ count of the number of times a Range Object is referenced within any .Match( , RangeObject , ) ( for the case of an all arrays code this will be the number of times the __ If SearcHeading =__ part ...Match... !!!!! But I had to remove it to get the full results ?)
_ the name of the procedure.

_.. Although the final number of rows achieved is important for me , this is less a good “performance” measure for the current testing. It will be become less significance as I go off into the theoretical academic discussions of the basis problem.
( Edit : The problem is “Weird memory of memory / uncleared Range Object in Stack or whatever wonk” )
_.. For one of the codes, it will be stripped down to the minimum to look at detail in the problem. This will concentrate on the count of the number of times a Range Object is referenced within any .Match( , RangeObject , ) and will, in its final form, be doing not much else.
_..................

Existing Codes check.
( For all these tests the same computer and XL version is used : - Computer: 64 Bit 4GB RAM with operating System Windows 7 –Excel version: 2007 32 Bit )

Sub USDAToDBArraysThirdWB()
My very original Code. Lost of extra unnecessary stuff but distinguishing characteristic in the main sorting is the use of a code line like
= .Match( , RangeObject , )
18 different Range Objects are used, the Range set each time in the Looping by

Please Login or Register  to view this content.

After the sorting completes a full row, ( held in a 1 Dimensional Array ), it is pasted out in one go in the Loop
( has some mental time characteristics detailed in Post This code #44 )

_..............................
Sub USDAToDBArraysxlnitwit()
This is characterised by being a “full Array” type code , the, “= .Match( , RangeObject ) “ code line replace by a looping through the second dimension, ( “pseudo column” ) , _.....

_.. ( The output “rows” are filled into a large Array for Output in one go at the end – “full Arrays type code” )
_... In addition all the extra stuff is done away with. ( I did not write it ! )
_ The code is discussed in detail inn post # 38 and # 37
_.............................

Sub USDAToDBArraysThirdWBarrOutLongRangeRow()
My original code, with the just the minor difference of filling a “row” in a large output Array in the Loop for pasting out in one go at the end of the Looping.

_.............................................

Sub USDAToDBArraysThirdWBMtchArr()
This is my original code, but doing away with the “= .Match( , RangeObject ) “ code line replace by a looping through the second dimension, ( “pseudo column” ). I still paste out each row once the One Dimensional Array for that is filled

_...............................

Sub USDAToDBArraysThirdWBarroutMtchArr()
This is my original code, but doing away with the “= .Match( , RangeObject ) “ code line replace by a looping through the second dimension, ( “pseudo column” ). In addition filling a “row” in a large output Array is done in the Loop for pasting out in one go at the end of the Looping. So this is basically Sub USDAToDBArraysxlnitwit()
But with still all the extra unnecessary crap in it

_.............................

Sub USDAToDBArraysThirdWB18RngOffsets()
My original code but just referencing the 18 ranges a bit differently , via an offset to the first row Range

Please Login or Register  to view this content.

_.......................................

Sub USDAToDBArraysThirdWB18RngObjects()
My original code but referencing 18 specific Range Objects set outside of any looping. Within the Looping the 1 to 18 loop for the 18 Range Objects is replace by 18 similar code sections gone through ( “down” ) in normal code progression.

_.............................................

Sub USDAToDBArraysThirdWB18RngObjectsFrmOfst()
This is almost the last code. Only difference is that the 18 specific Range Objects set outside of any looping are set by an Offfset to the First “row” Range Object

_............................................

Sub USDAToDBArraysThirdWB18RngObjectsFrmText()
My original code. The data however for 3 very deep “Columns” Arrays are imported from a text File rather than a large XL Workbook. The idea was to remove having to open such a Workbook, as a further anomaly was that XL held a memory of the memory used for such a Workbook , even after such a Workbook was closed. – So once the Workbook was opened a large amount of memory was irreversibly wiped out. The text file idea ( the first of two ) removed the need for the use of a large XL Workbook to be opened , ( at least at the time of the sorting code run. )
http://www.excelforum.com/excel-prog...al-arrays.html

_................................

_.________________________________________________________-


Results: ( Rows 8790 means code ran to completion )

Time	Rows	Match Attempts	Procedure under test	Remarks
00:21:33	Rows 340	rngRefs 153720	USDAToDBArraysThirdWB
00:29:39	Rows 8790	Count removed	USDAToDBArraysxlnitwit
00:28:23	Rows 8790	rngRefs 670481	USDAToDBArraysxlnitwit	Counting only matches
	Rows 3727	rngRefs 2147483647	USDAToDBArraysxlnitwit
00:00:17	Rows 266	rngRefs 123314	USDAToDBArraysThirdWBarrOutLongRangeRow
00:21:55	Rows 8790	Count removed	USDAToDBArraysThirdWBMtchArr
00:05:24	Rows 896	rngRefs 2147483647	USDAToDBArraysThirdWBMtchArr	Take off 2nd Exit For
00:10:05	Rows 3727	rngRefs 2147483647	USDAToDBArraysThirdWBMtchArr
00:10:02	Rows 3727	rngRefs 2147483647	USDAToDBArraysThirdWBMtchArr	Not close data Workbook
00:47:34	Rows 8790	Count removed	USDAToDBArraysThirdWBMtchArr	Take off both Exit For
00:48:18	Rows 8790	Count removed	USDAToDBArraysThirdWBMtchArr	Take off 2nd Exit For
00:21:54	Rows 8791	Count removed	USDAToDBArraysThirdWBarrOutMtchArr
00:05:23	Rows 897	rngRefs 2147483647	USDAToDBArraysThirdWBarrOutMtchArr	Take off 2nd Exit For
00:09:30	Rows 3728	rngRefs 2147483647	USDAToDBArraysThirdWBarrOutMtchArr
00:00:23	Rows 336	rngRefs 152864	USDAToDBArraysThirdWB18RngOffsets
00:00:23	Rows 338	rngRefs 153119	USDAToDBArraysThirdWB18RngObjects
00:00:24	Rows 333	rngRefs 150827	USDAToDBArraysThirdWB18RngObjectsFrmOfst
00:00:47	Rows 434	Count removed	USDAToDBArraysThirdWB18RngObjectsFrmText
00:00:46	Rows 411	rngRefs 181352	USDAToDBArraysThirdWB18RngObjectsFrmText
00:00:23	Rows 337	Count removed	USDAToDBSpreadsheetThirdBook	Not close data Workbook
00:00:24	Rows 357	rngRefs 160136	USDAToDBSpreadsheetThirdBook	Not close data Workbook

Conclusion:

Clearly the biggest problem still appears the use of the Range Object in a .Match ( , RangeObject , ) code line.
A few other interesting things come up. Just adding the count variable , Cnt = Cnt + 1 , in the Looping limits the runs to the Integer Limit, 2.147.483.647, !!!!! - strange I thought I was using Longs ?? . No code will run to completion with that count line included.
Still a mystery why the very first code takes so long - My Old computer seem a bit hot sometimes, thrashing about maybe... I cool it with ice blocks since I have been looking at this problem.

So an attempt to move on,
I will take Subs USDAToDBArraysThirdWB18RngOffsets and USDAToDBArraysThirdWB simplify them down to virtually nothing but the .Match ( , RangeObject , ) code line, then take it from there.

_.........
_....................Next Post..............

_................Continued from last post

Here some steps along the simplification

_ Remove pasting out to worksheet (Output rows)

_ Not closing the large data Workbook

_ No Output unique Food product Column
( Remove pasting out to worksheet (Unique dictionary ( Food names ) ) )

_ Fixed Search Heading
( This is part of the start of hard coding to eliminating the code actually doing anything other than just referencing Range objects in a .Match ( , RangeObject , ) code line. The search heading is given a heading ( Margarinsäure ) at a row 3 and column a bit under 200, this does not test the actual heading range of 18 x almost 2000, but will allow a possible comparison later with a simple Range Object to Array conversion via_...
Array() = RangeObject.Value
_.... ( Arrays in Worksheet Functions being limited to 256 “columns” )
This last simplification causes as expected a reduction in the range references needed, but never mind that is made up in the next simplifications:

_ Removing any actual action left after the match condition is met ( This removes an Exit For so results in an increase in range references done. )

_ All but the main Loop is removed ( an inner Do While Inner Loop did things in the actual code which is not ( possibly !! ) so relevant to these academic digressions.

_ Remove all initial Array work: There is no longer any point of all the initial Array work involved with the data used in the sorting, so all is removed

_ Leave main data Workbook closed. There is no need to open at all the large data Workbook, the contribution to the main problem having been found not too relevant.

_ headings in same Workbook . Finally for one Code , the external Workbook with the actual heading range is done away with by copying the range to the same Workbook as the code and referencing that in the code.

Results / Conclusions:
Results:

USDAToDBArraysThirdWB – USDAToDBArraysThirdWBRaped
USDAToDBArraysThirdWB

00:21:33

Rows 340

rngRefs 153720

USDAToDBArraysThirdWBRaped

00:00:31	Rows 339	rngRefs 153413	No output row paste
00:00:24	Rows 338	rngRefs 153136	Not close data Workbook
00:00:24	Rows 339	rngRefs 153284	No Output Food Column
00:00:16	Rows 336	rngRefs 89045	Fixed Search Heading
00:00:46	Rows 225	rngRefs 366968	Remove all action after match
00:00:38	Rows 20445	rngRefs 368012	Remove all but outer loop
00:00:41	Rows 24661	rngRefs 443900	Remove all initial data Arrays
00:00:48	Rows 28207	rngRefs 507728	No open of data Workbook

USDAToDBArraysThirdWB18RngOffsets - USDAToDBArraysThirdWB18RngOffsetsRaped
USDAToDBArraysThirdWB18RngOffsets

00:00:23

Rows 336

rngRefs 152864

USDAToDBArraysThirdWB18RngOffsetsRaped

00:00:20	Rows 335	rngRefs 152255	No output row paste
00:00:18	Rows 337	rngRefs 152998	Not close data Workbook
00:00:19	Rows 338	rngRefs 153175	No Output Food Column
00:00:13	Rows 347	rngRefs 91043	Fixed Search Heading
00:00:35	Rows 225	rngRefs 367940	Remove all action after match
00:00:36	Rows 20361	rngRefs 366500	Remove all but outer loop
00:00:38	Rows 24602	rngRefs 442838	Remove all initial data Arrays
00:00:44	Rows 28307	rngRefs 509528	No open of data Workbook
00:00:44	Rows 28522	rngRefs 513408	Heading range in This workbook

Conclusions:

The last 4 - 5 lines in the tables show an expected slight improvement as other stuff is done away with, but not too significant compared with the order of magnitude improvement we are interested in.

Interesting that we require less references ( as expected ) when we fix the Search for heading to one early on, BUT do not get much further generally .. ?? – maybe the number of references in the Loop is less significant than the actual Loop number done ..

Anyway probably best break off now with a simplified code based on the last_....
Sub USDAToDBArraysThirdWB18RngOffsetsRaped()
_.... and use that in a new Thread to support this one....

_..........................

Continued in Thread: ....................
Thrashing VBA Memory Leaks. Memory Loop Problems. Uncleared Memory in .Match Range Object


http://www.excelforum.com/showthread...96#post4495796

Hi
I am just adding a short addition / update to this Thread, the subject of which continues to give me headaches.
In another thread I once again occurred the strange phenomena/ Bug in Match which is the subject of this Thread. In the particular problem I had , I tried by chance an Evaluate(“ Match ____ “) alternative, and the problem at first glance appeared to be gone in that case.. ( https://www.excelforum.com/developme...ml#post4671944 )

So I thought I should do a quick check on the codes and test data files used in this Thread.
I did some simple mods to one of the codes tested in this Thread , ( Sub USDAToDBArraysThirdWB18RngObjects() ) .
This was the basic idea , replacing the Application.Match lines with Evaluate(“ Match ____ “ )

Please Login or Register  to view this content.

I tested the modified , ( Sub USDAToDBArraysThirdWB18RngObjectsEval() ) , code just on one system used in the previous measurements in this Thread (Acer Aspire 4810TZG 32Bit 4 GB RAM, XL 2007 32Bit )
_ Application Evaluate code bombed out at about 172 rows after about 38 seconds
_ Worksheets Evaluate code bombed out at about 355 rows after about 42 seconds.

The code which I modified for the measurements, ( Sub USDAToDBArraysThirdWB18RngObjects() ) , is bombing out today on my computer at about [size=3][b]350 rows after 36 seconds, which is similar to previous measurements.

_....................

So, Poo ! , that was no good either…

_.. I am quite frustrated at the little “quirks” in Worksheet Functions that I am finding. I did have some improvements in speed sometimes, over my preferred simple VBA Array manipulation codes, for things like Match for a long LookUp range with minimal data matches. But some of these little hidden quirks and bugs I am finding when you do something a bit more demanding in terms of size are making inclined to live with occasional slower performance but at least have a better idea and control of what is going on….
Possibly it is just down to the routines behind the functions not being written quite as well as they should have been….
Oh well…
Never mind.
Alan

_......................
I updated the File with macros in it to include the new code:
“MacrosOnly.xlsm” https://app.box.com/s/w4lz07w54l8twtuj2qwc8jg277tpeph1

Data file to be filled in with sorted data:
“DBSept2016.xlsx” https://app.box.com/s/enugbnro26tfduoehme7cdjvgwpf2ct5

Raw data file ( irrational rational database file ):
“NutritionalValues2016.xlsx” https://app.box.com/s/6i52eis914evpabl52gh3fr9jz4bdlbx







https://fastexcel.wordpress.com/2011.../#comment-4175