PDA

View Full Version : ListBox.ListCount limit in Excel 2010, and/or previous years...



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

Paul_Hossler
02-02-2014, 08:57 AM
1. Who knows what Microsoft did?

2. I would take a wild guess that your assumption is correct: .Clear walks the list one at a time

3. I did Google a reference that the assignment of a 'null' array and then the .Clear works quickly (like your lstEnd clear)




Me.lstStart.Enabled = True
Me.lstStart.ListIndex = 0

Me.lstStart.List = Array(vbNullString)
DoEvents
Me.lstStart.Clear



If you use that on .lstStart, how does memory usage look afterwards?

Paul

GTO
02-02-2014, 05:10 PM
3. I did Google a reference that the assignment of a 'null' array and then the .Clear works quickly (like your lstEnd clear)

Paul

Thanks for looking Paul. Sigh... I never did spot a similar thread, you must be picking better keywords. Of course nice to know how to solve even if I had to be the umpteenth millionth one to "discover":115: through trial and error.



If you use that on .lstStart, how does memory usage look afterwards?


Similar affected as dumping just the second box. If I dump both boxes, Excel returns to about 2500KB over what it started at (with the wb loaded). A few more hundred KB disappear from use when the form is unloaded.

snb
02-03-2014, 01:09 AM
It's a curious thing about comboboxes/listboxes:


They behave like collections if
- you populate them using .additem (including the option to indicate after/before which item the new item has to be added)
- you delete an item using.removeitem(j)
- you remove all items using .clear
- you use the property .selected (if multiselect=true)

In all these cases the method progress is very slowly (I assume because each item will be treated as in indexed item and the index has to been changed simultaneously)

They behave like arrays if
- you populate them using .list = array(..)
- you replace it's content using .list=array(..)
- you delete all items using .list= array(...)
- you assign the property .list to an array: sn=combobox1.list