Slow VBA Code - Help

[Snypa]

Hello guys,

I have some event in my spreadsheet that were taking over 2 seconds to fire. After some diagnostic work I have found the VBA code that is causing the 2-3 second slow down (when I comment this out it is instant again).

The code allows my users to type a reference number into a box OR the name of the customer and will bring up the customers details based on either. I am doing this be checking if a string or number has been entered and running code depending on the answer. If a string is entered it checks the string on a table in another worksheet and changes the value of the cell to that customer's number.

Please can you help me and tell my why this code takes 2-3 seconds to run and how I could improve that?

Many thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if value has changed in cell D4 (Store search)
    ' If it has, tries to find that customer in the customer list
    
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        If Range("B5").Value <> vbNullString Then
            If IsNumeric(Range("B5").Value) Then
                Range("B5").Value = Range("B5").Value

            Else
                Dim strSearch As String
                Dim strOut As String
                Dim bFailed As Boolean

                strSearch = "*" & Range("B5") & "*"

                On Error Resume Next

                strOut = Application.WorksheetFunction.VLookup(strSearch, Worksheets("StoreLookup").Range("A2:B1173"), 2, False)

                If Err.Number <> 0 Then bFailed = True

                On Error GoTo 0

                    If Not bFailed Then

                        Range("B5").Value = strOut

                    Else

                        If Right(Range("B5").Value, 9) <> "not found" Then
                        Range("B5").Value = strSearch & " not found"

                    End If

                    End If
            End If
        End If
    End If
  
End Sub

 

[wideboydixon]

Your function is implicitly recursive. It a Worksheet_Change() event but then you're changing the contents of the cells in the macro which will fire the macro again. You might consider using:

Application.EnableEvents = False

At the start of the function and turn them back on (=True) at the end.

WBD

 

[Snypa]

Your function is implicitly recursive. It a Worksheet_Change() event but then you're changing the contents of the cells in the macro which will fire the macro again. You might consider using:

Application.EnableEvents = False

At the start of the function and turn them back on (=True) at the end.

WBD

Legend! Thank you very much!!!