I have been working on updating code for a customer form with a list box control storing a long list of files in various folders. There are filters on this form that can limit the files available to choose from. During my coding I noticed some performance problems. The list was taking a long time to populate and the screen was flashing while it was updating. Even turning off screen updating ( using Application.Echo False ) did not really speed up the process much.

In analyzing the code, I noted that the list was being built programmatically with code. Each row of the list had 6 columns with various information about the file. The code to add an item to the list was using the Listbox.AddItem method. The Row Source Type was set to Value List.

The code looped through each file and in each loop would call Listbox.AddItem for each file element. It turned out this was slow for a large number of files. And not that large of a number. Let’s say 50 – 75 was noticeably slow, like a second or 2 to load.

I changed the code and built each line of the list box as a string separated by semi-colons. Then after the entire string was built after leaving the loop, I set the Listbox.RowSource property to the string. This worked really fast for the 50 – 75 files.

However, I then had another set of files that was about 200 files. In this case, the string was too long to go into the RowSource property. I would get an error message that the RowSource property could not be set because of an overflow.

After some additional research I learned that limitation can be worked around with a call back function for the RowSource property OR by setting the Row Source Type to table/query and setting the RowSource to that table or query.

In my case, I was building a temporary table of the files anyway, so I just wrote a query to display the data the way I wanted it and then set the RowSource property to that query. This also worked very quickly, did not have any limits based on the file, and required a lot less code. Woo-hoo, big win!