Extremely Slow Listbox Build... Need Help to Speed Up 2

[PatrioticTX]

I am building a multi-column MS Forms 2.0 listbox from a 2D array. The array has 8 columns and approximately 1300 rows. It takes nearly 30 seconds to add the array items to the listbox. The build section of the code is listed below. The listbox must be requeried when the user reopens the form (appox every 4 minutes) due to multi-user access, so a delay of ~30 seconds is unacceptable (about one hour of downtime for a full day)!

        For iCurRec = 0 To UBound(RSArray, 2)
            
            Me!lstWUCSubSystems.AddItem
            Me!lstWUCSubSystems.List(iCurRec, 0) = RSArray(0, iCurRec)
            Me!lstWUCSubSystems.List(iCurRec, 1) = RSArray(1, iCurRec)
            Me!lstWUCSubSystems.List(iCurRec, 3) = RSArray(3, iCurRec)
            
            If RSArray(8, iCurRec) <> 27 Then
                Me!lstWUCSubSystems.List(iCurRec, 2) = RSArray(2, iCurRec)
    
                If LenB(RSArray(4, iCurRec)) > 0 Then
                    Me!lstWUCSubSystems.List(iCurRec, 4) = RSArray(4, iCurRec)
                Else
                    Me!lstWUCSubSystems.List(iCurRec, 4) = vbNullString
                End If
            
                Me!lstWUCSubSystems.List(iCurRec, 5) = RSArray(5, iCurRec)
                Me!lstWUCSubSystems.List(iCurRec, 6) = RSArray(6, iCurRec)
                Me!lstWUCSubSystems.List(iCurRec, 7) = RSArray(7, iCurRec)
            End If
    
        Next iCurRec

Thank you!!!

 

[JohnYingling]

I am a "regular" VB guy. Does the listbox have a sorted property set to true? If so it will help to ADD the items in reverse sequence.

Also, try making the box invisble while loading. Possiibly it is trying to refresh after each add.

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript

 

[PatrioticTX]

Sorry about the formatting in the last post... here's another try...

For iCurRec = 0 To UBound(RSArray, 2)
                                     
  Me!lstWUCSubSystems.AddItem
  Me!lstWUCSubSystems.List(iCurRec, 0) = _
      RSArray(0, iCurRec)
  Me!lstWUCSubSystems.List(iCurRec, 1) = _
      RSArray(1, iCurRec)
  Me!lstWUCSubSystems.List(iCurRec, 3) = _
      RSArray(3, iCurRec)
                                  
  If RSArray(8, iCurRec) <> 27 Then
    Me!lstWUCSubSystems.List(iCurRec, 2) =_
       RSArray(2, iCurRec)
                             
    If LenB(RSArray(4, iCurRec)) > 0 Then
        Me!lstWUCSubSystems.List(iCurRec, 4) = _
           RSArray(4, iCurRec)
    Else
        Me!lstWUCSubSystems.List(iCurRec, 4) = _
           vbNullString
    End If
                                     
    Me!lstWUCSubSystems.List(iCurRec, 5) = _
      RSArray(5, iCurRec)
    Me!lstWUCSubSystems.List(iCurRec, 6) = _
      RSArray(6, iCurRec)
    Me!lstWUCSubSystems.List(iCurRec, 7) = _
      RSArray(7, iCurRec)
  End If
                             
Next iCurRec

 

[PatrioticTX]

Thank you for the fast reply, John. The MS Forms 2.0 listbox does not have a sorted property. I have tried to make the listbox invisible during the build, but it takes roughly the same time (give or take 25ms). I have not been successful getting the "regular" VB listbox to do multiple columns; otherwise, I would have used it. I have spent about 10 hours today trying to get the speed down, but nothing I do seems to have a great impact.

 

[JohnYingling]

I don't know about the "reversal" of Row, Col between the two e.g.
Me!lstWUCSubSystems.List(iCurRec, 0) = _
RSArray(0, iCurRec)
but at least take that Me!lstWUCSubSystems and put it in a With statement:

With Me!lstWUCSubSystems
    .List(iCurRec, 0 = _
           RSArray(0 iCurRec)
.......
End With

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript

 

[strongm]

The MS Forms listbox has the ability to be assigned an array in it's entirety (rather than element by element), a feature sadly lacking in the normal VB listbox. And it is pretty fast at doing it.

So you might want to consider altering your approach slightly. For example, if you could populate RSArray in such away that you could do away with the IF statements (e.g. modify the underlying query, if that is where it coming from), then you can just go:

Me!lstWUCSubsystems.List=RSArray

 

[LuckyLuke]

With a ListView control (created by CreateWindowEx, so I don't know how fast it is from the common controls) I added the array in 230 milliseconds, while it took my computer 33 seconds to fill the MS Forms 2.0 Listbox... So I guess you are simply trying to work with the wrong control...

 

[PatrioticTX]

I did what strongm suggested and the result was impressive. I increased the record count to about 5000 and tried it using my old code - it took about 90 seconds to load. Then I tried it using the

.List

property and it loaded in under 2 seconds. I had to transpose the array before assigning it because, as JohnYingling pointed out, the col and row were reversed (I'm not sure why MS did this). For future use, I have listed my transpose function below. Thank you again for your help!

Private Function TransposeMatrix(ByRef v2DMatrix As Variant)_
   As Variant

    Dim lRows As Long
    Dim lCols As Long
    Dim lSwapRow As Long
    Dim lSwapCol As Long

    'Set row and column counts
    lRows = UBound(v2DMatrix)
    lCols = UBound(v2DMatrix, 2)
    
    ReDim vTrans(lCols, lRows) As Variant

    'Transpose the matrix
    For lSwapRow = 0 To lRows
        For lSwapCol = 0 To lCols
            vTrans(lSwapCol, lSwapRow) = _
                v2DMatrix(lSwapRow, lSwapCol)
        Next
    Next
    
    TransposeMatrix = vTrans

End Function