GTO
02-02-2014, 03:25 AM
Hello all :-)
Reference:
I was dinking around (avoiding stuff I should have actually been doing, but hey, cleaning the stove doesn't sound nearly as interesting as writing an example) writing an example for: http://www.vbaexpress.com/forum/showthread.php?48813-selecting-specific-rows
Upon loading Excel 2010, Windows Task Manager Processes tab (hereafter: PT) shows Excel taking about 16,500KB. Upon loading the workbook, PT shows Excel at about 22,500KB. Run the form, and we're at 100,700 (because of loading a listbox). Load the second listbox, and we're at about 179,300KB.
Yes, I admit I wasn't really thinking about memory issues when I made the choice to dump about a million entries a piece into two listbox (After running into the glitch, I realized I was impressed that the listbox will now apparently take that many entries!).
Anyways, I really have just a couple of questions, in case it would not be excessive to explain, and/or you might know of an article or two that I did not find. (The only one I spotted reference listcount limitations was written around 1991 IIRC).
Okay, so after the first listbox is loaded (no problem with this), double-clicking an entry populates the second listbox with nearly the same number of elements. If I later use listbox.Clear, YIKES!
At first I thought it was hanging Excel, but then watching Excel in PT, I realized that upon executing .Clear, Excel jumps slightly to about 180,000KB, stays there for a number of seconds, then slowly (very slowly) dumps memory... If you wait it out, it will eventually return to about 132,000KB the first time. If you refill the second listbox, we return to about 179,000KB, and if we use .Clear again, same routine, this time though we stop dropping at just shy of 134,000KB.
When trying to resolve, I tried using a Do...Loop to remove item 0 - Oh Dear, even worse! I killed Excel after a couple of minutes of watching its memory fluxuate, but at least for as long as I tested, it never began dropping.
I tested looping backwards (.ListCount - 1) and the same. If I threw a DoEvents in there though, .ListCount was dropping, slowly.
I tested without the DoEvents, but with making the control hidden - back to just fluxuating with nothing evidently being accomplished.
I looped backwards, the control hidden, and with DoEvents. "Faster", but we're talking in minutes, not some portion of a second.
During those last several tests, Excel stays at about 180,000KB during the testing. I would note that for each test, I killed Excel and ran it and the workbook 'fresh' if-you-will.
While I had already tested this a bit earlier, I mention last as it works.
Me.lstEnd.List = Array(vbNullString)
DoEvents
Me.lstEnd.Clear
For some reason, replacing the massive .List with a one-element array is instantaneous.
Okay, I tried to list my steps so you would not need to take every one of them (file is attached in case you want to), here are my questions:
Well, the first one is of course - why does .Clear have such an 'issue' with clearing the larger number of items? Is it (behind the scenes) really removing items one-by-one?
Is there a limit, or, what is the limit for .ListCount in Excel 2010 (VBA7)? If known, what versions jumped, and from what to what?
Assuming I understand (stop giggling) what little I did find/read, in that a limit would be based upon a "pre-set" memory limit for a segment assigned to the listbox - would there be some way of fairly accurately calculating what the memory is that a listbox is taking at a given time?
Thank you so much,
Mark
Reference:
I was dinking around (avoiding stuff I should have actually been doing, but hey, cleaning the stove doesn't sound nearly as interesting as writing an example) writing an example for: http://www.vbaexpress.com/forum/showthread.php?48813-selecting-specific-rows
Upon loading Excel 2010, Windows Task Manager Processes tab (hereafter: PT) shows Excel taking about 16,500KB. Upon loading the workbook, PT shows Excel at about 22,500KB. Run the form, and we're at 100,700 (because of loading a listbox). Load the second listbox, and we're at about 179,300KB.
Yes, I admit I wasn't really thinking about memory issues when I made the choice to dump about a million entries a piece into two listbox (After running into the glitch, I realized I was impressed that the listbox will now apparently take that many entries!).
Anyways, I really have just a couple of questions, in case it would not be excessive to explain, and/or you might know of an article or two that I did not find. (The only one I spotted reference listcount limitations was written around 1991 IIRC).
Okay, so after the first listbox is loaded (no problem with this), double-clicking an entry populates the second listbox with nearly the same number of elements. If I later use listbox.Clear, YIKES!
At first I thought it was hanging Excel, but then watching Excel in PT, I realized that upon executing .Clear, Excel jumps slightly to about 180,000KB, stays there for a number of seconds, then slowly (very slowly) dumps memory... If you wait it out, it will eventually return to about 132,000KB the first time. If you refill the second listbox, we return to about 179,000KB, and if we use .Clear again, same routine, this time though we stop dropping at just shy of 134,000KB.
When trying to resolve, I tried using a Do...Loop to remove item 0 - Oh Dear, even worse! I killed Excel after a couple of minutes of watching its memory fluxuate, but at least for as long as I tested, it never began dropping.
I tested looping backwards (.ListCount - 1) and the same. If I threw a DoEvents in there though, .ListCount was dropping, slowly.
I tested without the DoEvents, but with making the control hidden - back to just fluxuating with nothing evidently being accomplished.
I looped backwards, the control hidden, and with DoEvents. "Faster", but we're talking in minutes, not some portion of a second.
During those last several tests, Excel stays at about 180,000KB during the testing. I would note that for each test, I killed Excel and ran it and the workbook 'fresh' if-you-will.
While I had already tested this a bit earlier, I mention last as it works.
Me.lstEnd.List = Array(vbNullString)
DoEvents
Me.lstEnd.Clear
For some reason, replacing the massive .List with a one-element array is instantaneous.
Okay, I tried to list my steps so you would not need to take every one of them (file is attached in case you want to), here are my questions:
Well, the first one is of course - why does .Clear have such an 'issue' with clearing the larger number of items? Is it (behind the scenes) really removing items one-by-one?
Is there a limit, or, what is the limit for .ListCount in Excel 2010 (VBA7)? If known, what versions jumped, and from what to what?
Assuming I understand (stop giggling) what little I did find/read, in that a limit would be based upon a "pre-set" memory limit for a segment assigned to the listbox - would there be some way of fairly accurately calculating what the memory is that a listbox is taking at a given time?
Thank you so much,
Mark