delay in response and hang in excel vba processing

Question

Hi every one I've written a code in Excel VBA it works , the code searches a worksheet and list the result in a list box specifying which columns to list but it takes very long time in responding to any change in search so I need to know the bug here is the code

Private Sub TbSearch_Change()
    Dim i As Long
    Dim MySheet As Worksheet
    With MySheet
        On Error Resume Next
        Set edRng = .Range("A4", .Range("A4").End(xlDown))
    End With
    'Set MySheet = Main
    'On Error Resume Next
    'Me.TbSearch.Text = StrConv(Me.TbSearch.Text, vbProperCase)

    Me.LBResult.Clear

    If CBRoads <> "" Then
        For i = 4 To Application.WorksheetFunction.CountA(edRng)
            For x = 1 To 5
                a = Len(Me.TbSearch.Text)
                If Left(MySheet.Cells(i, x).Value, a) = Me.TbSearch.Text And Me.TbSearch.Text <> "" Then

                    Me.LBResult.AddItem MySheet.Cells(i, 2).Value

                    For Each edCell In Me.LBResult.List
      
                        Me.LBResult.List(LBResult.ListCount - 1, 1) = MySheet.Cells(i, 3).Value
                        Me.LBResult.List(LBResult.ListCount - 1, 2) = MySheet.Cells(i, 8).Value
                        Me.LBResult.List(LBResult.ListCount - 1, 3) = MySheet.Cells(i, 9).Value
                        Me.LBResult.List(LBResult.ListCount - 1, 4) = MySheet.Cells(i, 10).Value
                        Me.LBResult.List(LBResult.ListCount - 1, 4) = MySheet.Cells(i, 12).Value
                    Next edCell

                End If
            Next x
        Next i
    Else
        MsgBox "choose first"
        Me.CBRoads.SetFocus
    End If
End Sub

Perhaps better posted on the Code review Stack since you say it works. — Solar Mike
– Solar Mike, Commented Mar 9, 2022 at 10:17

TinMan · Accepted Answer · 2022-03-09 10:34:47Z

1

There is no reason to iterate over each item in Me.LBResult.List. It is killing your performance.

 For Each edCell In Me.LBResult.List

answered Mar 9, 2022 at 10:34

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

delay in response and hang in excel vba processing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related