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

[Doc.AElstein]

Thrashing VBA Memory Leaks. Memory Loop Problems. Uncleared Memory in .Match Range Object
VBA does not like using more than one Range Object in a code using code lines like = .Match( SearchedFor , RangeObject , ___ )

Hello

This Thread is related to this one
http://www.excelforum.com/excel-prog...de-please.html

I have isolated a weird problem that came up there in a large program. It was a bit masked by other considerations in that Thread so I am hoping to get a little bit further by considering the main aspect in isolation.

The Problem / Phenomena
It would appear that, - when a code line of this form, pseudo code_....

= .Match( SearchedFor , RangeObject , ___ )

_.. is used, VBA uses some memory , presumably for aspects of the Range Object. That is perhaps reasonable.
_...What is not so understandable, and is the crux of my problem, is that it has not proved possible to clear that memory, or possibly some “memory leakage” is occurring. Or something just plain wierd!!
_.. The end result is that using that code line in a Loop, ( Or simply using it at all more than once ) can eventually lead to Excel running out of memory, even in the case, ( as in the simplified demo codes given here ) when no other actions are taken.
_..The end result of all this was that in my actual requirement , ( discussed in detail in the other Thread ) , I was not able to get a code to run. ( On 32 Bit XL ) . But it was found that with XL 64 Bit on a 8 GB RAM Computer it was possible.

_.. In this thread I am trying to get a better understanding of the problem, as it is looking like I am going to have to live with it, and either upgrade my computer or look at other Methods. I am not interested in this thread so much in alternative methods , as that has been covered extensively in the other Thread.
( _ .. The problem does not occur if I use the same Range Object: I can use a single Range Object seemingly forever, time and time again. As soon as I introduce a second Range Object, then after many uses of the second Range Object, Excel will eventually run out of memory !!!!)

My 2 main Question / requests for help in this Thread are:
Either or both of these:
_1 ) Running of the codes given and telling me

_1 a) The results you get ( as the code pastes out in cells A1:D1 )
_1 b) What version of XL you have ( incl. 32 Bit or 64 Bit ) and your computer details ( mainly the RAM and if 32 or 64 Bit )

_ 2 ) If you have any general explanation and ideas on what is actually going on here or ways to get Excel to “clear” this memory and / or get further. Again I am not looking here for alternatives to this way of searching a row, as I am considering that elsewhere. I am trying to understand these strange limitations in this way of doing it


Thanks
Alan
_....

The codes, Brief description.
Code1: Sub LeeksRng__OffsetsRaped()

The code stems from a large program part of which requires searching along up to 18 long ( approx 1700 column ) Header Rows in turn to match a given Nutrition value given for a product. ( 18 header rows allow for 18 possible spellings ). So I loop up to 18 times hoping to find a match in at least one Header Row
HeaderRows.jpg
http://imgur.com/VknkfvE
My problem arises when I have more than one Range Object, ( in the final code I use 18, one for each row ) that is two say I do a = .Match( SearchedFor , RangeObject , ___ ) for more than one specific Range Object.
The test code I have prepared for this Thread allows you to pick from 2 Range Objects.
It is fine if you do select the 2 and run the code once for me and give me the results for that run.
The Code is _...
Sub LeeksRng__OffsetsRaped()
_... in the uploaded File, ( In that File is also the required header range, rnglongSrcRow_ , which the code refers to )
Also given here, along with some of my results:
http://www.excelforum.com/showthread...=1#post4495737


Code2: Sub TwoRangeObjects()
_...This further demonstrates the strange problem. Two independent loops have a single = .Match( SearchedFor , RangeObject , ___ ) code line in each one.
The first Loop will pretty well loop forever. But as soon as you end that and start the next, the strange problem crops up and the 2nd Loop does not get very far..
_...Also the number of loops the 2nd Loop manages is fairly independent of how many times the first loop loops !!!weird !
_...Once again I would be grateful if you could run the code for me and give me the results it pastes out into range A1:D1, along with the details of your computer and Excel version
Sub TwoRangeObjects() is also in the uploaded File and also here with some of my results:
http://www.excelforum.com/showthread...=1#post4495782
( _ .. Note1 if you were to edit the code so that the same Range Object is used in the second Loop, then the code will go to completion and not error !!!!! )


Note2: If you run either of the codes for me ( or both if you have the time ) you will likely need to restart Excel after each run as Excel will have “used up” all of its memory after a run !!






MacrosOnly.xlsm
https://app.box.com/s/dvxkvwvebq4npqzrlxy9um86uwg4mr82

[Doc.AElstein]

Hi ( Bump )
I am still interested in any help here.

I am still trying to understand this strange phenomena whereby VBA uses up Excels memory to the point that a code crashes after it starts referencing a second Range Object in a code line of the form

= .Match ( , RangeObject , )

Thanks
Alan



P.s. In addition to the simple alternative of a simple VBA Array type alternative code, I have also just checked replacing the

= .Match ( SearchedFor , RangeObject , )

With a similarly working

= RangeObject.Find (SearchedFor , , , ).Column

http://www.excelforum.com/showthread...32#post4497131
http://www.excelforum.com/showthread...32#post4497132

Interestingly this alternative does not appear to be have the strange memory problem.

Unfortunately such an alternative has an increase in processing time in typical codes compared with the = .Match ( , RangeObject , ) , _...
http://www.eileenslounge.com/viewtop...176056#p175343

_.... , but strange and interesting that the memory problems are no longer occurring.

[Doc.AElstein]

Hi
The problem discussed in this Thread has irritated me for some time now. I have a feeling I will get no real solution.
It seems Microsoft would generally never be bothered to fix any of these Bugs, especially as , in this case, the faster , and bigger memory capacities available as time goes on will mean that this particular problem will likely never be noticed.
Briefly, again, what I am talking about here is a weird Bug that “eats” memory up in a loop to the point that Excel, and sometimes, your whole computer crashes. Results form this Thread , and several related Threads with similar problems suggest that with the newest / biggest computer you simply would not notice the problem, but I highly expect it is always there . The problem can occur as soon as you use more than one Range object in any Excel match, or , as we see in this Thread, if you change anything in a single Range object in an Excel match…

My personal conclusion is that I am more and more put off of relying on worksheet function for what I am doing. This is a shame as I had been “converted” to sometimes using worksheet functions in preference to my preferred VBA Arrays methods after I saw some speed advantages. In fact in the code examples in which I first experienced these weird bugs, the improved speed for the non looping case suggested it was the only practical way to do some things I was attempting to do.
( A recent foray into doing things with databases instead of with big Excel Files was not encouraging either… )

So I am ( was ) trying a last attempt at a Workaround here. ..

So, the conclusions so far were that , any attempt at using more than one Range object in a .Match in a code involving looping meant that eventually the loop containing a second ( or more ) Range object will eventually crash Excel and / or your computer.

The attempted workaround here will be to confine to using a single Range object, but to paste into that consecutively the values from the other ranges, - those range being those that would have been used in further .Match code lines using those different range objects.
Even if this works it will have consequences in increased in speed that might mean the solution has lost its improvement over a simple Array code.
If it does not work , ( Edit: it didn’t ) then the consequences of that suggest to me personally to give up totally with Excel Worksheet functions for anything demanding, regardless of how nice , simple and attractive they may initially appear to be ….

So I did a modified code, based on the codes discussed in the first post of this Thread.
Initially it looked promising, in terms of working, but I did not fell like waiting a month for a demo code to finish – It was taking ages, as , as expected, moving values in and out of a worksheet really slammed on the brakes. ( It is well known that interacting with a spreadsheet slows things down a lot). The existing code frequently used a different range, which means in the code done for this new idea, I was pasting in values into a spreadsheet in place of every time I used a different Range object in the previous codes.

So, the basic sorting logic need to be changed to suit the workaround.
Previously a LookUp value, SrchVal, was searched for in a long range row.
__If not found it was looked for in the next row down.
Etc… etc..
Once that search was finished, the next LookUp value was searched for. So for every search value I go through all ranges, pseudo like:
SrchVal 1 : rng1 , rng2 , rng3, ……. rng18
SrchVal 2 : rng1 , rng2 , rng3, ……. rng18
SrchVal 3 : rng1 , rng2 , rng3, ……. rng18
Etc…
The above logic needs to be changed slightly so as to go through all search values for each range and then move on to the next range, pseudo like:
SrchVal 1 : rng1 , SrchVal 2 : rng1 , SrchVal 3 : rng1 , ……. SrchVal 100000 : rng1
SrchVal 1 : rng2 , SrchVal 2 : rng2 , SrchVal 3 : rng2 , ……. SrchVal 100000 : rng2
SrchVal 1 : rng3 , SrchVal 2 : rng3 , SrchVal 3 : rng3 , ……. SrchVal 100000 : rng3
Etc…

That makes it a bit difficult to make a good comparison in this Thread, but never mind, - I will check again in some other Threads as well. ( http://www.eileenslounge.com/viewtop...209408#p209408 )
It is all a bit messy.
Trying to think ahead for the practical usage, I figure a logic like this:

Make an Array of all the stuff that I am looking for.
I go through all the stuff , one after the other.
If it is found then I ‘ do something which in a real code would be something that I want to do. ( For this demo code I will put in an Array for output, the searched for heading and the position of the match in the main headings as given by .Match )
But in addition, I take that thing out of the Array.
That all constitutes a search for one heading row. ( In my application I would have sorted some of the nutrients associated in a single Food product from a data worksheet, into the correct order. I have probably not sorted all the nutrients into their correct order as I have about 18 headings rows to go through, - that is necessary as my data file may have incorrectly spelt, or rather or slightly differently spelt, or in different language headings. So opefully at least one of the headings from the 18 rows matches that in my data file. Hence I use different range objects in each heading row search, and that is where the Match Bug rears its putrid head )
So I will then repeat the above a few times ( 2 – 18 can be set in the demo code ) , but before doing so, change the values in the , now fixed , heading range ..


I need two things:
Single Run Quick
_a) The above for 18 row range objects , would probably constitute a single real life run. I want that itself not to take too long, preferably something coming close to instant, - more than a few seconds then I have lost all the speed advantages of this code over simple VBA Array code. ( http://www.eileenslounge.com/viewtop...=22512#p204787 ). I need this to get quickly in semi real time at a single products info if I am using that product.

Multiple runs not to exhibit the Bug
_ b) For filling in a list of several thousand of these things, to build up a sorted library for later reference, the time taken is not too important, but I do not want it to crash due to a Bug…

( I don’t really want a Bug in _a) either, but as the problem clears on restarting Excel, I probably would never notice the problem if I only occasionally used the code for a single row entry. )


Here we go:
I have a code , Sub MakeArrayRngToFind() which makes a single row at row 150 from the main first 18 row headings.
It loops through each heading column, and puts the heading value of a random one of those 18 rows into the row at that column.
An Array of all values that then need to be found in the main headings is made. ( This means that the columns are in line, but in the practice they probably would not be, hence the need to use something like .Match to find the column where they are. )

' Code 8 June 2017
Sub SingleRangeChangingValues2() '
( https://www.excelforum.com/developme...ml#post4673263 )
This is similar, and made from, the demo codes explained already in this Thread already.

Full code and description Here:
https://www.excelforum.com/developme...ml#post4672924
https://www.excelforum.com/developme...ml#post4673263


_.......
Well,, I tried all that . Code here: https://www.excelforum.com/developme...ml#post4673263
For a single main outer loop, the time is around 2 – 3 seconds, which is OK. It has not noticeably worsened the performance for codes of this Match sort ( http://www.eileenslounge.com/viewtop...=22512#p204787 )

But… start increasing that main outer loop and Bollox! , it bombs out again! Same problem . Poo

Well, there you have it.
Any change in a Range object, even a value in it, and you can forget about doing anything big with Excel .Match.
Also , unless you use exactly the same Range object in it, every time you use a .Match, then something bad is happening in your computer as a result, …you may just not notice.
I personally feel uneasy about using Match for anything following my experiences with it.

Just now I hate Excel , ( or the programmers behind the Worksheet Function routines who messed up and did not do it right )

I have not done too many measurements as I am getting fed up with this problem.
The code here_....
https://www.excelforum.com/developme...ml#post4673722
_..... is bombing out, for the equivalent of 18 ranges ( so 18 uses of the same range object but with different values in it ) , at anything around
300000 Range references,
200 Loops ( I need about 8000 )
As noted the time taken for a single main run is 2-3 seconds..

_....

Once again I tried the Range.Find equivalent.

This is a simple code change as ever.. You change this _.....

Please Login or Register  to view this content.

_... to this

Please Login or Register  to view this content.

Full Code here: ….
https://www.excelforum.com/developme...ml#post4673753
As expected the code does not appear to cause the strange phenomena. It takes a long while but goes too completion for an extremely large number of loops and consequent range referencing without any problems

What is interesting is that the speed of measured as about 4 seconds is not too bad either.
Of course that speed improvement is not necessary anything to do with the value idea..It may be just the For Range First idea. So I did a quick code to check that..
https://www.excelforum.com/developme...ml#post4673753
_... The result was that the code was very fast at about 2 seconds

Oh well, maybe in a long winded round about way, I have a good workaround for a quick one off value search. If I do use a worksheet function then I will go for Find – I appear to have just about screwed out the best I can out of it. ( I confirmed this with some other measurements in previous Threads: http://www.eileenslounge.com/viewtop...=22512#p209408

Having said that, in another Thread, I stumbled on a small bug/ quirk with Find also ( https://www.excelforum.com/developme...ml#post4671061 ) . Usually that oddity is hidden when you use Range.Find followed by Range.FindNext. – Maybe the programmers put a bucket under .Find to catch the leak from it and used what spilled out to make Range.FindNext work….

I think I can probably mark this thread as Solved
The solution is:
Don’t use Excel Match for anything big or important. – It has a bad Bug in it..

Alan
…
_.____________

Uploaded File is updated with the new codes.

[MarvinP]

Hey Alan,

When you have multiple ranges in VBA you need to look at them as "Areas" I believe. You use "Areas(Index)" and loop through them. I stumbled on Areas while dealing with Union of ranges one day.

Hope this helps.

[Doc.AElstein]

Hi Marvin,

When you have multiple ranges in VBA you need to look at them as "Areas" .. use "Areas(Index)" .. loop through them. I stumbled on Areas while dealing with Union of ranges one day. .

I know all about Areas, ... but I had not thought of checking doing it that way to to see if that overcomes my problem.
Great idea, thanks, I'll check that later and report back
Thanks Marvin

Alan

[Doc.AElstein]

When you have multiple ranges in VBA you need to look at them as "Areas….

.. I'll check that later and report back..

Hi,
I tried out modifying a lot of my codes to have all my ranges as Areas of the same Range object, but it had no effect on the problem/ bug.
But it was an important thing to check.

I expect the problem has to do with the range that Match uses. Once it uses in any code more than one Range object, ( or if it uses one Range object and then something in that range object changes), then the leakage starts.
Thanks again
Alan

P.S I noticed something interesting along the way: I cannot use Union for my ranges as they are all in line,- It appears that then Union joins them up into one single Areas range object , so I had to do it like this to be sure I got the Areas I wanted..
Set rnglongSrcRows = wsDBlx.Range("A1:BOX1,A2:BOX2")
If I use like this instead,
Set rnglongSrcRows = Application.Union(wsDBlx.Range("A1:BOX1"), wsDBlx.Range("A2:BOX2"))
then I get just a single Areas Range object of
wsDBlx.Range("A1:BOX2")