Are listviews slow?

  • I'm loading 25000 records (with an array) into a listview. Is it normal that it takes almost 5 seconds for the userform to load?


    I tried adding a progress bar with the idea: show progress bar in a separate userform while the listview is being populated.
    This doesn't work either because then the progress bar is shown after the listview is ready.


    If the data does take so long to load (I'm happy to show you the code) is there a good way to at least show a progress bar at the same time?


    Thanks for your help


    Massimo

  • Re: Are listviews slow?


    Show the Progress Userform modeless (so code can continue in the calling procedure) and use 'DoEvents' to allow whatever you are using to display the progress to update. It would be useful to see your code but 5000items/sec is not too bad considering. Why do you need so much? It's not really a good idea to overwhelm the user with so many choices.

  • Re: Are listviews slow?


    I have an order table which contains the customerID and the shopID, and a table customers and a table shop.
    I'm putting everything together in a listview. I would, of course, put a filter in to only show this month's orders when the form starts - with the option to show the rest based on the desired time span.
    Or shall I fetch only this month's orders and, when the user changes the timespan, fetch the data again? Wouldn't that take a long time?


    Progress bar:
    Still doesn't work as I wish.


    Userform with listview:

    Code
    Private Sub UserForm_Activate()DoEvents
    frmProgressBar.Show vbModeless
    GetOrderData
    End Sub


    Progressbar userform:


    Do you - or does anyone here - know of a code which would allow me to enter a max value for the progress bar.
    So if I know that I have 2000 orders, I could let the progress bar run accordingly? Or shall I ask this in a separate post?
    I shall reply a bit later (night time here).

  • Re: Are listviews slow?


    It won't work using Ontime. Your other processing will prevent that firing.


    You need to set the max for the progress bar in the GetOrders routine, and then increment the value every x records. You will also need to use DoEvents to allow the progress bar to update.


    Late here too. If you don't figure it, I'll post an example am...

  • Re: Are listviews slow?


    Hello - I've managed to speed the listview up by a factor of 10 :)


    Now about the Progressbar ...


    I would appreciate it you could help me with the code. I still can't make the progressbar work properly in the pop-up userform.


    Should it be opened in a separate userform or is it better to just have it on the same userform with listview?
    For example: Once the progress bar reaches 100% it, the progress bar is set to invisible. What do you think?


    This is the part of code which populates the listview with the array data.


    Thanks for your help


  • Re: Are listviews slow?


    Example of a progress bar.


    Click the button on sheet1. Change the interval where the progress updates to see the overhead the extra processing for the progress bar causes - depending on the number of test records, don't go lower than about an interval of 5 for 25000 records - that takes 12 secs on my machine but will get a lot longer if you use 1


    We posted at the same time but

    Quote

    Should it be opened in a separate userform or is it better to just have it on the same userform with listview


    It all depends. Having it on a separate userform has 1 complication. The progress form has to be shown Modelessly which you cannot do if the other userform is Modal. There are ways around that but it gets a little complicated. The advantage is you have a 'plug-in progress' which can be used in any procedure simply by declaring a new instance of class1 (that really should be renamed). You can include the progress bar in another project simply by copying Class1 and Userform2 to that project.

  • Re: Are listviews slow?


    After a lot of trying, I was able to make it work. The progress bar now opens in a separate userform.


    However, how can I make it work in the same userform?


    I've changed this

    Code
    Private f As frmOrders


    but that doesn't work.


    Would you mind having a look at it?


    I had to put the file on to my Google Drive as attachments here are limited to 200 KB.


    Thanks

  • Re: Are listviews slow?


    I've had a look and changed it slightly to use the separate userform - but I have the same problem as you. I cannot attach and I only have Download permissions to to file on google (I don't use Google Drive or MS SkyDrive or whatever it is).

  • Re: Are listviews slow?


    I've just had a look and it works great! I also appreciate it that you've added the following code to speed up the Listview:


    Code
    ' Prevents the ListView from updating while items are being added. Stops the
    ' flickering effect and speeds up the code as Windows does not redraw the control
    ' THIS WILL NOT WORK IN 64 BIT versions of Office. 64 Bit Windows is fine, just Office
    Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long


    I've added it here in case someone has problems with slow listviews.


    I do have one more question. As you know, the orders can be filtered by year. The default year is the current year.
    When I change the year from to 2011 - I only get records from 2012 till today. 2011 isn't shown.


    Does this have to do with the listview?

  • Re: Are listviews slow?


    Nothing to do with the ListView, it's your logic that was faulty.


    Code
    If Year(gArrOrders(i, 2)) >= intYearFrom And Month(gArrOrders(i, 2)) >= intMonthFrom And Year(gArrOrders(i, 2)) <= intYearTo And Month(gArrOrders(i, 2)) <= intMonthTo Then


    The comparison 'And Month(gArrOrders(i, 2)) <= intMonthTo' in isolation could return False when it should return True for the Year and Month combined... You cannot check Years and Months separately - you must check real dates.



    The progress bar has also been reworked. It is now a control on the userform as you initially asked. The corresponding class has also been revised drastically to simplify the code. Also, the progress bar is not displayed for periods shorter than 3 months - the Listview is populated quickly enough for short periods.


    Loading 1 months data takes about 1 second on my machine. Loading everything from Jan 2011 to date takes about 6 seconds.


    Those changes will be emailed to you as the workbook cannot be attached because of size.

  • Re: Are listviews slow?


    Small fix.


    If the End Date on the worksheet contains a time element then the comparison will not work. 31/Jan/16 00:01, for example, is greater than 31/Jan/16. While you probably don't have times it might be worthwhile to make a small change if this is ported to other workbooks.


    End date becomes the 1st of the next month and the comparison is changed to <.


    This is not an issue for the Startdate.


    Code
    ' Make a real date as before assuming it's the first of the month      ' Just Add 1 month 
          EndDate = DateAdd("m", 1, CDate("01-" & MonthName(intMonthTo) & "-" & CStr(intYearTo))) 
    
    
    
    
          ' And the comparison becomes
          If CDate(gArrOrders(i, 2)) >= StartDate And CDate(gArrOrders(i, 2)) < EndDate Then




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!