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 , ___ )
This Thread is related to this one
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
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
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 _...
_... 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:
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:
( _ .. 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 !!