UDF causing Lag

Question

Thanks to the answer from Noodles I know that my UDF is the main cause of lag in my excel workbook. Can this UDF be re-written to not cause the lag? I am using this UDF with an excel formula (example =IF(OR(ISNUMBER($DH2),$DH2>" "),LookUpConcat($B2,Usage!$AM$2:$AM$5000,Usage!$AS$2:$AS$5000," ")," ")). Maybe the formula needs changed. The formula is pulled down through many cells and is adjusted to work in 2 different columns in the workbook. I am not IT so Noodles answer to my last lag question was not totally understood by me, but gave me enough information to know that this is the macro causing the most lag issue.

   Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                       Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                       Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)



     Dim X As Long, CellVal As String, ReturnVal As String, Result As String

     If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
    (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
   LookUpConcat = CVErr(xlErrRef)
 Else
  If Not MatchCase Then SearchString = UCase(SearchString)
   For X = 1 To SearchRange.Count
     If MatchCase Then
    CellVal = SearchRange(X).Value
    Else
    CellVal = UCase(SearchRange(X).Value)
      End If
     ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
       If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo     Continue
       Result = Result & Delimiter & ReturnVal
     ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
       If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo     Continue
    Result = Result & Delimiter & ReturnVal
  End If
Continue:
Next
   LookUpConcat = Mid(Result, Len(Delimiter) + 1)
 End If

End Function

Thank you @ Mark Fitzgerald your question got me to rethinking my program. I made an adjustment and was able to cut the search range to 1000 which helped. Also found other formulas that were searching and was able to adjust them. PROBLEM SOLVED. Would like to give you the credit for answering it because you were right in the search range being the issue. — user3554998
That's good news. I toyed with using Range.Find and Range.FindNext but it seems to break the "UDF shall not change any workbook/sheet properties" rule. You may be able to do something with VLOOKUP for each occurence of SearchString by modifying you search range on each pass through a loop. — Mark Fitzgerald

user3554998 user3554998 · Accepted Answer · 2014-12-09T20:05:49

The answer to this is, Thanks to Mark Fitzgerald, nothing is wrong with the UDF the lag is caused by the amount of search range. I adjusted the search range and that took care of the lag.

UDF causing Lag

1 Answers